esProc can import the data from and write them into Excel files by making the Excel one of its various datasources. Two simple examples will show how this works.
First let’s look at the data importing. The target files are data.xlsx and data.xls (excel97-2000 format) and have the same content. sheet0 is named employee and sheet1 is named orders.
The following is employee:
And this is orders:
esProc script for importing the data from the Excel files:
| A | |
| 1 | =file(“D:/files/work/excel/data.xlsx”) |
| 2 | =file(“D:/files/work/excel/data.xls”) |
| 3 | =A1.importxls@tx() |
| 4 | =A2.importxls@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 the data from A1’s file object using importxls function. @tx is function options. t represents making the first row the column names; without it, esProc will name the columns “_1,_2…” automatically. x represents that the file format is xlsx; without it, esProc will try to determine the format according to the filename extension; but if the format can’t be determined, the file will be imported in xls format. Without input parameters, importxls function will import all data from the first sheet, as shown below:
A4: Import A2’s file object using importxls function. @t represents making the first row the column names. The input parameters – ORDERID, SELLERID, AMOUNT and ORDERDATE – represent that only these specified columns will be imported. The parameter “orders” specifies the name of the to-be-imported sheet. Parameter “3:-3” represents importing data from the third row to the third-last row. Similarly, you can import data from the 3rd row to the 100th row, which is 3:100.
The result of data importing 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 script above need to be written into result1.xlsx, a sheet named re-emp, and result2.xls, a sheet named r-orders, 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.importxls@tx() |
| 4 | =A2.importxls@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.exportxls@tx(A3,EID:EMPLOYEEID,NAME;”r-emp”) |
| 8 | =A6.exportxls (A4;”r-orders”) |
A5, A6: Define two file objects as required.
A7: Export the result of A3 to A5’s file object using exportxls function. @tx option means 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 – are specified to-be-exported fields, in which EID:EMPLOYEEID means field EID will be renamed EMPLOYEEID when exported. Parameter “r-emp” specifies the name of the exported sheet. The exported Excel file is as follows:
A8: Export the result of A4 to A6’s file object using exportxls function. Without the @tx option, 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. The exported Excel file is as follows:
