Pivot: incorrect data

Aspose.Cells Product Family
1

Hi,

I have incorrect data calculating pivot of attached file.



I do for every pivot these steps:



pivot.refreshData();

pivot.calculateData();

pivot.calculateRange();



Seems that the orange row isn’t included in the sum.



Best regards

2

Hi,


Thanks for your posting and using Aspose.Cells.

Is the sample excel file your source excel file or the output excel file generated by Aspose.Cells? Please provide the complete runnable sample code which we could execute to replicate your issue.

Also when I open your excel file, I see a message box by Microsoft Excel 2013? What this message means? Is it the indication of normal or corrupt excel file? Please check the screenshot attached by me for your reference.

Please also download and try the latest version: Aspose.Cells for Java v9.0.9 and see if it makes any difference in resolving this issue. Also you provided the SVG file, is this file related to this issue, how?

3

Hi,
the file isn’t generated by Aspose. Th complete code is:

		Workbook book = new Workbook(strTemplate);
		WorksheetCollection sheets = book.getWorksheets();
		int nSheets = book.getWorksheets().getCount();
		CalculationOptions calcOpt = new CalculationOptions();
		calcOpt.setIgnoreError(true);
		
		for (int nPos = 0; nPos < nSheets; nPos++) {

			sheet = book.getWorksheets().get(nPos);
			sheet.calculateFormula(calcOpt, true);

			PivotTableCollection pivots = sheet.getPivotTables();
				int nPivot = pivots != null ? pivots.getCount() : 0;
				for (int nP = 0; nP < nPivot; nP++) {
					PivotTable pivot = pivots.get(nP);
					try {
						pivot.refreshData();
					} catch (Exception e) {
						;//ERROR
					}
					try {
						pivot.calculateData();
					} catch (Exception e) {
						;//ERROR						}
					try {
						pivot.calculateRange();
					} catch (Exception e) {
						;//ERROR
					}
				}
			}

We don’t have this message on our Excel version 2007, we have Italy region setting.

We use Aspose v9.0.9, the SVG file is the export of the file after calculation.

Best regards

4

Hi, attached the source code, missing the export in SVG format, but the same problem also exporting in HTML or PDF.

Regards

5

Hi,


Thanks for your posting and using Aspose.Cells for Java.

We have tested this issue with the following sample code with your source excel file using the latest version: Aspose.Cells for Java v9.0.9 and found that orange row is not included in the Sum of Pivot Table.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSJAVA-42016 - Orange Row is not included in the Sum of Pivot Table

I have attached the output pdf generated with this code which also contains a comment marking the issue as well as screenshot illustrating the issue for a reference.

Java
Workbook book = new Workbook(dirPath + “sam.xlsx”);
WorksheetCollection sheets = book.getWorksheets();
int nSheets = book.getWorksheets().getCount();
CalculationOptions calcOpt = new CalculationOptions();
calcOpt.setIgnoreError(true);

for (int nPos = 0; nPos < nSheets; nPos++) {

Worksheet sheet = book.getWorksheets().get(nPos);
sheet.calculateFormula(calcOpt, true);

PivotTableCollection pivots = sheet.getPivotTables();
int nPivot = pivots != null ? pivots.getCount() : 0;
for (int nP = 0; nP < nPivot; nP++) {
	PivotTable pivot = pivots.get(nP);
	try {
		pivot.refreshData();
	} catch (Exception e) {
		System.out.println("Error1");// ERROR
	}
	try {
		pivot.calculateData();
	} catch (Exception e) {
		System.out.println("Error2");// ERROR
	}
	try {
		pivot.calculateRange();
	} catch (Exception e) {
		System.out.println("Error3");// ERROR
	}
}

}

//Lines added by me
PdfSaveOptions opts = new PdfSaveOptions();
opts.setOnePagePerSheet(true);

book.save(dirPath + “output.pdf”, opts);


6
Hi,

Thanks for using Aspose.Cells.

This is to inform you that we have fixed your issue CELLSJAVA-42016 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

7

Hi,


Please try our latest version/fix: Aspose.Cells for Java v16.11.6 (attached).

Let us know your feedback.

Thank you.
8

The issues you have found earlier (filed as CELLSJAVA-42016) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.