以文本方式查看主题 - 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=160954) |
-- 作者:cnsjroom -- 发布时间:2021/2/27 14:02:00 -- 如何将专业报表中的数据 利用excel模板显示出来 如何将专业报表中的数据 利用excel模板显示出来 Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'集体表彰\'And 获奖等级=\'县市级\'") 类似这样的代码 怎么引用到模板文件中呢? []还是<>? 达到如下效果: excel模板 Dim Names As List(Of String) = Tables("表彰").DataTable.GetValues("登记单位") Dim doc As New PrintDoc Dim prs As Integer = 20 \'每页20行 Dim tb As Table = Tables("表彰") For p As Integer = 1 To math.Ceiling(tb.Rows.Count / prs) - 1 Dim rt As New prt.RenderTable rt.Cells(0,0).Text = "黔南州检察系统表彰统计汇总表" rt.Cells(1,0).Text = "登记单位" rt.Cells(1,1).Text = "总数" rt.Cells(1,2).Text = "个人表彰" rt.Cells(1,7).Text = "集体表彰" rt.Cells(2,0).Text = "登记单位" rt.Cells(2,1).Text = "总数" rt.Cells(2,2).Text = "个人表彰" rt.Cells(2,3).Text = "国家级" rt.Cells(2,4).Text = "省部级" rt.Cells(2,5).Text = "地州级" rt.Cells(2,6).Text = "县市级" rt.Cells(2,7).Text = "集体表彰" rt.Cells(2,8).Text = "国家级" rt.Cells(2,9).Text = "省部级" rt.Cells(2,10).Text = "地州级" rt.Cells(2,11).Text = "县市级" rt.Cells(0,0).SpanCols = 12 \'第1行第1个单元格向右合并12列 rt.Cells(1,0).SpanRows = 2 \'第1行第1个单元格向下合并2行 rt.Cells(1,1).SpanRows = 2 \'第1行第2个单元格向下合并2行 rt.Cells(1,2).SpanCols = 5 \'第1行第3个单元格向右合并3列 rt.Cells(1,7).SpanCols = 5 \'第1行第7个单元格向右合并5列 rt.Cols(0).Width = 40 rt.Style.TextAlignHorz = prt.AlignHorzEnum.Center \'内容水平居中 rt.Style.TextAlignVert = prt.AlignVertEnum.Center \'内容垂直居中 rt.Rows(0).Style.BackColor = Color.LightGray \'第一行背景颜色设为灰色. rt.Rows(1).Style.BackColor = Color.LightGray \'第一行背景颜色设为灰色. rt.RowGroups(0,4).Header = prt.TableHeaderEnum.All \'将前四行作为表头. rt.Cols(2).Style.BackColor = Color.LightGray \'第一列背景颜色设为灰色. rt.Cols(7).Style.BackColor = Color.LightGray \'第一列背景颜色设为灰色. rt.Cols(1).Style.BackColor = Color.LightGray \'第一列背景颜色设为灰色. rt.Cols(0).Style.BackColor = Color.LightGray \'第一列背景颜色设为灰色. rt.ColGroups(0,1).Header = prt.TableHeaderEnum.All \'将第一列作为表头. rt.Rows(0).Height = 10 For i As Integer = 0 To Names.Count -1 rt.Cells(i+3,0).Text= Names(i) rt.Cells(i+3,1).Text = Tables("表彰").DataTable.Compute("Count(获奖者)","登记单位=\'" & Names(i) & "\'") rt.Cells(i+3,2).Text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'个人表彰\' And 登记单位=\'" & Names(i) & "\'") rt.Cells(i+3,3).Text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'个人表彰\' And 获奖等级=\'国家级\' And 登记单位=\'" & Names(i) & "\'") rt.Cells(i+3,4).Text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'个人表彰\' And 获奖等级=\'省部级\' And 登记单位=\'" & Names(i) & "\'") rt.Cells(i+3,5).Text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'个人表彰\' And 获奖等级=\'地州级\' And 登记单位=\'" & Names(i) & "\'") rt.Cells(i+3,6).Text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'个人表彰\' And 获奖等级=\'县市级\' And 登记单位=\'" & Names(i) & "\'") rt.Cells(i+3,7).Text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'集体表彰\' And 登记单位=\'" & Names(i) & "\'") rt.Cells(i+3,8).Text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'集体表彰\' And 获奖等级=\'国家级\' And 登记单位=\'" & Names(i) & "\'") rt.Cells(i+3,9).Text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'集体表彰\' And 获奖等级=\'省部级\' And 登记单位=\'" & Names(i) & "\'") rt.Cells(i+3,10).Text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'集体表彰\' And 获奖等级=\'地州级\' And 登记单位=\'" & Names(i) & "\'") rt.Cells(i+3,11).Text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'集体表彰\' And 获奖等级=\'县市级\' And 登记单位=\'" & Names(i) & "\'") Next rt.Style.Gridlines.All = New prt.Linedef(Color.Gray) \'灰色网格线 rt.CellStyle.Spacing.All = 1 \'内容距离网格线1毫米 rt.Rows(0).Style.TextAlignHorz = prt.AlignHorzEnum.Center \'第一行内容水平居中 rt.Rows.Count = rt.Rows.Count + 1 \'增加本页小计行 rt.Rows(rt.Rows.count -1)(0).text = "本页小计" rt.Rows(rt.Rows.count -1)(1).text = Tables("表彰").DataTable.Compute("Count(获奖者)") rt.Rows(rt.Rows.count -1)(2).text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'个人表彰\'") rt.Rows(rt.Rows.count -1)(3).text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'个人表彰\'And 获奖等级=\'国家级\'") rt.Rows(rt.Rows.count -1)(4).text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'个人表彰\'And 获奖等级=\'省部级\'") rt.Rows(rt.Rows.count -1)(5).text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'个人表彰\'And 获奖等级=\'地州级\'") rt.Rows(rt.Rows.count -1)(6).text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'个人表彰\'And 获奖等级=\'县市级\'") rt.Rows(rt.Rows.count -1)(7).text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'集体表彰\'") rt.Rows(rt.Rows.count -1)(8).text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'集体表彰\'And 获奖等级=\'国家级\'") rt.Rows(rt.Rows.count -1)(9).text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'集体表彰\'And 获奖等级=\'省部级\'") rt.Rows(rt.Rows.count -1)(10).text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'集体表彰\'And 获奖等级=\'地州级\'") rt.Rows(rt.Rows.count -1)(11).text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'集体表彰\'And 获奖等级=\'县市级\'") If p = math.Ceiling(tb.Rows.Count / prs) - 1 \'如果是最后一页 rt.Rows.Count = rt.Rows.Count + 1 \'增加总计行 rt.Rows(rt.Rows.count -1)(0).text = "总计" rt.Rows(rt.Rows.count -1)(1).text = Tables("表彰").DataTable.Compute("Count(获奖者)") rt.Rows(rt.Rows.count -1)(2).text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'个人表彰\'") rt.Rows(rt.Rows.count -1)(3).text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'个人表彰\'And 获奖等级=\'国家级\'") rt.Rows(rt.Rows.count -1)(4).text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'个人表彰\'And 获奖等级=\'省部级\'") rt.Rows(rt.Rows.count -1)(5).text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'个人表彰\'And 获奖等级=\'地州级\'") rt.Rows(rt.Rows.count -1)(6).text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'个人表彰\'And 获奖等级=\'县市级\'") rt.Rows(rt.Rows.count -1)(7).text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'集体表彰\'") rt.Rows(rt.Rows.count -1)(8).text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'集体表彰\'And 获奖等级=\'国家级\'") rt.Rows(rt.Rows.count -1)(9).text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'集体表彰\'And 获奖等级=\'省部级\'") rt.Rows(rt.Rows.count -1)(10).text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'集体表彰\'And 获奖等级=\'地州级\'") rt.Rows(rt.Rows.count -1)(11).text = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'集体表彰\'And 获奖等级=\'县市级\'") Else rt.BreakAfter = prt.BreakEnum.Page \'否则换页 End If doc.Body.Children.Add(rt) Next doc.Preview() [此贴子已经被作者于2021/2/27 14:04:01编辑过]
|
-- 作者:有点蓝 -- 发布时间:2021/2/27 14:37:00 -- 方法1、做个分组统计,然后直接输出execl文件 vars("变量1") = Tables("表彰").DataTable.Compute("Count(获奖者)","登记单位=\'" & Names(i) & "\'") vars("变量2") = Tables("表彰").DataTable.Compute(xxxx
|
-- 作者:cnsjroom -- 发布时间:2021/2/27 22:32:00 -- 回复:(有点蓝)方法1、做个分组统计,然后直接输出e... 1、谢谢老师 按照老师提供的交叉统计 代码如下
Dim b As New CrossTableBuilder("统计表1",DataTables("表彰")) b.HGroups.AddDef("登记单位") \'添加客户列用于水平分组 b.VGroups.AddDef("表彰类别") \'添加产品列用于垂直分组 b.VGroups.AddDef("获奖等级") \'添加产品列用于垂直分组 b.Totals.AddDef("获奖者",AggregateEnum.Count) \'添加数量列用于统计 b.HorizontalTotal = True \'水平汇总 b.VerticalTotal= True \'垂直汇总 b.Build \'生成统计表 MainTable = Tables("统计表1") \'打开生成的统计表他 Tables("统计表1") .SaveExcel("C:\\test.xls","表彰统计") 运行得到 (2)有没有办法在导出的excel表中添加内外边框线 这样更加美观些 2、按照老师的指导 我做了如下代码: Dim Names As List(Of String) = Tables("表彰").DataTable.GetValues("登记单位") For i As Integer = 0 To Names.Count -1 vars("变量1") = Names(i) vars("变量2") = Tables("表彰").DataTable.Compute("Count(获奖者)","登记单位=\'" & Names(i) & "\'") vars("变量3") = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'个人表彰\' And 登记单位=\'" & Names(i) & "\'") vars("变量4") = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'个人表彰\' And 获奖等级=\'国家级\' And 登记单位=\'" & Names(i) & "\'") vars("变量5") = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'个人表彰\' And 获奖等级=\'省部级\' And 登记单位=\'" & Names(i) & "\'") vars("变量6") = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'个人表彰\' And 获奖等级=\'地州级\' And 登记单位=\'" & Names(i) & "\'") vars("变量7") = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'个人表彰\' And 获奖等级=\'县市级\' And 登记单位=\'" & Names(i) & "\'") vars("变量8") = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'集体表彰\' And 登记单位=\'" & Names(i) & "\'") vars("变量9") = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'集体表彰\' And 获奖等级=\'国家级\' And 登记单位=\'" & Names(i) & "\'") vars("变量10") = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'集体表彰\' And 获奖等级=\'省部级\' And 登记单位=\'" & Names(i) & "\'") vars("变量11") = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'集体表彰\' And 获奖等级=\'地州级\' And 登记单位=\'" & Names(i) & "\'") vars("变量12") = Tables("表彰").DataTable.Compute("Count(获奖者)","表彰类别=\'集体表彰\' And 获奖等级=\'县市级\' And 登记单位=\'" & Names(i) & "\'") Next Dim Book As New XLS.Book(ProjectPath & "Attachments\\表彰数据统计.xlsx") Dim fl As String = ProjectPath & "Reports\\出库单.xls" Book.Build() \'生成细节区 ’Book.Sheets(0).Rows.RemoveAt(0) \'删除第一行,Excel报表的第一行通常是标记行 Book.Save(fl) \'保存工作簿 Dim Proc As New Process \'打开工作簿 Proc.File = fl Proc.Start() 模板表如下: 运行结果如下: [此贴子已经被作者于2021/2/27 22:36:39编辑过]
|
-- 作者:有点蓝 -- 发布时间:2021/2/28 20:54:00 -- 这种情况需要登记单位的数量*12个变量..... 既然已经生成统计表,直接根据统计表输出即可,不需要变量了。把模板的表名改为统计表的名称然后参考普通的面板用法设置即可
|
-- 作者:cnsjroom -- 发布时间:2021/2/28 21:08:00 -- 回复:(有点蓝)这种情况需要登记单位的数量*12个变量... 如果采用变量方式 就是复制十三行一模一样的变量对么? |
-- 作者:有点蓝 -- 发布时间:2021/2/28 21:55:00 -- 需要总共13*12个变量,然后模板需要设置13行明细,填写所有的13*12个变量 |