MyPage is a personalized page based on your interests.The page is customized to help you to find content that matters you the most.


I'm not curious

A better way to read and write Excel files in batch

Published on 22 September 14
2825
0
0
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:
A better way to read and write Excel files in batch - Image 1
Sellersâ information list is as follows:
A better way to read and write Excel files in batch - Image 2
esProc code:
A better way to read and write Excel files in batch - Image 3
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.
result.xlsx
A better way to read and write Excel files in batch - Image 4
Code interpretation
  • Column name
import xls uses function option @t, which shows that the first row of sheet is regarded as column name of esProc table sequence. For example, click cell A1 and the values of its variables can be seen:
A better way to read and write Excel files in batch - Image 5
Therefore, the algorithm that follows can access data in A1 using column names directly.
  • Sheet name
The above code reads data according to the sheetâs serial number, but sometimes, clients prefers sheet name. This demand can be realized in esProc. For instance, sales is the sheet name for data of orders. In this case, reading by names will be realized only by changing the sheetâs serial number in A1 into sheet name directly. See below:
file("E:/data.xlsx").importxls@t(;sales)
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:
file("E:/result.xlsx").exportxls@t(A4;summary)
  • Scope of data
The above code is to read data from the first row of sheet to the end by default. In reality, however, we are often confronted with cases that a portion of the data is to be read. As shown in the following figure:
A better way to read and write Excel files in batch - Image 6
Importxls function defines the scope of row numbers waiting to be read. Say, reading from the 4th row, which could be written as:
file("E:/data.xlsx").importxls@t(;1,4)
Reading from the 4th row to 1000th row, which could be written as:
file("E:/data.xlsx").importxls@t(;1,4:1000)
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:
file("E:/data.xlsx").importxls@t(OrderID,SellerId,Amount;1)
Or read by column numbers:
file("E:/data.xlsx").importxls@t(#1,#3,#4;1)
Extension:
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.





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:

A better way to read and write Excel files in batch - Image 1

Sellersâ information list is as follows:

A better way to read and write Excel files in batch - Image 2

esProc code:

A better way to read and write Excel files in batch - Image 3

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.

result.xlsx

A better way to read and write Excel files in batch - Image 4

Code interpretation

  • Column name


import xls uses function option @t, which shows that the first row of sheet is regarded as column name of esProc table sequence. For example, click cell A1 and the values of its variables can be seen:

A better way to read and write Excel files in batch - Image 5

Therefore, the algorithm that follows can access data in A1 using column names directly.

  • Sheet name


The above code reads data according to the sheetâs serial number, but sometimes, clients prefers sheet name. This demand can be realized in esProc. For instance, sales is the sheet name for data of orders. In this case, reading by names will be realized only by changing the sheetâs serial number in A1 into sheet name directly. See below:

file("E:/data.xlsx").importxls@t(;sales)

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:

file("E:/result.xlsx").exportxls@t(A4;summary)

  • Scope of data


The above code is to read data from the first row of sheet to the end by default. In reality, however, we are often confronted with cases that a portion of the data is to be read. As shown in the following figure:

A better way to read and write Excel files in batch - Image 6

Importxls function defines the scope of row numbers waiting to be read. Say, reading from the 4th row, which could be written as:

file("E:/data.xlsx").importxls@t(;1,4)

Reading from the 4th row to 1000th row, which could be written as:

file("E:/data.xlsx").importxls@t(;1,4:1000)

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:

file("E:/data.xlsx").importxls@t(OrderID,SellerId,Amount;1)

Or read by column numbers:

file("E:/data.xlsx").importxls@t(#1,#3,#4;1)

Extension:

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.

This blog is listed under Development & Implementations and Data & Information Management Community

Related Posts:
Post a Comment

Please notify me the replies via email.

Important:
  • We hope the conversations that take place on MyTechLogy.com will be constructive and thought-provoking.
  • To ensure the quality of the discussion, our moderators may review/edit the comments for clarity and relevance.
  • Comments that are promotional, mean-spirited, or off-topic may be deleted per the moderators' judgment.
You may also be interested in
Awards & Accolades for MyTechLogy
Winner of
REDHERRING
Top 100 Asia
Finalist at SiTF Awards 2014 under the category Best Social & Community Product
Finalist at HR Vendor of the Year 2015 Awards under the category Best Learning Management System
Finalist at HR Vendor of the Year 2015 Awards under the category Best Talent Management Software
Hidden Image Url

Back to Top