自动导出 Excel 的利器

  有时我们需要用程序来自动生成 Excel 文件,但 Excel 本身带的 VBA 并不好用,而集算器作为数据处理工具实现这个需求就会方便很多。本文将介绍如何用集算器生成 Excel 文件,而集算器本身强大的数据计算能力不是本文重点,因此文中只是简单地用文本作为数据源举例,实际应用中可能会从各种各样的数据源中取数,再经过一系列运算得到需要导出的数据。
  本文中用到的函数请参看集算器文档《函数参考》。

基础篇

1. 单纯导出数据

1.1 导出新文件

  这个例子中,A1 单元格读入文本格式的某企业订单表,用来模拟可能通过计算得到的数据。A2 中的表达式是将 A1 的数据导出到 orders.xlsx 文件中 (如果文件不存在,程序运行时会自动创建)。例子中导出函数 xlsexport 参数中没有指定 x 和 F,因此将导出 A1 中的所有字段,同时保持字段名不变。由于没有指定参数 s,所以会导出到 sheet1 中。而函数使用了选项 @t,因此会将字段名导出到第一行。

A
1 =file(“orders.txt”:”UTF-8″).import@t()
2 =file(“orders.xlsx”).xlsexport@t(A1)

  下图中就是导出的 excel 文件:

1.2 追加数据

  假如某企业需要将每天的新订单追加到已经存在且有往日数据的 orders.xlsx 文件中,那么应该怎么做呢?与上例类似,在 A1 中是要追加的某日订单数据,数据结构保持相同,在 A2 中导出时不要加函数选项 @t,因为文件中已有标题,只需导出数据。由于文件已存在,因此会自动追加在原来数据的后面。

A
1 =file(“aday.txt”:”UTF-8″).import@t()
2 =file(“orders.xlsx”).xlsexport(A1)

1.3 导出到不同 sheet

  假如想对订单中针对山泰企业的订单作特殊关注,要将其数据的某些字段导出到专门的一个 sheet 中,又该如何进行呢?

  可以在 A2 单元格中,对序表 A1 进行过滤,只选出公司名称为山泰企业的数据记录,在 A3 中将新序表 A2 导出到 orders.xlsx 中,只导出订单 ID、公司名称、订购日期、订单金额四个字段,并将订购日期改名为日期,订单金额改名为金额,数据导出到一个名为山泰企业的新 sheet 中。对应的脚本修改如下:

A
1 =file(“orders.txt”:”UTF-8″).import@t()
2 =A1.select(公司名称 ==”山泰企业”)
3 =file(“orders.xlsx”).xlsexport@t(A2, 订单 ID, 公司名称, 订购日期: 日期, 订单金额: 金额;”山泰企业”)

  下图是导出结果:

2. 导出大量数据

  如果数据量很大时又该怎么办?

  集算器提供了游标来处理数据量很大的情况,游标在读取数据时从前向后遍历一次,逐条从数据源读取数据,并不是一次将所有数据读入内存,因此不会受到内存不足的限制。而且,集算器游标不仅可以应用于数据库,还可以应用于数据文件或者内存排列。

  本例中 A1 打开了文件游标,A2 中将游标所指的大数据导出到 big.xlsx 文件中。在用游标导出时,要添加 @s 这个函数选项,这样在导出时就会以流式导出,产生的 excel 结果文件也不会占用在内存中。

A
1 =file(“big.txt”:”UTF-8″).cursor@t()
2 =file(“big.xlsx”).xlsexport@st(A1)

  下图是本例的导出结果,本例中导出了 130727 条数据记录。事实上我们可以导出上亿条记录也不在话下,不过 excel 文件的一个 sheet 最多只能存放 1048576 行数据,所以当导出数据超过百万行时,会在 excel 中新增一个 sheet 来保存。

3. 指定显示属性

  除了直接导出数据,有时我们还希望生成的 excel 文件能够显示得比较美观,比如可以指定字体、颜色、背景色、对齐方式、显示格式等。这时,只要我们预先建好这个 excel 文件(模板),定义好我们需要的这些显示属性,然后再用集算器向这个文件中导出数据,定义好的显示属性就会随之呈现。

  如下图所示,我们在 orders.xlsx 文件 sheet1 的第一行写上表格名称,在第二行写上字段列名,并对表名和各列定义一些样式属性,第 1、3、4 列中间对齐,第 2 列左对齐,第 5 列右对齐,第 4 列显示格式为“yyyy 年 mm 月 dd 日”,第 5 列显示格式为“#,###.00”。

  集算器导出程序与本文第 1 例相同,导出结果如下图所示。在导出到已有文件时,会将文件的最后一个非空行当作表头,用导出的表头覆盖此行。导出时会使用原文件中定义的各种样式属性 (大数据流式导出时不支持)。

4. 固定行列填数据

  集算器里还提供了读写 excel 文件中指定的某单元格或某区块单元格的方法,这个功能在用 excel 作数据填报时非常有用。举个例子,比如某基金公司总公司向分公司下发了一张 excel 表格,要求分公司填入它的相关数据后回传给总公司,下发的 excel 文件如下:

  对于这样的填报表,由于每个季度都需向总公司填报,如果写一个集算器程序首先算出要填的单元格的数据,然后调用函数自动填入对应的格子,将会是非常省事又方便的办法。

  这个例子的脚本 dfx 文件如下图所示,假定要填的数据都算出来了,前 5 行是依次要填的数据。

  样表中前 6 个要填的单元格都是独立的,所以只能每次填一个格,第 6 行是可以连续填写的单元格,此时就把要填的数据拼成以 \t 分隔的字符串,可以同行中按顺序填入。数据全部填写完以后,再把 C6 打开的 excel 对象写回到 hb.xlsx 文件中。

A B C D E F G
1 猛牛基金 2017 58.2 364 300
2 8.5 50 200 100 400 200
3 182.6 76.3 43.7 28.5 16.4
4 120 1.07 30 0.27 90 0.8
5 154 6 4
6 =file(“hb.xlsx”) =A6.xlsopen()
7 =C6.xlscell(“B2”,1;A1) =C6.xlscell(“J2”,1;B1) =C6.xlscell(“L2”,1;C1)
8 =C6.xlscell(“B3”,1;D1) =C6.xlscell(“G3”,1;E1) =C6.xlscell(“K3”,1;F1)
9 =C6.xlscell(“B6”,1;[A2:F2].concat(“\t”)) =C6.xlscell(“H6”,1;[A3:E3].concat(“\t”))
10 =C6.xlscell(“B9”,1;[A4:F4].concat(“\t”)) =C6.xlscell(“B11”,1;[A5:G5].concat(“\t”))
11 =A6.xlswrite(C6)

  下图即是完成填报后的 excel 文件内容:

高级篇

  导出需求有时会非常复杂,比如导出时需要隔行显示不同的背景色、单元格颜色与数据值动态相关、用导出数据画统计图、对导出数据进行分组统计、多维交叉表等等。集算器如何实现这些复杂的导出需求呢?

  别急!集算器里还有高级兵器库——外部库,其中的润乾报表 5 就可以实现导出 excel 的复杂功能 (也可以导出 pdf 和 word)。如何部署外部库功能请参看《外部库使用指南》(下载地址 外部库指南 ),集算器中以 report_ 开头的系列函数就是实现此功能的。

1. 动态条件的显示属性

  下面来看一个导出订单表的例子,导出需求:数据行的背景色以两种颜色隔行交替显现,订单金额大于 2000 的用红色显示,低于 500 的用绿色显示。

  实现步骤:

  1、打开润乾报表 5 设计器,新建报表模板“订单表.rpx”,截图如下。

  新建报表数据集 ds1,这个数据集只用于从集算器接收导出的序表数据,所以只需指定数据集名称。报表的第一行是表名称,第二行是要导出的列名称,第三行是数据记录行,数据记录的具体写法可以参阅润乾报表的相关教程。

  选中第三行的所有单元格,在背景色表达式中填入:if(row()%2==0,-853778,-1),用来指定交替显示的两种背景色。

  选择第三行最后一个单元格,指定显示格式为 #.00,在前景色表达式中填入:if(value()>2000,-65536,if(value()<500,-16711936,-16777216)),指定根据不同金额显示不同的字体颜色。

  2、打开集算器设计器,新建 dfx 文件如下:

A
1 =file(“orders.txt”:”UTF-8″).import@t()
2 >report_config(“E:\\work\\raqsoftReport\\raqsoftConfig.xml”)
3 =report_open(“订单表.rpx”)
4 =report_run(A3;A1:”ds1″)
5 =report_exportXls@x(A3,”rpt.xlsx”)

  A1 中读入要导出的序表数据;

  A2 中进行报表环境的配置,主要是配置报表主目录;

  A3 中打开我们刚才设计的报表模板;

  A4 中将 A1 中的序表作为数据集 ds1 对传递给报表对象 A3 进行计算;

  A5 中将计算后的报表对象 A3 导出成 excel 文件。

3、运行上面的 dfx 文件,得到导出的 excel 文件如下:

2. 分组带明细及统计

  Excel 中根据数据的层次进行分组和明细的显示也是一个非常常见的需求,这里我们就针对这个需求,看看集算器是怎么处理的:

  1、打开润乾报表 5 设计器,新建报表模板“订单统计表.rpx”,截图如下。

  同上例类似,建立数据集 ds1,在 A3 格按货主地区进行分组,B3 格按公司名称进行分组,C3、D3、E3 显示订单明细。E4 格统计各公司的订单金额总和,E5 格统计各地区的订单金额总和。

  2、打开集算器设计器,新建 dfx 文件如下:

A
1 =file(“orders.txt”:”UTF-8″).import@t()
2 >report_config(“E:\\work\\raqsoftReport\\raqsoftConfig.xml”)
3 =report_open(“订单统计表.rpx”)
4 =report_run(A3;A1:”ds1″)
5 =report_exportXls@x(A3,”rpt.xlsx”)

  3、运行这个 dfx 文件,得到导出的 excel 如下图:

3. 交叉统计表

  同样,交叉统计表也是十分常见的表格之一,集算器加润乾报表,也可以完美实现将数据导出到 Excel 中的交叉统计表中:

  1、打开润乾报表 5 设计器,新建报表模板“订单交叉表.rpx”,截图如下。

  同上例类似,建立数据集 ds1,B2 格按订购日期的年份分组,A3 格按货主地区分组,B3 格统计各分组的订单金额总和。

  2、打开集算器设计器,新建 dfx 文件如下:

A
1 =file(“orders.txt”:”UTF-8″).import@t()
2 >report_config(“E:\\work\\raqsoftReport\\raqsoftConfig.xml”)
3 =report_open(“订单交叉表.rpx”)
4 =report_run(A3;A1:”ds1″)
5 =report_exportXls@x(A3,”rpt.xlsx”)

  3、运行这个 dfx 文件,得到导出的 excel 如下图:

  可以看到,在润乾报表丰富的设计能力基础上,通过集算器将计算得到的数据传递给润乾报表,然后再导出为 Excel,我们就能够将数据以更加丰富直观的方式提供给业务人员阅读使用,而处理过程也会因为自动化而变得更加快捷。

相关文章:

复杂 Excel 表格导入导出的最简方法 
自动导出 Excel 的利器 
提取复杂 Excel 数据 

例子程序:
Excel 去除重复数据 
去除 csv 文件中的重复行   
根据列名读入数据并变换  
读入指定列  
每隔 5 行做统计  
两种分隔符  
横向拼接多段组成   
按列的位置取数