CODEWITHSUNDEEP
c#wpfoffice-interopexport-to-excelcom-interop
ethanjames.2342
ethanjames.2342

Reputation: 73

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

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

enter image description here

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);
            }
        }

Upvotes: 0

Views: 166

Answers (2)

thatguy
thatguy

Reputation: 22079

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];
   }
}

Upvotes: 1

Andy
Andy

Reputation: 12276

When you do

dt_ReportList.Items[i].ToString();

You are referencing the row, the entire row. This is your basic problem here and why you get a datarowview.

You want the column value in that row.

I think you can reference that using the column index.

Hence try:

dt_ReportList.Items[i][j].ToString();

Upvotes: 0

Related Questions