自动导出Excel的利器

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

创新互联从2013年创立，先为孟州等服务建站，孟州等地企业，进行企业商务咨询服务。为孟州企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。

基础篇

1. 单纯导出数据

1.1 导出新文件

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

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

1.2 追加数据

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

1.3 导出到不同 sheet

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

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

下图是导出结果：

2. 导出大量数据

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

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

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

下图是本例的导出结果，本例中导出了 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 文件中。

下图即是完成填报后的 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 文件如下：

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 文件如下：

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

3. 交叉统计表

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

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

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

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

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

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