Error when pivoting a table and printing as PDF

Aspose.Cells Product Family
1

Hello,


We currently have automated processes that add data to an excel file’s sheet and later pivot based on a given set of parameters to finally print as pdf. Sadly, the pivoting isn’t working, and the final pdf shows with selections made on the pivot but no data appears in the table, as if it was never updating after the pivoting.

Any help will be greatly appreciated.

PS: I’m attaching the XLSX to pivot.

2

Hi,


Please try our latest version/fix: Aspose.Cells for Java v8.0.2.3

If you still find any issue, we need your JAVA program (runnable) to reproduce the issue on our end. Also provide the template files (input + output (XLS and Pdf)), it will help us to look into your issue more precisely to consequently figure it out soon.

Thank you.

3

Hi,


The problem persists with the latest version. I’m attaching the result of processing the excel file previously attached.
The pivot values are the ones on the REPORT_PARAMETERS sheet.

The relevant parts of the code are as follows:

for (ReportParameters parameter : parameters) {

int index = parameters.indexOf(parameter);


outputPath = outputPath.charAt(outputPath.length() - 1) == '/' ? outputPath

: outputPath + "/";


System.out.println("Printing " + outputPath

+ parameter.getReportFileName());

System.out.println("Index is: " + index + " options size is: " + options.size());

xlsxHandler.useAllPivotFieldAsMultipleValue();

for (ReportCellChanges change : changes) {

String sheetName = change.getSheetName();

String pivotTableName = change.getPivoteTableName();

String filterName = change.getCellAttributeName();

String value = parameter.getParameter(change

.getCellAttributeValue());

System.out.println("sheetName: " + sheetName + ", pivotTableName: " + pivotTableName + " filterName: " + filterName + ", value: " + value);


xlsxHandler.setValueOnFilter(sheetName, pivotTableName,

filterName, value);

}

xlsxHandler.updatePivotTables();

xlsxHandler.updateSheet();


// Prints the final pdf file

try {

String path = outputPath

+ parameter.getReportFileName();

ReportPrintOptions opts = options.get(index);

System.out.println("Orientation: " + opts.getOrientation());

System.out.println("Pages height: " + opts.getPagesHeight());

System.out.println("Pages width: " + opts.getPagesWidth());

System.out.println("From: " + opts.getFrom());

System.out.println("To: " + opts.getTo());

xlsxHandler.printSheet(path, options.get(index).getSheet(),

FileFormatType.PDF, options.get(index));


System.out.println("");


} catch (Exception e) {

System.out.println("Error printing "

+ parameter.getReportFileName() + "\n"

+ e.getMessage());

for (StackTraceElement element: e.getStackTrace()) {

System.out.println(element);

}

System.exit(5);

}

}

xlsxHandler ----

public void setValueOnFilter(String sheetName, String pivotTableName,

String filterName, String value) {

PivotTable table = sheets.get(sheetName).getPivotTables()

.get(pivotTableName);


PivotFieldHelper helper = new PivotFieldHelper(table);

helper.setFilterOptionByString(filterName, value);

try {

table.calculateData();

table.refreshData();

table.setRefreshDataOnOpeningFile(true);

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

PivotFieldHelper ----

public void setFilterOptionByString(String filterName, String value){

PivotField field = table.getPageFields().get(filterName);

HashSet values = new HashSet(Arrays.asList(value.split(":")));

field.setMultipleItemSelectionAllowed(true);

PivotItemCollection collection = field.getPivotItems();

for(int i=0; i < collection.getCount(); i++){

PivotItem item = collection.get(i);

String itemValue = item.getStringValue();

if(value.equals("(All)")){

item.setHidden(false);

}

else {

if(values.contains(itemValue)){

item.setHidden(false);

System.out.println("\t" + filterName+": "+itemValue);

}

else {

item.setHidden(true);

}

}

}

}




4

Hi,


While we evaluate your issue on our end, I think you should first call Workbook.CalculateFormula (as you have used formulas in the template Excel file). Also you should substitute the code segment i.e.,:
try {

table.calculateData();

table.refreshData();

table.setRefreshDataOnOpeningFile(true);

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

to:

try {

table.refreshData();

table.calculateData();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}


to check if it works fine.

Also, kindly provide your expected files (Excel file and output PDF file that you may render via MS Excel renderer), this will help us to evaluate your issue soon.

Thank you.
5

Hi,


I tried the suggested change, but the result was the same.
I’m attaching the pivoted tabled and a pdf print of it.

6

Hi,


Thanks for your expected files

I tried to evaluate your code snippet properly but I am not sure about some of your modules and their respective object variables/ pointers with their values e.g
useAllPivotFieldAsMultipleValue, ReportCellChanges, updatePivotTables, updateSheet() and others etc. Also, I tried to refresh all your pivot tables manually in your output file (by Aspose.Cells) but MS Excel could not refresh the values as per your expected files. Also, there are quite a long list of formulas which are pasted into the worksheets cells.

We appreciate if you could simplify it and create a simple console application (runnable - standalone JAVA program) with all the files and provide us here to reproduce the issue on our end, so we could analyze your issue accurately and try to figure it out (if we found the issue) on our end.

Thank you.
7

Hi,


Is there any progress on this?
8

Hello,


The fact that Aspose can’t update the data as expected -is- the issue.

I’m adding the snippets of the code you mention. (Most of them are helpers to move through severeal sheets, fields, etc).

public void useAllPivotFieldAsMultipleValue() {

for (int i = 0; i < sheets.getCount(); i++) {

for (int j = 0; j < sheets.get(i).getPivotTables().getCount(); j++) {

PivotTable table = sheets.get(i).getPivotTables().get(j);

PivotFieldHelper helper = new PivotFieldHelper(table);

helper.usePivotFieldAsMultipleValue();

}

}

}


public void usePivotFieldAsMultipleValue(){

for(int i = 0; i < table.getPageFields().getCount(); i++){

PivotField field = table.getPageFields().get(i);

field.setMultipleItemSelectionAllowed(true);

PivotItemCollection collection = field.getPivotItems();

for(int j=0; j < collection.getCount(); j++){

PivotItem item = collection.get(j);

item.setHidden(false);

}

}

}


/**

* Recalculate all pivot tables of the workbook

*/

public void updatePivotTables() {

for (int i = 0; i < sheets.getCount(); i++) {

@SuppressWarnings("unchecked")

Iterator iterator = sheets.get(i).getPivotTables()

.iterator();

while (iterator.hasNext()) {

PivotTable it = iterator.next();

System.out.println("Updating: " + it.getName());

updatePivotTable(it);

}

}

}


/**

* Recalculate a pivot table values after a pivot table filter changes

*

* @param table

*/

public void updatePivotTable(PivotTable table) {


try {

table.refreshData();

table.calculateData();

table.setRefreshDataOnOpeningFile(true);


} catch (Exception e) {

e.printStackTrace();

}

}


ReportCellChanges is just a container of the pivoting to do on the table.

9

Hi,


Well, we need more time to evaluate your issue to be reproduced on our end as your code segments might not been directly merged and compiled for somehow missing data and objects etc. I have logged an investigation ticket with an id “CELLSJAVA-40843” into our database. Our concerned developer will investigate and look into it if we could trace the issue on our end to consequently figure it out soon. We might provide some workaround (adjustments in the codes) or fix (if we found the issue)

Thank you.
10

Hi,


We have looked into your issue further.
We evaluated and can see that your provided “pivoted.xlsx” file has data specified in the spreadsheet so when we directly save this file to PDF, it would also contain data specified/ set with proper pivot tables. So, we need to know how did you specify the data in the spreadsheet as per the “pivoted.xlsx” so that we could simulate the issue on our end. We appreciate if you could also provide the code snippets on how did you specify the data and apply formulas with formattings as per your “pivoted.xlsx file” so it could be also saved to PDF properly. We still appreciate if you could simplify your project a bit and provide one complete JAVA program/ application (runnable) to reproduce the issue on our end.

Thank you.
11

@abaltra,
Please try our latest version/fix: Aspose.Cells for .NET v21.6.3 (attached)
Aspose.Cells21.6.3 For .Net2_AuthenticodeSigned.Zip (5.5 MB)
Aspose.Cells21.6.3 For .Net4.0.Zip (5.5 MB)
Aspose.Cells21.6.3 For .NetStandard20.Zip (5.5 MB)

Your issue should be fixed in it.
Let us know your feedback.