以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.net/bbs/index.asp)
--  专家坐堂  (http://foxtable.net/bbs/list.asp?boardid=2)
----  批量从EXcel表格中复制数据到数据库表，字段不一样  (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=142320)
--  作者：fubblyc
--  发布时间：2019/10/24 9:18:00
--  批量从EXcel表格中复制数据到数据库表，字段不一样
老师，如下代码是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
--  作者：有点蓝
--  发布时间：2019/10/24 9:39:00
--  
这些东西百度一下，很容易找到的。

Dim 表格字段() As String = {"导入编号","商品","商品描述",...}
Dim 数据库字段() As String = {"第一列","第一列","第一列",...}
For i as integer = 0 to 表格字段.length - 1
    copy.ColumnMappings.Add(表格字段(i),数据库字段(i))
Next