Export sql query to Excel 97 file using vb.net

Question

I have a desktop app, I use this code to create a .xls file using a FileStream object:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim i As Integer
    Dim strLine As String = ""
    Dim fileExcel As String
    Dim objFileStream As FileStream
    Dim objStreamWriter As StreamWriter
    Dim cnn As SqlConnection = New SqlConnection("...Connection...")

    'Create a pseudo-random file name.
    fileExcel = "c:\temp\test.xls"

    'Use FileStream to create the .xls file.
    objFileStream = New FileStream(fileExcel, FileMode.OpenOrCreate, FileAccess.Write)
    objStreamWriter = New StreamWriter(objFileStream)

    'Use a DataReader to connect to the Pubs database.
    cnn.Open()
    Dim sql As String = "select top 5 idWO, Number, Comment from tblWorkorder (nolock)"
    Dim cmd As SqlCommand = New SqlCommand(sql, cnn)
    Dim dr As SqlDataReader
    dr = cmd.ExecuteReader()

    'Enumerate the field names and records that are used to build the file.
    For i = 0 To dr.FieldCount - 1
        strLine = strLine & dr.GetName(i).ToString & Chr(9)
    Next

    'Write the field name information to file.
    objStreamWriter.WriteLine(strLine)

    'Reinitialize the string for data.
    strLine = ""

    'Enumerate the database that is used to populate the file.
    While dr.Read()
        For i = 0 To dr.FieldCount - 1
            strLine = strLine & dr.GetValue(i) & Chr(9)
        Next
        objStreamWriter.WriteLine(strLine)
        strLine = ""
    End While

    'Clean up.
    dr.Close()
    cnn.Close()
    objStreamWriter.Close()
    objFileStream.Close()

End Sub

This works fine, but when I open the .xls file created, I get: "The file you are trying to open, is in a different format than specified in the file extension..." so I guess that it creates a .xlsx (2007) file instead a .xls (97-2003). So I wonder if there is any way to specify the version in which I want the excel file?

score 1 · Accepted Answer · answered Jan 17 '12 at 15:35

Because the xls format is a proprietary format that is not easily writable, your best option, without using any library, is to export the file to a csv file. A default installation of Excel will associate itself with this format, which means it will automatically be opened by Excel.

Since your data is fairly small, you should be easily able to convert it.

For example:

strLine = strLine & dr.GetName(i).ToString & Chr(9)

becomes:

If Not String.IsNullOrEmpty(strLine) Then
    strLine &= ","
End If
strLine &= """" & dr.GetName(i).ToString & """"

You will need to adjust some of the output based on the type of data and whether or not it has embedded double-quotes, but this should get you going.

I also strongly recommend two additional changes to the code:

1) Use System.Text.StringBuilder for building your strings

2) Use Using statements to ensure all of your disposable objects are cleaned up correctly.

Export sql query to Excel 97 file using vb.net

1 Answers1