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:
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!