复杂 Excel 表格导入导出的最简方法

把 Excel 文件导入关系数据库是数据分析业务中经常要做的事情，但许多 Excel 文件的格式并不规整，需要事先将其中的数据结构化后再用 SQL 语句写入数据库。JAVA程序猿经常选择使用POI或者HSSFWorkbook等第三方类库来实现，通常都要硬编码，如果碰到格式复杂的表格，解析工作量还会成倍增加，Java没有表格对象，总要利用集合加实体类去实现，导致代码冗长、不通用。集算器的 SPL 是专业处理结构化数据的语言，它能够轻松读取 excel 数据，然后结构化成“序表”后导入数据库。使用 SPL 语言后，以往需要编写数千行代码才能完成的 Excel 数据结构化入库工作，现在只需要不到 10 行代码就可以胜任，简单情况下甚至只需要 2、3 行代码。

而关于导出，有时我们需要用程序来自动生成 Excel 文件，但 Excel 本身带的 VBA 并不好用，集算器作为数据处理工具实现这个需求就会方便很多。

本文中用到的函数请参看集算器文档《函数参考》。

下面我们就来了解一下集算器是如何对表格数据进行导入或导出的：

导入

1、普通行式

表格样式：

集算器脚本：

 

脚本说明：

A1：打开“学生成绩表.xlsx”文件并导入成序表，选项@t表示文件第一行是列标题；

A2：连接demo数据库；

A3：将A1中的序表存入到demo数据库的xscj表中，由于表中的列名和序表中的字段名一样，所以只需指定数据表名即可。

导入效果：

2、多行表头行式

表格样式：

集算器脚本：

 

脚本说明：

A1：打开文件并导入数据成序表，参数“1,5”表示读第一个 sheet，从第 5 行开始读，一直读到文件结尾；

A2：将 A1 中读到的序表列名依次改为“序号、项目编码、项目名称、计量单位、数量、单价、合价”，即要存入的数据表的列名。

导入效果：

3、自由格式

表格样式：

集算器脚本：

 

脚本说明：

A1：创建列名为“雇员 ID, 姓名, 性别, 职位, 生日, 电话, 地址, 邮编”的空序表

A2： 打开 Excel 数据文件

A3：定义雇员信息所在单元格列号序列

B3：定义雇员信息所在单元格行号序列

A4：用 for 循环读取每个雇员信息

B4：A3.(~/B3(#))先算出当前雇员单元格编号序列, 再读出这些单元格值组成雇员信息序列。第一次循环时为 [C1,C2,F2,C3,C4,D5,C7,C8]，第二次循环时为[C10,C11,F11,C12,C13,D14,C16,C17]……每次行号加 9。$[A2.xlscell(] 与 "A2.xlscell(" 相同，都是表示一个字符串，它的好处是在 IDE 中编写程序时，如果 A2 单元格的编号发生了变化，$[A2.xlscell(]中的 A2 会自动变化，比如在 A2 前插入了一行，这个表达式就会变成 $[A3.xlscell(]，而用引号的话，就不会自动变了。

B5：判断雇员 ID 值是否为空，为空则退出循环，结束运行

B6：将一条雇员信息存入 A1 序表尾

B7：让雇员信息的行号序列都加上 9，读取下一条雇员信息

导入效果：

4、交叉表

表格样式：

集算器脚本：

 

脚本说明：

A1：打开文件并导入数据成序表，参数“1,2”表示读第一个 sheet，从第 2 行开始读，一直读到文件结尾。选项 @t 表示开始行是列标题。

A2：由于第二行第一个单元格是图片，读的数据为 null，第一列没有列标题，所以将第一列列名改为运货商。

A3：以运货商为分组，对序表数据进行行列转换，选项 @r 表示将列数据转换为行数据，转换后新的列名分别为“货主地区”、“订单数量”。

导入效果：

5、主子表

表格样式：

集算器脚本：

 

脚本说明：

A1：创建列名为“×××号, 姓名, 性别, 出生日期, 民族, 手机号, 部门, 家庭地址, 婚姻状况, 入职时间”的空序表，用于保存主表员工信息；

A2：创建列名为“×××号, 姓名, 关系, 工作单位, 联系电话”的空序表，用于保存子表员工家庭成员信息；

A3：定义主表员工信息所在单元格序列；

A4：打开 Excel 数据文件；

A5：循环读取 Excel 文件各 sheet 数据；

B6：读取员工信息序列；

C6：将 B6 读取的员工信息保存到序表 A1；

B7：从第 6 行开始读取员工家庭成员信息，只读指定的“家庭成员, 姓名, 关系, 工作单位, 联系电话”5 列；

B8：将 B7 序表的家庭成员列改名为×××号；

C8：为 B8 序表的×××号列赋值为员工信息中的×××号；

B9：将 B8 中的员工家庭成员信息保存到序表 A2。

导入效果：

序表A1如下图：

序表A2如下图：

 

上面这些情况基本罗列了常见的 Excel 数据格式，如果遇到更复杂的文件，也可以灵活使用例子中的技巧予以应对。

 

导出

基础篇

1、单纯导出数据

（1）导出新文件

集算器脚本：

 

脚本说明：

A1：读入文本格式的某企业订单表，用来模拟可能通过计算得到的数据；

A2：将 A1 的数据导出到 orders.xlsx 文件中 (如果文件不存在，程序运行时会自动创建)。例子中导出函数 xlsexport 参数中没有指定 x 和 F，因此将导出 A1 中的所有字段，同时保持字段名不变。由于没有指定参数 s，所以会导出到 sheet1 中。而函数使用了选项 @t，因此会将字段名导出到第一行。

导出效果：

（2）追加数据

集算器脚本：

 

脚本说明：

A1：读入文本格式的某日订单数据表；

A2：导出时不要加函数选项 @t，因为文件中已有标题，只需导出数据。由于文件已存在，因此会自动追加在原来数据的后面。

（3）导出到不同 sheet

集算器脚本：

 

脚本说明：

A1：读入文本格式的某企业订单表；

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”。

集算器脚本：

 

导出样式：

注：导出时会使用原文件中定义的各种样式属性；×××式导出时不支持。

4、固定行列填数据

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

集算器脚本：

 

脚本说明：

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

导出样式：

高级篇

1、动态条件的显示属性

导出需求：

数据行的背景色以两种颜色隔行交替显现，订单金额大于 2000 的用红色显示，低于 500 的用绿色显示。

报表设计：

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

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

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

集算器脚本：

 

脚本说明：

A1：读入要导出的序表数据；

A2：进行报表环境的配置，主要是配置报表主目录以及授权文件；

A3：打开我们刚才设计的报表模板；

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

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

导出效果：

2、分组带明细及统计

报表设计：

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

集算器脚本：

 

脚本说明：

A1：读入要导出的序表数据；

A2：进行报表环境的配置，主要是配置报表主目录以及授权文件；

A3：打开我们刚才设计的报表模板；

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

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

导出样式：

3、交叉统计表

报表设计：

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

集算器脚本：

 

脚本说明：

A1：读入要导出的序表数据；

A2：进行报表环境的配置，主要是配置报表主目录以及授权文件；

A3：打开我们刚才设计的报表模板；

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

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

导出样式：

总结

集算器提供了非常灵活的在 excel 文件中定位和读取数据的功能，既可以成片读取网格数据，也可以精确定位单元格进行读取。再结合特有的“序表”对象，以往需要编写数千行代码才能完成的 Excel 数据结构化入库工作，现在只需要不到 10 行，甚至两三行代码就可以胜任。

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