Page 1 of 1

Change Excel DateTime Display Format When Using XML as the Source Rate Topic: -----

#1 Psyguy  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 69
  • View blog
  • Posts: 310
  • Joined: 12-January 11

Posted 23 May 2013 - 09:49 AM

In one of my blogs (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, though.

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", although there are some checks you should perform prior to using the method (like checking to see if the file already exists).

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 tutorial requires that your datatable be part of a dataset (or you could change the parameter to accept a DataSet object). 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 my 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 (or many fields if needed) 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 (in my personal code), 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!

Is This A Good Question/Topic? 0
  • +

Page 1 of 1