Dim cj As WinForm.ComboBox = e.Form.Controls("ComboBox1")
Dim dm As WinForm.ComboBox = e.Form.Controls("ComboBox2")
Dim dt As DataTable = DataTables("箱单数据")
'Dim ggs = dt.GetValues("规格", "款号 = '" & dm.Text & "'and 合同号 like '%" & cj.Text & "%'","_sortkey")
Dim ggs() As String ={"XXXS","XXS","XS","S","M","L","XL","XXL","XXXL","F","60","65","70","75","80","85","90","95","100","105","110","115","120","125","130","135","140","145","150","155","160","165","170","175","180","185","190","195","200"}
Dim ggs2 As List(Of String) = dt.GetValues("规格", "款号 = '" & dm.Text & "'and 合同号 like '%" & cj.Text & "%'")
Dim ggs3 As new List(Of String)
Dim Products As New List(Of String)
Products = DataTables("箱单数据").GetValues("分仓信息","","_sortkey")
For Each gg As String In ggs
If ggs2.Contains(gg) Then ggs3.Add(gg)
Next
Dim dtb As New DataTableBuilder("临时箱单")
dtb.AddDef("合同号",Gettype(String),10)
dtb.AddDef("款号",Gettype(String),10)
dtb.AddDef("箱号",Gettype(String),10)
dtb.AddDef("颜色",Gettype(String),10)
dtb.AddDef("仓库",Gettype(String),10)
dtb.AddDef("排序",Gettype(Integer))
For Each v As String In ggs3
dtb.AddDef(v , Gettype(Double), "", "规格_" & v)
Next
dtb.AddDef("箱数",Gettype(String),10)
dtb.AddDef("数量",Gettype(String),10)
dtb.Build()
For Each fc As String In Products
Dim drs = dt.Select("款号 = '" & dm.Text & "'and 合同号 like '%" & cj.Text & "%'and 分仓信息 = '" & fc & "'", "_sortkey")
Dim pdr As DataRow = Nothing
Dim xs As Double = 0
Dim xhs As String = ""
Dim sl As Double = 0
Dim pr As object = Nothing
For Each dr As DataRow In drs
If pdr Is Nothing OrElse (pdr("箱号") <> dr("箱号")-1 OrElse pdr("规格") <> dr("规格") OrElse pdr("颜色") <> dr("颜色") OrElse pdr("数量") <> dr("数量") OrElse pdr("分仓信息") <> dr("分仓信息")) Then
If pr IsNot Nothing Then
pr("合同号") = cj.Text
pr("款号") = dm.Text
pr("箱号") = xhs.trim("-")
pr(pdr("规格")) = pdr("数量")
pr("箱数") = xs
pr("数量") = sl
Dim arr() As String = pr("箱号").split("-")
If arr.length > 2
pr("箱号") = arr(0) & "-" & arr(arr.length - 1)
End If
End If
Dim r As DataRow = DataTables("临时箱单").AddNew()
r("颜色") = dr("颜色")
r("仓库") = dr("分仓信息")
r("排序") = dr("箱号")
xhs = dr("箱号")
xs = 1
sl = dr("数量")
pr = r
Else
xhs &= "-" & dr("箱号")
xs += 1
sl += dr("数量")
End If
pdr = dr
Next
If drs.count > 0
Dim dr = drs(drs.count - 1)
If pr IsNot Nothing Then
pr("合同号") = cj.Text
pr("款号") = dm.Text
pr("箱号") = xhs.trim("-")
pr(pdr("规格")) = dr("数量")
pr("箱数") = xs
pr("数量") = sl
End If
End If
Next
MainTable = Tables("临时箱单")
Dim de As Table = Tables("临时箱单")
Dim Book As New XLS.Book
Dim Sheet As XLS.Sheet = Book.Sheets(0)
With Sheet.PrintSetting
.AutoScale = True '自动缩放
.FitPagesAcross = 1 '垂直方向缩为1页
End With
Dim Style As XLS.Style = Book.NewStyle() '定义新样式
Dim styel1 As XLS.Style = Book.NewStyle() '定义新样式
styel1.font = New Font("微软雅黑", 16)
Dim styel2 As XLS.Style = Book.NewStyle() '定义新样式
styel2.font = New Font("微软雅黑", 12)
Style.BorderTop = XLS.LineStyleEnum.Thin
Style.BorderBottom = XLS.LineStyleEnum.Thin
Style.BorderLeft = XLS.LineStyleEnum.Thin
Style.BorderRight = XLS.LineStyleEnum.Thin
styel1.BorderTop = XLS.LineStyleEnum.Thin
styel1.BorderBottom = XLS.LineStyleEnum.Thin
styel1.BorderLeft = XLS.LineStyleEnum.Thin
styel1.BorderRight = XLS.LineStyleEnum.Thin
styel2.BorderTop = XLS.LineStyleEnum.Thin
styel2.BorderBottom = XLS.LineStyleEnum.Thin
styel2.BorderLeft = XLS.LineStyleEnum.Thin
styel2.BorderRight = XLS.LineStyleEnum.Thin
Style.BorderColorTop = Color.Black
Style.BorderColorBottom = Color.Black
Style.BorderColorLeft = Color.Black
Style.BorderColorRight = Color.Black
styel1.BorderColorTop = Color.Black
styel1.BorderColorBottom = Color.Black
styel1.BorderColorLeft = Color.Black
styel1.BorderColorRight = Color.Black
styel2.BorderColorTop = Color.Black
styel2.BorderColorBottom = Color.Black
styel2.BorderColorLeft = Color.Black
styel2.BorderColorRight = Color.Black
Sheet.Rows(0).Height = 70 '设置第1行的行高
styel1.WordWrap = True
styel2.AlignHorz = XLS.AlignHorzEnum.Center
styel2.AlignVert = XLS.AlignVertEnum.Center
styel1.AlignHorz = XLS.AlignHorzEnum.Center
styel1.AlignVert = XLS.AlignVertEnum.Center
Style.AlignHorz = XLS.AlignHorzEnum.Center
Style.AlignVert = XLS.AlignVertEnum.Center
de.CreateSheetHeader(Sheet,2,0,True)'生成表头
Sheet(0,0).Value = "产品销售统计" & vbcrlf & "箱单"
Dim dtd As DataTable = DataTables("临时箱单")
Dim idx As Integer = 0
For Each ck As String In dtd.GetValues("仓库","", "排序,仓库")
Dim Rows = dtd.Select("仓库='" & ck & "'", "排序")
Sheet(idx+4,0).Value = "仓库: " & ck
idx += 1
Dim mcount As Integer = 0
Dim msum As Integer = 0
For r As Integer = 0 To Rows.Count - 1 '填入数据
If r > 0 Then
If Rows(r-1)("箱号") = Rows(r)("箱号") Then
mcount += 1
msum += Rows(r-1)("数量")
ElseIf mcount > 0 Then
'msgbox(
Sheet.MergeCell(r+idx +4-mcount-1,2,mcount+1,1)
Sheet.MergeCell(r+idx +4-mcount-1,5+ggs3.count+1,mcount+1,1)
Sheet.MergeCell(r+idx +4-mcount-1,6+ggs3.count+1,mcount+1,1)
Sheet(r+idx +4-mcount-1, 6+ggs3.count+1).Value = msum
mcount = 0
msum = Rows(r)("数量")
Else
msum = Rows(r)("数量")
End If
Else
msum = Rows(r)("数量")
End If
For c As Integer = 0 To de.Cols.Count -1
Sheet.Cols(0).Width = 100
Sheet.Cols(1).Width = 100
Sheet.Cols(2).Width = 70
Sheet.Cols(3).Width = 120
For a As Integer = 4 To de.Cols.Count -3
Sheet.Cols(a).Width = 30 '设置第1列的宽度
Next
For a As Integer = de.Cols.Count -2 To de.Cols.Count -1
Sheet.Cols(a).Width = 60 '设置第1列的宽度
Next
Sheet(0,0).Style = styel1
Sheet.MergeCell(0,0,1,c+1)
Sheet.MergeCell(idx +3,0,1,c+1)
If rows(r)(de.cols(c).name) = Nothing Then
Sheet(r+idx+4 , c).Value = Nothing
Else
Sheet(r+idx+4 , c).Value = rows(r)(de.cols(c).name)
End If
Sheet(r+idx +4, c).Style = Style
Sheet(0,c).Style = Style
Sheet(2,c).Style = Style
Sheet(3,c).Style = Style
Sheet(2,c).Style = styel2
Sheet(3,c).Style = styel2
Next
Next
idx += Rows.Count
Next
Dim Sum As Double= de.Compute("Sum(数量)")
Dim Sum3 As Double = de.Compute("Sum(箱数)")
Dim Sum1 As Integer
Dim Sum2 As Integer
sum1 = de.Cols.Count
sum2 = idx
Dim dre = dtd.Select("款号 = '" & dm.Text & "'and 合同号 like '%" & cj.Text & "%'", "仓库,箱数")
Dim ppdr As DataRow = Nothing
For Each drf As DataRow In dre
If ppdr Is Nothing OrElse ppdr("箱号") <> drf("箱号") OrElse ppdr("仓库") <> drf("仓库") Then
Sum3 = Sum3 + drf("箱数")
End If
ppdr = drf
Sum = Sum + drf("数量")
Next
If ppdr("箱号") <> dre(dre.count-1)("箱号")
Sum3 = Sum3 + dre(dre.count-1)("箱数")
End If
Sheet(sum2+4, 0).Value = "合计:"
Sheet(sum2+4, sum1-1).Value = sum
Sheet(sum2+4, sum1-2).Value = sum3
Sheet.Cols.RemoveAt(4)
Book.Save("c:\reports\test.xls")
Dim Proc As New Process
Proc.File = "c:\reports\test.xls"
Proc.Start()
加好友 
发短信
Post By：2019/4/24 22:11:00 [只看该作者]
此主题相关图片如下：qq拼音截图20190415223417.png
