Exporting DataGrid contents to Excel

Here’s an example of code (the quick n’dirty way as usual) to export the contents of a DataGrid control (called MyDataGrid in the example). The example uses an empty excel file as a template. This template is to be added to the project resources. I used a template first to try and then to make some formatting for the (empty) cells before they get filled by our program and avoid to do that formatting pro grammatically.

    Private Sub Export_File_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Export_File.Click
        If Me.MyDataGrid.Rows.Count > 0 Then
            'SVFileDialog is a SaveFileDialog
            If SVFileDialog.ShowDialog() = DialogResult.OK Then
                Dim FilePath As String
                Dim i, j As Integer
                Dim appExcel As Excel.Application
                Dim wbExcel As Excel.Workbook
                Dim wsExcel As Excel.Worksheet
                FilePath = Application.UserAppDataPath & "\empty.tmp"
                Dim Ob As Object = My.Resources.ResourceManager.GetObject("empty")
                Try
                    Dim fs As New IO.FileStream(FilePath, IO.FileMode.OpenOrCreate)
                    Dim w As New IO.BinaryWriter(fs)
                    w.Write(Ob)
                    w.Close()
                    fs.Close()
                    Dim CurCulInfo As System.Globalization.CultureInfo
                    'Get current culture info, back it up
                    CurCulInfo = System.Threading.Thread.CurrentThread.CurrentCulture
                    'Forcing en-US culture info cos due to a bug, any other culture info will raise an exception
                    System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
                    appExcel = CreateObject("Excel.Application")
                    wbExcel = appExcel.Workbooks.Open(FilePath)
                    wsExcel = wbExcel.Worksheets(1)
                    For j = 0 To MyDataGrid.ColumnCount - 1
                        wsExcel.Cells(1, j + 1) = MyDataGrid.Columns(j).HeaderText
                        For i = 0 To MyDataGrid.RowCount - 1
                            wsExcel.Cells(i + 2, j + 1) = MyDataGrid(j, i).Value.ToString()
                        Next
                    Next
                    wsExcel.Columns.AutoFit()
                    wsExcel.SaveAs(SVFileDialog.FileName)
                    wbExcel.Close()
                    appExcel.Quit()
                    System.Threading.Thread.CurrentThread.CurrentCulture = CurCulInfo
                    releaseObject(appExcel)
                    releaseObject(wbExcel)
                    releaseObject(wsExcel)
                Catch er As Exception
                    _Display_Error(er.Message)
                End Try
            End If
        End If
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

Note that the lines 18 to 22 are used to save the current culture info of the executing thread in order to switch to English culture info. Line 36 is to restore it. This trick is used to overcome the “Old format or invalid type library” bug in Microsoft’s libraries described here. “releaseObject”, you guessed it, is just for cleaning up the mess.

I also remember I spent a lot of time looking for the required dlls so I uploaded those for Excel and also for Word. You just add them as a reference in your projects.

Related Stuff

Some Crystal Reports useful formulas Creating a (unique*) time-based identifier (time-stamp), could be used to uniquely identify reports for example: *: Only if you do not call it more than once per second Replacing NULL...

A Convenient Way to Fill-In Word Custom Properties In a previous post, we discussed an easy way to fill-in forms and reports having recurrent data with the help of custom properties and fields. Thanks to a comment from...

Word Custom Properties: How to Improve Productivity When working with Microsoft Word, a great part of administrative tasks consist in filling forms and generating reports on a pre-established templates or models. Now if within your document you...

ChlankMail: Extract Email Addresses From Any Text When you get emails like “you’re not gonna be able to pee for 3 years, if you don’t forward this mail to 23.6 of your friends”, the best revenge is...

Using WndProc to Disabe Text Box Context Menu Whether you wanted to forbid users to paste like crazy within textboxes or you wanted to create your own, the default textbox context menu should be deactivated. It’s a good...