It is convenient to read and write Excel files with esProc. We’ll illustrate this through an example:Example description: Read data of orders and sellers’ information list from data.xlsx, compute sales amount of every department according to the two sheets, and enter the result into result.xlsx.
Original Excel File: data.xlsx contains two sheets. The data of orders is shown in the following figure:
Sellers'information list is as follows:
A1-A2：Read the first sheet, data of orders, and the second sheet, sellers’ information list, respectively from the excel file, data.xlsx, and stores them in cell A1 and A2 in the form of table sequence.
A3-A4：First, make a left join of data of orders and seller’s information list according to employee ID numbers, then seek the sales amount of each department by grouping and summarizing. We won’t discuss the algorithm here for it is not important in this example.
A5：Store the result in result.xlsx.
- Column name
Therefore, the algorithm that follows can access data in A1 using column names directly.
- Sheet name
The same will do when writing a result into a file. Say, we want to export result in A4 to a sheet named summary, the code is:
- Scope of data
Importxls function defines the scope of row numbers waiting to be read. Say, reading from the 4th row, which could be written as:
Reading from the 4th row to 1000th row, which could be written as:
Column numbers for reading can also be defined. Say, three columns OrderID, SellerId and Amount are to be read, the code for this could be:
Or read by column numbers:
With for loop statement, esProc’s can read and write Excel files in batches.
With parameters and macros, esProc makes computations based on Excel data source more flexible.
By providing computations of multiple data sources, esProc is able to do hybrid computation of database, text files and Excel, as well as data migration.