...

Extract specified cell data in Excel

 

1. Methods

Extracting the data in specified cells, exists two scenarios:

1. The cell name is fixed. In this case, the xlscell function can be used to read directly by cell name. If the entire data area is a data table, the T function can be used to read the table sequence and then read the data by row number and column name of the record.

2. The cell is not fixed. In this case, we need to find the target cell according to certain conditions. Open the Excel file as an Excel object in SPL, loop through the cells in each row and column, read the cell data, and identify whether it is the target cell according to the conditions.

 

2. Examples

2.1 Cell name is fixed

The data of book1.xlsx is shown in the figure below, and now the task is to extract the data of B4 and C4 cells.

..

1. We can read the data directly and write the following SPL script since the name of to-be-extracted cells are already known:

A

1

=file("E:/work/book1.xlsx”).xlsopen()

2

=A1.xlscell("B4",1)

3

=A1.xlscell("C4")

A1 Open the file book1.xlsx as an Excel object.

A2 Read the value of cell B4, and parameter 1 is the number of the sheet. If it is the first sheet, this parameter can be omitted.

A3 Read the value of cell C4.

 

2. The data in this file is a data table, which can also be read as a table sequence. And the SPL script can be written as:

A

1

=T("E:/work/book1.xlsx”)

2

=A1(3).Quantity

3

=A1(3).Amount

A1 Open the file book1.xlsx as the table sequence in SPL.

A2 Retrieve the Quantity column data in the third row of the A1 table sequence.

A3 Retrieve the Amount column data in the third row of the A1 table sequence.

 

2.2 Find cells by condition

A company has multiple balance sheets, one of which is shown in the figure below:

..

Now the task is to extract the Ending balance data of 4 items, including Cash, Accounts receivable, Other receivables, Accounts payable, from each table for financial analysis. The cell positions of these four items in different tables may be different, so we need to find them by item names.

 

The SPL script is:

A

B

C

D

1

=create(Cash,'Accounts     receivable','Other   receivables','Accounts payable').insert(0)

2

=file("E:/zcfzb/zcfzb1.xlsx").xlsopen()

3

for A2.nrows

for A2.ncols

=trim(A2.xlscell(cellname(A3,B3)))

4

if  A1.fname().contain(C3)

>A1(1).field(C3,A2.xlscell(cellname(A3,B3+2)))

A1 Create a table sequence to save the extracted data and insert a new record.

A2 Open the file zcfzb1.xlsx as an Excel object.

A3 Loop through each row of the file.

B3 Loop through each column of the current row.

C3 Read the content of the current cell and trim the blanks on both ends. The cellname function converts the row and column number into the cell name.

C4D4 Identity whether the current cell content is the column name of the A1 table sequence. If so, read the second cell content on the right of it and store it in the corresponding column of the first record in A1.


 
 •  163 View  •