将数据从Excel工作表导入SQL Server数据库 [英] Import data from an Excel sheet into a SQL Server database

问题描述

如何将数据从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数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！