[2005] Create Excel File From DataTable

[bmahler]

I figured this was worth posting as I had one hell of a time getting it to work properly. This uses the Microsoft Excel 11.0 Object Library. One of the issues with using this com object was the process hanging in memory until your application closed. This solution addresses this issue.

The following code will Take the contents of a DataTable and save it as an Excel Document.

First you need to add reference to the Microsoft Excel 11.0 Object Library

then import it like so

Imports Microsoft.Office.interop

The Function to save the file as excel

    Private Sub ExportToExcel(ByVal FileName As String, ByVal dt As DataTable)
        Dim oExcel As Excel.ApplicationClass = Nothing
        Dim oBook As Excel.WorkbookClass = Nothing
        Dim oBooks As Excel.Workbooks = Nothing
        Dim oXLWsheet As Excel.Worksheet = Nothing
        Dim oRange As Excel.Range = Nothing
        Dim intColumn, intRow, intColumnValue As Integer


        Try
            'CREATE A NEW EXCEL OBJECT
            oExcel = New Excel.Application
            'HIDE THE EXCEL APPLICATION
            oExcel.Visible = False
            'CREATE A NEW BOOKS OBJECT
            oBooks = oExcel.Workbooks
            'CREATE A NEW BOOK OBJECT
            oBook = oBooks.Add
            'CREATE A NEW WORKSHEET OBJECT
            oXLWsheet = oBook.Sheets(1)
            'CREATE A NEW CELLS OBJECT (RANGE)
            oRange = oXLWsheet.Cells

            'SET THE COLUMN NAMES
            For intColumn = 0 To dt.Columns.Count - 1
                oRange(1, intColumn + 1) = dt.Columns(intColumn).ColumnName.ToString
            Next
            'SET THE CELLS VALUES
            For intRow = 0 To dt.Rows.Count - 1
                For intColumnValue = 0 To dt.Columns.Count - 1
                    oRange(intRow + 2, intColumnValue + 1) = dt.Rows(intRow).ItemArray(intColumnValue).ToString
                Next
            Next
            'SAVE THE WORKSHEET (PROMPT FOR OVERWRITE)
            oXLWsheet.SaveAs(FileName)
               
        Catch ex As Exception
            MessageBox.Show("Unable to export excel file" & Environment.NewLine & ex.Message, "Unable to export file")
        Finally
            'RELEASE THE CELLS OBJECT
            If Not oRange Is Nothing Then
                Release(oRange)
            End If
            'RELEASE THE WORKSHEET OBJECT
            If Not oXLWsheet Is Nothing Then
                Release(oXLWsheet)
            End If
            'CLOSE AND RELEASE THE BOOK OBJECT
            If Not oBook Is Nothing Then
                oBook.Close(False)
                Release(oBook)
            End If
            'RELEASE THE BOOKS OBJECT
            If Not oBooks Is Nothing Then
                Release(oBooks)
            End If
            'QUIT AND RELEASE THE EXCEL OBJECT
            If Not oExcel Is Nothing Then
                oExcel.Quit()
                Release(oExcel)
            End If
        End Try
    End Sub

The following function is also required and is used to release the com objects from memory.

    Private Sub Release(ByVal o As Object)
        Try
            While System.Runtime.InteropServices.Marshal.ReleaseComObject(o) <> 0
                'CONTINUE UNTIL ALL OBJECTS ARE RELEASED
            End While
        Catch
        Finally
            'SET THE OBJECT = NOTHING
            o = Nothing
        End Try
    End Sub

Here is a module that contains these function