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

浏览: 1665

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

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

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

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

导入

1、普通行式

表格样式:

undefined

集算器脚本:

undefined 

脚本说明:

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

A2:连接demo数据库;

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

导入效果:

undefined

2、多行表头行式

表格样式:

undefined

集算器脚本:

undefined 

脚本说明:

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

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

导入效果:

undefined

3、自由格式

表格样式:

undefined

集算器脚本:

undefined 

脚本说明:

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,读取下一条雇员信息

导入效果:

undefined

4、交叉表

表格样式:

undefined

集算器脚本:

undefined 

脚本说明:

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

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

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

导入效果:

undefined

5、主子表

表格样式:

undefined

集算器脚本:

undefined 

脚本说明:

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

A2:创建列名为“身份证号, 姓名, 关系, 工作单位, 联系电话”的空序表,用于保存子表员工家庭成员信息;

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

A4:打开 Excel 数据文件;

A5:循环读取 Excel 文件各 sheet 数据;

B6:读取员工信息序列;

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

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

B8:将 B7 序表的家庭成员列改名为身份证号;

C8:为 B8 序表的身份证号列赋值为员工信息中的身份证号;

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

导入效果:

序表A1如下图:

undefined

序表A2如下图:

undefined

 

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

 

导出

基础篇

1、单纯导出数据

(1)导出新文件

集算器脚本:

undefined 

脚本说明:

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

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

导出效果:

undefined

(2)追加数据

集算器脚本:

undefined 

脚本说明:

A1:读入文本格式的某日订单数据表;

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

(3)导出到不同 sheet

集算器脚本:

undefined 

脚本说明:

A1:读入文本格式的某企业订单表;

A2:对序表 A1 进行过滤,只选出公司名称为山泰企业的数据记录;

A3:将新序表 A2 导出到 orders.xlsx 中,只导出订单 ID、公司名称、订购日期、订单金额四个字段,并将订购日期改名为日期,订单金额改名为金额,数据导出到一个名为山泰企业的新 sheet 中。

导出效果:

undefined

2、导出大量数据

集算器脚本:

undefined 

脚本说明:

A1:读入文本格式某数据量较大的表;

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

导出效果:

undefined

注:

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

3、指定显示属性

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

表格样式:

undefined

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

集算器脚本:

undefined 

导出样式:

undefined

注:导出时会使用原文件中定义的各种样式属性;大数据流式导出时不支持。

4、固定行列填数据

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

undefined

集算器脚本:

undefined 

脚本说明:

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

导出样式:

undefined

高级篇

1、动态条件的显示属性

导出需求:

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

报表设计:

undefined

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

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

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

集算器脚本:

undefined 

脚本说明:

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

A2:进行报表环境的配置,主要是配置报表主目录以及授权文件;

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

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

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

导出效果:

undefined

2、分组带明细及统计

报表设计:

undefined

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

集算器脚本:

undefined 

脚本说明:

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

A2:进行报表环境的配置,主要是配置报表主目录以及授权文件;

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

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

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

导出样式:

undefined

3、交叉统计表

报表设计:

undefined

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

集算器脚本:

undefined 

脚本说明:

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

A2:进行报表环境的配置,主要是配置报表主目录以及授权文件;

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

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

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

导出样式:

undefined

总结

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

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

推荐 0
本文由 润乾软件 创作,采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者,并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点, 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台,并不用于任何商业目的,如果有任何问题,请及时联系我们,我们将根据著作权人的要求,立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

0 个评论

要回复文章请先登录注册