|
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.
|