将数据从Excel工作表导入SQL Server数据库

问题描述：

如何将数据从Excel工作表导入ASP.NET中的SQL Server数据库?

How to import the data from an Excel sheet into SQL Server database in asp net?

Dim OleDbcon As New OleDbConnection((Convert.ToString("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=") & path) + ";Extended Properties=Excel 12.0;")

Dim cmd As New OleDbCommand("SELECT * FROM [Sheet1$]", OleDbcon)
Dim objAdapter1 As New OleDbDataAdapter(cmd)

OleDbcon.Open()
Dim dr As DbDataReader = cmd.ExecuteReader()

Dim con_str As String = "Data Source=.;Initial Catalog=studentdetails;Integrated Security=True"

' Bulk Copy to SQL Server 
Dim bulkInsert As New SqlBulkCopy(con_str)
bulkInsert.DestinationTableName = "Table name"
bulkInsert.WriteToServer(dr)
OleDbcon.Close()e here

答

将此分为两步:

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

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

string saveFolder = @"C:\ temp \ uploads";//在计算机上选择一个文件夹来存储上传的文件

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

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

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

FileUpload1.SaveAs(filePath);现在，您将文件保存在本地，即可完成真正的工作.

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);然后，您可以考虑删除刚刚上传并导入的文件.

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:\ temp \ uploads"，FileUpload1));

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

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

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

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

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数据库

相关推荐