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). |
Pointer
Corporation