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

问题描述

我开发这个简单的应用程序上传Excel文件（的.xlsx ），并导入在Excel工作表中的数据present到SQL服务器前$ P在.NET $ PSS数据库

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

我使用进口按钮点击以下code浏览并选择文件做了。

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();
    }

但是，当我使用code不运行

But the code doesn't run when I use

string path = FileUpload1.PostedFile.FileName;`

甚至

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

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

The dReader is unable to take the path in this format.

这是唯一能够采取的路径按以下格式

It is only able to take path in the following format

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

即。与在 \\\\ 中，我要好好code的路径path.For但我们要浏览文件。

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;这是上载到Web服务器的文件。你真的需要在一些地方保存文件，然后使用它，因为...

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

...在你的情况下，这恰好是您所承载的文件驻留在同一台机器上你的网站，这样的路径是可访问的。一旦你部署你的网站在不同的机器，你的code未去上班。

...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:\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）获得从该文件中的数据。您code应该工作的不过是你可以简单地写你的连接字符串是这样的：

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.

要提供一个更具体的例子，我们可以重构code到两种方法：

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：\\ TEMP \\上传，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屋！