5.7 Format conversion - split tables with primary-sub relationship into cards
There are two tables with primary-sub relationship as follows:
Now we need to associate them by OrderID, and generate one card with primary-sub relationship for each order, with OrderID as the file name, as shown below:
1 . Create an empty card-style template.
2 . Script:
|6||for A1||>A4.xlscell(“B1”,1;A6.OrderID),A4.xlscell(“E1”,1;A6.OrderDate),A4.xlscell(“B2”,1;A6.CustomerID), A4.xlscell(“E2”,1;A6.EmployeeID),A4.xlscell(“B3”,1;A6.Consignee),A4.xlscell(“B4”,1;A6.ShipAddress)|
A1: Read the data of Orders/Orders.xlsx.
A2: Read the data of Orders/OrderDetails.xlsx.
A3: Align A2 to A1, @a means many-to-one, and the associated column is OrderID.
A4: Open the empty template file Orders/Order.xlsx.
A5: Delete the column OrderID from A2, because this column does not need to be displayed in the sub table when outputting.
A6: Loop by primary table A1.
B6: Write the primary table data to the corresponding position of the A4 template.
B7: Write the sub-table data at the corresponding position in A3 to the cell starting with A7 of A4 template.
B8: Output the final generated primary-sub card-style table to the file named with the order number.