关于excel模板的一个问题 - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

FileSys.CopyFile(ProjectPath & "Attachments\销售结算单-ZB.xls", ProjectPath & "Attachments\销售结算单.xls",True)

Dim BookMB As New XLS.Book(ProjectPath & "Attachments\销售结算单.xls")

Dim s1 As XLS.Sheet = BookMB.Sheets("s1")

BookMB.Sheets.Remove(s1)

Dim Book As New XLS.Book(ProjectPath & "Attachments\空白.xls")

Dim fl As String = ProjectPath & "Reports\销售发货单.xls"

Dim Style1 As XLS.Style = Book.NewStyle() '单元格边框

Style1.BorderTop = XLS.LineStyleEnum.Thin

Style1.BorderBottom = XLS.LineStyleEnum.Thin

Style1.BorderLeft = XLS.LineStyleEnum.Thin

Style1.BorderRight = XLS.LineStyleEnum.Thin

Style1.AlignHorz = XLS.AlignHorzEnum.Center

Dim tbM As Table = e.form.controls("table1").Table

Dim rs As List(of Row) = tbM.GetCheckedRows()

If rs.count = 0 Then

msgbox("错误:尚未选中任何记录")

Return

End If

SystemReady = False

Forms("正在更新提示").open

'新建一个临时表,把原表中的数据格式化新增到临时表中,再绑定打印

Dim dtb As New DataTableBuilder("子表")

dtb.AddDef("销售订单编号", Gettype(String), 32)

dtb.AddDef("序", Gettype(Integer))

dtb.AddDef("物料编号", Gettype(String), 32)

dtb.AddDef("物料名称", Gettype(String), 50)

dtb.AddDef("单位", Gettype(String),16)

dtb.AddDef("需求量", Gettype(String),16)

dtb.AddDef("计划量", Gettype(String),16)

dtb.AddDef("实发量", Gettype(String),16)

dtb.AddDef("实收量", Gettype(String),16)

dtb.Build()

Dim tb As Table = Tables("子表")

Relations.Add("单据报表",tbM.DataTable.DataCols("销售订单编号"),tb.DataTable.DataCols("销售订单编号"))

Dim cmd As new sqlcom mand

cmd.c

For Each trM As Row In rs

'tb.DataTable.deletefor("")

cmd.com ma ndtext = "se l ect * fr om 销售订单明细表 wh ere 销售订单编号 = '" & trm("销售订单编号") & "'"

Dim dbS As DataTable = cmd.execu ter eader

If dbS.datarows.count = 0 Then

Continue For

End If

Dim i As Integer = 1

Dim n1,n2,n3,n4 As Decimal

For Ea ch trs As Data Row In dbS.datarows

Dim dr As Row = tb.ad dne w

dr("销售订单编号") = trs("销售订单编号")

dr("序") = i

dr("物料编号") = trs("物料编号")

dr("物料名称") = trs("物料名称")

dr("单位") = fu nc tions.ex ec ute("SYS_字典值换取字典名称",trs("结算单位"),"单位")

If trs.isnull("需求量") = False Then

dr("需求量") = for mat(trs("需求量"),"f")

n1 = n1 + trs("需求量")

End If

If trs.isnull("计划量") = False Then

dr("计划量") = fo rmat(trs("计划量"),"f")

n2 = n2 + trs("计划量")

End If

If trs.isnull("实发量") = False Then

dr("实发量") = for mat(trs("实发量"),"f")

n3 = n3 + trs("实发量")

End If

If trs.isnull("实收量") = False Then

dr("实收量") = for mat(trs("实收量"),"f")

n4 = n4 + trs("实收量")

End If

i = i + 1

'msgbox(tb.rows.count)

s1.name = trM("客户名称") & trM("销售订单编号")

Book.Sheets.ins ert(0,s1)

Dim sheet As XLS.Sheet = Book.Sheets(0)

'打印设置

With Sheet.PrintSetting

'.PaperKind = 9 '设为A4纸

'.LandScape = True '横向打印

.MarginLeft = 10 '左右边距设为20毫米

.MarginRight = 10

.MarginTop = 15 '上下边距设为15毫米

.MarginBottom = 15

End With

'msgbox(2)

Sheet(2,0).Value = "<" & tbM.name & ",1>" '主表数据开始标记

Sheet(2,1).Value = "销售发货单" '表单标题

Sheet(3,1).Value = "订单编号:" & trM("销售订单编号")

Sheet(4,1).Value = "客户编号:" & trM("客户编号")

Sheet(5,1).Value = "客户名称:" & trM("客户名称")

Sheet(3,13).Value = "创建时间:" & trM("创建时间")

Sheet(4,13).Value = "创建人:" & functions.execute("SYS_其它_纯值换其它字段",trM("创建人"),"用户名表","工号","用户名称","",0)

Sheet(3,25).Value = "发货日期:" & trM("发货日期")

Sheet(4,25).Value = "所在区域:" & functions.execute("SYS_字典值换取字典名称",trM("所在区域"),"所在区域:")

If trm.isnull("是否调拨") OrElse trm("是否调拨") = 0 Then

Sheet(5,25).Value = "是否调拨:" & "否"

Else

Sheet(5,25).Value = "是否调拨:" & "是"

End If

Sheet(7,0).Value = "<headerrow>" '明细区域表头

Sheet(7,1).Value = "序"

Sheet(7,3).Value = "物料编号"

Sheet(7,7).Value = "物料名称"

Sheet(7,15).Value = "单位"

Sheet(7,17).Value = "需求量"

Sheet(7,20).Value = "计划量"

Sheet(7,23).Value = "实发量"

Sheet(7,26).Value = "实收量"

Sheet(8,0).Value = "<子表>" '明细区域数据

Sheet(8,1).Value = "[序]"

Sheet(8,3).Value = "[物料编号]"

Sheet(8,7).Value = "[物料名称]"

Sheet(8,15).Value = "[单位]"

Sheet(8,17).Value = "[需求量]"

Sheet(8,20).Value = "[计划量]"

Sheet(8,23).Value = "[实发量]"

Sheet(8,26).Value = "[实收量]"

Sheet(9,15).Value = "合计:"

Sheet(9,17).Value = format(n1,"f")

Sheet(9,20).Value = format(n2,"f")

Sheet(9,23).Value = format(n3,"f")

Sheet(9,26).Value = format(n4,"f")

Sheet(11,0).Value = "<" & tbM.name & ">" '主表数据开始标记

Sheet(11,1).Value = "送货人:"

Sheet(11,13).Value = "品控部:"

Sheet(11,25).Value = "收货人:"

'设置页头页脚

Sheet.PrintSetting.Header = "&L单据名称:" & "销售发货单" & "&C单据编号:" & trM("销售订单编号") & " &R客户名称:" & trM("客户名称")

Sheet.PrintSetting.Footer = "&L打印时间:" & Date.now & " &C打印人:" & _UserName1 & " &R第&P页,总&N页"

Book.Build() '生成细节区

Sheet = Book.Sheets(0)

Dim k As Integer = 7 + i

Sheet.MergeCell(2,0,1,36)

'进行合并

For j As Integer = 7 To k

Sheet.MergeCell(j,0,1,2)

Sheet.MergeCell(j,2,1,4)

Sheet.MergeCell(j,6,1,8)

Sheet.MergeCell(j,14,1,2)

Sheet.MergeCell(j,16,1,3)

Sheet.MergeCell(j,19,1,3)

Sheet.MergeCell(j,22,1,3)

Sheet.MergeCell(j,25,1,3)

For r As Integer = 7 To k

For c As Integer =0 To 27

Sheet(r,c).Style = Style1

Book.Save(fl) '保存工作簿

If Relations.Contains("单据报表") Then

Relations.Delete("单据报表")

End If

Forms("正在更新提示").close

SystemReady = True

tbM.Refresh

Dim Proc As New Process '打开工作簿

Proc.File = fl

Proc.Start()

[此贴子已经被作者于2019/1/3 22:11:38编辑过]

主题：关于excel模板的一个问题