准备一下,近段时间,需要把Excel的数据导入数据库中。
引用命名空间:
using System.Configuration; using System.Data; using System.Data.OleDb; using System.Data.SqlClient;
你可以写一个方法,是去读取Excel文档的方法,返回DataSet数据集:
class="code_img_closed" src="/Upload/Images/2017102713/0015B68B3C38AA5B.gif" alt="" />logs_code_hide('935fa629-37f1-4fe5-a649-f2f2ae87745e',event)" src="/Upload/Images/2017102713/2B1B950FA3DF188F.gif" alt="" />
private DataSet ImportExcelToDataSet(string virtualPath, string sqlQueryStatement) { string excelConnectionString = DB.ExcelConnectionString(Server.MapPath(virtualPath)); OleDbConnection dc = new OleDbConnection(excelConnectionString); OleDbDataAdapter da = new OleDbDataAdapter(sqlQueryStatement, dc); DataSet ds = new DataSet(); da.Fill(ds); return ds; }Source Code
导入数据库,在数据库中,需要创建一个表来存储Excel导入来的数据:
接下来,你可以使作SqlBulkCopy的方法进行复制数据库:
try { string cs = ConfigurationManager.ConnectionStrings["InsusSqlConnectionString"].ConnectionString; using (SqlConnection sqlConn = new SqlConnection(cs)) { string sqlQueryStatement = "SELECT [Material],[Plnt],[Level],[Item],[Component],[Object description] FROM [Sheet1$]"; string virtualPath = "~/App_Data/Book1.xlsx"; DataSet ds = ImportExcelToDataSet(virtualPath, sqlQueryStatement); DataTable dt = ds.Tables[0]; sqlConn.Open(); using (SqlBulkCopy sqlbc = new SqlBulkCopy(sqlConn)) { sqlbc.DestinationTableName = "BOM"; sqlbc.ColumnMappings.Add("Material", "Material"); sqlbc.ColumnMappings.Add("Plnt", "Plnt"); sqlbc.ColumnMappings.Add("Level", "Level"); sqlbc.ColumnMappings.Add("Item", "Item"); sqlbc.ColumnMappings.Add("Component", "Component"); sqlbc.ColumnMappings.Add("Object description", "Object description"); sqlbc.WriteToServer(dt); Response.Write("数据导入成功!"); } } } catch (Exception ex) { throw ex; }Source Code
演示: