将Excel文件导入关系数据库是数据分析工作中经常遇到的事情,但许多Excel文件的格式并不规则,需要先对其中的数据进行结构化,然后用SQL语句写入数据库。 JAVA编程猴子经常选择使用第三方类库(如POI或HSSFWorkbook )实现,通常是硬编码的。 接触复杂格式的表时,分析工作量也会加倍。 JAVA没有表对象,必须始终利用集合和实体类实现,代码冗长且不通用。 汇编程序的SPL是专门处理结构化数据的语言,可以简单地读取excel数据,结构化为“序号表”并导入数据库。 使用SPL语言,以前需要编写几千行代码才能完成的Excel数据的结构化签入工作现在只需要少于10行代码,简单的情况下只需要2、3行代码即可。
对于导出,可能需要使用程序自动生成Excel文件,但由于Excel本身所拥有的VBA很难使用,因此汇编程序作为数据处理工具来实现这一需要非常有用。
请参考收集器文档《函数参考》以获取本文中使用的函数。
在本节中,我们来看看收集器是如何导入或导出表数据的。
3358 www.Sina.com/http://www.Sina.com/http://www.Sina.com /
导入
1、普通行式
A1 :打开“学生成绩单. xlsx”文件,导入到计划表中。 选项@t表示文件的第一行是列标题;
A2 )连接到demo数据库;
A3 )将A1的顺序表存储在demo数据库的xscj表中。 由于表的列名和顺序表的字段名相同,因此只需指定数据表名称。
表格样式:
http://www.Sina.com/http://www.Sina.com /
集算器脚本:
脚本说明:
A1 )打开文件,读入数据的序列图。 参数“1,5”意味着读取最初的sheet,从第5行读取到文件的末尾;
A2 )将A1中读取的程序表的列名依次变更为“编号、项目编号、项目名、计量单位、数量、单价、单价”,即存储的数据表的列名。
导入效果:
http://www.Sina.com/http://www.Sina.com /
2、多行表头行式
表格样式:
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.xls cell () '类似地表示字符串。 这具有以下优点:在IDE中编写程序时,如果A2单元的编号发生变化,$[A2.xlscell中的A2将自动更改。 例如,如果在a2之前插入了一行,则该
B5 )判断员工ID的值是否为null,为null时结束循环,结束运转
B6 )将员工信息存储在A1序列的末尾
B7 )将员工信息行号排列为9,读取下一个员工信息
集算器脚本:
http://www.Sina.com/http://www.Sina.com /
脚本说明:
导入效果:
A1 :打开文件,导入数据并创建序列图。 参数“1,2”表示要读取第一个sheet,从第二行开始读取到文件末尾。 选项@t表示开始行是列标题。
A2 )第二行的第一个单元格是图像,读取的数据为null,第一列没有列标题,因此将第一列的列名称更改为承运人。
A3 )将承运人分组,对顺序表数据进行矩阵变换。 选项@r表示将列数据转换为行数据,转换后的新列名分别为“tzdjb地区”、“订单数量”。
3、自由格式
http://www.Sina.com/http://www.Sina.com /
/p>集算器脚本:
脚本说明:
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 格按tzdjb地区进行分组,B3 格按公司名称进行分组,C3、D3、E3 显示订单明细。E4 格统计各公司的订单金额总和,E5 格统计各地区的订单金额总和。
集算器脚本:
脚本说明:
A1:读入要导出的序表数据;
A2:进行报表环境的配置,主要是配置报表主目录以及授权文件;
A3:打开我们刚才设计的报表模板;
A4:将 A1 中的序表作为数据集 ds1 对传递给报表对象 A3 进行计算;
A5:将计算后的报表对象 A3 导出成 excel 文件。
导出样式:
3、交叉统计表
报表设计:
建立数据集 ds1,B2 格按订购日期的年份分组,A3 格按tzdjb地区分组,B3 格统计各分组的订单金额总和。
集算器脚本:
脚本说明:
A1:读入要导出的序表数据;
A2:进行报表环境的配置,主要是配置报表主目录以及授权文件;
A3:打开我们刚才设计的报表模板;
A4:将 A1 中的序表作为数据集 ds1 对传递给报表对象 A3 进行计算;
A5:将计算后的报表对象 A3 导出成 excel 文件。
导出样式:
总结集算器提供了非常灵活的在 excel 文件中定位和读取数据的功能,既可以成片读取网格数据,也可以精确定位单元格进行读取。再结合特有的“序表”对象,以往需要编写数千行代码才能完成的 Excel 数据结构化入库工作,现在只需要不到 10 行,甚至两三行代码就可以胜任。
而关于导出,在润乾报表丰富的设计能力基础上,通过集算器将计算得到的数据传递给润乾报表,然后再导出为 Excel,我们就能够将数据以更加丰富直观的方式提供给业务人员阅读使用,而处理过程也会因为自动化而变得更加快捷。