Subscribe to Cache Cow        RSS Feed
-----

More XML to Excel Tricks

Icon Leave Comment
In a previous blog (Export your data table to Excel the quick way (via XML)) I describe how to quickly convert your datatable into XML, which has been specifically designated for Excel use. This makes things very fast and easy, since double clicking the file will automatically open Excel. I did find one irritating issue when doing this.

When you export your data to an XML file using the previously described method, any dates you have in the table will be converted to UTC time. This means your date will look something like this:

2013-05-16T13:53:37-07:00

For those of us with OCD that need things "just so", this is unacceptable. I went about trying to convert this several ways, but was unable to manage it. Below you will find the solution i used which finally did the trick. As always, you will find my specs below as well:

Visual Studio 2010
.NET Framework 4.0
VB.NET (I can translate to C# if necessary, but I would suggest you familiarize yourself with both languages)

I am not going to show the supporting application, as that information is unnecessary to show the functionality of the methods used here. Additionally, there are many versions of similar code out there. This one has been tweaked to fit my specific needs of exporting for use with Excel.

All necessary code is held my method "CreateExcelFile".
Private Sub CreateExcelFile(ByVal dt As DataTable, ByVal savepath As String)
        'You could always pass a dataset in here instead of a datatable, but I rarely use the DataSet object.
        'make sure you name your table as well.  I did mine before it was passed here.
        Dim ds As DataSet = New DataSet("MyDataSet") 
        Dim elementName As String = String.Empty
        Dim xtr As XmlTextReader
        Dim fs As System.IO.FileStream = New System.IO.FileStream(savepath, IO.FileMode.Create)
        Dim xtw As System.Xml.XmlTextWriter = New XmlTextWriter(fs, System.Text.Encoding.Unicode)

        Try
            ds.Tables.Add(dt)

            xtr = New XmlTextReader(ds.GetXml(), XmlNodeType.Element, Nothing)
            xtr.Normalization = True
            xtw.Indentation = 5
            xtw.WriteProcessingInstruction("xml", "version='1.0'")
            xtw.WriteProcessingInstruction("mso-application", "progid='Excel.Sheet'")
            ' Parse & display each node
            While xtr.Read()
                Select Case xtr.NodeType
                    Case XmlNodeType.Element
                        xtw.WriteStartElement(xtr.Name)
                        elementName = xtr.Name
                    Case XmlNodeType.Text
                        If elementName = "MyUnformattedDate" Then 'this needs to be the name of the field, with a date, that you want to format
                            xtw.WriteString(CDate(xtr.Value).ToString("ddMMMyyyy hh:mm:ss"))
                        Else
                            xtw.WriteString(xtr.Value)
                        End If
                    Case XmlNodeType.EndElement
                        xtw.WriteEndElement()
                End Select
            End While
        Catch ex As Exception
            'your exception handling code here
        End Try

        xtw.Close()
    End Sub



Unlike my previous blog, this one requires that your datatable be part of a dataset. The reason we need this dataset is because it gives us access to the method GetXml(), which provides the XmlTextReader with the necessary XML code to read through. Instead of just writing the entire XML code to the XmlTextWriter all at once, like in the previous blog, this time we are going to write it to the XmlTextWriter line by line. The reason we have to do this is because we have to change the format of one specific field and there is no other available method to do so. Personally, i would have thought that Microsoft would have thought that maybe people wouldn't always want their dates in UTC format, but thats just me. At any rate, the xtw.WriteStartElement basically writes any node start line (i.e <MyDataSet>, <MyTable>, <MyRecord>), the xtw.WrtiteEndElement method performs the same operation, but on the closing statement for the node (i.e. </MyDataSet>, </MyTable>, </MyRecord>). The third case in the select statement actually reads what is between the start and end of the node. It is where we change the date and time format for our XML. In actuality, we are changing the data type as well, but I haven't had any issue with it in Excel. If it were an issue, you could simply select the column and format the datatype in Excel. Obviously, I am using military time, but any datetime format will work. The last thing needed is to close the XmlTextWriter and voila! Your brand new file is in the place you designated.

The one problem I have with this particular method is when I go to examine the XML as XML. The whole darn thing is on one line...not very readable. I have inserted line breaks to help with it, but I would prefer something a little more elegant. If anyone has any good suggestions, hit me with a comment. Thanks for reading. I hope this helps. Happy coding!

0 Comments On This Entry

 

Trackbacks for this entry [ Trackback URL ]

There are no Trackbacks for this entry

October 2014

S M T W T F S
   1234
567891011
12131415161718
192021222324 25
262728293031