上传 Excel 表格并将数据导入 SQL Server 数据库 [英] Uploading an Excel sheet and importing the data into SQL Server database

问题描述

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

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

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

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;`

甚至

string path="C: Users HemantDocumentsexample.xlsx";` 

dReader 无法采用这种格式的路径.

The dReader is unable to take the path in this 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.

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

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

推荐答案

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

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...

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

...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.

将其分为两步:

1) 将文件保存在某处 - 很常见:

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

string saveFolder = @"C:	empuploads"; //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) 从文件中获取数据.您的代码应该可以正常工作，但您可以简单地以这种方式编写连接字符串:

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

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;

    }

然后你可以简单地调用 SaveFileToDatabase(GetLocalFilePath(@"C: empuploads", FileUpload1));

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

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

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

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

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!

这篇关于上传 Excel 表格并将数据导入 SQL Server 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！