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

Expand multiple N columns horizontally

 

Example

In the Excel file book1.xlsx, there is a customer's entry and exit time registration table of a certain bathroom on a certain day. Part of the data is shown in the following figure:

Calculate the situation of customers in the hourly period from 18:00 to 22:00. During this period, fill in 1 for a customer in the bathroom, otherwise leave it blank, as shown in the figure below:

Write SPL script:

A

1

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

2

=A1.xlsimport@t(Name,Sex,Enter,Leave;,2)

3

=create(${(["Male","Female"]*4).string()})

4

=time("18:00","HH:mm")

5

=A2.run(A3.insert(0),4.run(t1=elapse@s(A4,(~-1)*3600),t2=elapse@s(A4,~*3600),k=if(A2.Sex=="M",~*2-1,~*2),if(time(A2.Enter)<t2     && time(A2.Leave)>t1,A3(A2.#).field(k,1))))

6

=A1.xlscell("F3",1;A3.export())

7

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

A1 Open the book1.xlsx file as an Excel object 

A2 Read in the four columns of Name, Sex, Enter, and Leave from the first Sheet of A1, and read the data from the second row. The option @t means that the first row read as the column name.

A3 Create a table sequence consisting of 4 sets of Male and Female columns 

A4 Start time 18:00 

A5 Loop each row of A2, add a new record in A3, loop four time periods, calculate the start time t1 and end time t2 of the period, and according to the customer's gender, calculate the field number k to be filled in. If the customer is still in the bathroom at this time, Fill in 1 in the k-th field of the current line of A3 

A6 Fill in the text converted from the A3 table sequence in cell F3 of the first Sheet of A1 and then fill in the cell data in A3 into the subsequent cells of F3 in Excel. 

A7 Save A1's Excel object to the file book1.xlsx

 
 •  432 View  •