How to create an Excel file using a database query in VB.Net?

Question

I would like to generate an Excel file using a database query in VB.Net. How can I do it?

To be more precise: I would like to "bind" a query (much like binding a query to a GridView) to an Excel file such that the rows in the table occupy corresponding cells in a new Excel file, and save the file to my computer. And then, mail that file to someone.

While I can handle the mailing part, it's the creation of such a file that I need help with. Anyone know how to achieve what I want to achieve?

PS: I need to do this in VB.Net and I'm using SQL Server 2008.

Solution

OK this isn't perfect but it should get you started. First of all you will want to add a reference to the version of Excel you are using. In my case it is 12.0 (2007) but this code should work with the last two or three version with a minor change or two. At the top of your page add this

Imports Microsoft.Office.Interop

Next add a function to create a datatable

Public Function CreateTable() As DataTable
    Dim cn As New SqlConnection(My.Settings.con)
    Dim cmd As New SqlCommand
    Using da As New SqlDataAdapter()
        Dim dt As New DataTable()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "[dbo].[MyStoredProcedure]"
        cmd.CommandTimeout = 0
        cn.Open()
        cmd.Connection = cn
        da.SelectCommand = cmd
        da.Fill(dt)
        cn.Close()
        Return dt
    End Using
End Function

Next the code to take that DataTable and dump it into Excel.

Public Shared Sub PopulateSheet(ByVal dt As DataTable, ByVal File As String)
            Dim oXL As Excel.Application = CType(CreateObject("Excel.Application"), Excel.Application)
        Dim oWB As Excel.Workbook
        Dim oSheet As Excel.Worksheet
        Dim oRng As Excel.Range
        oXL.Visible = True

        oWB = oXL.Workbooks.Add
        oSheet = CType(oWB.ActiveSheet, Excel.Worksheet)

        Dim dc As DataColumn
        Dim dr As DataRow
        Dim colIndex As Integer = 0
        Dim rowIndex As Integer = 0
        For Each dc In dt.Columns
            colIndex = colIndex + 1
            oXL.Cells(1, colIndex) = dc.ColumnName
        Next
        For Each dr In dt.Rows
            rowIndex = rowIndex + 1
            colIndex = 0
            For Each dc In dt.Columns
                colIndex = colIndex + 1
                oXL.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
            Next
        Next

        oSheet.Cells.Select()
        oSheet.Columns.AutoFit()
        oSheet.Rows.AutoFit()

        oXL.Visible = True
        oXL.UserControl = True

        oWB.SaveAs(File)
        oRng = Nothing
        oXL.Quit()

        ExcelCleanUp(oXL, oWB, oSheet)
    End Sub

Now you can call it from a button or whatever event you choose with this

    Dim dt As New DataTable
    Try
        dt = CreateTable()
        PopulateSheet(dt, "c:\test\ExcelFile.xlsx")
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    Finally
        dt.Dispose()
    End Try

Now this is really basic but with a little work you can do cell formatting, page setup and just about anything that can be done inside Excel with the menus/options.

We should also finish this out by adding code to clean things up.

Private Shared Sub ExcelCleanUp( _
    ByVal oXL As Excel.Application, _
    ByVal oWB As Excel.Workbook, _
    ByVal oSheet As Excel.Worksheet)

    GC.Collect()
    GC.WaitForPendingFinalizers()

    Marshal.FinalReleaseComObject(oXL)
    Marshal.FinalReleaseComObject(oSheet)
    Marshal.FinalReleaseComObject(oWB)

    oSheet = Nothing
    oWB = Nothing
    oXL = Nothing

End Sub

Comments

[Buck Hicks]

The ExcelCleanUp is supposed to make sure that an Excel process is not left running in the Processes list after Excel closes but as written it is not doing that. The code itself does work but the process is left running. I will try and correct this later today when I have more time to look into it. Anyway this should get you started.

[CodingInCircles]

Can I do the same using Lists instead? Instead of a dataset? Is there a straightforward way? Thanks!