Why Join
Become a member
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
C# Corner Home
Technologies
Monthly Leaders
ASK A QUESTION
Forumguidelines
Amit Sharma
2k
23
467
Convret XML with all nodes to Excel in C#
Dec 3 2020 3:59 AM
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
Reply
Answers (
2
)
asp.net mvc, razor partial view - boolean property not showing
Asp .Net MVC project Web Deployment to hosting site