[Psyguy]

A while back I wrote about the way I had come up with to export data to Excel. This worked for me for a little while, but I am always looking for ways to speed up the execution of my code. When I started working with framework 4.0 and the accompanying Excel interop COM library (v14 i think it is) the execution of saving some 50 columns and 300 rows of data was taking somewhere in the area of 30 seconds. That was just plain unacceptable to me. The following is the solution I pieced together from the days of research i conducted. The disclaimer: using this method will generate a .xml file, not a .xls or any other Excel specific files. The XML file will open in Excel when double clicked and display the Excel icon in any file browser you open.

First, here is the complete code for the save to Excel button:

Private Sub tsbtnSaveToExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsbtnSaveToExcel.Click, tsbtnSaveToXML.Click
        Try
            Dim fileName As String
            Dim response As DialogResult

            sfdSaveToExcel.InitialDirectory = excelFileLocation 'this was set ahead of time
            sfdSaveToExcel.FileName = "myFile"
            response = sfdSaveToExcel.ShowDialog()

            If response = Windows.Forms.DialogResult.OK Then
                fileName = sfdSaveToExcel.FileName

                If fileName <> String.Empty Then
                    fileName = SetFileType(fileName, ".xml") 'this is a method i created that will make sure the filename doesnt have an extension on it, then adds the xml extention.

                    If Not IO.File.Exists(fileName) Then
                        Dim fs As System.IO.FileStream
                        Dim xtw As System.Xml.XmlTextWriter

                        myDataTable.TableName = "MyTable"
                        fs = New System.IO.FileStream(fileName, IO.FileMode.Create)
                        xtw = New System.Xml.XmlTextWriter(fs, System.Text.Encoding.Unicode)
                        xtw.WriteProcessingInstruction("xml", "version='1.0'")
                        xtw.WriteProcessingInstruction("mso-application", "progid='Excel.Sheet'")
                        myDataTable.WriteXml(xtw)
                        xtw.Close()
                        tsslMessagesRight.Text = "File Successfully saved."
                    Else
                        tsslMessagesRight.Text = "That file already exists.  Please try again with a unique file name."
                    End If
                End If
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

As you can see, the code is not that complicated. Basically, you open a file stream to stream all of the information to the file you want. The file stream creates the desired file, then the xml text writer goes to work. You add the xml version information via the WriteProcessingInstruction method, which would be auto generated if you only used the WriteXML method of the DataTable class. The next processing instruction is the key that will unlock the Excel functionality. Basically, xtw.WriteProcessingInstruction("mso-application", "progid='Excel.Sheet'") tells the system that this xml file is meant for use in Excel. When you double click the file or open it from the Excel file menu, you will get a small popup box that will ask you how you want to open the xml file. You choose the option which best suits your needs, usually the “As an XML table” option.

That's it. Pretty easy, eh? Hope this helps someone out there avoid the difficulty of piecing together the information through the same several day process I had to go through. Special thanks to my co worker BK for his assistance.