C# 上传 Excel 表格并将数据导入 SQL Server 数据库

Question

提问by Hemantsom

I am developing this simple application to upload an Excel file (.xlsx) and import the data present in that Excel worksheet into a SQL Server Express database in .NET

我正在开发这个简单的应用程序来上传 Excel 文件 ( .xlsx) 并将该 Excel 工作表中的数据导入到 .NET 中的 SQL Server Express 数据库中

I'm using the following code on click of the import button after browsing and selecting the file to do it.

在浏览并选择文件后，我在单击导入按钮时使用以下代码。

protected void Button1_Click(object sender, EventArgs e)
{
        String strConnection = "Data Source=.\SQLEXPRESS;AttachDbFilename='C:\Users\Hemant\documents\visual studio 2010\Projects\CRMdata\CRMdata\App_Data\Database1.mdf';Integrated Security=True;User Instance=True";
        //file upload path
        string path = FileUpload1.PostedFile.FileName;
        //string path="C:\ Users\ Hemant\Documents\example.xlsx";
        //Create connection string to Excel work book
        string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
        //Create Connection to Excel work book
        OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
        //Create OleDbCommand to fetch data from Excel
        OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Designation] from [Sheet1$]", excelConnection);
        excelConnection.Open();
        OleDbDataReader dReader;
        dReader = cmd.ExecuteReader();
        SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
        //Give your Destination table name
        sqlBulk.DestinationTableName = "Excel_table";
        sqlBulk.WriteToServer(dReader);
        excelConnection.Close();
    }

But the code doesn't run when I use

但是当我使用时代码没有运行

string path = FileUpload1.PostedFile.FileName;`

and even

乃至

string path="C:\ Users\ Hemant\Documents\example.xlsx";`

The dReaderis unable to take the path in this format.

在dReader无法采取的路径这种格式。

It is only able to take path in the following format

它只能采用以下格式的路径

string path="C:\ Users\ Hemant\Documents\example.xlsx";

i.e. with the the \\in the path.For which I have to hard code the path but we have to browse the file.

即\\路径中的。为此，我必须对路径进行硬编码，但我们必须浏览文件。

So,can any one please suggest a solution to use the path taken by the FileUpload1to import the data?

那么，有人可以提出一种解决方案来使用FileUpload1导入数据所采用的路径吗？

Answer 1

采纳答案by dash

You are dealing with a HttpPostedFile; this is the file that is "uploaded" to the web server. You really need to save that file somewhere and then use it, because...

您正在处理 HttpPostedFile；这是“上传”到网络服务器的文件。您确实需要将该文件保存在某处然后使用它，因为...

...in your instance, it just so happens to be that you are hosting your website on the same machine the file resides, so the path is accessible. As soon as you deploy your site to a different machine, your code isn't going to work.

...在您的实例中，恰好您在文件所在的同一台机器上托管您的网站，因此该路径是可访问的。一旦您将站点部署到另一台机器，您的代码将无法运行。

Break this down into two steps:

将其分解为两个步骤：

1) Save the file somewhere - it's very common to see this:

1）将文件保存在某处 - 看到这个很常见：

string saveFolder = @"C:\temp\uploads"; //Pick a folder on your machine to store the uploaded files

string filePath = Path.Combine(saveFolder, FileUpload1.FileName); 

FileUpload1.SaveAs(filePath);

Now you have your file locally and the real work can be done.

现在您在本地拥有您的文件并且可以完成真正的工作。

2) Get the data from the file. Your code should work as is but you can simply write your connection string this way:

2）从文件中获取数据。您的代码应该按原样工作，但您可以简单地以这种方式编写连接字符串：

string excelConnString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties="Excel 12.0";", filePath);

You can then think about deleting the file you've just uploaded and imported.

然后您可以考虑删除您刚刚上传和导入的文件。

To provide a more concrete example, we can refactor your code into two methods:

为了提供更具体的示例，我们可以将您的代码重构为两种方法：

    private void SaveFileToDatabase(string filePath)
    {
        String strConnection = "Data Source=.\SQLEXPRESS;AttachDbFilename='C:\Users\Hemant\documents\visual studio 2010\Projects\CRMdata\CRMdata\App_Data\Database1.mdf';Integrated Security=True;User Instance=True";

        String excelConnString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0\"", filePath);
        //Create Connection to Excel work book 
        using (OleDbConnection excelConnection = new OleDbConnection(excelConnString))
        {
            //Create OleDbCommand to fetch data from Excel 
            using (OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Designation] from [Sheet1$]", excelConnection))
            {
                excelConnection.Open();
                using (OleDbDataReader dReader = cmd.ExecuteReader())
                {
                    using(SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection))
                    {
                        //Give your Destination table name 
                        sqlBulk.DestinationTableName = "Excel_table";
                        sqlBulk.WriteToServer(dReader);
                    }
                }
            }
        } 
    }


    private string GetLocalFilePath(string saveDirectory, FileUpload fileUploadControl)
    {


        string filePath = Path.Combine(saveDirectory, fileUploadControl.FileName);

        fileUploadControl.SaveAs(filePath);

        return filePath;

    }

You could simply then call SaveFileToDatabase(GetLocalFilePath(@"C:\temp\uploads", FileUpload1));

你可以简单地然后打电话 SaveFileToDatabase(GetLocalFilePath(@"C:\temp\uploads", FileUpload1));

Consider reviewing the other Extended Propertiesfor your Excel connection string. They come in useful!

考虑查看Excel 连接字符串的其他扩展属性。他们来有用！

Other improvements you might want to make include putting your Sql Database connection string into config, and adding proper exception handling. Please consider this example for demonstration only!

您可能想要进行的其他改进包括将 Sql 数据库连接字符串放入配置，并添加适当的异常处理。请考虑此示例仅用于演示！

Answer 2

回答by Dante

Not sure why the file path is not working, I have some similar code that works fine. But if with two "\" it works, you can always do path = path.Replace(@"\", @"\\");

不知道为什么文件路径不起作用，我有一些类似的代码可以正常工作。但是如果有两个“\”它可以工作，你总是可以做到path = path.Replace(@"\", @"\\");

Answer 3

回答by Pratik

Try Using

尝试使用

string filename = Path.GetFileName(FileUploadControl.FileName);

Then Save the file at specified location using:

然后使用以下命令将文件保存在指定位置：

FileUploadControl.PostedFile.SaveAs(strpath + filename);

Answer 4

回答by Vishal Kotak

    public async Task<HttpResponseMessage> PostFormDataAsync()    //async is used for defining an asynchronous method
    {
        if (!Request.Content.IsMimeMultipartContent())
        {
            throw new HttpResponseException(HttpStatusCode.UnsupportedMediaType);
        }
        var fileLocation = "";
        string root = HttpContext.Current.Server.MapPath("~/App_Data");
        MultipartFormDataStreamProvider provider = new MultipartFormDataStreamProvider(root);  //Helps in HTML file uploads to write data to File Stream
        try
        {
            // Read the form data.
        await Request.Content.ReadAsMultipartAsync(provider);

            // This illustrates how to get the file names.
            foreach (MultipartFileData file in provider.FileData)
            {
                Trace.WriteLine(file.Headers.ContentDisposition.FileName); //Gets the file name
                var filePath = file.Headers.ContentDisposition.FileName.Substring(1, file.Headers.ContentDisposition.FileName.Length - 2); //File name without the path
                File.Copy(file.LocalFileName, file.LocalFileName + filePath); //Save a copy for reading it
                fileLocation = file.LocalFileName + filePath; //Complete file location
            }
    HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, recordStatus);
            return response;
}
catch (System.Exception e)
    {
            return Request.CreateErrorResponse(HttpStatusCode.InternalServerError, e);
    }
}
public void ReadFromExcel()
{
try
        {
            DataTable sheet1 = new DataTable();
            OleDbConnectionStringBuilder csbuilder = new OleDbConnectionStringBuilder();
            csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
            csbuilder.DataSource = fileLocation;
            csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");
            string selectSql = @"SELECT * FROM [Sheet1$]";
            using (OleDbConnection connection = new OleDbConnection(csbuilder.ConnectionString))
            using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectSql, connection))
            {
                connection.Open();
                adapter.Fill(sheet1);
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }          
}

Answer 5

回答by Sergey Petrov

You can use OpenXml SDK for *.xlsx files. It works very quickly. I made simple C# IDataReader implementation for this sdk. See here. Now you can easy import excel file to sql server database using SqlBulkCopy. It uses small memory because it reads by SAX(Simple API for XML) method (OpenXmlReader)

您可以将 OpenXml SDK 用于 *.xlsx 文件。它工作得非常快。我为这个 sdk 做了简单的 C# IDataReader 实现。见这里。现在您可以使用 SqlBulkCopy 轻松将 excel 文件导入 sql server 数据库。它使用小内存，因为它通过 SAX(Simple API for XML) 方法 (OpenXmlReader) 读取

Example:

例子：

private static void DataReaderBulkCopySample()
{            
    using (var reader = new ExcelDataReader(@"test.xlsx"))
    {
        var cols = Enumerable.Range(0, reader.FieldCount).Select(i => reader.GetName(i)).ToArray();
        DataHelper.CreateTableIfNotExists(ConnectionString, TableName, cols);

        using (var bulkCopy = new SqlBulkCopy(ConnectionString))
        {
            // MSDN: When EnableStreaming is true, SqlBulkCopy reads from an IDataReader object using SequentialAccess, 
            // optimizing memory usage by using the IDataReader streaming capabilities
            bulkCopy.EnableStreaming = true;

            bulkCopy.DestinationTableName = TableName;
            foreach (var col in cols)
                bulkCopy.ColumnMappings.Add(col, col);

            bulkCopy.WriteToServer(reader);
        }
    }
}

Answer 6

回答by Arulshankar R

A proposed solution will be:   


protected void Button1_Click(object sender, EventArgs e)
{
        try
        {
            CreateXMLFile();



        SqlConnection con = new SqlConnection(constring);
        con.Open();

        SqlCommand cmd = new SqlCommand("bulk_in", con);

        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@account_det", sw_XmlString.ToString ());

       int i= cmd.ExecuteNonQuery();
            if(i>0)
            {
                Label1.Text = "File Upload successfully";
            }
            else
            {
                Label1.Text = "File Upload unsuccessfully";
                return;

            }


        con.Close();
            }
        catch(SqlException ex)
        {
            Label1.Text = ex.Message.ToString();
        }




    }
     public void CreateXMLFile()
        {

          try
            {
                M_Filepath = System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName);
                fileExtn = Path.GetExtension(M_Filepath);
                strGuid = System.Guid.NewGuid().ToString();
                fNameArray = M_Filepath.Split('.');
                fName = fNameArray[0];

                xlRptName = fName + "_" + strGuid + "_" + DateTime.Now.ToShortDateString ().Replace ('/','-');
                 fileName =  xlRptName.Trim()  + fileExtn.Trim() ;



                 FileUpload1.PostedFile.SaveAs(ConfigurationManager.AppSettings["ImportFilePath"]+ fileName);



                strFileName = Path.GetFileName(FileUpload1.PostedFile.FileName).ToUpper() ;
                if (((strFileName) != "DEMO.XLS") && ((strFileName) != "DEMO.XLSX"))
                {
                    Label1.Text = "Excel File Must be DEMO.XLS or DEMO.XLSX";
                }
               FileUpload1.PostedFile.SaveAs(System.Configuration.ConfigurationManager.AppSettings["ImportFilePath"] + fileName);
               lstrFilePath = System.Configuration.ConfigurationManager.AppSettings["ImportFilePath"] + fileName;
               if (strFileName == "DEMO.XLS")
                {

                    strConn = "Provider=Microsoft.JET.OLEDB.4.0;" + "Data Source=" + lstrFilePath + ";" + "Extended Properties='Excel 8.0;HDR=YES;'";

                } 

                if (strFileName == "DEMO.XLSX")
                {
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + lstrFilePath + ";" + "Extended Properties='Excel 12.0;HDR=YES;'";

                }

                strSQL = " Select [Name],[Mobile_num],[Account_number],[Amount],[date_a2] FROM [Sheet1$]";



                OleDbDataAdapter mydata = new OleDbDataAdapter(strSQL, strConn);

                mydata.TableMappings.Add("Table", "arul");
                mydata.Fill(dsExcl);
                dsExcl.DataSetName = "DocumentElement";
                intRowCnt = dsExcl.Tables[0].Rows.Count;
                intColCnt = dsExcl.Tables[0].Rows.Count;

                if(intRowCnt <1)
                {

                    Label1.Text = "No records in Excel File";
                    return;
                }
                if  (dsExcl==null)
                {

                }
                else
                    if(dsExcl.Tables[0].Rows.Count >= 1000 )
                    {

                        Label1.Text = "Excel data must be in less than 1000  ";
                    }


                for (intCtr = 0; intCtr <= dsExcl.Tables[0].Rows.Count - 1; intCtr++)
                {

                    if (Convert.IsDBNull(dsExcl.Tables[0].Rows[intCtr]["Name"]))
                    {
                        strValid = "";

                    }
                    else
                    {
                        strValid = dsExcl.Tables[0].Rows[intCtr]["Name"].ToString();
                    }
                    if (strValid == "")
                    {
                        Label1.Text = "Name should not be empty";
                        return;

                    }
                    else
                    {
                        strValid = "";
                    }



                    if (Convert.IsDBNull(dsExcl.Tables[0].Rows[intCtr]["Mobile_num"]))
                    {
                        strValid = "";

                    }
                    else
                    {
                        strValid = dsExcl.Tables[0].Rows[intCtr]["Mobile_num"].ToString();
                    }
                    if (strValid == "")
                    {
                        Label1.Text = "Mobile_num should not be empty";

                    }
                    else
                    {
                        strValid = "";
                    }

                    if (Convert.IsDBNull(dsExcl.Tables[0].Rows[intCtr]["Account_number"]))
                    {
                        strValid = "";

                    }
                    else
                    {
                        strValid = dsExcl.Tables[0].Rows[intCtr]["Account_number"].ToString();
                    }
                    if (strValid == "")
                    {
                        Label1.Text = "Account_number should not be empty";

                    }
                    else
                    {
                        strValid = "";
                    }





                    if (Convert.IsDBNull(dsExcl.Tables[0].Rows[intCtr]["Amount"]))
                    {
                        strValid = "";

                    }
                    else
                    {
                        strValid = dsExcl.Tables[0].Rows[intCtr]["Amount"].ToString();
                    }
                    if (strValid == "")
                    {
                        Label1.Text = "Amount should not be empty";

                    }
                    else
                    {
                        strValid = "";
                    }



                    if (Convert.IsDBNull(dsExcl.Tables[0].Rows[intCtr]["date_a2"]))
                    {
                        strValid = "";

                    }
                    else
                    {
                        strValid = dsExcl.Tables[0].Rows[intCtr]["date_a2"].ToString();
                    }
                    if (strValid == "")
                    {
                        Label1.Text = "date_a2 should not be empty";

                    }
                    else
                    {
                        strValid = "";
                    }
                }


            }
         catch 
            {

            }

         try
         {
             if(dsExcl.Tables[0].Rows.Count >0)
             {

                 dr = dsExcl.Tables[0].Rows[0];
             }
             dsExcl.Tables[0].TableName = "arul";
             dsExcl.WriteXml(sw_XmlString, XmlWriteMode.IgnoreSchema);

         }
         catch
         {

         }
}`enter code here`

Answer 7

回答by JIYAUL MUSTAPHA

 protected void btnUpload_Click(object sender, EventArgs e)
    {
          if (Page.IsValid)
            {
                bool logval = true;
                if (logval == true)
                {
                    String img_1 = fuUploadExcelName.PostedFile.FileName;
                    String img_2 = System.IO.Path.GetFileName(img_1);
                    string extn = System.IO.Path.GetExtension(img_1);

                    string frstfilenamepart = "";
                    frstfilenamepart = "Emp" + DateTime.Now.ToString("ddMMyyyyhhmmss"); ;
                    UploadExcelName.Value = frstfilenamepart + extn;
                    fuUploadExcelName.SaveAs(Server.MapPath("~/Emp/EmpExcel/") + "/" + UploadExcelName.Value);
                    string PathName = Server.MapPath("~/Emp/EmpExcel/") + "\" + UploadExcelName.Value;
                    GetExcelSheetForEmp(PathName, UploadExcelName.Value);

                }
            }


    }

    private void GetExcelSheetForEmp(string PathName, string UploadExcelName)
    {
        string excelFile = "EmpExcel/" + PathName;
        OleDbConnection objConn = null;
        System.Data.DataTable dt = null;
        try
        {

            DataSet dss = new DataSet();
            String connString = "Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=True;Extended Properties=Excel 12.0 Xml;Data Source=" + PathName;
            objConn = new OleDbConnection(connString);
            objConn.Open();
            dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dt == null)
            {
                return;
            }
            String[] excelSheets = new String[dt.Rows.Count];
            int i = 0;
            foreach (DataRow row in dt.Rows)
            {
                if (i == 0)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString();
                    OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + excelSheets[i] + "]", objConn);
                    OleDbDataAdapter oleda = new OleDbDataAdapter();
                    oleda.SelectCommand = cmd;
                    oleda.Fill(dss, "TABLE");

                }
                i++;
            }
            grdByExcel.DataSource = dss.Tables[0].DefaultView;
            grdByExcel.DataBind();


        }

        catch (Exception ex)
        {
            ViewState["Fuletypeidlist"] = "0";
            grdByExcel.DataSource = null;
            grdByExcel.DataBind();
        }
        finally
        {
            if (objConn != null)
            {
                objConn.Close();
                objConn.Dispose();
            }
            if (dt != null)
            {
                dt.Dispose();
            }
        }

    }

Answer 8

回答by Athira Rajinesh

using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;

protected void Button1_Click(object sender, EventArgs e)

{

    //Upload and save the file

    string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);

    FileUpload1.SaveAs(excelPath);



    string conString = string.Empty;

    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, excelPath);

    using (OleDbConnection excel_con = new OleDbConnection(conString))

    {

        excel_con.Open();

        string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["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[2] { new DataColumn("Id", typeof(int)),

            new DataColumn("Name", typeof(string)) });



        using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))

        {

            oda.Fill(dtExcelData);

        }

        excel_con.Close();



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

        using (SqlConnection con = new SqlConnection(consString))

        {

            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))

            {

                //Set the database table name

                sqlBulkCopy.DestinationTableName = "dbo.Table1";



                //[OPTIONAL]: Map the Excel columns with that of the database table

                sqlBulkCopy.ColumnMappings.Add("Sl", "Id");

                sqlBulkCopy.ColumnMappings.Add("Name", "Name");

                con.Open();

                sqlBulkCopy.WriteToServer(dtExcelData);

                con.Close();

            }

        }

    }

}

Copy this in web config

在网络配置中复制这个

<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>

<add name="Excel07+ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>

you can also refer this link : https://athiraji.blogspot.com/2019/03/how-to-upload-excel-fle-to-database.html

你也可以参考这个链接：https: //athiraji.blogspot.com/2019/03/how-to-upload-excel-fle-to-database.html

C# 上传 Excel 表格并将数据导入 SQL Server 数据库

提问by Hemantsom

采纳答案by dash

回答by Dante

回答by Pratik

回答by Vishal Kotak

回答by Sergey Petrov

回答by Arulshankar R

回答by JIYAUL MUSTAPHA

回答by Athira Rajinesh

相关推荐

最近更新

标签

C# 上传 Excel 表格并将数据导入 SQL Server 数据库

提问by Hemantsom

采纳答案by dash

回答by Dante

回答by Pratik

回答by Vishal Kotak

回答by Sergey Petrov

回答by Arulshankar R

回答by JIYAUL MUSTAPHA

回答by Athira Rajinesh

相关推荐

C# 等待 Selenium Webdriver 更改的最佳实践？

C# 枚举设置为字符串并在需要时获取刺痛值

C# 如何取消选择/空白数据绑定组合框？SelectedIndex = -1 不起作用

C# 使用 XAML 使“默认”文本显示在没有焦点的空文本框中

相关推荐

最近更新

标签