老师，如下代码是Excel表格和数据库表的字段一摸一样的情况，代码可行，可以批量从EXcel表格中复制数据到数据库表。
现在有另外一个表格也是要导入这个数据库表，但是字段类似，名称不一样。是要怎么调整吗？
这个表格的字段是：{"导入编号","商品","商品名称","库存地点","库存地点描述","期末库存","吊牌价","货号","大类"})
字段一摸一样的Excel表格的代码如下：
Dim count As Integer = 1000
Dim lst As new List(of String)
lst.AddRange(new String() {"导入编号","商品","商品描述","库存地点","库存地点描述","期末数量","吊牌","销售年度","季节","期末成本金额","货号","大类"})
'就是这里不一样了
Dim cols() As String = lst.ToArray
Dim dt As new System.Data.DataTable
Dim d1 As Date = Date.Now
Dim constring As String = "server=10.10.0;uid=a;pwd=z1;database=a"
using conn As System.Data.SqlClient.SqlConnection = new System.Data.SqlClient.SqlConnection(constring)
conn.Open() '打开链接
output.Show("打开数据库链接")
using copy = new System.Data.SqlClient.SqlBulkCopy(conn)
copy.Destinati '指定服务器上目标表的名称
Dim cls() As String = {"导入编号","商品","商品描述","库存地点","库存地点描述","期末数量","吊牌","销售年度","季节","期末成本金额","货号","大类"}
For Each cl As String In cls
copy.ColumnMappings.Add(cl,cl)
Next
copy.BatchSize = 10000
copy.BulkCopyTimeout = 60
output.Show("开始导入数据")
Dim _execl As New ExOpenXml.ExOXExecl()
try
Dim page As Integer = Math.Ceiling(allCount / count) - 1
Dim lastcount As Integer = allCount Mod count
For i As Integer = 0 To page
Dim idx As Integer = i*count
output.Show("idx=" & idx)
output.show(wjlj)
_execl.Open(wjlj,True) '不需要保存文档,第二个参数设置为True
dt = _execl.ReadToDataTable( ,cols , idx , IIF(i<page OrElse lastcount=0,count,lastcount))
output.Show(i & "readend=" & Format(Date.Now,"mm:ss.ffff"))
If dt IsNot Nothing Then
output.Show("dt.Rows.count=" & dt.Rows.count)
copy.WriteToServer(dt)
output.Show(i & "saveend=" & Format(Date.Now,"mm:ss.ffff"))
End If
Next
Finally
_execl.dispose
End Try
End using
End using
加好友 
发短信
Post By：2019/10/24 9:18:00 [显示全部帖子]
