本文将采用NPOI插件来读取execl文件里的数据，将数据加载到内存中的DataTable中
1 /// <summary> 2 /// 将Excel转换为DataTable 3 /// </summary> 4 /// <param name="extension"></param> 5 /// <param name="stream"></param> 6 /// <returns></returns> 7 public static System.Data.DataTable GetExcelDataTable(string extension, System.IO.Stream stream) { 8 NPOI.SS.UserModel.IWorkbook workBook; 9 if (extension == ".xls") 10 workBook = new NPOI.HSSF.UserModel.HSSFWorkbook(stream); 11 else if (extension == ".xlsx") 12 workBook = new NPOI.XSSF.UserModel.XSSFWorkbook(stream); 13 else 14 throw new Exception("文件格式出错!"); 15 var sheet = workBook.GetSheetAt(0); 16 var row = sheet.GetRow(0); 17 System.Data.DataTable dt = new System.Data.DataTable(sheet.SheetName); 18 foreach (var i in row) { 19 var name = i.StringCellValue; 20 if (string.IsNullOrEmpty(name)) 21 break; 22 dt.Columns.Add(name); 23 } 24 dt.PrimaryKey = new DataColumn[1] { dt.Columns[0] }; 25 var rowCount = sheet.LastRowNum + 1; 26 for (var i = 1; i < rowCount; ++i) { 27 row = sheet.GetRow(i); 28 string[] cells = new string[dt.Columns.Count]; 29 for (var j = 0; j < cells.Length; ++j) { 30 var cell = row.GetCell(j); 31 if (cell != null) 32 cells[j] = cell.ToString(); 33 } 34 if (dt.Rows.Find(cells[0]) == null) { 35 dt.Rows.Add(cells); 36 } 37 } 38 return dt; 39 }View Code
上面的方法中，把execl表格第一列数据设为DataTable的主键，同时使用dt.Rows.Find()方法去除重复项，主要是为了避免后面合并数据发生意外
加载到内存中的DataTable之后，在业务层处理一下，把execl文件里的第一行数据(即DataTable中的列名)修改成后面创建的临时表的列名一一对应
1 public static async Task BatchImport(System.Data.DataTable dt) { 2 dt.Columns["商品id"].ColumnName = "GOODSID"; 3 dt.Columns["商品名称"].ColumnName = "GOODSNAME"; 4 dt.Columns["商品主图"].ColumnName = "GOODSMAINIMG"; 5 dt.Columns["商品详情页链接地址"].ColumnName = "GOODSDETAIL"; 6 dt.Columns["商品一级类目"].ColumnName = "GOODSLEVEL"; 7 dt.Columns["推广链接"].ColumnName = "SHORTLINK2"; 8 dt.Columns["商品价格(单位：元)"].ColumnName = "GOODSPRICE"; 9 dt.Columns["商品月销量"].ColumnName = "GOODSMONTHSALE"; 10 dt.Columns["收入比率(%)"].ColumnName = "GOODSINCOME"; 11 dt.Columns["佣金"].ColumnName = "GOODSCOMMISSION"; 12 dt.Columns["卖家名称"].ColumnName = "SELLER"; 13 dt.Columns["卖家id"].ColumnName = "SELLERID"; 14 dt.Columns["店铺名称"].ColumnName = "STORENAME"; 15 dt.Columns["平台类型"].ColumnName = "PLATFORMTYPE"; 16 dt.Columns["优惠券id"].ColumnName = "COUPONID"; 17 dt.Columns["优惠券总量"].ColumnName = "COUPONTOTAL"; 18 dt.Columns["优惠券剩余量"].ColumnName = "COUPONSURPLUS"; 19 dt.Columns["优惠券面额"].ColumnName = "COUPONCONTENT"; 20 dt.Columns["优惠券开始时间"].ColumnName = "CONPONSTRATETIME"; 21 dt.Columns["优惠券结束时间"].ColumnName = "COUPONENDTIME"; 22 dt.Columns["优惠券链接"].ColumnName = "COUPONSHORTLINK"; 23 dt.Columns["商品优惠券推广链接"].ColumnName = "COUPONLINK"; 24 using (var con =await mssql.tbk.GetConnection()) {//一下执行的方法会放后面 25 await mssql.tbk.CreateTmepTable(con);//创建临时表 26 await mssql.tbk.BatchImport("#TMD", dt,con);//将DataTable中的数据导入到临时表(#TMD) 27 await mssql.tbk.Combine(con); 28 await mssql.tbk.DropTempTable(con); 29 } 30 }View Code
下面开始创建临时表，临时表的名字命名必须以#开头，结构要和execl文件的一模一样，字段不能多也不能少，否则导入数据时某些数据丢失
1 /// <summary> 2 /// 创建临时表 3 /// </summary> 4 /// <param name="con"></param> 5 /// <returns></returns> 6 public static async Task CreateTmepTable(sqlClient.SqlConnection<System.Data.SqlClient.SqlConnection, System.Data.SqlClient.SqlParameter> con) { 7 await con.ExecuteNonQueryAsync("CREATE TABLE #TMD([GOODSID] BIGINT NOT NULL PRIMARY KEY,[GOODSNAME][varchar](250) NOT NULL,[GOODSMAINIMG][varchar](1024) NOT NULL,[GOODSDETAIL][varchar](1024) NOT NULL,[GOODSLEVEL][varchar](50) NOT NULL,[SHORTLINK2][varchar](1024) NOT NULL,[GOODSPRICE][varchar](100) NOT NULL,[GOODSMONTHSALE][varchar](100) NOT NULL,[GOODSINCOME][varchar](100) NOT NULL,[GOODSCOMMISSION][varchar](100) NOT NULL,[SELLER][varchar](100) NOT NULL,[SELLERID][varchar](100) NOT NULL,[STORENAME][varchar](100) NOT NULL,[PLATFORMTYPE][varchar](50) NOT NULL,[COUPONID][varchar](100) NOT NULL,[COUPONTOTAL][varchar](100) NOT NULL,[COUPONSURPLUS][varchar](100) NOT NULL,[COUPONCONTENT][varchar](100) NOT NULL,[CONPONSTRATETIME][varchar](100) NOT NULL,[COUPONENDTIME][varchar](100) NOT NULL,[COUPONSHORTLINK][varchar](1024) NOT NULL,[COUPONLINK][varchar](1024) NOT NULL)"); 8 }View Code
成功创建临时表之后，开始将DataTable数据导入临时表
1 /// <summary> 2 /// 将execl数据批量导入临时表 3 /// </summary> 4 /// <param name="tableName"></param> 5 /// <param name="dt"></param> 6 /// <returns></returns> 7 public static async Task BatchImport(string tableName,System.Data.DataTable dt, sqlClient.SqlConnection<System.Data.SqlClient.SqlConnection, System.Data.SqlClient.SqlParameter> con) { 8 await con.InsertBulkCopyAsync(tableName,dt,System.Data.SqlClient.SqlBulkCopyOptions.Default); 9 }View Code
重点来了，重点来了，重点来了，如何将临时表的数据写到数据库主表(主表结构与临时表结构可相同可不相同)里呢？请看代码
1 /// <summary> 2 /// 合并临时表数据到数据库主表 3 /// </summary> 4 /// <param name="con"></param> 5 /// <returns></returns> 6 public static async Task Combine(sqlClient.SqlConnection<System.Data.SqlClient.SqlConnection, System.Data.SqlClient.SqlParameter> con) { 7 await con.ExecuteNonQueryAsync("MERGE INTO TMD ts USING #TMD t ON ts.GOODSID=t.GOODSID WHEN MATCHED AND ts.GOODSID=t.GOODSID THEN UPDATE SET ts.GOODSNAME = t.GOODSNAME, ts.GOODSMAINIMG = t.GOODSMAINIMG, ts.GOODSDETAIL = t.GOODSDETAIL, ts.GOODSLEVEL = t.GOODSLEVEL, ts.SHORTLINK2 = t.SHORTLINK2, ts.GOODSPRICE = t.GOODSPRICE, ts.GOODSMONTHSALE = t.GOODSMONTHSALE, ts.GOODSINCOME = t.GOODSINCOME, ts.GOODSCOMMISSION = t.GOODSCOMMISSION, ts.SELLER = t.SELLER, ts.SELLERID = t.SELLERID, ts.STORENAME = t.STORENAME, ts.PLATFORMTYPE = t.PLATFORMTYPE, ts.COUPONID = t.COUPONID, ts.COUPONTOTAL = t.COUPONTOTAL, ts.COUPONSURPLUS = t.COUPONSURPLUS, ts.COUPONCONTENT = t.COUPONCONTENT, ts.CONPONSTRATETIME = t.CONPONSTRATETIME, ts.COUPONENDTIME = t.COUPONENDTIME, ts.COUPONSHORTLINK = t.COUPONSHORTLINK, ts.COUPONLINK = t.COUPONLINK WHEN NOT MATCHED THEN INSERT VALUES(t.GOODSID, t.GOODSNAME, t.GOODSMAINIMG, t.GOODSDETAIL, t.GOODSLEVEL, t.SHORTLINK2, t.GOODSPRICE, t.GOODSMONTHSALE, t.GOODSINCOME, t.GOODSCOMMISSION, t.SELLER, t.SELLERID, t.STORENAME, t.PLATFORMTYPE, t.COUPONID, t.COUPONTOTAL, t.COUPONSURPLUS, t.COUPONCONTENT, t.CONPONSTRATETIME, t.COUPONENDTIME, t.COUPONSHORTLINK, t.COUPONLINK,0);"); 8 }View Code
上面方法中使用了merge into和using，Merge和using搭配用于特别是BI上数据统计和分析上 比如 要求子表中没有的数据那么父表中就要删除对应的数据 保证子表和父表的数据对应 如果按照常规的做法是 跑个作业 然后通过游标/表值函数/临时表等等循环的获取数据然后更新父表 这样是很浪费效率的 这时Merge派上用场了
merge的语法：
merge into 主表 T
using #临时表 D on T.关联字段=D.关联字段
when matched
then update set T.字段=D.字段。。。。。。。。。。。
when not matched --为not matched时 不能为update(没有匹配成功 当然不能update了)
then insert (D字段。。。)values(D.字段);
想了解更详情请自行查找
合并完数据之后删除临时表（临时表会在数据库连接断开时清除临时表，这一步也可省）
1 /// <summary> 2 /// 删除临时表 3 /// </summary> 4 /// <param name="con"></param> 5 /// <returns></returns> 6 public static async Task DropTempTable(sqlClient.SqlConnection<System.Data.SqlClient.SqlConnection, System.Data.SqlClient.SqlParameter> con) { 7 await con.ExecuteNonQueryAsync("drop table #TAOBAO_SELECTED"); 8 }View Code
写到这里基本搞定批量导入数据的问题了，如果你有更好的办法实现几万行的execl文件导入数据库，请留言，指导指导下。
相关文章
