将execl里的数据批量导入数据库

本文将采用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文件导入数据库，请留言，指导指导下。

秒客网

将execl里的数据批量导入数据库

相关文章