共计 2764 个字符,预计需要花费 7 分钟才能阅读完成。
【摘要】
本文介绍如何将经各种渠道得来的结构化数据主动生成各种格局的 Excel 文件,如单纯导出数据、导出大量数据、指定显示属性、固定行列填充、动静条件显示属性、分组带明细及统计、穿插统计表等,并用 esProc SPL 举例实现。请点击 生成各种格局的 Excel 文件示例 理解详情
在数据处理时,有时要把查问、剖析计算、统计、数据采样等各种渠道得来的结构化数据,用程序主动生成各种格局的 Excel 文件。
本文将别离针对单纯导出数据、导出大量数据、指定显示属性、固定行列填充、动静条件显示属性、分组带明细及统计、穿插统计表等介绍实现办法,并提供用 esProc SPL 编写的代码示例。esProc 是业余的数据计算引擎,SPL 中有欠缺的 Excel 文件导出函数,将结构化数据导出成 Excel 十分不便。本文不探讨要导出的数据如何得来,假如它曾经存在一个文本文件里了。
1. 单纯导出数据
1.1 导出新文件
将列名导出到 Excel 文件的第一行,第二行开始导出数据记录,每行一条记录。
示例:将订单表数据导出到一个新的 Excel 文件 orders.xlsx,esProc SPL 脚本如下:
导出时如果文件不存在,会主动创立文件。函数 xlsexport 的参数中,能够指定须要导出的列,本例中没有指定,则导出所有的列;还能够指定导出数据的 sheet 名字,本例没有指定,则导出到第一个 sheet 中。
导出后的 orders.xlsx 局部数据如下图所示:
1.2 追加数据
Excel 文件曾经存在,要将新的数据追加在已有数据的前面。此时只须要追加数据记录,不必再导出列名了。
示例:将今日新减少的订单表数据追加到原有的 orders.xlsx 中,esProc SPL 脚本如下:
1.3 导出到指定 Sheet
导出数据时,指定保留数据的 Sheet 的名称。
示例:将来自 Shantai 公司的订单表数据导出到 orders.xlsx 文件里名为 Shantai 的 Sheet 中。本例中只导出指定的列数据,并批改某些列名,esProc SPL 脚本如下:
导出后的 orders.xlsx 局部数据如下图所示:
2. 导出大量数据
当有大量的、不能一次性全副载入内存的数据须要导出时,要用游标分批读入数据,再用流的形式将数据写入 OpenXml 格局 (即扩大名为 xlsx) 的 Excel 文件,只需占据大量内存。Excel 限度一个 sheet 最多只能寄存 1048576 行数据,所以当导出数据行数超过此限度时,咱们须要新减少一个 sheet 来保留。
示例:将大数据订单表导出到 big.xlsx 文件中,esProc SPL 脚本如下:
导出后的 big.xlsx 最初几行数据如下图所示:
本例中导出了 123663 条数据记录,其实用这种形式能够导出任意条记录(不过得保障硬盘存储空间短缺)。
3. 指定显示属性
除了间接导出数据,有时还心愿生成的 excel 文件可能显示得比拟好看,比方能够指定字体、色彩、背景色、对齐形式、显示格局等。这时须要事后建好这个 excel 文件(模板),定义好须要的这些显示属性,而后再向这个文件中导出数据,定义好的显示属性就会随之出现。
示例:把订单表数据导入到 orders.xlsx 中,并按如下图所示格局显示:
本例将多种预约义显示属性都集中在一起作示范,显示比拟芜杂,仅仅为了演示性能,没有从好看上登程去设计。esProc SPL 脚本与 1.1 节雷同。
导出后的 Excel 文件如下图所示:
4. 固定行列填充
有一个行列数和单元格格局都固定的 Excel 文件,外面有些空单元格,须要把相干数据填写到这些格子里,这要求提供往单个单元格填写数据的性能。在数据填报业务中会常常用到。
示例:某基金公司总公司向分公司下发了一张 excel 表格,要求分公司填入它的相干数据后回传给总公司,下发的 excel 文件如下:
esProc SPL 脚本如下:
假设要填的数据已计算好(在前 5 行)。样表中前 6 个要填的单元格都是独立的,所以只能每次填一个格,第 6 行有间断填写的单元格,此时把待填数据拼成以 \t 分隔的字符串,这样能够从指定单元格开始程序填充。数据填完当前,再把 C6 的 Excel 对象写回到 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 脚本如下:
导出后的 orders.xlsx 如下图所示:
6. 分组带明细及统计
导出 Excel 时,要求对数据进行分组,显示各组明细数据及统计数据。这同样须要用润乾报表工具作辅助。
示例:将订单表数据导出到 orders.xlsx 中,要求按地区和公司分组,显示各组订单明细及订单金额总和。
关上润乾报表设计器,新建报表模板“orders_group.rpx”,如下图所示。
报表的第一行是表名称,第二行是列名称,在 A3 格按货主地区进行分组,B3 格按公司名称进行分组,C3、D3、E3 显示订单明细。E4 格统计各公司的订单金额总和,E5 格统计各地区的订单金额总和。
esProc SPL 脚本如下:
导出后的 orders.xlsx 如下图所示:
7. 穿插表
将数据导出成穿插表格局的 Excel 文件,依然须要用润乾报表工具作辅助。
示例:将订单表数据导出到 orders.xlsx 中,要求按地区和年份做穿插统计表显示。
关上润乾报表设计器,新建报表模板“orders_cross.rpx”,如下图所示。
报表的第一行是表名称,B2 格按订购日期的年份分组,A3 格按货主地区分组,B3 格统计各分组的订单金额总和。
esProc SPL 脚本如下:
导出后的 orders.xlsx 如下图所示:
《SPL CookBook》(http://www.raqsoft.com.cn/wx/…)中有更多相干计算示例。