Dim cmd As New SQLCommand
Dim dt As DataTable
Dim dt1 As DataTable
Dim dt2 As DataTable
Dim dt3 As DataTable
cmd.C
cmd.CommandText = "SELECT * Fr om {面料订单明细}"
dt = cmd.ExecuteReader(True)
cmd.CommandText = "SELECT * Fr om {颜色对照表}"
dt1 = cmd.ExecuteReader(True)
cmd.CommandText = "SELECT * Fr om {英文数字对照表}"
dt2 = cmd.ExecuteReader(True)
cmd.CommandText = "SELECT * Fr om {面料信息}"
dt3 = cmd.ExecuteReader(True)
Dim dlg As New OpenFileDialog '定义一个新的OpenFileDialog
dlg.Filter = "Excel文件|*.xlsx|Excel文件|*.xls" '设置筛选器
If dlg.ShowDialog = DialogResult.Ok Then '如果用户单击了确定按钮
Dim Book As New XLS.Book(dlg.FileName)
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Dim dtb As New DataTableBuilder("订单")
For n As Integer = 0 To Sheet.cols.Count - 1
If Sheet(0, n).Value <> "" Then
dtb.AddDef(Sheet(0, n).Value, GetType(String), 255)
End If
Next
dtb.Build()
Dim tb As Table = Tables("订单")
For n As Integer = 1 To Sheet.Rows.Count - 1
Dim r As Row = tb.AddNew()
For m As Integer = 0 To tb.cols.Count - 1
r(tb.Cols(m).Name) = Sheet(n, m).Value
Next
Tables("订单").ResumeRedraw()
Next
Dim Vals As List(Of String())
Vals = DataTables("订单").GetValues("物料色号|物料色名", "", "物料色号")
For i As Integer = 0 To Vals.Count - 1
Dim sgdr As DataRow
sgdr = dt1.Find("色号 = '" & Vals(i)(0) & "'")
If sgdr IsNot Nothing Then
e.Cancel = True
Else
Dim dr As DataRow = dt1.AddNew()
dr("色号") = Vals(i)(0)
dr("颜色") = Vals(i)(1)
If Len(dr("色号")) = 3 Then
dr("代码") = dr("色号") & "00"
Else
Dim s1 As String = dr("色号").SubString(0, 3)
Dim s2 As String = dr("色号").SubString(3, 1)
Dim s3 As String
Dim sdr As DataRow
sdr = dt2.Find("[英文字母] = '" & s2 & "'")
s3 = sdr("代码")
dr("代码") = s1 & s3
End If
End If
Next
Dim Cols1() As String = {"物料编号", "物料色号", "品牌", "大货款号", "明细件数", "合计数量/kg", "要求成品截止时间", "实际大货毛衣工厂", "面料收货地址" }
Dim Cols2() As String = {"客户编号", "色号", "品牌", "款号", "件数", "数量", "交货日期", "交货单位", "备注" }
For Each dr1 As DataRow In DataTables("订单").DataRows
Dim Filter As String = "[客户编号] = '" & dr1("物料编号").Trim(" ", "-") & "' And [色号] = '" & dr1("物料色号") & "' And [款号] = '" & dr1("大货款号") & "' And [数量] = '" & dr1("合计数量/kg") & "' And [交货日期] = '" & dr1("要求成品截止时间") & "'"
Dim fzdr As DataRow
fzdr = dt.Find(Filter)
If fzdr IsNot Nothing Then
e.Cancel = True
Else
Dim dr2 As DataRow = dt.AddNew()
For i As Integer = 0 To Cols1.Length - 1
If dr1("物料编号").Contains("-") = True Then
dr2("客户编号") = dr1("物料编号").Trim(" ", "-")
Dim mdr As DataRow
mdr = dt3.Find("[客户编号] = '" & dr2("客户编号") & "'")
dr2("面料名称") = mdr("面料名称")
dr2("面料门幅") = mdr("门幅")
dr2("面料克重") = mdr("克重")
If dr2("色号") <> "" Then
Dim ydr As DataRow
ydr = dt1.Find("[色号] = '" & dr2("色号") & "'")
dr2("颜色") = ydr("颜色")
dr2("面料识别号") = mdr("面料编号") & ydr("代码")
End If
End If
dr2(Cols2(i)) = dr1(Cols1(i))
Next
End If
Next
End If
'下面的代码，将列出所有已经修改过的DataTable：
For Each svdt As DataTable In DataTables
If svdt.Type = 1 OrElse svdt.Type = 3 Then
If svdt.HasChanges Then
svdt.save()
svdt.Load
End If
End If
Next
不知道为什么EXCEL 导入数据库不行
加好友 
发短信
Post By：2022/5/1 23:30:00 [只看该作者]
此主题相关图片如下：01.jpg
