以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.com/bbs/index.asp)
--  专家坐堂  (http://foxtable.com/bbs/list.asp?boardid=2)
----  [求助]导出非关联表数据并字段分工作表  (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=118743)
--  作者：syjylyq
--  发布时间：2018/5/10 9:09:00
--  [求助]导出非关联表数据并字段分工作表
如题：
比如说有一张"订单表"和一张"物料表"，根据订单号导出物料清单，并根据供应商分类。


以下代码只能导出某用户名所输入的所有物料信息，怎么才能只导出在订单表中选择的行所对应的相关物料信息。
Dim nams As List(Of String) = DataTables("备料明细").GetValues("供货方","下单人 = \'" & _UserName & "\'")
Dim App As New MSExcel.Application
Dim bname As String = "d:\\物料采购\\物料采购清单.xlsx"
Dim Wb As MSExcel.WorkBook = App.WorkBooks.Add
Dim ws As MSExcel.WorkSheet = Wb.WorkSheets(1)
For Each nam As String In nams
    Dim Book As New XLS.Book ( ProjectPath & "Attachments\\物料采购清单.xls" ) \'调用模板
    Dim sheet As XLS.Sheet = Book.Sheets(0)
    Tables("备料明细").filter = "供货方 = \'" & nam & "\' and 下单人 = \'" & _UserName & "\'"
    Book.Build()
    Dim fl As String = "d:\\物料采购\\" & nam & ".xls"
    Book.Save(fl) \'保存工作簿
    Dim Wb_temp As MSExcel.WorkBook = App.WorkBooks.Open(fl)
    Dim Ws_temp As MSExcel.WorkSheet = wb_temp.WorkSheets(1)
    Ws_temp.name = nam
    Ws_temp.Copy(System.Reflection.Missing.Value, ws)
    wb_temp.close(False, System.Reflection.Missing.Value, System.Reflection.Missing.Value)
Next
ws.delete
Wb.saveas(bname)
App.Quit
Dim Proc As New Process \'打开工作簿
Proc.File = bname
Proc.Start()
--  作者：有点甜
--  发布时间：2018/5/10 9:23:00
--  

Dim cr As Row = Tables("订单").Current

Dim nams As List(Of String) = DataTables("备料明细").GetValues("供货方","订单号 = \'" & cr("订单号") & "\'")
Dim App As New MSExcel.Application
Dim bname As String = "d:\\物料采购\\物料采购清单.xlsx"
Dim Wb As MSExcel.WorkBook = App.WorkBooks.Add
Dim ws As MSExcel.WorkSheet = Wb.WorkSheets(1)
For Each nam As String In nams
    Dim Book As New XLS.Book ( ProjectPath & "Attachments\\物料采购清单.xls" ) \'调用模板
    Dim sheet As XLS.Sheet = Book.Sheets(0)
    Tables("备料明细").filter = "供货方 = \'" & nam & "\' and 订单号 = \'" & cr("订单号") & "\'"
    Book.Build()
    Dim fl As String = "d:\\物料采购\\" & nam & ".xls"
    Book.Save(fl) \'保存工作簿
    Dim Wb_temp As MSExcel.WorkBook = App.WorkBooks.Open(fl)
    Dim Ws_temp As MSExcel.WorkSheet = wb_temp.WorkSheets(1)
    Ws_temp.name = nam
    Ws_temp.Copy(System.Reflection.Missing.Value, ws)
    wb_temp.close(False, System.Reflection.Missing.Value, System.Reflection.Missing.Value)
Next
ws.delete
Wb.saveas(bname)
App.Quit
Dim Proc As New Process \'打开工作簿
Proc.File = bname
Proc.Start()
--  作者：syjylyq
--  发布时间：2018/5/10 9:41:00
--  
如果订单表选择的不知一行而是多行改怎么办？

[此贴子已经被作者于2018/5/10 9:41:43编辑过]
--  作者：有点甜
--  发布时间：2018/5/10 10:01:00
--  

Dim t As Table = Tables("订单")
Dim filter As String = "1=2"
For i As Integer = t.TopPosition To t.BottomPosition
    filter &= " or 订单号 = \'" & t.rows(i)("订单号") & "\'"
Next
Dim nams As List(Of String) = DataTables("备料明细").GetValues("供货方",filter)
Dim App As New MSExcel.Application
Dim bname As String = "d:\\物料采购\\物料采购清单.xlsx"
Dim Wb As MSExcel.WorkBook = App.WorkBooks.Add
Dim ws As MSExcel.WorkSheet = Wb.WorkSheets(1)
For Each nam As String In nams
    Dim Book As New XLS.Book ( ProjectPath & "Attachments\\物料采购清单.xls" ) \'调用模板
    Dim sheet As XLS.Sheet = Book.Sheets(0)
    Tables("备料明细").filter = "供货方 = \'" & nam & "\' and (" & filter & ")"
    Book.Build()
    Dim fl As String = "d:\\物料采购\\" & nam & ".xls"
    Book.Save(fl) \'保存工作簿
    Dim Wb_temp As MSExcel.WorkBook = App.WorkBooks.Open(fl)
    Dim Ws_temp As MSExcel.WorkSheet = wb_temp.WorkSheets(1)
    Ws_temp.name = nam
    Ws_temp.Copy(System.Reflection.Missing.Value, ws)
    wb_temp.close(False, System.Reflection.Missing.Value, System.Reflection.Missing.Value)
Next
ws.delete
Wb.saveas(bname)
App.Quit
Dim Proc As New Process \'打开工作簿
Proc.File = bname
Proc.Start()
--  作者：syjylyq
--  发布时间：2018/5/10 10:25:00
--  
谢谢！