2 replies to this topic

[HighKing Scott]

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.

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.

Once you have added the reference, then we need to add a button to our form.

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.

[carcrazed484]

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

[HighKing Scott]

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