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

本文介绍了将数据从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!