Exporting Data to Excel from a windows forms .Net application

Exporting data to excel from a web application is really easy. All you need to do is write out an HTML table and change the content type to excel.

But how do you accomplish the same in a windows forms application ? The most obvious solution is Excel Automation. So, here is an example of how I exported a Datagrid to excel. This example is for a .Net 2.0 winform app. But the same code can be used with minor alterations for .Net 1.1 also. (You will know what to alter when your code doesn’t compile. When it starts compiling, it will start working)

Private Sub ExportGridToExcel()
Dim Excel As Object = CreateObject(“Excel.Application”)

If Excel Is Nothing Then
MsgBox(“It appears that Excel is not installed on this machine. This operation requires MS Excel to be installed on this machine.”, MsgBoxStyle.Critical)
Return
End If

‘Make Excel visible
Excel.Visible = True

‘Initialize Excel Sheet
With Excel
.SheetsInNewWorkbook = 1
.Workbooks.Add()
.Worksheets(1).Select()

‘Add header row to Excel Sheet by copying column headers from the Datagrid

Dim Col As DataGridViewColumn
Dim i As Integer = 1
For Each Col In dgRemovaLog.Columns
.Cells(1, i).Value = Col.HeaderText
i += 1
Next

‘Add data to excel sheet by looping through the rows
‘in the datagrid
i = 2
Dim RowItem As DataGridViewRow
Dim Cell As DataGridViewCell
For Each RowItem In dgRemovaLog.Rows
Dim j As Integer = 1
For Each Cell In RowItem.Cells
.Cells(i, j).Value = Cell.Value
j += 1
Next
i += 1
Next
End With

Excel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
Excel = Nothing
MsgBox(“Export to Excel Complete”, MsgBoxStyle.Information)
End Sub

---

Possibly related posts: (automatically generated)

• Moving Excel data into .net