1.2.1 Structured tables

The structured Excel table is relatively regular, and SPL reads and writes it with the xlsimport/xlsexport function.

For example: the business meanings of every column of the first sheet in ordersNT.xlsx sequentially are: order ID, customer NO, sales ID, order amount and order date. Part of the data is as follows:

	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

The following is the partial result after the table is processed in a way that first sort the orders in ascending order alphabetically by customer NO, and then sort the orders with same customer NO in descending order by order amount, and finally save it to a new Excel in the original format:

SPL script:

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

A1, A3: read and write the first sheet of Excel. If you want to read the specified sheet, you can use:

xlsimport(;Sheet number or Sheet name)

If you want to write to the specified sheet, you can use:

xlsexport(A2; Sheet number or Sheet name)

SPL can also process the structured table with column name (title) in a similar way to process text files. For example, partial data of orders.xlsx is as follows:

	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

Sort this file and write the result to a new file together with column name:

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

Sometimes the first few rows of the table are useless and need to be skipped, for example:

	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

Reading from row 4:

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

Sometimes we need to append new data with the same structure to the original table. In this case, we can use the option @a:

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

If the appearance attribute is set for the last non-blank row of the original table, the appended data will inherit the style of this row. For example, the display format of column D of original table is #,##0.00, and the style of column E is mmm-dd-yyyy, as shown in the table below:

	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

After appending the data, the result is as follows:

	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

The style attribute has been set for the first blank row after the last non-blank row of original table, the appended data will inherit the style attribute of this row. Using this feature, we can achieve the data output from scratch in the specified format. For example, first create a blank Excel, and set the display format of column D in row 2 to #,##0.00, and that of column E to mmm-dd-yyyy.

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

Then append data to the blank table, and the result is as follows:

	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 Two-dimensional string sequence

Some Excel tables are not regular in format and do not have clear column attributes. In this case, they can be processed as a two-dimensional string sequence.

For example, the following key-value data:

	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

Now, we want to split the above data into 2 columns by key and value, and sort them by key and value, and finally write them to a new Excel. The result is as follows:

	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 script:

	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: read in the Excel, @w means reading as a two-dimensional string sequence. The whole is a large sequence, and each row is not only a member of the large sequence, but also a small sequence; each cell in the row is a member of the small sequence.

A2: Concatenate the 2D sequence into 1D sequence and remove possible blank cells such as A1, B1, E2.

A3: Split the string sequence into key and value.

A4: Sort by key and value.

A5: Write the result to a new Excel, @w means writing a sequence of sequences.

1.2.3 Cells

Sometimes we need to read and write the Excel cells accurately.

For example: the table below has the editor and edit date in row 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

Now we want to copy the editor and edit date to the corresponding position on row 7, the result is as follows:

	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 script:

	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: Open the Excel file as an object.

A3: Read the cell A1 and assign the variable str. By default, it is to read from the first sheet. If you want to read the cell A1 in the specified sheet, you can use:

A2.xlscell("A1",Sheet number or Sheet name)

B3: Write contents of cell A1 to cell A7. Similarly, if you want to write to cell A7 of the specified sheet, you can use:

A2.xlscell("A7",Sheet number or Sheet name;str)

A4-B4: Read the contents of cell E1, and write to cell E7.

A5: Write Excel object to Excel file.

In the above example, A1-E1 that need to be read are consecutive cells, and A7-E7 that need to be written are also consecutive cells. For the reading and writing of such consecutive cells, SPL can implement with a more simplified code:

	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: Read consecutive cells in sequence format

A4: Write the sequence to consecutive cells, and each member of the sequence corresponds to one cell. You can either use a sequence to write data to consecutive cells or use a string separated by TAB (\t) or Enter (\r), where TAB means horizontal (column) separation and Enter means vertical (row) separation.

1.2.4 Multi-sheet processing

Using Excel objects, not only can read and write cells but also process multiple sheets.

A certain Excel uses multiple sheets to store the order tables. Each sheet has the same format, but the number and name are not the same. Now we want to sort these orders into a new Excel to make each sheet store one year’s data.

SPL script:

	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: Open the source Excel file as an object.

A2: Traverse each sheet, read the order of each sheet, and concatenate all the orders. A1.(stname) means taking out all sheet names from the Excel object A1.

A3: Group the orders by year.

A4: Open the target Excel file as an object. @w means write mode, and a new file will be created if the file does not exist.

A5: Traverse each group (yearly) orders of A3 and write them to the new sheet of A4 in turn.

A6: The Excel object opened in @w mode must be closed with the function xlsclose.

---

esProc Desktop and Excel Processing
1.1 Text file
1.3 Files and directories