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

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 UsingEnd 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 = NothingEnd Sub

You can automate Excel in VB.Net and put those values into the spreadsheet via code:

You can find some excellent articles about this on CodeProject:

Excel Export Component Using XSL: In this article you can find a component to export data to Excel whithout using any office component.

Export to Excel using VB.Net: In this one the author uses Excel automation to do the job.