5.6 Split by row - split multiple cards to make one card generate one file

 

There is a multi-card style table Cards.xlsx as follows:

imagepng

Now we need to split it to make one card generate one file, with person’s name as the file name. The splitting results are as follows:
Andrew.Fuller.xlsx:

imagepng

Janet.Leverling.xlsx:

imagepng

Margaret.Peacock.xlsx:

imagepng

Nancy.Davolio.xlsx:

imagepng

Operation steps:

1 . Create an empty card-style file Card.xlsx, as the initial file format:

imagepng

2 . Script:

A B C
1 =file(“Card.xlsx”).xlsopen()
2 =file(“Cards.xlsx”).xlsopen()
3 [B,D,B,D,B,B,B,B] [1,1,2,2,3,4,5,6,7]
4 =A3.(~/B3(#))
5 for =A3.(~/B3(#)).(A2.xlscell(~))
6 if B5(1)=="" break
7 for A4 =A1.xlscell(B7,1;B5(#B7))
8 =file(B5(3)+“.”+B5(4)+“.xlsx”).xlswrite(A1)
9 >B3=B3.(~+8)

A1: Open the empty card-style template Card.xlsx.
A2: Open the multi-card style table Cards.xlsx.
A3: List the column number of the cells to be read.
B3: List the row number of the cells to be read.
A4: Piece together the cell names of the first card.
A5: Loop.
B5: Piece together the cell names of the current card, and read the data in it.
B6: Exit loop if cards have been read.
B7: Loop through A4 and write the data in B5 into A1 one by one.
B8: Write A1 to the file named after the current card.
B9: Plus 8 after row number, because one card has 8 rows.