How to create an Excel file from XML

By: Rajesh P.S.

XML is a versatile and self-describing language that not only contains data but also provides the necessary rules to identify and interpret the information it holds.

Excel file from XML

In the following scenario, we will demonstrate how to read the contents of an XML file and write the same content to an Excel file. To accomplish this, we will utilize an XmlReader to read the XML file and populate a Dataset with the retrieved data. We can then iterate through the Dataset and add the content to the Excel file.

To create an Excel file, you will need to add a reference to the Excel library in your project. This library provides the necessary functionality to interact with Excel files programmatically. By referencing the Excel library, you gain access to the required classes and methods to create and manipulate Excel files.

To read the XML file, we will employ an XmlReader, which allows us to traverse through the XML structure and extract the desired data. The XmlReader will provide us with a stream of XML data, which can be used to populate a Dataset. The Dataset is a powerful ADO.NET component that enables us to store, manipulate, and process data in a tabular format.

xmlFile = XmlReader.Create("Product.xml", New XmlReaderSettings()) ds.ReadXml(xmlFile) For i = 0 To ds.Tables(0).Rows.Count - 1 For j = 0 To ds.Tables(0).Columns.Count - 1 xlWorkSheet.Cells(i + 1, j + 1) = _ ds.Tables(0).Rows(i).Item(j) Next Next

Once the Dataset contains the XML data, we can iterate through its rows and columns to retrieve the desired content. With each iteration, we can write the data to the Excel file using the Excel library's functionality. This process ensures that the content from the XML file is accurately transferred and represented in the Excel file.

Full Source VB.NET

Imports System.Xml Imports System.Data Imports Excel = Microsoft.Office.Interop.Excel Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim xlApp As Excel.Application Dim xlWorkBook As Excel.Workbook Dim xlWorkSheet As Excel.Worksheet Dim misValue As Object = System.Reflection.Missing.Value Dim ds As New DataSet Dim xmlFile As XmlReader Dim i, j As Integer xlApp = New Excel.ApplicationClass xlWorkBook = xlApp.Workbooks.Add(misValue) xlWorkSheet = xlWorkBook.Sheets("sheet1") xmlFile = XmlReader.Create("Product.xml", New XmlReaderSettings()) ds.ReadXml(xmlFile) For i = 0 To ds.Tables(0).Rows.Count - 1 For j = 0 To ds.Tables(0).Columns.Count - 1 xlWorkSheet.Cells(i + 1, j + 1) = _ ds.Tables(0).Rows(i).Item(j) Next Next xlWorkSheet.SaveAs("xml2excel.xlsx") xlWorkBook.Close() xlApp.Quit() releaseObject(xlApp) releaseObject(xlWorkBook) releaseObject(xlWorkSheet) End Sub Private Sub releaseObject(ByVal obj As Object) Try System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) obj = Nothing Catch ex As Exception obj = Nothing Finally GC.Collect() End Try End Sub End Class

Click here to download the input file product.xml

Handling Exceptions

When working with Excel files, it is essential to handle exceptions, validate the data, and ensure proper formatting to maintain the integrity and usability of the Excel file. Additionally, consider optimizing the data transfer process and implementing appropriate security measures to protect against any potential vulnerabilities.

Conclusion

The outlined solution demonstrates how to read content from an XML file and write it to an Excel file. By utilizing an XmlReader, Dataset, and the Excel library, we can effectively extract and transfer data from the XML file to the Excel file, providing a seamless and efficient data transformation process.

Next > How to create an XML file from Excel

Related Topics

More Related Topics.....