...

Fill in column data horizontally

 

The data records are generally stored in the row direction of Excel data tables, that is, all data of one record are in the same row. But there are also some special Excel tables where the data records are located in the column direction. To generate this kind of table, we need to first transpose the table sequence column headers and data and then use the xlsexport@w function in SPL to store the data.

 

Example

There is a financial data table book1.xlsx, and the data is shown in the figure below:

..

Now the task is to regenerate a table as shown in the figure below:

..

The SPL script is:

A

1

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

2

=[A1.fname()]|A1.(~.array())

3

=transpose(A2)

4

=file("E:/work/book2.xlsx").xlsexport@w(A3)

A1 Import the data of book1.xlsx into a table sequence.

A2 Concatenate the field name sequence of A1 and the sequence of record values in each row as a sequence of sequences.

A3 Transpose A2 from rows into columns.

A4 Store A3 to book2.xlsx, and the @w option means the object A3 to be exported is a sequence of sequences.

 

If we just want to transpose the table, there is no need to read it as structured data of table sequence. For example, the SPL script can be written as:

A

1

=file("E:/work/book1.xlsx").xlsimport@w()

2

=transpose(A1)

3

=file("E:/work/book2.xlsx").xlsexport@w(A2)

A1 Import the data of book1.xlsx. @w option means to read the data as a sequence of sequences.

A2 Transpose the sequence of A1 from row to column.

A3 Store A2 to book2.xlsx, and the @w option means the object A2 to be exported is a sequence of sequences.


 
 •  247 View  •