Export Data (including headers) from DataGridView to .xls file

Question

So I am trying to add an "Export to .XLS" option for record data. I have the following code segments for handling the exporting of the data from the DataGridView, however when it opens the Excel file instead of the data being present Cell A1 simply contains the string "System.Drawing."

Can anyone point out what I'm doing incorrectly? Thanks.

    private void copyGridToClipboard(DataGridView dg)
    {
        dg.SelectAll();
        dg.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
        DataObject dataObj = dg.GetClipboardContent();
        if (dataObj != null)
            Clipboard.SetDataObject(dataObj);
    }
    private void myButton_Click(object sender, EventArgs e)
    {
        copyGridToClipboard(myDataGridView);
        Excel.Application xlexcel;
        Excel.Workbook xlWorkbook;
        Excel.Worksheet xlWorksheet;
        object misValue = System.Reflection.Missing.Value;
        xlexcel = new Excel.Application();
        xlexcel.Visible = true;
        xlWorkbook = xlexcel.Workbooks.Add(misValue);
        xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.get_Item(1);
        Microsoft.Office.Interop.Excel.Range CR = (Excel.Range)xlWorksheet.Cells[1, 1];
        CR.Select();
        xlWorksheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
    }

are you familiar with `OpenXML` you can do this a lot easier using that than relying on the slow yet painful process of using `ComInterop` I have an actual posted example on how to do this using `OpenXML` on this `SO` posting via web page Response http://stackoverflow.com/questions/35351849/export-gridview-to-excel-using-closedxml-without-warning-the-file-you-are-tryin/35544487#35544487 also if you are not willing to use a 3rd party dll you can write your own parser very quickly to write the values to comma delimited file and save it as `.CSV` — MethodMan, Feb 22 '16 at 16:31
You may find this post helpful: [Export data from Windows Forms DataGridView and TextBox to Excel](http://stackoverflow.com/questions/33636602/export-data-from-windows-forms-datagridview-and-textbox-to-excel/33684750#33684750) — Reza Aghaei, Apr 08 '16 at 14:17

Arina · Answer 1 · 2016-04-26T08:09:05.447

I've been doing this with DevExpress GridView, so I'm not so sure how different it is to a DataGridView, but I usually take the datatable from the datasource then convert it to an array and directly set the values of the range. Here is some VB.Net that might work.

Dim dt As DataTable = (DirectCast(DataGridView1.DataSource, DataTable))

'convert to array
Dim columnCount As Integer = dt.Columns.Count - 1
Dim dataArray(dt.Rows.Count, columnCount) As Object
For columnIndex As Integer = 0 To columnCount
    dataArray(0, columnIndex) = dt.Columns(columnIndex).Caption
Next columnIndex
For rowIndex As Integer = 0 To dt.Rows.Count - 1
    For columnIndex As Integer = 0 To columnCount
        dataArray(dt.Rows.Count, columnIndex) = dt.Rows(rowIndex)(columnIndex)
    Next columnIndex
Next


'import to excel sheet
Dim lastRow As Integer = dataArray.GetLength(0) + 1
Dim lastColumn As Integer = dataArray.GetLength(1) + 1
Dim cellRange As Excel.Range = xlWorksheet .Range(xlWorksheet .Cells(1, 1), xlWorksheet .Cells(lastRow, lastColumn))
cellRange.Value = dataArray

You may be able to get the data straight as an array from the DataGridView, but I am not familiar with it. This code should be able to go through a VB -> C# Converter.

Export Data (including headers) from DataGridView to .xls file

1 Answers1