生成各种格式的 Excel 文件示例

【摘要】
本文介绍如何将经各种渠道得来的结构化数据自动生成各种格式的 Excel 文件,如单纯导出数据、导出大量数据、指定显示属性、固定行列填充、动态条件显示属性、分组带明细及统计、交叉统计表等,并用 esProc SPL 举例实现。请点击生成各种格式的 Excel 文件示例了解详情

 

在数据处理时,有时要把查询、分析计算、统计、数据采样等各种渠道得来的结构化数据,用程序自动生成各种格式的Excel文件。

本文将分别针对单纯导出数据、导出大量数据、指定显示属性、固定行列填充、动态条件显示属性、分组带明细及统计、交叉统计表等介绍实现方法,并提供用 esProc SPL 编写的代码示例。esProc 是专业的数据计算引擎, SPL 中有完善的 Excel 文件导出函数,将结构化数据导出成 Excel 非常方便。本文不探讨要导出的数据如何得来,假设它已经存在一个文本文件里了。

 

1.  单纯导出数据

1.1  导出新文件

将列名导出到Excel文件的第一行,第二行开始导出数据记录,每行一条记录。

示例:将订单表数据导出到一个新的Excel文件orders.xlsxesProc SPL脚本如下:


A

注释

1

=file("e:/txt/orders.txt":"UTF-8").import@t()  

读入文本数据

2

=file("e:/excel/orders.xlsx").xlsexport@t(A1)  

导出Excel文件,@t表示将列名导出到第一行

导出时如果文件不存在,会自动创建文件。函数xlsexport的参数中,可以指定需要导出的列,本例中没有指定,则导出所有的列;还可以指定导出数据的sheet名字,本例没有指定,则导出到第一个sheet中。

导出后的orders.xlsx部分数据如下图所示:

..

 

1.2  追加数据

Excel文件已经存在,要将新的数据追加在已有数据的后面。此时只需要追加数据记录,不用再导出列名了。

示例:将今日新增加的订单表数据追加到原有的orders.xlsx中,esProc SPL脚本如下:


A

注释

1

=file("e:/txt/aday.txt":"UTF-8").import@t()  

读入文本数据

2

=file("e:/excel/orders.xlsx").xlsexport@a(A1)  

@a表示将数据追加到原文件之尾

 

1.3  导出到指定 Sheet

导出数据时,指定保存数据的Sheet的名称。

示例:将来自Shantai公司的订单表数据导出到orders.xlsx文件里名为ShantaiSheet中。本例中只导出指定的列数据,并修改某些列名,esProc SPL脚本如下:


A

注释

1

=file("e:/txt/orders.txt":"UTF-8").import@t()  

读入文本数据

2

=A1.select(Company==“Shantai")  

过滤出Shantai公司数据

3

=file(“e:/excel/orders.xlsx”).xlsexport@t(A2,ID,Company,OrderDate:Date,Amount:Money;”Shantai")  

导出IDCompanyOrderDateAmount四列,并将OrderDate改名为DateAmount改名为Money,数据导出到名为Shantaisheet

导出后的orders.xlsx部分数据如下图所示:

..

 

2.  导出大量数据

当有大量的、不能一次性全部载入内存的数据需要导出时,要用游标分批读入数据,再用流的方式将数据写入OpenXml格式(即扩展名为xlsx)Excel文件,只需占据少量内存。Excel限制一个sheet最多只能存放1048576行数据,所以当导出数据行数超过此限制时,我们需要新增加一个sheet来保存。

示例:将大数据订单表导出到big.xlsx文件中,esProc SPL脚本如下:


A

注释

1

=file("e:/txt/big.txt":"UTF-8").cursor@t()  

用游标读文本数据

2

=file("e:/excel/big.xlsx").xlsexport@st(A1)  

@s表示用流式导出

导出后的big.xlsx最后几行数据如下图所示:

..

本例中导出了123663条数据记录,其实用这种方式可以导出任意条记录(不过得保证硬盘存储空间充足)

 

3.  指定显示属性

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

示例:把订单表数据导入到orders.xlsx中,并按如下图所示格式显示:

..

本例将多种预定义显示属性都集中在一起作示范,显示比较杂乱,仅仅为了演示功能,没有从美观上出发去设计。esProc SPL脚本与1.1节相同。

导出后的Excel文件如下图所示:

..

 

4.  固定行列填充

有一个行列数和单元格格式都固定的Excel文件,里面有些空单元格,需要把相关数据填写到这些格子里,这要求提供往单个单元格填写数据的功能。在数据填报业务中会经常用到。

示例:某基金公司总公司向分公司下发了一张excel表格,要求分公司填入它的相关数据后回传给总公司,下发的excel文件如下:

..

esProc SPL脚本如下:


A

B

C

D

E

F

1

Mengniu Funds

2017

3

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("e:/excel/result.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:C5].concat("\t"))

11

=A6.xlswrite(C6)




假定要填的数据已计算好(在前5行)。样表中前6个要填的单元格都是独立的,所以只能每次填一个格,第6行有连续填写的单元格,此时把待填数据拼成以\t分隔的字符串,这样可以从指定单元格开始顺序填充。数据填完以后,再把C6Excel对象写回到result.xlsx文件中。

填写完成以后的result.xlsx如下图所示:

..

 

5.  动态条件的显示属性

导出Excel时,要求单元格的显示属性是根据条件动态变化的,比如要隔行显示不同的背景色、单元格颜色或字体与数据值相关等。这种情况需要提供一种能定义显示属性条件表达式、并在导出时能计算表达式的值来得到显示属性的工具,在esProc SPL中,这种工具就是润乾报表。

示例:将订单表数据导出到orders.xlsx中,要求数据行的背景色以两种颜色隔行交替显现,订单金额大于2000的用红色显示,低于500的用绿色显示。

打开润乾报表设计器,新建报表模板“orders.rpx”,如下图所示。

..

报表的第一行是表名称,第二行是列名称,第三行是数据记录行,如何制作报表可以参阅润乾报表的相关教程。

选中第三行的所有单元格,在背景色表达式中填入:if(row()%2==0,-853778,-1),用来指定交替显示的两种背景色。选择第三行最后一个单元格,指定显示格式为#.00,在前景色表达式中填入:if(value()>2000,-65536,if(value()<500,-16711936,-16777216)),指定根据不同金额显示不同的字体颜色。

esProc SPL脚本如下:


A

注释

1

=file("e:/txt/orders.txt":"UTF-8").import@t()

读入文本数据

2

>report_config("e:/cfg/raqsoftConfig.xml")

装载报表环境配置

3

=report_open("orders.rpx")

打开设计的报表模板

4

=report_run(A3;A1:"ds1")

A1序表作为数据集ds1传递给报表A3进行计算

5

=report_exportXls@x(A3,"e:/excel/orders.xlsx")

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

导出后的orders.xlsx如下图所示:

..

 

6.  分组带明细及统计

导出Excel时,要求对数据进行分组,显示各组明细数据及统计数据。这同样需要用润乾报表工具作辅助。

示例:将订单表数据导出到orders.xlsx中,要求按地区和公司分组,显示各组订单明细及订单金额总和。

打开润乾报表设计器,新建报表模板“orders_group.rpx”,如下图所示。

..

报表的第一行是表名称,第二行是列名称,在A3格按货主地区进行分组,B3格按公司名称进行分组,C3D3E3显示订单明细。E4格统计各公司的订单金额总和,E5格统计各地区的订单金额总和。

esProc SPL脚本如下:


A

注释

1

=file("e:/txt/orders.txt":"UTF-8").import@t()

读入文本数据

2

>report_config("e:/cfg/raqsoftConfig.xml")

装载报表环境配置

3

=report_open("orders_group.rpx")

打开设计的报表模板

4

=report_run(A3;A1:"ds1")

A1序表作为数据集ds1传递给报表A3进行计算

5

=report_exportXls@x(A3,"e:/excel/orders.xlsx")

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

导出后的orders.xlsx如下图所示:

..

 

7.  交叉表

将数据导出成交叉表格式的Excel文件,仍然需要用润乾报表工具作辅助。

示例:将订单表数据导出到orders.xlsx中,要求按地区和年份做交叉统计表显示。

打开润乾报表设计器,新建报表模板“orders_cross.rpx”,如下图所示。

..

报表的第一行是表名称,B2格按订购日期的年份分组,A3格按货主地区分组,B3格统计各分组的订单金额总和。

esProc SPL脚本如下:


A

注释

1

=file("e:/txt/orders.txt":"UTF-8").import@t()

读入文本数据

2

>report_config("e:/cfg/raqsoftConfig.xml")

装载报表环境配置

3

=report_open("orders_cross.rpx")

打开设计的报表模板

4

=report_run(A3;A1:"ds1")

A1序表作为数据集ds1传递给报表A3进行计算

5

=report_exportXls@x(A3,"e:/excel/orders.xlsx")

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

导出后的orders.xlsx如下图所示:

..

 

SPL CookBook》中有更多相关计算示例。