ASP.NET, Coding

How to Export DataGridView to Excel

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