HELP to export data in excel from sql server table

[SHIVPREET2K1]

Dear friends

I m using vb.net 2008. I have created an application in which data is needed to be exported from sql server 2005 to excel sheet. i do not know that how can i export data from the sql server table to the excel sheet.

Plz help me.


Thanks in advance

Shivpreet2k1

[Eclipsyo]

So lets say you queried your SQL database and stored the records in a datatable object in your app. Below is a SUB that takes a couple of necessary parameters like where u want the file to be saved, the file name, the optional delimiter, the save as file type (.csv) should be used. Pretty sure .xls will work but I modified this sub a few times since for my specific case and dont have time to retest the .xls file type.

Ex... calling the function:
createFileFromDataTable("FileNameStaticPart" & Format(Now(), "MM-dd-yyyy-hh-mm"), strFilePath, ".csv", myDataTable, delimiter.comma)

'list of delimiters 
Enum delimiter
                vbCr
                vbCrLf
                vbLf
                vbNewLine
                vbTab
                vbVariant
                comma
 End Enum

'public var
Public strFilePath As String = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) & "\"

'sub to create .csv file which is excel readable.
Public Sub createFileFromDataTable(ByVal strFileName As String, 
ByVal strFilePath As String, ByVal strPeriodWithFileType As String, 
ByVal tbl As DataTable, Optional ByVal delimiterType As delimiter = Nothing)

                Dim strDelim As String
                Dim strPad As String = """"
                Dim oWrite As StreamWriter
                Dim sbLine As New System.Text.StringBuilder

                strFileName = strFileName & strPeriodWithFileType
                strFilePath = strFilePath & strFileName
                oWrite = System.IO.File.CreateText(strFilePath)

                If delimiterType = delimiter.vbCr Then
                    strDelim = vbCr
                ElseIf delimiterType = delimiter.vbCrLf Then
                    strDelim = vbCrLf
                ElseIf delimiterType = delimiter.vbLf Then
                    strDelim = vbLf
                ElseIf delimiterType = delimiter.vbNewLine Then
                    strDelim = vbNewLine
                ElseIf delimiterType = delimiter.vbTab Then
                    strDelim = vbTab
                ElseIf delimiterType = delimiter.vbVariant Then
                    strDelim = vbVariant
                ElseIf delimiterType = delimiter.comma Then
                    strDelim = ","
                Else
                    strDelim = ""
                End If

                With oWrite

                    'headers
                    With sbLine
                        For Each col As DataColumn In tbl.Columns
                            .Append(strPad + col.Caption.ToString + strPad + strDelim)
                        Next
                    End With
                    .WriteLine(sbLine.ToString)

                    For Each row As DataRow In tbl.Rows
                        sbLine = New System.Text.StringBuilder
                        With sbLine
                            For Each col As DataColumn In tbl.Columns
                                .Append(strPad + row.Item(col).ToString + strPad + strDelim)
                            Next
                        End With
                        .WriteLine(sbLine.ToString)
                    Next

                    .Close()

                End With

            End Sub