How to get the data in a DataGridView into an EPPlus ExcelWorksheet

Question

I've tried this code which throws a cast exception:

Dim worksheet As ExcelWorksheet = package.Workbook.Worksheets.Add(pageTitle)
Dim dataTable As DataTable
dataTable = dataGridView.DataSource ' cast exception

Exception:

System.InvalidCastException: Unable to cast object of type 'System.Windows.Forms.BindingSource' to type 'Microsoft.Office.Interop.Excel.DataTable'.

I need a DataTable to set the worksheet:

worksheet.Cells("A1").LoadFromDataTable(dataTable, True)

Answer 1

If you cannot convert the DataSource to a DataTable directly, you can extract the visible data in the DataGridView to a new DataTable using the following routine:

  Public Function Dgv2Dtb(ByVal dgv As DataGridView) As DataTable
    Dim dtbOutput As New DataTable
    For intCol As Integer = 0 To dgv.Columns.Count - 1
      Dim dgvc As DataGridViewColumn = dgv.Columns(intCol)
      Dim dtyCol As System.Type = dgvc.ValueType
      If dtyCol Is Nothing And dgvc.CellType.Name = "DataGridViewTextBoxCell" Then
        dtyCol = GetType(String)
      End If
      Dim dclOutput As DataColumn = New DataColumn(dgvc.HeaderText, dtyCol)
      dtbOutput.Columns.Add(dclOutput)
    Next intCol
    For intRow As Integer = 0 To dgv.Rows.Count - 1
      Dim drwNew As DataRow = dtbOutput.NewRow()
      Dim dgvr As DataGridViewRow = dgv.Rows(intRow)
      For intCol As Integer = 0 To dgv.Columns.Count - 1
        drwNew.Item(intCol) = dgvr.Cells(intCol).Value
      Next intCol
      dtbOutput.Rows.Add(drwNew)
    Next intRow
    Return dtbOutput
  End Function

Usage:

Dim dtbNew As DataTable = Dgv2Dtb(dgvMyDataGridView)