Pivot year/month grouping is getting cleared after injecting data in the source range

Aspose.Cells Product Family
1

Hi,
We are using Aspose.Cells (23.2.0).
We have a preconfigured excel file which has a pivot that is grouping by date column by year and month.
After filling source range with some data using Aspose.Cells the pivot data is not grouped by year and month any more.

Instead of correct
correct_pivot.png (26.8 KB)

We get an invalid one without grouping
invalid_pivot.png (5.2 KB)

I’ve attached the full file after processing.invalid_report.zip (44.0 KB)

We use this code to update DateTime cells:

var dateStyle = worksheet.Cells.GetCellStyle(index.Row, index.Column);
dateStyle.Custom = “mm/dd/yyyy”;
worksheet.Cells[index.Row, index.Column].SetStyle(dateStyle);
worksheet.Cells[index.Row, index.Column].PutValue((DateTime)value);

Can you advice what should we do so the grouping is not lost?

Regards

2

@simonatingage,
After filling data, please use the following code to refresh and calculate the pivot table.

pivotTable.RefreshDataFlag = true;
pivotTable.RefreshData();
pivotTable.CalculateData();
pivotTable.RefreshDataFlag = false;

If the issue still cannot be solved, would you like to provide your sample file and executable console project? We need to check if any actions have been made that have caused the grouping settings in the original file to be changed.

3

Hi John,

Thank you for the super fast response.

I think this was caused on our end. By accident I’ve found that we add too many rows so at the end of pivot range and there were couple empty rows which I think caused excel to remove extra grouping as the empty cells would not met date format. After removing extra rows at the end pivot is presenting correct now.

Regards,

4

@simonatingage,
Thank you for your feedback. I’m glad your issue has been solved. If you have any questions, please feel free to contact us.