Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Export Datagrid to Excel

grid

  • Please log in to reply
3 replies to this topic

#1 HighKing Scott

HighKing Scott

    CC Newcomer

  • Just Joined
  • PipPip
  • 14 posts

Posted 08 June 2011 - 05:59 AM

In this tutorial you need to have a connection to a database already in place, for instruction on how to do this, see this tutorial.

First we need to add a reference to Excel Object Library in our project. Click on Project > Add Reference.
Posted Image
Depending on your version of Visual Studio, your add reference dialog may look different and depending on the version of Microsoft Office you have installed, your object reference version may be different as well.
Posted Image
Once you have added the reference, then we need to add a button to our form.
Posted Image
Double click on the button to bring up the code window. At the very top of the code window type the following:
Imports Microsoft.Office.Interop
Then place the following code in the button-click event:
        Dim rowsTotal, colsTotal As Short
        Dim I, j, iC As Short
        System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
        Dim xlApp As New Excel.Application
 
        Try
            Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
            Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
            xlApp.Visible = True
            'Be sure to rename the datagrid to the name of your own datagrid
            rowsTotal = ContactsDataGridView.RowCount - 1
            colsTotal = ContactsDataGridView.Columns.Count - 1
 
            With excelWorksheet
                .Cells.Select()
                .Cells.Delete()
                For iC = 0 To colsTotal
                    .Cells(1, iC + 1).Value = ContactsDataGridView.Columns(iC).HeaderText
                Next
 
                For I = 0 To rowsTotal
                    For j = 0 To colsTotal
                        .Cells(I + 2, j + 1).value = ContactsDataGridView.Rows(I).Cells(j).Value
                    Next j
                Next I
 
                .Rows("1:1").Font.FontStyle = "Bold"
                .Rows("1:1").Font.Size = 10
                .Cells.Columns.AutoFit()
                .Cells.Select()
                .Cells.EntireColumn.AutoFit()
                .Cells(1, 1).Select()
            End With
 
        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            'RELEASE ALLOACTED RESOURCES
            System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
            xlApp = Nothing
        End Try

Make sure you replace the name of your datagrid in the code above.

This code will export the data in your datagrid and do some formatting to boot.

Enjoy.
  • 1

#2 carcrazed484

carcrazed484

    CC Lurker

  • Just Joined
  • Pip
  • 6 posts

Posted 27 June 2011 - 09:12 AM

Hey! I'm just wondering if this is also applicable with Open Office spreadsheet?
  • 0

#3 HighKing Scott

HighKing Scott

    CC Newcomer

  • Just Joined
  • PipPip
  • 14 posts

Posted 27 June 2011 - 09:28 AM

I'm not familiar with Open Office Documents, but I do recall reading that Open Office is compatible with XLSX/XLS doc types. If that's the case, Open Office should still be able to open the XLSX/XLS doc that this will export.

I will do some checking on Open Office and I may add that to an update in the future on this code.

Thanks
  • 1

#4 JorgeGarcia

JorgeGarcia

    CC Lurker

  • Just Joined
  • Pip
  • 1 posts

Posted 08 August 2014 - 10:53 AM

It worked like a charm!

Thank you so much man :)


  • 0





Also tagged with one or more of these keywords: grid