Export all DataGrid rows (including non visible) WPF to Excel

Question

I am trying to export data from a WPF DataGrid into an excel document. When I run this button, it exports everything that is visible on the screen and or fits in the window. The DataGrid is filled enough that you have to scroll down to see all the values, these hidden values are not exported into the excel document and the error "Object reference not set to an instance of an object." occurs. If all the values fit on the screen, there is no problem.

private void Button_Click(object sender, RoutedEventArgs e)
  {
    Excel.Application excel = new Excel.Application();
    excel.Visible = true;
    Workbook workbook = excel.Workbooks.Add(System.Reflection.Missing.Value);
    Worksheet sheet1 = (Worksheet)workbook.Sheets[1];

    for (int ExcelRow = 1, GridRow = 0; ExcelRow <= dgrid.Items.Count - 1; 
         ExcelRow++, GridRow++)

        {
         Range myRange = (Range)sheet1.Cells[ExcelRow, 1];
         myRange.Value2 = dgrid.Columns[0].Header;

         TextBlock b = dgrid.Columns[0].GetCellContent(dgrid.Items[GridRow]) 
                       as TextBlock;

         Microsoft.Office.Interop.Excel.Range myRange2 = 
                  (Microsoft.Office.Interop.Excel.Range)sheet1.Cells[2, 1];

         myRange.Value2 = b.Text;
         ///////////////////////

         myRange = (Range)sheet1.Cells[ExcelRow, 2];
         myRange.Value2 = dgrid.Columns[1].Header;

         b = dgrid.Columns[1].GetCellContent(dgrid.Items[GridRow]) as 
             TextBlock;

         myRange2 = (Microsoft.Office.Interop.Excel.Range)sheet1.Cells[3, 2];
         myRange.Value2 = b.Text;
         /////////////////////////

         myRange = (Range)sheet1.Cells[ExcelRow, 3];
         myRange.Value2 = dgrid.Columns[2].Header;

         b = dgrid.Columns[2].GetCellContent(dgrid.Items[GridRow]) as 
             TextBlock;

         myRange2 = (Microsoft.Office.Interop.Excel.Range)sheet1.Cells[4, 3];
         myRange.Value2 = b.Text;
        }

    }

Answer 1

If you don't need need anything from the datagrid (like custom column sorting or whatever), you should just export the data directly from the datasource itself.

With that, there are many options available. Personally, I like the approach here, where an extension method handles everything.

https://stackoverflow.com/a/13973274/1415307

using System;
using System.Collections.Generic;
using System.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data;
using System.Data.OleDb;

DataTable dt;
// fill table data in dt here 
...

// export DataTable to excel
// save excel file without ever making it visible if filepath is given
// don't save excel file, just make it visible if no filepath is given
dt.ExportToExcel(ExcelFilePath);

Extension method for DataTable class:

public static class My_DataTable_Extensions
{

    // Export DataTable into an excel file with field names in the header line
    // - Save excel file without ever making it visible if filepath is given
    // - Don't save excel file, just make it visible if no filepath is given
    public static void ExportToExcel(this DataTable tbl, string excelFilePath = null) {
        try {
            if (tbl == null || tbl.Columns.Count == 0)
                throw new Exception("ExportToExcel: Null or empty input table!\n");

            // load excel, and create a new workbook
            var excelApp = new Excel.Application();
            excelApp.Workbooks.Add();

            // single worksheet
            Excel._Worksheet workSheet = excelApp.ActiveSheet;

            // column headings
            for (var i = 0; i < tbl.Columns.Count; i++) {
                workSheet.Cells[1, i + 1] = tbl.Columns[i].ColumnName;
            }

            // rows
            for (var i = 0; i < tbl.Rows.Count; i++) {
                // to do: format datetime values before printing
                for (var j = 0; j < tbl.Columns.Count; j++) {
                    workSheet.Cells[i + 2, j + 1] = tbl.Rows[i][j];
                }
            }

            // check file path
            if (!string.IsNullOrEmpty(excelFilePath)) {
                try {
                    workSheet.SaveAs(excelFilePath);
                    excelApp.Quit();
                    MessageBox.Show("Excel file saved!");
                }
                catch (Exception ex) {
                    throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
                                        + ex.Message);
                }
            } else { // no file path is given
                excelApp.Visible = true;
            }
        }
        catch (Exception ex) {
            throw new Exception("ExportToExcel: \n" + ex.Message);
        }
    }
}

Also, this is placing a requirement on consumers that they have Excel installed. This can cause some complications. If you don't need anything from Excel and just need output that Excel can read, I'd suggest just creating a CSV.

Answer 2

I got the same problem before, and what I found out is you need to add your own DataGrid to inside a ScrollViewer, let's check it out this following example After added scrollViewer I'm sure your code will runs perfect with Excel active.

 <ScrollViewer Grid.Row="0"
                  Margin="5 5 5 5">
        <Grid Grid.Row="0">
            <DataGrid 
                Name="DataGridName"
                HorizontalAlignment="Stretch" 
                Margin="10,10,10,10" 
                VerticalAlignment="Top" 
                Height="Auto" Width="Auto" 
                IsReadOnly="True"
                CanUserSortColumns="False"
                FontSize="13"
                ItemsSource="{Binding Path=ListCustomer}"/>
        </Grid>
    </ScrollViewer>