Fill in column data horizontally

Common Excel data tables have data records in the row direction. That is, the recorded data are all 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, you need to first transpose the table sequence column headings and data and then use the xlsexport@w function in SPL to save.

Example

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

Now you need to regenerate the table as shown in the figure below:

Write SPL script:

A1 Read the book1.xlsx file data into a table sequence.

A2 combines the field name sequence of A1 and the sequence of each row record value into a sequence of sequences.

A3 turns A2 rows into columns.

A4 Save A3 to the file book2.xlsx, the option @w means the object to be written out A3 is the sequence of the sequence.

If you just turn the table over, you don’t need to read it as a list of structured data. For example, write an SPL script:

A1 Read in book1.xlsx file data. Option @w means to read the data as the sequence of the sequence.

A2 Converts the sequence of A1 from row to column.

A3 Save A2 to the file book2.xlsx, the option @w means the object to be written out A2 is the sequence of the sequence.