Aspose.Cells (.NET) Pivot Table

Aspose.Cells Product Family
1

Hello,

I have a problem here that seems to be a bug. The Pivot Table doesn’t do the count is based on a DateTime. works fine for other data types.

Here is the example:

I have a PivotTable that counts all the rows of a table by month. This works just fine in Excel. When it’s added to the table and the refresh is forced, the PivotTable sets the dates to 1/1/1900. When saved and the Excel opened, the PivotTable has the wrong date, but a refresh puts everything good again.

The Code:

foreach (PivotTable pt in worksheets.SelectMany(w => w.PivotTables))
            {
                //pt.RefreshDataFlag = true;
                pt.RefreshData();
                //pt.RefreshDataFlag = false;
                pt.CalculateData();
            }

Not much to say…except the row is added previously.

Thank you,

2

@julio_Almeida,

Could you please share your template Excel file and complete sample code (runnable) that can be used to reproduce the issue on our end. We will check and try to figure out your issue (if found) soon.

PS. please zip the Excel file or other resources prior attaching here.

3

Here it is. Thank you for the fast reply.

InvoiceServiceDebug.zip (12.9 KB)

4

@julio_Almeida,
This issue is reproduced using the following sample code and logged in our database for further investigation. You will be notified here once any update is ready for sharing.

Workbook workbook = new Workbook("InvoiceServiceDebug.xlsx");
workbook.Worksheets["Sheet1"].Cells.InsertRow(2);
workbook.Worksheets["Sheet1"].Cells[2,0].Value = 3;
workbook.Worksheets["Sheet1"].Cells[2,1].Value = 16;
workbook.Worksheets["Sheet1"].Cells[2,2].Value = 2;
workbook.Worksheets["Sheet1"].Cells[2,3].Value = new DateTime(2021,7,14,1,0,0);
workbook.Worksheets["Sheet1"].Cells[2,4].Value = 1;
workbook.Worksheets["Sheet1"].Cells[2,5].Value = 1;

foreach (PivotTable pt in workbook.Worksheets.SelectMany(w => w.PivotTables))
{
    //pt.RefreshDataFlag = true;
    pt.RefreshData();
    //pt.RefreshDataFlag = false;
    pt.CalculateData();
}
workbook.Save("InvoiceServiceDebugOut.xlsx");

This issue is logged as:
CELLSNET-49375 - Issue while refreshing Pivot table after adding data

5

Thank you, I will stay put.

6

@julio_Almeida,

This is to inform you that we have fixed your issue now. We will provide you the fixed version here within the next few days (after performing QA and incorporating other enhancements and fixes).

7

Quite nice! I will be waiting. Thank you,

8

@julio_Almeida,

You are welcome. Please keep in touch.

9

@julio_Almeida,
Please try the latest fix 21.9.5.
Aspose.Cells21.9.5 For .Net2_AuthenticodeSigned.Zip (5.6 MB)
Aspose.Cells21.9.5 For .Net4.0.Zip (5.6 MB)
Aspose.Cells21.9.5 For .NetStandard20.Zip (5.6 MB)

10

Hello,
A great thank you for the weekend work. I use the .NET standard, just replace the .dll (didn’t use the .xml file) and it didn’t work. Is this correct?

Thank you,

11

@julio_Almeida,
I have tried it with the latest .NET standard version shared above but could not observe the issue. The output file does not contain dates now and proper counts are visible in the output file. You may please give it a try and share your feedback. If the issue is not resolved, share the details along with the image showing the issue. Here is the output file created by the latest version.

InvoiceServiceDebugOut.xlsx.zip (14.2 KB)

12

After a thoroughly test, everything works great! The dll was in the wrong project. Thank you for your time!

13

@julio_Almeida,
Good to know that your issue is sorted out. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

14

The issues you have found earlier (filed as CELLSNET-49375) have been fixed in this update. This message was posted using Bugs notification tool by simon.zhao