I am very new to programming in general and am seeking some guidance on how to achive what I need to.
I need to import an Excel file (Already doing so using DocumentFormat.OpenXml) and use the data in the Excel file to create an order header and detail JSON payload for our ERP. The ERP accepts the JSON data via Web API.
The Payload needs to look like this:
{
"CustomerNumber": "1400",
"OrderDescription": "Order Entry",
"OrderDate": "2020-01-15",
"InvoiceWillBeProduced": true,
"InvoiceNumber" : "IN123456",
"OrderDetails": [
{
"LineType": "Item",
"Item": "A1-105/0",
"Location": "1",
"QuantityOrdered": 20,
"QuantityShipped": 20,
"UnitPrice": 100.50,
"DiscountAmt": 10.05
},
{
"LineType": "Item",
"Item": "A1-103/0",
"Location": "1",
"QuantityOrdered": 10,
"QuantityShipped": 10,
"UnitPrice": 50.50,
"DiscountAmt": 5.05
}
]
}
The biggest concern is the OrderDetails section where I would need multiple items to be attached to one Order Header.
The Excel file contains columns InvoiceNumber,Customer,Date,ItemCode,QuantityOrdered,Price,Discount
Each customer might have multiple records, but the one unique value per order is the Invoice Number. Therefor I was considering somehow grouping the data by invoice number and add the details in. However, I have never done this before and need some guidance, please.
Thank you in advance.
What I have tried:
So far I have tried dumping the Excel data into a DataTable and iterating over it with a foreach loop. This works great if there is only one order detail item, but not when there are multiple order detail items