How to create an XML file from Excel

The .NET Framework offers a range of classes to facilitate reading, writing, and performing various operations on XML-formatted files.

Create an XML file from the contents of an Excel file

In the following program, we will demonstrate how to create an XML file from the contents of an Excel file. To achieve this, we will utilize an OleDbConnection to establish a connection to the Excel file and retrieve the data. The OleDbConnection class provides methods and properties to interact with various data sources, including Excel files.

Once the connection is established, we will read the Excel file and store the data in a Dataset. The Dataset is a powerful ADO.NET component that allows us to store, manipulate, and process data in a tabular format. By populating the Dataset with the data from the Excel file, we can easily manipulate and transfer the information to other formats, such as XML.

MyCommand = New System.Data.OleDb.OleDbDataAdapter _ ("select * from [Sheet1$]", MyConnection) MyCommand.TableMappings.Add("Table", "Product") ds = New System.Data.DataSet MyCommand.Fill(ds) MyConnection.Close() ds.WriteXml("Product.xml")

To create the XML file, we will call the WriteXml method of the Dataset. This method generates an XML representation of the Dataset and writes it to the specified file. The generated XML file will contain the data from the Excel file, structured according to the Dataset's schema.

Full Source VB.NET
Imports System.Data Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Try Dim MyConnection As System.Data.OleDb.OleDbConnection Dim ds As System.Data.DataSet Dim MyCommand As System.Data.OleDb.OleDbDataAdapter MyConnection = New System.Data.OleDb.OleDbConnection _ ("provider=Microsoft.Jet.OLEDB.4.0;Data Source='xl2xml.xls';Extended Properties=Excel 8.0;") MyCommand = New System.Data.OleDb.OleDbDataAdapter _ ("select * from [Sheet1$]", MyConnection) MyCommand.TableMappings.Add("Table", "Product") ds = New System.Data.DataSet MyCommand.Fill(ds) MyConnection.Close() ds.WriteXml("Product.xml") Catch ex As Exception MsgBox(ex.ToString) End Try End Sub End Class

Click here to download the input excel file xl2xml.xls

By exploring the capabilities of the OleDbConnection, Dataset, and the WriteXml method, we can effectively extract the data from the Excel file and create an XML representation of that data. This allows for easy integration with other systems and provides flexibility in working with XML-formatted files.

Conclusion

The provided program demonstrates how to create an XML file from the content of an Excel file using the OleDbConnection and Dataset classes. By reading the Excel file, storing the data in a Dataset, and utilizing the WriteXml method, we can generate an XML file containing the information from the Excel file in a structured format.






Related Topics
  1. How to XML in VB.NET
  2. How to create an XML file in VB.NET
  3. How to open and read XML file in VB.NET , XmlReader in VB.Net , XmlTextReader in VB.Net
  4. How to create an XML file in VB.NET using Dataset
  1. How to open and read an XML file in VB.NET using Dataset
  2. How to create an XML file from SQL in VB.NET
  3. How to search in an XML file
  4. How to filter data in an XML file
  5. How to insert data from xml to database
  6. How to create an Excel file from XML
  7. How to xml to DataGridView
  8. How to create a TreevView from XML
  9. How to create Crystal Reports from XML
  10. How to serialization in xml
  11. How to serialize a .Net Object to XML
  12. How to de-serialize from an XML file to .Net Object