This code will allow you to Export data from different data sources such as DataTable, DataGridView, and ListView. to Excel. One key thing about it is that it will not convert strings with leading zero to numeric. i.e. 0747 will export as is and not as 747 as it’s usually the case when you send data to excel.
It will also not export data from columns of DataGridView that are not visible.
''' <summary>
''' Exports the supplied data to excel, displaying supplied caption for caption
''' Allows mainly DataTable, DataGridView, and ListView
''' </summary>
''' <param name="data"></param>
''' <param name="caption"></param>
''' <remarks></remarks>
Public Sub ExportToExcel(ByVal data As Object, ByVal caption As String)
Dim excelApp As New Excel.Application
Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
Dim excelRange As Excel.Range
Dim cellColumnIndex As Integer
Try
If data Is Nothing Then Return
If String.IsNullOrEmpty(caption) Then caption = "Data"
' Start Excel and get application object
excelApp.Visible = True
excelApp.Caption = caption
excelWorksheet.Name = caption
excelApp.Cursor = Excel.XlMousePointer.xlWait
'excelApp.WindowState = Excel.XlWindowState.xlMinimized
If TypeOf data Is DataTable Then
Dim _data As DataTable = CType(data, DataTable)
If _data.Columns.Count < 1 Then Return
' Set the table headers from the column names
For columnIndex As Integer = 0 To _data.Columns.Count - 1
excelWorksheet.Cells(1, (columnIndex + 1)) = _data.Columns(columnIndex).ColumnName
Next
' Assign the headers as bold
excelRange = excelWorksheet.Range(excelWorksheet.Cells(1, 1), excelWorksheet.Cells(1, _data.Columns.Count))
excelRange.Font.Bold = True
For rowIndex As Integer = 0 To _data.Rows.Count - 1
For columnIndex As Integer = 0 To _data.Columns.Count - 1
If IsDBNull(_data.Rows(rowIndex).Item(columnIndex)) Then
excelWorksheet.Cells(rowIndex + 2, columnIndex + 1) = String.Empty
ElseIf _data.Columns(columnIndex).DataType.FullName.Equals(GetType(String).FullName) Then
excelWorksheet.Cells(rowIndex + 2, cellColumnIndex) = "'" & CStr(_data.Rows(rowIndex).Item(columnIndex))
Else : excelWorksheet.Cells(rowIndex + 2, columnIndex + 1) = _data.Rows(rowIndex).Item(columnIndex)
End If
Next
Next
' Autofit the columns
excelRange.EntireColumn.AutoFit()
ElseIf TypeOf data Is DataGridView Then
Dim _data As DataGridView = CType(data, DataGridView)
If _data.Columns.Count < 1 Then Return
' Set the table headers from the column names
cellColumnIndex = 0
For columnIndex As Integer = 0 To _data.Columns.Count - 1
If _data.Columns(columnIndex).Visible = False Then Continue For
cellColumnIndex += 1
excelWorksheet.Cells(1, (cellColumnIndex)) = _data.Columns(columnIndex).HeaderText
Next
' set the header row bold
excelRange = excelWorksheet.Range(excelWorksheet.Cells(1, 1), excelWorksheet.Cells(1, _data.Columns.Count))
excelRange.Font.Bold = True
'Export the base data
For rowIndex As Integer = 0 To _data.Rows.Count - 1
cellColumnIndex = 0
For columnIndex As Integer = 0 To _data.Columns.Count - 1
If _data.Columns(columnIndex).Visible = False Then Continue For
cellColumnIndex += 1
If IsDBNull(_data.Item(columnIndex, rowIndex).Value) Then
excelWorksheet.Cells(rowIndex + 2, cellColumnIndex) = String.Empty
ElseIf Not _data.Columns(columnIndex).ValueType Is Nothing _
AndAlso _data.Columns(columnIndex).ValueType.FullName.Equals(GetType(String).FullName) Then
excelWorksheet.Cells(rowIndex + 2, cellColumnIndex) = "'" & CStr(_data.Item(columnIndex, rowIndex).Value)
Else : excelWorksheet.Cells(rowIndex + 2, cellColumnIndex) = _data.Item(columnIndex, rowIndex).Value
End If
Next
Next
' Autofit the columns
excelRange.EntireColumn.AutoFit()
ElseIf TypeOf data Is ListView Then
Dim _data As ListView = CType(data, ListView)
If _data.Columns.Count < 1 Then Return
' Set the table headers from the column names
For columnIndex As Integer = 0 To _data.Columns.Count - 1
excelWorksheet.Cells(1, (columnIndex + 1)) = _data.Columns(columnIndex).Text
Next
' Assign the headers as bold
excelRange = excelWorksheet.Range(excelWorksheet.Cells(1, 1), excelWorksheet.Cells(1, _data.Columns.Count))
excelRange.Font.Bold = True
For rowIndex As Integer = 0 To _data.Items.Count - 1
If IsDBNull(_data.Items(rowIndex).Text) Then
excelWorksheet.Cells(rowIndex + 2, 1) = String.Empty
Else : excelWorksheet.Cells(rowIndex + 2, 1) = "'" & _data.Items(rowIndex).Text
End If
For columnIndex As Integer = 0 To _data.Columns.Count - 1
If IsDBNull(_data.Items(rowIndex).SubItems(columnIndex).Text) Then
excelWorksheet.Cells(rowIndex + 2, columnIndex + 1) = String.Empty
Else : excelWorksheet.Cells(rowIndex + 2, columnIndex + 1) = "'" & _data.Items(rowIndex).SubItems(columnIndex).Text
End If
Next
Next
' Autofit the columns
excelRange.EntireColumn.AutoFit()
ElseIf TypeOf data Is DataGrid Then
Dim _data As DataGrid = CType(data, DataGrid)
' add other data sources
End If
' Make sure Excel is visible and give the user control
excelApp.Visible = True
excelApp.UserControl = True
Catch ex As Exception
Throw ex
Finally
excelApp.Cursor = Excel.XlMousePointer.xlDefault
End Try
End Sub