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

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

 

1.   解析

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

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

1.1   简单行式文件

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

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

file函数的参数可以是文件的绝对路径，也可以是相对路径(相对于SPL配置文件中的主目录mainPath），xlsimport函数默认读取文件中第1个Sheet所有行列数据生成序表，选项@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)

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

 

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

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

 

1.3   读取指定 Sheet

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

表示读取名称为School2的Sheet中所有列数据。

 

1.4   读取 Sheet 信息

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

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

打开后的Excel对象如下图：

每行表示一个Sheet，stname、nrows、ncols分别表示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   创建列名为“ID、Name、Sex、Position、Birthday、Phone、Address、PostCode”的空序表

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文件解析介绍请参考《SPL：Excel文件读写》

 

2.   生成

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

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

2.1   简单行式文件

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

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

表示将序表A1中的数据全部写入scores.xlsx文件的第1个Sheet中，选项@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文件的第2个Sheet中。

 

=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文件第1个Sheet末尾，同时沿用最后一行的格式。

当需要写出有预定格式的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分隔的字符串，这样可以从指定单元格开始顺序填充。数据填完以后，再把C6的Excel对象写回到result.xlsx文件中。

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

 

 

更多Excel文件生成介绍请参考《SPL：Excel文件读写》