RaqForum 22 No.
1 Reply  •  381 View  • 
     ...

Fixed format table alignment summary

 

There are N Excel files with exactly the same format. These files should be aggregated into one file. The value of the number cell is the sum of the number cells with the same name in each file.

 

Example

The head office has received balance sheets from various branches. The table of a branch is as shown in the figure below (there are 37 rows in total, and only 14 rows are listed in the figure):

Now we need to use the forms of each branch company to summarize the balance sheet of the head office.

 

Write SPL script:

A

B

C

1

=directory@p("e:/zcfzb/zc*.xlsx")

2

=A1.(file(~).xlsopen())

3

=to(4,37)

[B,C,E,F]

=A3.(B3.(~/A3.~)).conj()

4

for   C3

>v=null

5

for   A2

>v+=number(B5.xlscell(A4,1))

6

>A2(1).xlscell(A4,1;string(v))

7

=file("e:/zcfzb/total.xlsx").xlswrite(A2(1))

A1 List all the file names of the balance sheet beginning with zc to be summarized in the folder, the option @p means to list the full path

A2 Open the file listed in A1 as an Excel object

A3 Specify the row number range of the number cells to be summarized 4-37

B3 Specify the column numbers B, C, E, F of the number cells to be summarized

C3 Use A3 row number and B3 column number to spell out the names of all number cells to be summarized

A4 Loop all the cells to be summarized in C3

B4 Define summary value variable v

B5 Circulate balance sheets of all branches

C5 Read the value of the current summary cell from the balance sheet of the current branch, convert it to a value and add it to v

B6 Save the accumulated v in the balance sheet of the first branch

A7 Save the balance sheet of the first branch to the balance sheet of the head office total.xlsx

 
 •  381 View  •