2
Answers

Convret XML with all nodes to Excel in C#

Amit Sharma

Amit Sharma
4y
1.1k
1
Hello Friends,
I am using below code to convert xml to excel But not able to bring complete xml into excel. There are only first level nodes date is coming in excel without child nodes. Can anybody help in this case.
 
 
This Is xml File: 
 
<Invoices>
<Invoice>
<Contact>
<ContactID>79475cda-87f1-459e-8709-fbf77cea6835</ContactID>
<Name>Bestpets</Name>
</Contact>
<Date>2020-10-01T00:00:00</Date>
<DueDate>2020-10-08T00:00:00</DueDate>
<PlannedPaymentDate>2020-10-02T00:00:00</PlannedPaymentDate>
<Status>DRAFT</Status>
<LineAmountTypes>Exclusive</LineAmountTypes>
<LineItems>
<LineItem>
<Description>601450101</Description>
<UnitAmount>99.82</UnitAmount>
<TaxType>INPUT2</TaxType>
<TaxAmount>19.98</TaxAmount>
<LineAmount>99.82</LineAmount>
<AccountCode>1690</AccountCode>
<Tracking>
<TrackingCategory>
<Name>Market Place</Name>
<Option>01 - None</Option>
<TrackingCategoryID>1896d5bf-5c81-4ec4-98f7-eaff2601a84b</TrackingCategoryID>
</TrackingCategory>
</Tracking>
<Quantity>1.0000</Quantity>
<LineItemID>50a2cda4-1b49-4ce1-89e1-ebff69431712</LineItemID>
</LineItem>
</LineItems>
<SubTotal>99.82</SubTotal>
<TotalTax>19.98</TotalTax>
<Total>119.80</Total>
<UpdatedDateUTC>2020-10-02T14:26:21.523</UpdatedDateUTC>
<CurrencyCode>GBP</CurrencyCode>
<FullyPaidOnDate>2020-10-02T00:00:00</FullyPaidOnDate>
<Type>ACCPAY</Type>
<InvoiceID>2ce181be-c04e-490a-a008-891ae5916b79</InvoiceID>
<InvoiceNumber>7191/301084</InvoiceNumber>
<Payments>
<Payment>
<PaymentID>0f35b2c0-c459-4b65-b9e2-70705d82c1a7</PaymentID>
<BatchPaymentID>5a8b4a30-850b-4681-a2bc-32c712d278a1</BatchPaymentID>
<Date>2020-10-02T00:00:00</Date>
<Amount>119.80</Amount>
<CurrencyRate>1.000000</CurrencyRate>
</Payment>
</Payments>
<AmountDue>0.00</AmountDue>
<AmountPaid>119.80</AmountPaid>
<AmountCredited>0.00</AmountCredited>
<CurrencyRate>1.000000</CurrencyRate>
<HasAttachments>true</HasAttachments>
<HasErrors>false</HasErrors>
</Invoice>
<Invoice>
<Contact>
<ContactID>79475cda-87f1-459e-8709-fbf77cea6835</ContactID>
<Name>Bestpets</Name>
</Contact>
<Date>2020-10-01T00:00:00</Date>
<DueDate>2020-10-08T00:00:00</DueDate>
<PlannedPaymentDate>2020-10-02T00:00:00</PlannedPaymentDate>
<Status>DRAFT</Status>
<LineAmountTypes>Exclusive</LineAmountTypes>
<LineItems>
<LineItem>
<Description>601536502</Description>
<UnitAmount>128.09</UnitAmount>
<TaxType>INPUT2</TaxType>
<TaxAmount>25.60</TaxAmount>
<LineAmount>128.09</LineAmount>
<AccountCode>1690</AccountCode>
<Tracking>
<TrackingCategory>
<Name>Market Place</Name>
<Option>01 - None</Option>
<TrackingCategoryID>1896d5bf-5c81-4ec4-98f7-eaff2601a84b</TrackingCategoryID>
</TrackingCategory>
</Tracking>
<Quantity>1.0000</Quantity>
<LineItemID>cd1a858a-359a-48b7-aa90-36278a7fd12b</LineItemID>
</LineItem>
</LineItems>
<SubTotal>128.09</SubTotal>
<TotalTax>25.60</TotalTax>
<Total>153.69</Total>
<UpdatedDateUTC>2020-10-02T14:26:22.997</UpdatedDateUTC>
<CurrencyCode>GBP</CurrencyCode>
<FullyPaidOnDate>2020-10-02T00:00:00</FullyPaidOnDate>
<Type>ACCPAY</Type>
<InvoiceID>b735d967-e3df-44b2-9635-223bed4ab860</InvoiceID>
<InvoiceNumber>7191/301083</InvoiceNumber>
<Payments>
<Payment>
<PaymentID>aaff83cb-b614-437a-b250-d2cf92685248</PaymentID>
<BatchPaymentID>5a8b4a30-850b-4681-a2bc-32c712d278a1</BatchPaymentID>
<Date>2020-10-02T00:00:00</Date>
<Amount>153.69</Amount>
<CurrencyRate>1.000000</CurrencyRate>
</Payment>
</Payments>
<AmountDue>0.00</AmountDue>
<AmountPaid>153.69</AmountPaid>
<AmountCredited>0.00</AmountCredited>
<CurrencyRate>1.000000</CurrencyRate>
<HasAttachments>true</HasAttachments>
<HasErrors>false</HasErrors>
</Invoice>
</Invoices>
 
 
 This is code: 
 
 
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = Missing.Value;
DataSet ds = new DataSet();
XmlReader xmlFile;
xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xmlFile = XmlReader.Create("MyXML.xml", new XmlReaderSettings());
ds.ReadXml(xmlFile, XmlReadMode.InferSchema);
for (int iRow = 1; iRow <= ds.Tables[0].Rows.Count - 1; iRow++)
{
for (int iCol = 0; iCol <= ds.Tables[0].Columns.Count - 1; iCol++)
{
xlWorkSheet.Cells[iRow + 1, iCol + 1] = ds.Tables[0].Rows[iRow].ItemArray[iCol].ToString();
}
}
Console.Write(xmlFile);
xlWorkBook.SaveAs(@"E:\Documents\Visual Studio 2015\WebSites\ConnectXero\Schema\xml2excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlApp);
releaseObject(xlWorkBook);
releaseObject(xlWorkSheet);

Attachment: MyXML.zip

Answers (2)
Next Recommended Forum