Extract mixed-style structure in Excel

In some Excel files, the data is presented in the form of a main and sub-table, the main table information is in free format, and the sub-table information is a row structure with one record per row and the number of rows is indefinite. When extracting data, you want to append the main table information to each sub-table record.

Example

The data in the order detail list order.xlsx is shown in the figure below:

Write SPL script:

A1 Open the order.xlsx file as an Excel object.

A2 Read the row data starting from row 5 in the first sheet of A1, and filter out rows where Model is empty. The option @t means that the first row is column headings.

A3 Add 4 columns to A2: The value of the Name column is the content of cell D2, the value of the Phone column is the content of cell F2, the value of the Email column is the content of cell D3, and the value of the Address column is the content of cell D4.

The final result of grid A3 is shown in the figure below: