Datat is shown as System.Data.DataRowView when DataGrid table is exported to MS Excel

Question

I have an export to Microsoft Excel button click event. The code that I have takes the DataGrid table and exports it to MS Excel.

The issue I am having is when the DataGrid is exported to Microsoft Excel the columns headers are correct but the data in the cells are showing as System.Data.DataRowView

Below is the code for my button click event:

    private void butn_ExportResults_Click(object sender, RoutedEventArgs e)
    {
        if (dt_ReportList.Items.Count > 0)
        {
            try
            {

                Microsoft.Office.Interop.Excel.Application xcelApp = new Microsoft.Office.Interop.Excel.Application();
                xcelApp.Application.Workbooks.Add(Type.Missing);
               // Worksheet sheet1 = (Worksheet)workbook.Sheets[1];

                for (int i = 1; i < dt_ReportList.Columns.Count + 1; i++)
                {
                    xcelApp.Cells[1, i] = dt_ReportList.Columns[i - 1].Header;
                }

                for (int i = 0; i < dt_ReportList.Items.Count; i++)
                {
                    for (int j = 0; j < dt_ReportList.Columns.Count; j++)
                    {
                        xcelApp.Cells[i + 2, j + 1] = dt_ReportList.Items[i].ToString();
                    }
                }
                xcelApp.Columns.AutoFit();
                xcelApp.Visible = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

Answer 1

You are using a DataTable, so the row data type in the DataGrid will be DataRowView. It contains the values for all columns for the current row. The problem is that you do not access these values but convert the DataRowView instance to string in this line:

xcelApp.Cells[i + 2, j + 1] = dt_ReportList.Items[i].ToString();

It does not override ToString, so the result will just be its type name. Instead you have to access the corresponding columns by index in the Row property, e.g.:

xcelApp[i + 2, j + 1] = dataRowView.Row[j];

The DataRowView only changes in the outer loop, so we get and cast it there via pattern matching. The if statement ensures that the given item is a DataRowView, as the last row can be a blank row of type NewItemPlaceholder when CanUserAddRows is enabled on the DataGrid that needs to be skipped.

for (int i = 0; i < dt_ReportList.Items.Count; i++)
{
   if (!(dt_ReportList.Items[i] is DataRowView dataRowView))
      continue;

   for (int j = 0; j < dt_ReportList.Columns.Count; j++)
   {
      xcelApp[i + 2, j + 1] = dataRowView.Row[j];
   }
}