专家坐堂-[求助]EXCEL导出汇总行Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  Foxtable(狐表)  (http://www.foxtable.com/bbs/index.asp)
--  专家坐堂  (http://www.foxtable.com/bbs/list.asp?boardid=2)
----  [求助]EXCEL导出汇总行  (http://www.foxtable.com/bbs/dispbbs.asp?boardid=2&id=175662)

--  作者：lgj716330
--  发布时间：2022/3/14 19:19:00
--  [求助]EXCEL导出汇总行

Dim b162 As New CrossTableBuilder("自定义费用汇总",DataTables("自定义费用明细"))
b162.HGroups.AddDef("部门名称")
b162.HGroups.AddDef("事业部")
b162.HGroups.AddDef("月份")
b162.VGroups.AddDef(HZ3)
b162.Totals.AddDef("金额")
b162.HorizontalTotal=True
b162.Subtotal=True
b162.Filter = "(" & filter & ") and 年份 = \'" & n1 & "\'"
b162.Build

Dim dt As Table = Tables("自定义费用汇总")

Dim Book As New XLS.Book(ProjectPath & "Attachments\\报表.xlsx")
Dim fl As String = ProjectPath & "Reports\\报表.xlsx"

Dim Sheet As XLS.Sheet = Book.Sheets(16)
For c As Integer = 0 To dt.Cols.Count -1
Sheet(0, c+1).Value = dt.Cols(c).Caption
Next
For r As Integer = 0 To dt.Rows.Count - 1
For c As Integer = 0 To dt.Cols.Count -1
Sheet(r +1, c+1).Value = dt.rows(r)(c)
Next
Next

如何将汇总行一起导出来呢

--  作者：有点蓝
--  发布时间：2022/3/14 20:27:00
--

参考：http://www.foxtable.com/webhelp/topics/1594.htm

For r As Integer = 0 To dt.Rows.Count(true) - 1
For c As Integer = 0 To dt.Cols.Count -1
Sheet(r +1, c+1).Value = dt.rows(r,true)(c)
Next
Next

--  作者：lgj716330
--  发布时间：2022/3/14 21:55:00
--

Dim Style2 As XLS.Style = Book.NewStyle()
Style2.BackColor = Color.SteelBlue
For r As Integer = 0 To dt.Rows.Count(True) - 1
For c As Integer = 0 To dt.Cols.Count -1
If dt.Rows(r, True)("部门名称") Like "*部门小计" Then
Sheet(0, c+1).Style = Style2
End If
Next
Next

想设置汇总行的背景颜色，以上代码未生效，要怎么调整

--  作者：有点蓝
--  发布时间：2022/3/14 22:01:00
--
Sheet(r +1, c+1).Style = Style2

--  作者：lgj716330
--  发布时间：2022/3/15 16:57:00
--

Dim Sheet As XLS.Sheet = Book.Sheets(16)
Dim hdr As Integer = dt.HeaderRows
dt.CreateSheetHeader(Sheet,0,0,False)
Dim Style1 As XLS.Style = Book.NewStyle()
Style1.Format = "0.00%"
For c As Integer = 0 To dt.Cols.Count -1
For r As Integer = 0 To dt.Rows.Count(True) - 1
sheet(r + hdr,c+1).value = dt.rows(r,True)(c)
Next
If dt.Cols(c).Caption Like "*占比" Then
Sheet.Cols(c+1).Style = Style1
End If
Next

上面标黄色部分，怎样对汇总行一样生效

--  作者：有点蓝
--  发布时间：2022/3/15 17:03:00
--
这个设置的是整列，肯定都有效

--  作者：lgj716330
--  发布时间：2022/3/15 20:10:00
--

代码1
Dim Sheet As XLS.Sheet = Book.Sheets(16)
Dim hdr As Integer = dt.HeaderRows
dt.CreateSheetHeader(Sheet,0,1)
For c As Integer = 0 To dt.Cols.Count -1
If dt.Cols(c).Visible Then
For r As Integer = 0 To dt.Rows.Count(True) - 1
sheet(r + hdr,cnt+1).value = dt.rows(r,True)(c)
Next
cnt = cnt + 1
End If
next

代码2
Dim Sheet2 As XLS.Sheet = Book.Sheets(17)
Dim hdr2 As Integer = dt2.HeaderRows
dt2.CreateSheetHeader(Sheet2,0,1)
For c As Integer = 0 To dt2.Cols.Count -1
If dt2.Cols(c).Visible Then
For r As Integer = 0 To dt2.Rows.Count(True) - 1
sheet2(r + hdr2,cnt+1).value = dt2.rows(r,True)(c)
Next
cnt = cnt + 1
End If
next

上述代码1和代码2在同一个按钮里，同时导出报表，发现代码1导出来了，代码2只导出了标题，其他内容是空的，然后把代码2改成下面这样又是可以的,会是什么原因呢
Dim Sheet2 As XLS.Sheet = Book.Sheets(17)
Dim hdr2 As Integer = dt2.HeaderRows
dt2.CreateSheetHeader(Sheet2,0,1,False)
For c As Integer = 0 To dt2.Cols.Count - 1
For r As Integer = 0 To dt2.Rows.Count(True) - 1
sheet2(r + hdr2,c+1).value = dt2.rows(r,True)(c)
Next
next

--  作者：有点蓝
--  发布时间：2022/3/15 20:20:00
--
代码1执行后cnt值已经很大了，代码2执行前需要重新赋值为0，重头开始。

代码2
cnt = 0
Dim Sheet2 As XLS.Sheet = Book.Sheets(17)