c# - 我想在保存格式更改时从 Excel 上传数据并存储在 SQL Server 中

问题描述

我从 Excel 导入数据并保存到临时表中，之后它将保存在主表中，临时表将truncate.all正确保存数据，仅以日期格式出现问题，因为在 Excel 中它是DD/MM/YYYY但在保存时保存MM/DD/YYYY在 SQL 中服务器。
如何在 SQL Server 中节省MM/DD/YYYY时间？DD/MM/YYYY
我尝试了许多解决方案，但没有得到任何积极的结果。甚至尝试在查询中进行转换，但也反映了相同的结果。
protected void Upload(object sender, EventArgs e)
{
    try
    {
        string FilePath = Convert.ToString(Session["FilePath"]);

        string conString = string.Empty;
        string Filename = Session["Filename"].ToString();

        //string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
        string extension = Filename.Substring(Filename.LastIndexOf('.'));

        switch (extension)
        {
            case ".xls": //Excel 97-03
                conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;
            case ".xlsx": //Excel 07 or higher
                conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
                break;

        }

        conString = string.Format(conString, FilePath);

        using (OleDbConnection excel_con = new OleDbConnection(conString))
        {
            excel_con.Open();
            //string sheet1 = "Index Of Charge";

            //string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[1]["TABLE_NAME"].ToString();
            DataTable dtExcelData = new DataTable();

            //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
            dtExcelData.Columns.AddRange(new DataColumn[9] { 
            new DataColumn("SNo", typeof(int)),
            new DataColumn("SRN", typeof(string)),
            new DataColumn("Charge Id",typeof(string)),
            new DataColumn("Charge Holder Name",typeof(string)),
            new DataColumn("Date of Creation",typeof(string)),
            new DataColumn("Date of Modification",typeof(string)), 
            new DataColumn("Date of Satisfaction",typeof(string)), 
            new DataColumn("Amount",typeof(decimal)), 
            new DataColumn("Address",typeof(string))});
            //new DataColumn("CreatedDate",typeof(string)),
            //new DataColumn("ModifiedDate",typeof(string)),
            //new DataColumn("CreatedBy",typeof(int))});
            string sheet1 = DdlSheetNames.SelectedItem.Text;

            using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
            {
                oda.Fill(dtExcelData);
            }

            excel_con.Close();

            string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

            using (SqlConnection con = new SqlConnection(consString))
            {
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                {
                    //Set the database table name
                    sqlBulkCopy.DestinationTableName = "dbo.INDEXOFCHARGE";

                    //[OPTIONAL]: Map the Excel columns with that of the database table
                    sqlBulkCopy.ColumnMappings.Add("SNo", "SNo");
                    sqlBulkCopy.ColumnMappings.Add("SRN", "SRN");
                    sqlBulkCopy.ColumnMappings.Add("Charge Id", "ChargeId");

                    sqlBulkCopy.ColumnMappings.Add("Charge Holder Name", "ChargeHolderName");
                    sqlBulkCopy.ColumnMappings.Add("Date of Creation", "DateofCreation");

                    sqlBulkCopy.ColumnMappings.Add("Date of Modification", "DateofModification");
                    sqlBulkCopy.ColumnMappings.Add("Date of Satisfaction", "DateofSatisfaction");
                    sqlBulkCopy.ColumnMappings.Add("Amount", "Amount");
                    sqlBulkCopy.ColumnMappings.Add("Address", "Address");

                    con.Open();
                    sqlBulkCopy.WriteToServer(dtExcelData);
                    con.Close();
                }
            }
        }

        //update Fields
        Myconn.Open();
        SqlCommand cmd = new SqlCommand("dbo.SP_InsetCreatedby", Myconn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.ExecuteNonQuery();

        SqlCommand cmd1 = new SqlCommand("dbo.UpdateDateFormat", Myconn);
        cmd1.CommandType = CommandType.StoredProcedure;
        cmd1.ExecuteNonQuery();
        Myconn.Close();

        //update complete

        //show Details

        ShowDetails_Click(sender, e);
        //show details complete
    }
    catch (Exception ex)
    {
        string script = "<script>alert('" + ex.Message + "');</script>";
    }
    finally
    {
    }

    //show Details complete
}

protected void Button1_Click(object sender, EventArgs e)
{
    if (FileUpload1.FileName != "")
    {
        string FolderName = "BasicDetails";
        string Filename = FileUpload1.FileName;

        Session["Filename"] = FileUpload1.FileName;

        string RightNm = Filename.Substring(Filename.LastIndexOf('.') + 1);
        int FileLen = RightNm.Length;



        string LeftNm = Filename.Substring(0, Convert.ToInt32(Filename.Length) - (FileLen + 1)).ToString();
        Filename = LeftNm + "-" + DateTime.Now.ToString("ddmmyyyy") + "." + RightNm;

        bool folderExists = Directory.Exists(Server.MapPath(FolderName));

        if (!folderExists)
            Directory.CreateDirectory(Server.MapPath(FolderName));

        string path = Server.MapPath(FolderName) + "\\" + Filename;
        Session["FilePath"] = path;
        FileUpload1.PostedFile.SaveAs(path);

        string conString = string.Empty;
        OleDbConnection excelConnection;
        string extension = Path.GetExtension(FileUpload1.PostedFile.FileName);

        switch (extension)
        {
            case ".xls": //Excel 97-03
                conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;
            case ".xlsx": //Excel 07 or higher
                conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
                break;

        }

        conString = string.Format(conString, path);

        excelConnection = new OleDbConnection(conString);
        excelConnection.Open();
        DataTable Sheets = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        int rowcnt = 0;
        DdlSheetNames.Items.Add("-Select-");

        foreach (DataRow dr in Sheets.Rows)
        {
            string sht = dr[2].ToString().Replace("'", "");
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter("select * from [" + sht + "]", excelConnection);

            rowcnt = rowcnt + 1;
            DdlSheetNames.Items.Add(sht);
        }

        excelConnection.Close();
        //
    }
}
标签： c#sql-serverexcel
c# - 我想在保存格式更改时从 Excel 上传数据并存储在 SQL Server 中

问题描述

解决方案

推荐阅读
