Pointer Corporation
     The Information Technology Architects
Converting Excel Data to XML Using Visual Basic .NET

Out with the Old; In with the New!


So why should we convert our Excel data into XML, you might ask?  That is actually a very legitimate question.  After all, Excel offers a rich and sophisticated environment to personalize your data to your heart's content!  Put any element where it's most pleasant to the eye.  Any shape, any color, anywhere.  XML, on the other hand, is virtually the complete opposite.

Therefore, the answer to the "why" question may not be so obvious at the beginning.  So let's see if we can come up with a compare and contrast list between Excel and XML:

 

  • Excel is a Microsoft technology, whereas XML is a globally accepted format that crosses corporate boundaries.  So whether your organization is in the Microsoft camp or a Java advocate, in Windows or Unix platform, IBM admirer or Oracle believer, XML is there for you.
  • Even in its simplest form with a grid of rows and columns of data, Excel is not easily readable by other applications.  XML, on the other hand, is fundamentally a protocol for data transfer between applications; a de-facto standard for cross-platform interchange.
  • As such, the decision to convert your Excel data into XML may no longer be your decision after all.  If another system, project, or application mandates that you deliver your data in XML data, you need to do it.

Now that we have settled the "why" part, it's time to talk about "how" in a step-by-step fashion.

There are two general approaches you can take in order to convert your Excel data into XML: Directly and Indirectly.  Unlike vacation planning when I would probably recommend a direct flight without stop-over in another place, in this case I recommend the latter; an indirect conversion, simply because it's easier and offers you a greater deal of control.

For example, if you're comfortable in the .NET platform and prefer to use Visual Basic .NET, then you want to read your Excel data into an ADO dataset first and then export the dataset into XML.

Step 1: Reading your Excel data into an ADO Dataset, using VB.NET

Dim MyConnection As System.Data.OleDb.OleDbConnection

MyConnection = New System.Data.OleDb.OleDbConnection( _

"Provider=Microsoft.Jet.OLEDB.4.0; " & _

"Extended Properties=Excel 8.0; " & _

"Data Source=" & strExcelFileName & ";")

Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

MyCommand = New System.Data.OleDb.OleDbDataAdapter( _

"SELECT * FROM [Data$]", MyConnection)

Dim DS As System.Data.DataSet

DS = New System.Data.DataSet

MyCommand.Fill(DS)

MyConnection.Close()

MyCommand.Dispose()

Step 2: Exporting your ADO Dataset to XML

DS.WriteXml(strXMLFileName)

That's it!  The resulting XML file will take the field names from the 1st row of data in your Excel sheet (named "Data" in this example).

 

For further information, please refer to our feedback page.