主题：相同格式多Excel表合并

多表合并，格式相同其他数据都可以导入，就是“结算单位”不会写，需要给每条数据添加一个单位名称，不能用动态加载列，那会冲突

下载信息  [文件大小：   下载次数： ]

点击浏览该文件:销售开piao明细账数据-2018_09_03-13_53_03.xls

分组excel报表？

 

http://www.foxtable.com/webhelp/scr/1542.htm

 

我的意思是，像上传的那样的文件有许多，格式结构都一样，需要合并多张表，并且每张表里面的客户名称（不在数据列中，在表头）自动赋值到合并后的table，每张表都有不同的客户。

意思是每个Excel表在Foxtable中生成一个新的表，新表的名称是客户名称？

 

Dim dlg As New OpenFileDialog
dlg.Filter = "Excel文件|*.xls;*.xlsx"
If dlg.ShowDialog =DialogResult.OK Then
    Dim t As Table = Tables("表B")
    t.StopRedraw()
    Dim Book As New XLS.Book(dlg.FileName)
    Dim Sheet As XLS.Sheet = Book.Sheets(0)
    msgbox(sheet(10,1).text)
    For n As Integer = 15 To Sheet.Rows.Count -1
        Dim r As DataRow = t.DataTable.Find("第一列 = '" & sheet(n, 0).text & "'")
        If r Is Nothing Then r = t.DataTable.AddNew()
        For i As Integer = 0 To sheet.Cols.Count -1
            Dim cname As String = sheet(14, i).text
            output.show(sheet(n, i).Text)
            If t.Cols.Contains(cname) Then
                r(cname) = sheet(n, i).Text
            End If
        Next
    Next
    t.ResumeRedraw()
End If

If MainTable.Name = "成品统计表" Then
    For Each file As String In filesys .GetFiles("C:\Users\福义\Desktop\产品统计\产品开piao信息")
        If file.EndsWith(".xls") OrElse file.EndsWith(".xlsx") Then
            Dim Book As New XLS.Book(file)
            Dim Sheet As XLS.Sheet = Book.Sheets(0)
            Dim Khnm As String = sheet(4,3).Text
            Dim nms() As String = {"行号","商品编号","商品名称","单据日期","单据类型","单据编号","出库数量","原币出库金额","开piao数量","原币开piao金额","未开piao数量","原币未开piao金额","超额开piao数量","原币超额开piao金额","手工开piao数量","原币手工开piao金额"}
            For n As Integer = 16 To Sheet.Rows.Count -1 '注意下面的循环变量从2开始,而不是从0开始,因为Excel表的第1-2行是标题和列名
                Dim r As Row = Tables("成品统计表").AddNew()
                For m As Integer = 0 To nms.Length - 1
                    r(nms(m)) = Sheet(n,m).Value
                Next
            Next
        End If
    Next
End If

 

这是合并多个excel表格的代码，现在的问题是：所有列都能合并到Foxtable里面来，需要把excel的B12单元格内的值（结算单位）随着每张表合并而自动添加到Foxtable里面来，形成一个新列：“结算单位”。说明：每张excel表都有不同的结算单位。

下载信息  [文件大小：   下载次数： ]

点击浏览该文件:销售开piao明细账数据-2018_09_03-13_52_29.xls

下载信息  [文件大小：   下载次数： ]

点击浏览该文件:销售开piao明细账数据-2018_09_03-13_52_14.xls

If MainTable.Name = "成品统计表" Then
    For Each file As String In filesys .GetFiles("C:\Users\福义\Desktop\产品统计\产品开piao信息")
        If file.EndsWith(".xls") OrElse file.EndsWith(".xlsx") Then
            Dim Book As New XLS.Book(file)
            Dim Sheet As XLS.Sheet = Book.Sheets(0)
            Dim jsdw As String = sheet(11,1).Text

msgbox(jsdw)
            Dim nms() As String = {"行号","商品编号","商品名称","单据日期","单据类型","单据编号","出库数量","原币出库金额","开piao数量","原币开piao金额","未开piao数量","原币未开piao金额","超额开piao数量","原币超额开piao金额","手工开piao数量","原币手工开piao金额"}
            For n As Integer = 16 To Sheet.Rows.Count -1 '注意下面的循环变量从2开始,而不是从0开始,因为Excel表的第1-2行是标题和列名
                Dim r As Row = Tables("成品统计表").AddNew()
                For m As Integer = 0 To nms.Length - 1
                    r(nms(m)) = Sheet(n,m).Value
                Next

r("结算单位") = jsdw
            Next
        End If
    Next
End