...

Insert a row after a specific row

 

Example

There is data in the Excel file book1.xlsx as shown in the figure below:

Insert a line after the number 100, and fill in 001, 002, ... etc.

Write SPL script:

A

1

=file("e:/work/book1.xlsx").xlsimport()

2

=A1.group@i(~[-1].#1==100)

3

=A2.(~|new(string(#,"000"))).conj()

4

=file("e:/work/book2.xlsx").xlsexport(A3)

A1 Read in book1.xlsx file data

A2 Group A1. When the first column of the previous row is 100, a new group is added.

A3 Loop each group in A2, insert a record at the end of the group, record the value in the first column of the current group number #, formatted as a 3-character text

A4 Save the results in A3 to the file book2.xlsx

Finally, book2.xlsx is shown in the figure below:

 
 •  344 View  •