- 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=176912)
-- 发布时间：2022/5/1 23:30:00
-- EXCEL导入数据库为什么不行
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 导入数据库不行
-- 发布时间：2022/5/2 0:18:00
--
判断后缀，指定要导入的excel文件格式！
-- 发布时间：2022/5/2 8:01:00
--
没理解，原本是内部表，现在改成数据库了，改好后导入不了了，也没有错误提示
-- 发布时间：2022/5/2 10:57:00
--
代码放在什么事件的？
For Each svdt As DataTable In DataTables
msgbox(svdt.name & "," & svdt.Type)
If svdt.Type = 1 OrElse svdt.Type = 3 Then
msgbox(svdt.HasChanges )
If svdt.HasChanges Then
svdt.save()
svdt.Load
End If
End If
Next
-- 发布时间：2022/5/2 12:19:00
--
全部在Button1——Click
[此贴子已经被作者于2022/5/2 12:19:07编辑过]
-- 发布时间：2022/5/2 12:25:00
--
改好了，是save错误
但是有一个问题
此主题相关图片如下：01.jpg
此主题相关图片如下：02.jpg
填不满框或者超过整行长度，有什么办法解决？或者正确换行？
[此贴子已经被作者于2022/5/3 9:48:05编辑过]
-- 发布时间：2022/5/4 20:05:00
--
放在什么控件的？