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