Excel files

esProc supports using an Excel file as the data source, importing data from it and writing data to it. Two simple examples will show how this works.

First let’s look at data importing. The target files are data.xlsx and data.xls (excel97-2000 format). They respectively contain sheet0 named employee and sheet1 named orders. The following is employee:

And this is orders:

esProc script for importing data from the Excel files:

	A
1	=file("D:/files/work/excel/data.xlsx")
2	=file("D:/files/work/excel/data.xls")
3	=A1. xlsimport@tx()
4	=A2.xlsimport@t(ORDERID,SELLERID,AMOUNT,ORDERDATE;"orders",3:-3)

A1: Define a file object; the path and name is D:/files/work/excel/data.xlsx.

A2: Define a file object; the path and name is D:/files/work/excel/data.xls.

A3: Import data from A1’s file object using the xlsimport function. @tx are function options. @t makes the first row the column names; without it, esProc will name the columns “_1,_2…” automatically. @x represents an xlsx file format; without it, esProc will try to determine the format according to the filename extension; if it fails to determine the format, it will import the file as xls format. Here without input parameters, the xlsimport function imports all data from the first sheet:

A4: Import data from A2’s file object using the xlsimport function. @t makes the first row the column names. The input parameters (ORDERID, SELLERID, AMOUNT and ORDERDATE) specifiy that the correponding columns will be imported. The parameter “orders” specifies the name of the to-be-imported sheet. Parameter “3:-3” specifies importing data from the third row to the third-last row. Similarly, you can import data from the 3^rd row to the 100^th row, which is 3:100. Note: When setting the starting row for the import, the use of @t option will position headers on the starting row. In this instance, the header row needs to be the third row; otherwise there’ll be error about the table structure. The resulting table sequence is as follows:

Then let’s look at how to write data into an Excel file. Suppose the results of A3 and A4 in the above will be written into the re-emp sheet of the result1.xlsx file, and the r-orders re-emp sheet of the result2.xls file respectively. esProc script for writing them into the Excel files:

	A
1	=file("D:/files/work/excel/data.xlsx")
2	=file("D:/files/work/excel/data.xls")
3	=A1.xlsimport@tx()
4	=A2.xlsimport@t(ORDERID,SELLERID,AMOUNT,ORDERDATE;"orders",3:-3)
5	=file("D:/files/work/excel/result1.xlsx")
6	=file("D:/files/work/excel/result2.xls")
7	=A5.xlsexport@tx(A3,EID:EMPLOYEEID,NAME;"r-emp")
8	=A6.xlsexport (A4;"r-orders")

A5, A6: Define two file objects as required.

A7: Export the result of A3 to A5’s file object using xlsexport function. @tx options mean the exported file format is xlsx and the first exported row is the field names. The input parameter A3 is the to-be-exported table sequence. The parameters – EID:EMPLOYEEID and NAME – specify the to-be-exported fields, in which EID:EMPLOYEEID means EID field will be renamed EMPLOYEEID when exported. Parameter "r-emp" specifies the name of the exported sheet. Here’s the exported Excel file:

A8: Export the result of A4 to A6’s file object using xlsexport function. Without the @tx options, the exported file format will be xls determined by the filename extension and the first row won’t be exported. The input parameter A4 is the to-be-exported table sequence. All fields will be exported as none is specified. The parameter "r-orders" specifies the name of the to-be-exported sheet. Here’s the exported Excel file:

esProc also support exporting data from cursor to an Excel file. For example:

	A
1	=file("D:/files/work/excel/data.xlsx")
2	=demo.cursor("select * from EMPLOYEE")
3	=A5.xlsexport@txs(A2;"employee2")

Since cursor is often used to handle a great amount of data, xlsexport () function uses @s option to handle the data export in a stream style. But as a data import will precede the export to an Excel file, the to-be-exported xlsx file shouldn’t be too large. Besides, Excel imposes a restriction on the number of records written from an Excel file to an Excel sheet, which is 1000,000. Once the maximum is reached, the export will stop automatically.

esProc also provides the xo.xlscell(a:b,s,t) function to update a sheet named s with string t starting from cell c. The text of t is separated by Tab to represent multiple columns, or by carriage returns to represent multiple rows. For example:

	A
1	=file("D:/files/work/excel/data.xls")
2	=A1.xlsopen()
3	=A2 .xlscell("A1":"employee","COL1\tCOL2\nV1\tV2")

A3 modifies page employee of the data.xls file starting from cell A1. The string t includes tabs and carriage returns. After execution, values of A1, B1, A2, and B2 are all changed: