[浏览完整版]
标题：多个EXECL合并如何实现
1楼
lgz518 发表于：2024/11/2 9:45:00多个EXECL合并如何实现？图中有表1，2，3
此主题相关图片如下：exedl多表合并.jpg
问题1：如何表1，2，3的内容合并表一张表，比如表10？
问题2：来源表是多表头，或表结构（列数不一样）可以合并？
此主题相关图片如下：exedl多表合并.jpg2楼
有点蓝 发表于：2024/11/2 9:51:00表10在哪？最终效果是怎么样的
3楼
lgz518 发表于：2024/11/2 10:09:00此主题相关图片如下：多表合并1.jpg
4楼
有点蓝 发表于：2024/11/2 10:19:00逐行读出其它sheet，然后填入需要的sheet
大概这样
Dim Book As New XLS.Book("c:\test\订单.xls")
Dim Sheet As XLS.Sheet = Book.Sheets(0) '表10所在的sheet
Dim st As XLS.Sheet
Dim k As Integer = Sheet.Rows.Count
For i As Integer = 1 To Book.Sheets.Count - 1
st = Book.Sheets(i)
k += 1
For n As Integer = 1 To st.Rows.Count - 1
For m As Integer = 0 To st.Cols.Count - 1
Sheet(k, m).Value = st(n, m).Value
Next
Next
Next
[此贴子已经被作者于2024/11/2 10:19:47编辑过]
5楼
lgz518 发表于：2024/11/2 14:42:001.执行后，没报错，但没合并，如何处理？
2.能否EXECL多表的数据合并到狐表中，比如：表A?
6楼
有点蓝 发表于：2024/11/2 14:44:007楼
lgz518 发表于：2024/11/2 16:07:00问题1：执行后，报错从字符串“产品”到类型“Double”的转换无效。如何处理？
Dim Book As New XLS.Book("c:\test\订单.xls")
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Tables("订单").StopRedraw()
'注意以下数组中列名称的顺序,必须和Excel表中的列顺序一致
Dim nms() As String = {"序号","产品","数量","日期"}
'注意下面的循环变量从1开始,而不是从0开始,因为Excel表的第一行是标题
For n As Integer = 1 To Sheet.Rows.Count -1
Dim bh As String = sheet(n,0).Text
If DataTables("订单").Find("序号 = '" & bh & "'") Is Nothing Then '如果不存在同编号的订单
Dim r As Row = Tables("订单").AddNew()
For m As Integer = 0 To nms.Length - 1
r(nms(m)) = Sheet(n,m).Value
Next
End If
Next
Tables("订单").ResumeRedraw()
Dim Sheet As XLS.Sheet = Book.Sheets(0)
Tables("订单").StopRedraw()
'注意以下数组中列名称的顺序,必须和Excel表中的列顺序一致
Dim nms() As String = {"序号","产品","数量","日期"}
'注意下面的循环变量从1开始,而不是从0开始,因为Excel表的第一行是标题
For n As Integer = 1 To Sheet.Rows.Count -1
Dim bh As String = sheet(n,0).Text
If DataTables("订单").Find("序号 = '" & bh & "'") Is Nothing Then '如果不存在同编号的订单
Dim r As Row = Tables("订单").AddNew()
For m As Integer = 0 To nms.Length - 1
r(nms(m)) = Sheet(n,m).Value
Next
End If
Next
Tables("订单").ResumeRedraw()
8楼
lgz518 发表于：2024/11/2 16:08:00问题2：执行后，报错，详细错误信息：
索引超出范围。必须为非负值并小于集合大小。
参数名: index，如何处理？Dim Book As New XLS.Book("D:\test\订单.xlsx")
Dim Sheet As XLS.Sheet = Book.Sheets(10) '表10所在的sheet
Dim st As XLS.Sheet
Dim k As Integer = Sheet.Rows.Count
For i As Integer = 1 To Book.Sheets.Count - 1
st = Book.Sheets(i)
k += 1
For n As Integer = 1 To st.Rows.Count - 1
For m As Integer = 0 To st.Cols.Count - 1
Sheet(k, m).Value = st(n, m).Value
Next
Next
Next
[此贴子已经被作者于2024/11/2 16:13:14编辑过]
9楼
有点蓝 发表于：2024/11/2 16:21:00回7楼，应该是标题有多行，数据不是从第2行开始
回8楼，不存在第11个sheet
10楼
lgz518 发表于：2024/11/2 16:43:00Dim Sheet As XLS.Sheet = Book.Sheets(9) '表10所在的sheet
执行后，报错，详细错误信息：
索引超出范围。必须为非负值并小于集合大小。
参数名: index，如何处理？Dim Sheet As XLS.Sheet = Book.Sheets(0) '表10所在的sheet
没报错，但也没合并
Dim Book As New XLS.Book("D:\test\订单.xlsx")
Dim Sheet As XLS.Sheet = Book.Sheets(10) '表10所在的sheet
Dim st As XLS.Sheet
Dim k As Integer = Sheet.Rows.Count
For i As Integer = 1 To Book.Sheets.Count - 1
st = Book.Sheets(i)
k += 1
For n As Integer = 1 To st.Rows.Count - 1
For m As Integer = 0 To st.Cols.Count - 1
Sheet(k, m).Value = st(n, m).Value
Next
Next
Next