Generate continuous intervals

Example1

There is a column of serial number No in the Excel file book1.xlsx, as shown in the figure below:

Use two numbers in each row to convert into a continuous numbering interval, as shown in the following figure:

Write SPL script:


A

1

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

2

=A1.news((a=No.split("-").(int(~)),to(a(1),a(2)));~:No)

3

=T("E:/work/book2.xlsx",A2)

A1 Read in book1.xlsx data 

A2 Loop through each line of A1, split the No field with a minus sign and convert it into an integer sequence and assign it to the variable a. Then a (1) and a(2) form a continuous sequence of numbers. The news function means that the number sequence is converted to Multiple rows, the numeric member is the value of No in the new row 

A3 Save A2 to the file book2.xlsx

 

Example2

The A and B columns of the Excel file book1.xlsx are natural numbers, which represent the start and end points of the sequence, as shown in the following figure:

Use columns A and B to generate a text composed of a sequence of natural numbers, and fill in column D, as follows:

Write SPL script:


A

1

=clipboard().import@t()

2

=A1.(to(start,end).concat@c())

3

=clipboard(A2.concat("\n"))

A1 Read the data in the clipboard 

A2 Loop through each row of A1, create a new sequence of numbers from start and end, and then use commas to concatenate the sequence into the text. 

A3 Concatenate each row in A2 into text with \n, and put it into the clipboard 

First open the book1.xlsx file, select the A1:B4 area data, and Ctrl+C to copy the data. Go back to esProc and run the SPL script. After running it, go back to Excel, select cell D2, and Ctrl+V to paste the results in the clipboard.