Excel文件是很常见的文件格式，读写时需要注意如下一些问题：

1、  标题行：此行是各列的名称，也可以没有标题行，第一行就是数据记录。

2、  大文件：文件数据很多，不能全部装入内存中。

3、  工作表：读写文件中哪一个Sheet工作表

4、  部分行列：只读写某些列的数据，或只读某些行的数据。

SPL在读写 Excel 文件时，可以很方便地处理这几个问题。

 

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   去除列值两端空白

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

增加的选项n表示读入时去除字符串两端的空白字符，空串则读成null

 

1.6   读成两层序列

有时候，文件数据不适合直接读成序表，需要先读成两层序列来进行处理或运算，即第一层是以行为成员组成序列，行成员又是由同行内各单元格值为成员组成的第二层序列。

比如下面这个文件，它是每列形成一个记录：

=file("e:/courses.xlsx").xlsimport@w()

选项@w表示将数据读成上述的两层序列。读入以后结果如下图所示：

之后再经过SPL转置函数处理，就可以转换成常用的序表数据。

 

1.7   读成 TAB 串

=file("e:/courses.xlsx").xlsimport@s()

选项@s表示将所有要读的数据读成一个字符串，各行数据间用换行符\n分隔，行内单元格数据间用Tab(\t)分隔。

 

1.8   复杂表头

有时候，Excel文件的表头格式并不简单，往往是由多行构成的，比如有表标题、项目名称、填表人、填写日期、页码等信息。读取这种格式的文件时，需要跳过复杂的表头，指定从数据行的位置开始读取，然后指定数据各列的列名。

如在项目造价文件itemPrices.xlsx中，部分数据如下图：

=file("e:/itemPrices.xlsx").xlsimport(;,5)

用起始行参数5表示从文件第5行开始读。

 

1.9   大文件

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

当要读的是大文件数据时，增加选项c表示将数据读成游标，文件必须是xlsx格式。函数参数基本与小文件时相同，但在指定结束行参数时不能用负数表示倒数第几行。

 

1.10 自由格式

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

读取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单元格数据如下图所示：

 

1.11 交叉表

交叉表是统计学中常见的一种矩阵式表格，可以清晰地表达两个变量间的数量关系。交叉表数据逐行读入后，需要以某个列变量为基准，另一个变量及交叉值进行行转置；或者以某个行变量为基准，另一个变量及交叉值进行列转置。

示例：订单地区与货运方式交叉表cross.xlsx文件数据如下图所示。

读取数据的SPL脚本如下：

	A	注释
1	=file("e:/excel/cross.xlsx").xlsimport@t(;1,2)	读入文件第一个sheet，从第2行开始，第2行作列名
2	=A1.rename(#1:Type)	第一列列名改为Type
3	=A2.pivot@r(Type;Area,Amount)	对A2以Type为基准进行转置，选项@r表示列转行，转换后新的列名分别为Area、Amount

A3格的部分数据如下图所示：

 

1.12 主子表

每个sheet是一条主表记录，同时sheet中也包含N条子表记录。文件中有多少主表记录，就有多少个sheet。对这种主子表结构的数据，需要创建两个数据表分别保存主表和子表的记录。

示例：在员工信息登记表文件staff.xlsx中，每个sheet有员工信息及他的家庭成员信息，请将员工信息及家庭成员信息分别读成两个数据表。其中一个sheet如下图：

读取数据的SPL脚本如下：

	A	B	C
1	=create(IDCard,Name,Sex,Birthday,Nation,Phone,Depart,Home,Marital,Entry)
2	=create(IDCard,Name,Relation,Workplace,Phone)
3	[B4,B3,D3,F3,H3,F4,H4,B5,F5,H5]
4	=file(“e:/excel/staff.xlsx").xlsopen()
5	for A4	=A3.(A4.xlscell(~,A5.stname))	>A1.record(B5)
6		=A4.xlsimport@t(Family,Name,Relation,Workplace,Phone;A5.stname,6)
7		=B6.rename(Family:IDCard)	>B7.run(IDCard=B5(1))
8		>A2.insert@r(0:B7)

A1   创建列名分别为IDCard、Name、Sex、Birthday、Nation、Phone、Depart、Home、Marital、Entry的空序表，用于保存主表员工信息

A2   创建列名分别为IDCard、Name、Relation、Workplace、Phone的空序表，用于保存子表员工家庭成员信息

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

A4   打开Excel数据文件，生成一个Excel对象

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

B5   读取员工信息序列

C5   将B5读取的员工信息保存到序表A1

B6   从第6行开始读取员工家庭成员信息，只读指定的5列Family、Name、Relation、Workplace、Phone

B7   将B6序表的Family列改名为IDCard

C7   为B7序表的IDCard列赋值为员工信息中的IDCard

B8   将B7中的员工家庭成员信息追加到序表A2

A1读到的部分数据如下：

A2读到的部分数据如下：

 

 

更多读取用法请参考SPL资料中xlsimport和xlscell函数说明。

 

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   两层序列或 TAB 串

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

选项@w表示要写出的数据对象A1是个两层序列或TAB串。

 

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

选项p表示要写出的数据对象A1是个两层序列，并且要先将A1的行列转置后再写入文件。选项p必须与选项w同时使用。

 

2.5   大文件

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

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

当要写出游标中的大数据时，增加选项c使用流式写出大文件。参数用法与小文件相同，文件必须是xlsx格式。

 

2.6   固定行列填充

有一个行列数和单元格格式都固定的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如下图所示：

 

 

更多写入用法请参考SPL资料中xlsexport和xlscell函数说明。