[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

[Hack]

I just used the parts that released the objects from memory, but that seems to work well.

I have a question. My app creates a report. Running this from the routine that creates the worksheet actually destorys the worksheet before it can be viewed. I need it visible on the screen so the user can either just view it, or use Excel to print it, depending on what they want to do.

So, I would need to move the Releasing part out of my Sub. So, I moved it to the Form_Closing.

Private Sub frmUserMaint_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        If Not oRange Is Nothing Then
            Release(oRange)
        End If
        If Not shWorkSheet Is Nothing Then
            Release(shWorkSheet)
        End If
        If Not bkWorkBook Is Nothing Then
            bkWorkBook.Close(False)
            Release(bkWorkBook)
        End If
        If Not objExcel Is Nothing Then
            objExcel.Quit()
            Release(objExcel)
        End If
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Sub

When I closed the form, I got a runtime error saying "The object invoked has disconnected from its clients".

Other than it immediately closed the spreadsheet, which won't work for me, it worked fine when it was part of the Sub itself.

[bmahler]

In my example the excel application is not visible. In the case where you wnt it visible and the user to interact, if they close the excel instance that is open, you will get that error. Try just removing the bworkBook.close(False) line.

also, with this method you can remove the Garbage Collection items
GC.Collect()
GC.WaitForPendingFinalizers()

they are not needed if all objects are released properly

[RobDog888]

I still have never needed to use ReleaseCOMObject in .NET. I think I can figure out what your issue is but I'll look into it in a while as I have to finish my work that my client is desperately waiting for

Just quickly, I see you are using ...
oBooks = oExcel.Workbooks

But never actually using it. Things like this just leave the door open for potential issues.

[bmahler]

I use it afterwords for setting the book object
oBooks = oExcel.Workbooks
'CREATE A NEW BOOK OBJECT
oBook = oBooks.Add

This was something I found on the msdn that they suggested.

[RobDog888]

You can also shortcut it by going ...
oBook = oExcel.Workbooks/Add()

[bmahler]

When I was creating this example I was following the instructions from this example from Microsoft http://support.microsoft.com/default...b;en-us;317109

To make sure that the Office application quits, make sure that your automation code meets the following criteria:
• Declare each object as a new variable. For example, change the following line of code

oBook = oExcel.Workbooks.Add()

to the following:

dim oBooks as Excel.Workbooks
oBooks = oExcel.Workbooks
oBook = oBooks.Add()

• Use System.Runtime.InteropServices.Marshal.ReleaseComObject when you have finished using an object. This decrements the reference count of the RCW.
• To release the reference to the variable, set the variable equal to Nothing or Null.
• Use the Quit method of the Office application object to tell the server to shut down.

[cyberpd]

bmahler

i have used your code
ihave added one line to align the contents of the cells center like the following...

 '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
                    oRange.HorizontalAlignment = HorizontalAlignment.Center
                Next
            Next

after the file is created and data added. i opened it.
the cells are not aligned centrally.
they are aligned left.

what i am missing??

[MikeBinVT]

Is there a way to filter the data being saved? I need to save out the data based on dates input by the user. DataTable doesn't have the RowFilter that DataViews do.

[bmahler]

You should have those filters in your SQL that generates the Datatable.