以文本方式查看主题 - Foxtable(狐表) (http://www.foxtable.com/bbs/index.asp) -- 专家坐堂 (http://www.foxtable.com/bbs/list.asp?boardid=2) ---- 导入excel中所有的sheet内容 (http://www.foxtable.com/bbs/dispbbs.asp?boardid=2&id=117945) |
-- 作者:huhu -- 发布时间:2018/4/21 11:28:00 -- 导入excel中所有的sheet内容 就是如果这个excel中包括了多个sheet,每个sheet的列名格式都是一样的。 怎么样一次导入?
|
-- 作者:有点蓝 -- 发布时间:2018/4/21 11:31:00 -- 循环逐个sheet导入 Dim Book As New XLS.Book("c:\\test\\订单.xls") Tables("订单").StopRedraw()for each Sheet as XLS.Sheet in Book.Sheets \'注意以下数组中列名称的顺序,必须和Excel表中的列顺序一致 Dim nms() As String = {"编号","产品","客户","雇员","单价","折扣","数量","日期"} \'注意下面的循环变量从1开始,而不是从0开始,因为Excel表的第一行是标题 For n As Integer = 1 To Sheet.Rows.Count -1 Dim r As Row = Tables("订单").AddNew() For m As Integer = 0 To nms.Length - 1 r(nms(m)) = Sheet(n,m).Value Next Next next
Tables("订单").ResumeRedraw() |
-- 作者:huhu -- 发布时间:2018/8/14 11:48:00 -- Systemready = False Dim mr As Row = Tables("库存管理界面_table29").AddNew() mr.save Dim App As New MSExcel.Application try Dim tsxx As WinForm.Label = e.Form.Controls("Label73") tsxx.text = "正在导入OEM IN 直销序列号请耐心等待!" Dim Wb As MSExcel.Workbook = App.WorkBooks.Open("D:\\直销序列号.xls") For Each Sheet As XLS.Sheet In Wb.Sheets Dim Ws As MSExcel.WorkSheet = Wb.WorkSheets(1) \'这个应该是第几个sheet吧,这个怎么改? Tables("库存管理界面_table29").DataTable.StopRedraw() \'注意以下数组中列名称的顺序,必须和Excel表中的列顺序一致 Dim ra = ws.UsedRange Dim nms() As String = {"采购订单号","存货编码","存货名称","SN","MAC"} ‘如果execl里面的列名或者顺序与数组的不一致,该如何报错提醒用户要导入的execl格式不对呢 \'注意下面的循环变量从1开始,而不是从0开始,因为Excel表的第一行是标题 For n As Integer = 2 To ra.Rows.Count Dim xlh As String = ws.cells(n,4).Text \'序列号----SN,xlh If DataTables("库存明细表").SQLFind("SN = \'" & xlh & "\'") Is Nothing Then \'如果不存在同编号的计划表 Dim r As Row = Tables("库存管理界面_table29").AddNew() r("工单行号") = "ZXSN" & r("_identify") r("订单行号") = "ZXSN" & r("_identify") r("库位类别") = "30" r("数量") = 1 r("订单号") = ws.cells(n,1).Value r("生产订单号码") = "ZX" & r("订单号") r("物料编码") = ws.cells(n,2).Value r("物料名称") = ws.cells(n,3).Value r("SN") = ws.cells(n,4).Value r("MAC") = ws.cells(n,5).Value r("出库完成时间") = Date.Today r("货运单号") = "直销发货" r.Save End If Next Next Tables("库存管理界面_table29").DataTable.ResumeRedraw() DataTables("库存明细表").SQLDeleteFor("SN is null") DataTables("库存明细表").RemoveFor("SN is null") tsxx.text = "请导入OEM IN 直销序列号!" MessageBox.show("OEM IN直销序列号已导入完成","导入完成",MessageBoxButtons.OK,MessageBoxIcon.Information) catch ex As exception finally app.quit End try Systemready = True
|
-- 作者:有点甜 -- 发布时间:2018/8/14 11:55:00 -- Systemready = False Dim mr As Row = Tables("库存管理界面_table29").AddNew() mr.save Dim App As New MSExcel.Application try Dim tsxx As WinForm.Label = e.Form.Controls("Label73") tsxx.text = "正在导入OEM IN 直销序列号请耐心等待!" Dim Wb As MSExcel.Workbook = App.WorkBooks.Open("D:\\直销序列号.xls") For Each ws As object In Wb.worksSheets Tables("库存管理界面_table29").DataTable.StopRedraw() \'注意以下数组中列名称的顺序,必须和Excel表中的列顺序一致 Dim ra = ws.UsedRange Dim nms() As String = {"采购订单号","存货编码","存货名称","SN","MAC"} \'如果execl里面的列名或者顺序与数组的不一致,该如何报错提醒用户要导入的execl格式不对呢 Dim flag = False For i As Integer = 0 To nms.length-1 If nms(i) <> ws.cells(1, i+1).text Then msgbox(ws.name & " 格式不匹配") flag = True Exit For End If Next If flag Then Continue For \'注意下面的循环变量从1开始,而不是从0开始,因为Excel表的第一行是标题 For n As Integer = 2 To ra.Rows.Count Dim xlh As String = ws.cells(n,4).Text \'序列号----SN,xlh If DataTables("库存明细表").SQLFind("SN = \'" & xlh & "\'") Is Nothing Then \'如果不存在同编号的计划表 Dim r As Row = Tables("库存管理界面_table29").AddNew() r("工单行号") = "ZXSN" & r("_identify") r("订单行号") = "ZXSN" & r("_identify") r("库位类别") = "30" r("数量") = 1 r("订单号") = ws.cells(n,1).Value r("生产订单号码") = "ZX" & r("订单号") r("物料编码") = ws.cells(n,2).Value r("物料名称") = ws.cells(n,3).Value r("SN") = ws.cells(n,4).Value r("MAC") = ws.cells(n,5).Value r("出库完成时间") = Date.Today r("货运单号") = "直销发货" r.Save End If Next Next Tables("库存管理界面_table29").DataTable.ResumeRedraw() DataTables("库存明细表").SQLDeleteFor("SN is null") DataTables("库存明细表").RemoveFor("SN is null") tsxx.text = "请导入OEM IN 直销序列号!" MessageBox.show("OEM IN直销序列号已导入完成","导入完成",MessageBoxButtons.OK,MessageBoxIcon.Information) catch ex As exception finally app.quit End try Systemready = True |
-- 作者:huhu -- 发布时间:2018/8/14 14:50:00 -- 万一某个sheet是空表呢?这样不就认为格式不对。空表应该不算才行。 |
-- 作者:有点甜 -- 发布时间:2018/8/14 14:59:00 -- If ra.rows.count>0 OrElse ra.columns.count>0 Then \'不是空表 |
-- 作者:huhu -- 发布时间:2018/8/14 16:23:00 -- Systemready = False Dim mr As Row = Tables("库存管理界面_table29").AddNew() mr.save Dim App As New MSExcel.Application try Dim tsxx As WinForm.Label = e.Form.Controls("Label73") tsxx.text = "正在导入OEM IN 直销序列号请耐心等待!" Dim Wb As MSExcel.Workbook = App.WorkBooks.Open("D:\\直销序列号.xls") For Each ws As object In Wb.worksSheets msgbox(11) \'没弹出来,后面的都没执行 Tables("库存管理界面_table29").DataTable.StopRedraw() msgbox(22) \'注意以下数组中列名称的顺序,必须和Excel表中的列顺序一致 Dim ra = ws.UsedRange msgbox(33) msgbox(ra.Rows.count & vbcrlf & ra.Columns.count) If ra.rows.count > 0 OrElse ra.columns.count > 0 Then \'不是空表 msgbox(0) Dim nms() As String = {"采购订单号","存货编码","存货名称","SN","MAC"} \'如果execl里面的列名或者顺序与数组的不一致,该如何报错提醒用户要导入的execl格式不对呢 Dim flag = False For i As Integer = 0 To nms.length-1 If nms(i) <> ws.cells(1, i+1).text Then msgbox(ws.name & " 格式不匹配") flag = True Exit For End If Next If flag Then Continue For \'注意下面的循环变量从1开始,而不是从0开始,因为Excel表的第一行是标题 For n As Integer = 1 To ra.Rows.Count - 1 Dim xlh As String = ws.cells(n,4).Text \'序列号----SN,xlh If DataTables("库存明细表").SQLFind("SN = \'" & xlh & "\'") Is Nothing Then \'如果不存在同编号的计划表 Dim r As Row = Tables("库存管理界面_table29").AddNew() r("工单行号") = "ZXSN" & r("_identify") r("订单行号") = "ZXSN" & r("_identify") r("库位类别") = "30" r("数量") = 1 r("订单号") = ws.cells(n,1).Value r("生产订单号码") = "ZX" & r("订单号") r("物料编码") = ws.cells(n,2).Value r("物料名称") = ws.cells(n,3).Value r("SN") = ws.cells(n,4).Value r("MAC") = ws.cells(n,5).Value r("出库完成时间") = Date.Today r("货运单号") = "直销发货" r.Save End If Next msgbox(1) End If Next Tables("库存管理界面_table29").DataTable.ResumeRedraw() DataTables("库存明细表").SQLDeleteFor("SN is null") DataTables("库存明细表").RemoveFor("SN is null") msgbox(2) tsxx.text = "请导入OEM IN 直销序列号!" MessageBox.show("OEM IN直销序列号已导入完成","导入完成",MessageBoxButtons.OK,MessageBoxIcon.Information) catch ex As exception finally app.quit End try Systemready = True
|
-- 作者:有点甜 -- 发布时间:2018/8/14 16:40:00 -- 关闭项目,重新打开,重新测试。
测试时,去掉 Systemready = False |
-- 作者:huhu -- 发布时间:2018/8/14 16:55:00 -- 按照你说的做好像也没用,请再帮忙排查一下。 |
-- 作者:有点甜 -- 发布时间:2018/8/14 16:57:00 -- 加上红色代码
catch ex As exception
msgbox(ex.message)
finally
|