This code sample shows how to use the Office PIA (Primary Interop Assemblies) to export DataGridView rows into an Excel Worksheet. We do come across this type of requirement frequently where the users need data represented in the form of spreadsheets which they can easily carry with them. With the help PIA (Primary Interop Assemblies) we can use the COM object as an interop assembly which is mapped to the actuall COM component.

For the below sample please add the following references in project.

  • Microsoft Excel 11.0 Object Library
  • Microsoft Office 11.0 Object Library
  • Microsoft Visual basic for Application Extensibility 5.3

Also put add following Excel Namespace in the code

Imports Excel = Microsoft.Office.Interop.Excel

You can place the code below on the Button_Click Event. This code also uses a DataGridView which can be populated using Auto Binding or Manual Binding.
It will write the first row in the excel sheet as the header row with some formatting.

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
rowsTotal = DataGridView1.RowCount - 1
colsTotal = DataGridView1.Columns.Count - 1

With excelWorksheet
.Cells.Select()
.Cells.Delete()For iC = 0 To colsTotal
.Cells(1, iC + 1).Value = DataGridView1.Columns(iC).HeaderText
Next

For I = 0 To rowsTotal - 1
For j = 0 To colsTotal - 1
.Cells(I + 2, j + 1).value = DataGridView1.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("Export Excel Error " & ex.Message)
Finally
'RELEASE ALLOACTED RESOURCES
System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
xlApp = Nothing

End Try 
  • AussieLearner

    Thankyou, Thankyou,Thankyou, after days of searching you are the only one that has shown me how this is done ans as I over 50, yes a late starter It is just wonderfull to know that my application now does what I want
    So much to learn yet but you have helped me so much
    Thankyou

    • http://www.coderewind.com/ Binoj Daniel

      Thanks for your feedback.

      • Mahmoud

        I tried this code but I keep getting empty excel worksheet.

  • Allan

    OK Buy type date no run.

    exemple:

    01/02/2013 06:00:03 in datagrid

    in excel 02/01/2013 06:00

    help

  • new

    works great. Just had to seperate between delete () and For on line 15
    .Cells.Delete()
    For iC = 0 To colsTotal

  • sc

    this code does not show the last row or the data in the last column