Excel 文件的解析和生成

Excel文件是非常常见的数据文件,在数据处理业务中,经常要把Excel文件数据解析成结构化数据,或者把处理好的结构化数据保存到Excel文件中。Java中可以使用Apache Poi开源包读写Excel单元格数据。POI虽然强大,但有一定的学习曲线。许多Excel文件的格式并不规整,而且文件结构也多种多样,导致编程读写的工作量会比较大,而且很难通用,每次都要针对文件格式进行分析后再进行开发。

Open esProc开源包提供了读写Excel文件数据的功能,比POI更简便易用,它的SPL脚本语法简洁,只需一两句代码就可完成。

 

1.   解析

SPLfile函数打开Excel文件,用xlsimport函数将数据读成序表或游标。当文件数据不大,能够全部装入内存时,将数据读成序表;当文件数据很大,可以将数据读成游标以减少内存占用。SPL还可以只读入指定单元格数据,或某片区单元格数据。

下面是一些Excel文件读取的例子。

1.1   简单行式文件

行式Excel文件每行对应一条记录,各行有相同的列,相当于数据库中的一张数据表。

..

=file("e:/scores.xlsx").xlsimport@t()

file函数的参数可以是文件的绝对路径,也可以是相对路径(相对于SPL配置文件中的主目录mainPath),xlsimport函数默认读取文件中第1Sheet所有行列数据生成序表,选项@t表示第1行是标题行,此行的列值为对应序表列的名称;若无选项@t,表示无标题行,此时用#1#2等序号方式来引用列。

 

1.2   读取部分数据

=file("e:/scores.xlsx").xlsimport@t(No,Name,Class,Maths)

指定了列名参数,表示只读取No,Name,Class,Maths列的数据。

 

=file("e:/scores.xlsx").xlsimport@t(;,1:100)

读取文件中第1Sheet中第1行到第100行的所有列数据。

 

=file("e:/scores.xlsx").xlsimport@t(;,1:-10)

读取文件中第1Sheet中第1行到倒数第10行的所有列数据。负数表示倒数的行序号。

 

1.3   读取指定 Sheet

=file("e:/scores.xlsx").xlsimport@t(;"School2")

表示读取名称为School2Sheet中所有列数据。

 

1.4   读取 Sheet 信息

Excel文件中有多个SheetSPL提供xlsopen函数将文件打开生成一个Excel对象,它是一个由各Sheet信息组成的序表。

=file("e:/scores.xlsx").xlsopen()

打开后的Excel对象如下图:

..

每行表示一个Sheetstnamenrowsncols分别表示Sheet的名称、数据行数、数据列数。

 

1.5   自由格式

一条数据记录分散在多行,字段列值单元格紧跟在列名单元格后面或下面,可能有跨列或跨行的合并单元格。但每条记录所占的行数以及对应行结构是相同的。循环读取时要以每条记录所占行数为单位组成一条记录。

读取Excel指定单元格数据时,使用SPL中的xlsopen函数将文件读成Excel对象,再调用此对象的xlscell函数。

示例:自由格式的员工信息文件employee.xlsx中,部分数据如下图:

..

读取数据的SPL脚本如下:


A

B

C

1

=create(ID,Name,Sex,Position,Birthday,Phone,Address,PostCode)  

2

=file(“e:/excel/employe.xlsx").xlsopen()  

3

[C,C,F,C,C,D,C,C]

[1,2,2,3,4,5,7,8]


4

For

=A3.(~/B3(#)).(A2.xlscell(~))


5


if len(B4(1))==0

break

6


>A1.record(B4)


7


>B3=B3.(~+9)


A1   创建列名为“IDNameSexPositionBirthdayPhoneAddressPostCode”的空序表

A2   打开Excel数据文件,生成一个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

B5   判断雇员ID值是否为空,为空则退出循环,结束读数

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

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

读取出来的A1单元格数据如下图所示:

..

 

更多Excel文件解析介绍请参考《SPLExcel文件读写

 

2.   生成

SPLfile函数打开Excel文件,然后用xlsexport函数将序表或游标数据写入到此文件中。用xlsopen将文件打开成Excel对象,再调用此对象的xlscell函数,可以将数据写入指定的单元格。

下面是一些写入Excel文件的例子。

2.1   简单行式文件

假设SPL脚本文件中的A1单元格中是要写入Excel文件的序表数据。

=file("e:/scores.xlsx").xlsexport@t(A1)

表示将序表A1中的数据全部写入scores.xlsx文件的第1Sheet中,选项@t表示在第1行写入列名称,即第1行为标题行。没有此选项则不写列标题。

 

=file("e:/scores.xlsx").xlsexport@t(A1,No,Name,Class,Maths)

指定了列名参数,表示只将A1中的No,Name,Class,Maths列数据写入Excel文件。

 

2.2   写入指定 Sheet

=file("e:/scores.xlsx").xlsexport@t(A1;2)

表示将序表A1中的数据全部写入scores.xlsx文件的第2Sheet中。

 

=file("e:/scores.xlsx").xlsexport@t(A1; "School2")

表示将序表A1中的数据全部写入scores.xlsx文件Sheet名为School2的工作表中。

 

2.3   追加数据及预定格式

=file("e:/scores.xlsx").xlsexport@a(A1)

选项a表示文件已经存在时,将序表A1中的数据全部追加写入scores.xlsx文件第1Sheet末尾,同时沿用最后一行的格式。

当需要写出有预定格式的Excel数据文件时,可以事先建好文件,在文件的最后一行各列定义好本列的数据格式。在写入数据时,采用追加写入选项a,就会按照预定的格式写入数据了。

例如定义格式文件如下:

..

然后删除第2行数据,使之成为空行。追加写入时,会将最后一个非空行当做标题行,其下一行作为数据行的格式。写入数据后的文件如下:

..

 

2.4   固定行列填充

有一个行列数和单元格格式都固定的Excel文件,里面有些空单元格,需要把相关数据填写到这些格子里。这时要使用SPL中的xlsopen函数将文件读成Excel对象,再调用此对象的xlscell函数往指定格写入数据。

示例:某基金公司总公司向分公司下发了一张excel表格,要求分公司填入它的相关数据后回传给总公司,下发的excel文件如下:

..

填写数据的SPL脚本如下:


A

B

C

D

E

F

1

Mengniu Funds

2017

3

58.2

364

300

2

8.5

50

200

100

400

200

3

182.6

76.3

43.7

28.5

16.4


4

120

1.07

30

0.27

90

0.8

5

154

6

4




6

=file("e:/excel/result.xlsx")

=A6.xlsopen()

7

=C6.xlscell("B2",1;A1)

=C6.xlscell("J2",1;B1)

=C6.xlscell("L2",1;C1)

8

=C6.xlscell("B3",1;D1)

=C6.xlscell("G3",1;E1)

=C6.xlscell("K3",1;F1)

9

=C6.xlscell("B6",1;[A2:F2].concat("\t"))

=C6.xlscell("H6",1;[A3:E3].concat("\t"))

10

=C6.xlscell("B9",1;[A4:F4].concat("\t"))

=C6.xlscell("B11",1;[A5:C5].concat("\t"))

11

=A6.xlswrite(C6)




假定要填的数据已计算好(在前5行)。样表中前6个要填的单元格都是独立的,所以只能每次填一个格,第6行有连续填写的单元格,此时把待填数据拼成以\t分隔的字符串,这样可以从指定单元格开始顺序填充。数据填完以后,再把C6Excel对象写回到result.xlsx文件中。

填写完成以后的result.xlsx如下图所示:

..

 

 

更多Excel文件生成介绍请参考《SPLExcel文件读写

 

 


以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾