...

Fill in the specified cell in Excel

 

There are some data that need to be filled in the specified cell in the existing Excel file, which can be done using the xlscell function in SPL. With this function, we can fill in not only a cell but also an area of contiguous cells. And the data structure to be filled in at this time is: the data of consecutive cells in the same row are separated by the Tab key (\t), and the data of adjacent rows are separated by the Enter key (\n).

Example

The experimental data summary table book1.xlsx with the sand cylinder is shown in the figure below:

..

If the Frequency data of the experiment are already available, we need to fill them in this table and write the SPL script as:

A

B

1

A1   1 1132
  A1   2 1102
  ......   
  A2   1 1035 
  ......   
  A3   10 962

2

=A1.import().group(_1)

3

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

4

for   A2

>col=int(right(A4._1,-1))*2+1

5

=A3.xlscell(cellname(5,col),1;A4.(_3).concat("\n"))

6

=A3.xlscell(cellname(15,col),1;A4.(_3).avg())

7

=file("E:/work/book1.xlsx").xlswrite(A3)

A1 The Frequency data to be filled in are: the first column is the sand cylinder number, the second column is the sequence number, and the third column is the main frequency. The ellipsis indicates that there are more similar data.

A2 Read the experimental data into a table sequence and group them by the first column.

A3 Open book1.xlsx as an Excel object.

A4 Loop through each group of experimental data.

B4 Calculate the column number “col” to be filled in with data using the following formula: col = sand cylinder number after removing the first letter*2+1.

B5 After concatenating the data in the third column of the current group with \n, fill it in the cell of Row 5 and Column col. Then fill in the next ten cells from the current cell.

B6 Fill the average value of the data in the third column of the current group in the cell of Row 15 and Column col. What is filled in is a single value, so only the current cell is filled. 

A7 Store the Excel object in A3 to book1.xlsx.

 


 
 •  244 View  •