将Excel文件导入关系数据库是数据分析工作中经常做的事情,但很多Excel文件的格式是不统一的,因此必须事先结构化相应的数据,然后用SQL语句将其写入数据库。JAVA Processors猿经常选择使用第三方类库(如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”读取第一张图纸,从第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 对象写回到 文件中。

导出样式:

高级篇

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,我们就能够将数据以更加丰富直观的方式提供给业务人员阅读使用,而处理过程也会因为自动化而变得更加快捷。

1.《【weka怎么导出excel】这是汇入和汇出复杂Excel表格的最简单方法》援引自互联网,旨在传递更多网络信息知识,仅代表作者本人观点,与本网站无关,侵删请联系页脚下方联系方式。

2.《【weka怎么导出excel】这是汇入和汇出复杂Excel表格的最简单方法》仅供读者参考,本网站未对该内容进行证实,对其原创性、真实性、完整性、及时性不作任何保证。

3.文章转载时请保留本站内容来源地址,https://www.lu-xu.com/keji/2500043.html