ClosedXML export datagrid to excel only 10 rows

Question

I have a Datagrid with 60 rows full of data and a button to import it to excel:

 <DataGrid AutoGenerateColumns="False" ItemsSource="{Binding Source}"
          CanUserAddRows="False" HeadersVisibility="All"
          Name="dgDisplay">
      <DataGrid.Columns>
          <DataGridTextColumn Header="Day" Binding="{Binding Day}"/>
          <DataGridTextColumn Header="Data" Binding="{Binding Data}"/>
      </DataGrid.Columns>
  </DataGrid>
  <Button Command="{Binding SaveDataGridToExcelCommand}" 
          CommandParameter="{Binding ElementName=dgDisplay}"/>

Where Day and Data just some int data generated random. And my code using ClosedXML to export data from it to Excel, which call in MainWindowViewModel: ObservableObject, using MVVM.Toolkit.

[RelayCommand]
public void SaveDataGridToExcel(DataGrid dataGrid)
{
    DataTable dt = new DataTable();
    foreach (DataGridColumn column in dataGrid.Columns)
    {
        dt.Columns.Add(column.Header.ToString());
    }

    foreach (var item in dataGrid.Items)
    {
        DataRow dr = dt.NewRow();
        bool rowHasData = false;
        for (int i = 0; i < dataGrid.Columns.Count; i++)
        {
            var cellContent = dataGrid.Columns[i].GetCellContent(item);
            if (cellContent is TextBlock textBlock)
            {
                //check if row empty, dont add this row.I add it on purpose to check
                //if the datagrid recognite the rest 50 rows not have data. It actually 
                //dont save those data
                dr[i] = textBlock.Text;
                if (!string.IsNullOrEmpty(textBlock.Text))
                {
                    rowHasData = true;
                }
            }
        }
        if (rowHasData)
        {
            dt.Rows.Add(dr);
        }
    }
    SaveFileDialog saveFileDialog = new SaveFileDialog();
    saveFileDialog.Filter = "Excel files (*.xlsx)|*.xlsx";
    if (saveFileDialog.ShowDialog() == DialogResult.OK)
    {
        using (XLWorkbook wb = new XLWorkbook())
        {
            wb.Worksheets.Add(dt, "Sheet1");
            wb.SaveAs(saveFileDialog.FileName);
        }
    }
}

However, the result saved 60 rows only show 10 rows of data, the rest 50 rows are all empty. If question for why not use Microsoft.Interop.Excel, its because that package is not fit my excel version. I don't read anywhere in ClosedXML has limit or license for this, so I wonder why. Any help is appreciated.

Answer 1

I found answer myself after many hours browsing github. Instead of accessing the cell content, I access the data directly from the ItemsSource of the DataGrid:

public void SaveDataGridToExcel(DataGrid dataGrid)
{
    DataTable dataTable = new DataTable();
    foreach (DataGridColumn column in dataGrid.Columns)
    {
        dataTable.Columns.Add(column.Header.ToString());
    }
    var itemsSource = dataGrid.ItemsSource as IEnumerable;
    if (itemsSource != null)
    {
        foreach (var item in itemsSource)
        {
            var properties = item.GetType().GetProperties();
            var row = dataTable.NewRow();
            foreach (var property in properties)
            {
                row[property.Name] = property.GetValue(item);
            }
            dataTable.Rows.Add(row);
        }
    }
//show dialog...
}