5.7 Format conversion - split tables with primary-sub relationship into cards

 

There are two tables with primary-sub relationship as follows:
Orders.xlsx:

imagepng

OrderDetails.xlsx:

imagepng

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:

Orders10248.xlsx:

imagepng

Orders10249.xlsx:

imagepng

Orders10250.xlsx:

imagepng

Operation steps:

1 . Create an empty card-style template.

Order.xlsx

imagepng

2 . Script:

A B
1 =T(“Orders/Orders.xlsx”)
2 =T(“Orders/OrderDetails.xlsx”)
3 =A2.align@a(A1:OrderID,OrderID)
4 =file(“Orders/Order.xlsx”).xlsopen()
5 =A2.alter(;OrderID)
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)
7 =A4.xlscell(“A7”,1;A3(#A6))
8 =file(“Orders/Order”+A6.OrderID+“.xlsx”).xlswrite(A4)

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.