"[链接]1.2.1 结构化表格结构化 Excel 表格比较规范， SPL 用 xlsimport/xlsexport 函数读写。比如：ordersNT.xlsx 中第 1 个 sheet .."

Mars 开封
乾学院 6 号会员
852 浏览 • 2 年前

1.2 Excel 文件

图书

1.2.1 结构化表格

结构化 Excel 表格比较规范， SPL 用 xlsimport/xlsexport 函数读写。

比如：ordersNT.xlsx 中第 1 个 sheet 的各列的业务意义依次是订单 ID、客户编号、销售 ID、订单金额、订单日期。部分数据如下：

	A	B	C	D	E
1	26	TAS	1	2142.4	2009/8/5
2	33	DSGC	1	613.2	2009/8/14
3	84	GC	1	88.5	2009/10/16
4	133	HU	1	1419.8	2010/12/12
5	32	JFS	3	468	2009/8/13
6	39	NR	3	3016	2010/8/21
7	43	KT	3	2169	2009/8/27

将该表按客户编号的字母顺序从小到大的排序，相同的客户编号再按订单金额从大到小排序，最后保持原格式存入新 Excel。

SPL 脚本：

	A
1	=file(“D:/data/ordersNT.xlsx”).xlsimport()
2	=A1.sort(_2,-_4)
3	=file(“D:/data/ordersNT_sort.xlsx”).xlsexport(A2)

A1、A3：读写 Excel 的第 1 个 Sheet。如果要读入指定 Sheet，可用

xlsimport(;Sheet序号或Sheet名)

如果要写入指定的 Sheet，可用

xlsexport(A2; Sheet序号或Sheet名)

SPL 也可以处理带列名（标题） 的结构化表格，方法与文本文件类似。比如 orders.xlsx 部分数据如下：

	A	B	C	D	E
1	OrderID	Client	SellerId	Amount	OrderDate
2	26	TAS	1	2142.4	2009/8/5
3	33	DSGC	1	613.2	2009/8/14
4	84	GC	1	88.5	2009/10/16
5	133	HU	1	1419.8	2010/12/12
6	32	JFS	3	468	2009/8/13
7	39	NR	3	3016	2010/8/21
8	43	KT	3	2169	2009/8/27

对该文件进行排序，结果带列名写入新文件：

	A
1	=file(“D:/data/orders.xlsx”).xlsimport@t()
2	=A1.sort(Client,-Amount)
3	=file(“D:/data/orders_sort.xlsx”).xlsexport@t(A2)

有时候表格的前几行无用需要跳过，比如下面的数据：

	A	B	C	D	E
1	orders from 2009 to 2010
2
3
4	OrderID	Client	SellerId	Amount	OrderDate
5	26	TAS	1	2142.4	2009/8/5
6	33	DSGC	1	613.2	2009/8/14
7	84	GC	1	88.5	2009/10/16
8	133	HU	1	1419.8	2010/12/12
9	32	JFS	3	468	2009/8/13

从第 4 行开始读取：

=file("D:/data/ orders.xlsx").xlsimport@t(;,4)

有时候我们需要在原表格后面追加结构相同的新数据，可使用 @a 选项：

=file("D:/data/orders_sort.xlsx").xlsexport@at(A2)

如果原表格的最后一个有内容的行设置了外观属性，则追加的数据会继承该行的风格。比如原表格 D 列显示格式为 #,##0.00，E 列风格为 mmm-dd-yyyy。如下表：

	A	B	C	D	E
1	OrderID	Client	SellerId	Amount	OrderDate
2	26	TAS	1	2,142.40	Aug-05-2009
3	33	DSGC	1	613.20	Aug-14-2009
4	84	GC	1	88.50	Oct-16-2009

则追加数据后，结果如下表：

	A	B	C	D	E
1	OrderID	Client	SellerId	Amount	OrderDate
2	26	TAS	1	2,142.40	Aug-05-2009
3	33	DSGC	1	613.20	Aug-14-2009
4	84	GC	1	88.50	Oct-16-2009
5	133	HU	1	1,419.80	Dec-12-2010
6	32	JFS	3	468.00	Aug-13-2009

原表格最后一个有内容的行之后的第一个空行设置了风格属性，则追加的数据会继承该行的风格属性。利用这个特点，我们可以实现按指定格式从无到有的输出数据。比如先建立空白 Excel，设置第 2 行的 D 列显示格式为 #,##0.00，E 列显示格式为 mmm-dd-yyyy。

	A	B	C	D	E
1	OrderID	Client	SellerId	Amount	OrderDate
2

再向该空白表格追加数据，结果如下：

	A	B	C	D	E
1	OrderID	Client	SellerId	Amount	OrderDate
2	133	HU	1	1,419.80	Dec-12-2010
3	32	JFS	3	468.00	Aug-13-2009

1.2.2 二维字符串序列

有些 Excel 的格式不够规范，没有明确的列属性，这种情况下可当作二维字符串序列来处理。

比如下面的 key-value 数据：

	A	B	C	D	E
1			A=123	B=456	C=789
2	A=678	B=783	A=900	U=89
3	A=330	Y=67	B=890	C=311	F=19

现在要将上述数据按 key 和 value 拆分为 2 列，并按 key 和 value 排序，最后写入新的 Excel。结果如下：

	A	B
1	A	123
2	A	330
3	A	678
4	A	900
5	B	456
6	B	783
7	B	890
8	C	311
9	C	789
10	F	19
11	U	89
12	Y	67

SPL 脚本：

	A
1	=file(“D:/data/keyvalue.xlsx”).xlsimport@w()
2	=A1.conj().select(~)
3	=A2.(~.split(“=”))
4	=A3.sort(~(1),~(2))
5	=file(“D:/data/keyvalue_result.xlsx”).xlsexport@w(A4)

A1：读入 Excel，@w 表示读为二维字符串序列，整体是大序列，每行既是大序列的成员，同时也是个小序列；行内每格是小序列的成员。

A2：将二维序列合并为一维序列，并去除可能的空白格，比如 A1、B1、E2。

A3：将字符串序列拆分为 key 和 value。

A4：按 key、value 排序。

A5：将结果写入新 Excel，@w 表示写入序列的序列。

1.2.3 单元格

有时候我们要精细地读写 Excel 中的某些单元格。

比如：下面的表格在第 1 行有编辑者和编辑日期。

	A	B	C	D	E
1	editor:emily				date:Dec-30-2011
2	OrderID	Client	SellerId	Amount	OrderDate
3	26	TAS	1	2,142.40	Aug-05-2009
4	33	DSGC	1	613.20	Aug-14-2009
5	84	GC	1	88.50	Oct-16-2009
6
7

现在要将编辑者和编辑日期复制到第 7 行对应的位置上，结果如下：

	A	B	C	D	E
1	editor:emily				date:Dec-30-2011
2	OrderID	Client	SellerId	Amount	OrderDate
3	26	TAS	1	2,142.40	Aug-05-2009
4	33	DSGC	1	613.20	Aug-14-2009
5	84	GC	1	88.50	Oct-16-2009
6
7	editor:emily				date:Dec-30-2011

SPL 脚本：

	A	B
1	=file(“D:/data/cell.xlsx”)
2	=A1.xlsopen()
3	=str=A2.xlscell(“A1”)	=A2.xlscell(“A7”;str)
4	=str=A2.xlscell(“E1”)	=A2.xlscell(“E7”;str)
5	=A1.xlswrite(A2)

A2：以对象方式打开 Excel 文件。

A3：读出 Excel 对象的 A1 格，赋予变量 str。默认从第 1 个 Sheet 读，如果想读取指定 Sheet 里的 A1 单元格，可用

A2.xlscell("A1",Sheet序号或Sheet名)

B3：将 A1 格内容写入 A7 格。类似地，如果想在指定 Sheet 里写入 A7 单元格，可用

A2.xlscell("A7",Sheet序号或Sheet名;str)

A4-B4：读出 E1 格内容，并写入 E7 格

A5：将 Excel 对象写入 Excel 文件。

上面例子中，需要读入的 A1-E1 是连续单元格，需要写入的 A7-E7 也是连续单元格，对于这种连续单元格的读写，SPL 可以用更简化的代码来实现：

	A
1	=file(“cell.xlsx”)
2	=A1.xlsopen()
3	=arry=A2.xlscell@w(“A1”:“E1”)
4	=A2.xlscell(“A7”:“E7”;arry)
5	=A1.xlswrite(A2)

A3：以序列的格式读出连续单元格

A4：向连续单元格写入序列，序列的每个成员对应一个单元格。既可以用序列向连续单元格写入数据，也可以用 TAB（\t）或回车（\r）分隔的字符串，其中 TAB 表示横向（列）分隔，回车表示纵向（行）分隔。

1.2.4 多 Sheet 处理

使用 Excel 对象不仅可用读写单元格，也可以处理多个 Sheet。

某 Excel 用多个 Sheet 存储订单表格，每个 Sheet 的格式相同，但数量和名称不定。现在要将这些订单整理到新 Excel 中，每个 Sheet 存一年的数据。

SPL 脚本：

	A
1	=file(“orders_sheet.xlsx”).xlsopen()
2	=A1.(stname).(A1.xlsimport@t(;~)).conj()
3	=A2.group(string(year(OrderDate)):name;~:content)
4	=file(“orders_result.xlsx”).xlsopen@w()
5	=A3.(A4.xlsexport@t(~.content;string(~.name)))
6	=A4.xlsclose()

A1：以对象方式打开源 Excel 文件。

A2：遍历每个 Sheet，读取每个 Sheet 的订单，再合并所有订单。A1.(stname) 表示从 Excel 对象 A1 中取出所有的 Sheet 名。

A3：将订单按年份分组。

A4：以对象方式打开目标 Excel 文件。@w 表示写模式，文件不存在时将新建文件。

A5：遍历 A3 的每组（每年）订单，依次写入到 A4 的新 Sheet 中。

A6：@w 模式打开的 Excel 对象必须用函数 xlsclose 关闭。

帮你早下班 - esProc 桌面版与 Excel 数据处理
 1.1 文本文件
 1.3 文件和目录

帮你早下班 - 桌面与 excel 数据处理例程(180)

	A	B
1	A	123
2	A	330
3	A	678
4	A	900
5	B	456
6	B	783
7	B	890
8	C	311
9	C	789
10	F	19
11	U	89
12	Y	67

	A	B
1	A	123
2	A	330
3	A	678
4	A	900
5	B	456
6	B	783
7	B	890
8	C	311
9	C	789
10	F	19
11	U	89
12	Y	67

	A	B
1	A	123
2	A	330
3	A	678
4	A	900
5	B	456
6	B	783
7	B	890
8	C	311
9	C	789
10	F	19
11	U	89
12	Y	67