Dim ct As Date =Cdate(Format(Date.now, "yyyy-MM-dd HH:mm:ss"))
Dim drpc As String =ct.year & format(ct.month, "00") & Format(ct.day, "00") & Format(ct.hour, "00") & Format(ct.minute, "00") & Format(ct.second, "00")
Dim pc As String = e.Form.Controls("TextBox5").value
If pc="" Then
messagebox.show("请输入批次号")
Return
End If
Dim drs As List (of DataRow) = DataTables("手动订单对账表").SQLSelect("对账_设定对账批次号='" & pc & "'")
If drs.count<1 Then
messagebox.show("表中没有对应批次的行")
Return
End If
Dim nms() As String = {"备注", "订单号", "收货人姓名","收货人地址", "发货品名", "发货数量", "发货单价", "货值小计", "运费", "订单收款总计", "客户", "平台"}
Dim Book As New XLS.Book(ProjectPath & "Attachments\手工订单对账单导出.xls")
book.sheets.add("对账单2")
Dim sheet0 As xls.sheet= book.sheets(0)
Dim sheet1 As xls.sheet= book.sheets("对账单2") '从表,
sheet1(0,0).value = "账单批次:" & pc
sheet1(1,0).value = "手工订单"
sheet1(2,0).value = "导出人:" & user.name & " 导出时间:" & Date.now
For n0 As Integer =0 To nms.length-1 Step 1
sheet1(3,n0).value = nms(n0)
Next
Dim i As Integer=0
For Each dr As DataRow In drs
sheet1(i+4,0).value = dr("对账_备注1")
sheet1(i+4,1).value = dr("订单号")
sheet1(i+4,2).value = dr("收货人姓名")
sheet1(i+4,3).value = dr("收货人省") & dr("收件人市") & dr("收件人区") & dr("收件人详细地址")
sheet1(i+4,8).value = dr("订单金额_运费")
Dim sum2 As Double = DataTables("平台订单表1").SQLCompute("SUM(手工订单参数_货款应收小计)", "原始订单信息_主单号='" & dr("订单号") & "'")
sheet1(i+4,9).value = dr("订单金额_运费") + sum2
Dim drs1 As List (of DataRow) = DataTables("平台订单表1").SQLSelect("原始订单信息_主单号='" & dr("订单号") & "'")
For Each dr1 As DataRow In drs1
sheet1(i+4,4).value = dr1("订单货品验证_产品链接名称")
sheet1(i+4,5).value = dr1("订单货品验证_产品链接数量")
sheet1(i+4,6).value = dr1("手工订单参数_手工订单连接单价")
sheet1(i+4,7).value = dr1("手工订单参数_货款应收小计")
sheet1(i+4,10).value = dr1("平台备案_客户名称")
sheet1(i+4,11).value = dr1("平台备案_平台名称")
i=i+1
Next
Next
'sheet0(1,21).VALUE= "<ALL>"
'sheet0(6,21).VALUE= "<对账_设定账期='" & pc & "'>"
Dim dlg As New SaveFileDialog '定义一个新的SaveFileDialog
dlg.Filter= "Excel文件|*.xls" '设置筛选器
dlg.FileName = "手动订单对账单" & drpc & "导出时间" & drpc
If dlg.ShowDialog = DialogResult.Ok Then
Book.Build()
Book.Save(dlg.FileName)
Dim Proc As New Process
Proc.File = dlg.FileName
Proc.Start()
End If