Hi,
Pivot table style is not consistent between xls and xlsx when no table style is set. I have enclosed both excel outputs and images. I am expecting white font on dark background similar to xls in xlsx. Please help me with your inputs to resolve. Fyi, i have attached java code (only method), as it is several hundred of lines i have attached instead of copying here. Additional Details, assume that the code should not execute, setPivotTableStyleName() statement and report layout is Outline.
xlsx pivot table style issue.zip (562.3 KB)
Sure. Thanks for responding. i just need the pivot format similar to xls i.e. dark background and white font color. Currently xlsx is producing dark background and black font color.
@koteswaragunda
Please check the attached simple project
code.zip (203.3 KB)
It can work as your excepted .
It’s better that you can change sample codes to reproduce your issue.
Thanks a lot @simon.zhao let me go through the project and come back for any further support. Thanks once again 
You are welcome. Please take your time to evaluate and improve your project as needed. If you still encounter any issues, kindly provide a simplified and standalone Java program or sample (runnable) code to reproduce the problem. We will review it and assist you soon.
Hi @amjad.sahi, I have reproduced the problem. Please find the enclosed details. Few point for your reference are,
- I have added following code for the sample program suggested by @simon.zhao in the earlier reply.
Style dxfStyle = workbook.createStyle();
*** Font font = dxfStyle.getFont();***
*** font.setSize(8);***
*** pt1.formatAll(dxfStyle);*** - The code is giving expected result for excel 97 to 2003 format. Both i/p and o/p types are in xls
- But same is failing for excel 2007 i.e. xlsx. Both i/p and o/p types are in xlsx and the pivot o/p has dark back-ground and dark font color instead of white font color.
Please suggest. I am expecting that the xlsx o/p also should match with xls.
FYI, I have enclosed 5 files,
- 2 files related xls i/p & o/p
- 2 files related to xlsx i/p & o/p
- 1 java sample code
pivot-format-issue.zip (887.4 KB)
@koteswaragunda
We can obtain the correct results by testing on the latest version v24.8 using the following sample code and sample files. Please refer to the attachment. result.zip (897.5 KB)
Workbook workbook;
try {
workbook = new Workbook(filePath + "2.xlsx");
//workbook = new Workbook(filePath + "2.xls");
workbook.getWorksheets().add();
Worksheet sheet = workbook.getWorksheets().get(1);
sheet.getPivotTables().add("Data!A1:AL1419", "A15", "PivotTable1");
PivotTable pt = sheet.getPivotTables().get(0);
pt.addFieldToArea(PivotFieldType.ROW, "Organization Name");
pt.addFieldToArea(PivotFieldType.DATA, "Annual Salary");
pt.setAutoFormatType(PivotTableAutoFormatType.REPORT_1);
PivotTable pt1 = sheet.getPivotTables().get(0);
Style dxfStyle = workbook.createStyle();
Font font = dxfStyle.getFont();
font.setSize(8); //// commented for bug #20621,setting font size based on profile value entire
//// workbook in GenerateExcel jsp
pt1.formatAll(dxfStyle);
workbook.save(filePath + "2_dest_java.xlsx");
//workbook.save(filePath + "2_dest_java.xls");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Would you like to use the latest version v24.8 for further testing? If there are still issues, please provide your detailed information, including but not limited to operating system, Excel version, Java version, etc. We will check it soon.
Hi @John.He,
we were able to get the expected results on 18.5
Now i am working on code migration of our project, not sure what i am missing and it leads into this issue. I am providing the both code samples (old and new) versions of our project files but its so lengthy code. Please have a look, i am sure you definitely find the reason behind it. Thank you.
FYI, project files contains,
- old version folder: please check, GenerateExcelHelper → generateExcel() → addPivotTable2Sheet()
- new version folder: please check, ExcelOutputGenerator → generateOutputFile() → ExcelOutputPivotHelper → addPivotTable2Sheet()
Old version is able to generate expected result
proj-old-new-java-code.zip (36.9 KB)
@koteswaragunda
We have checked both the old and new versions of the code. Sorry, we are currently unable to locate the code that caused the issue. Would you like to provide sample files and executable sample code to reproduce the issue? We will check it soon.
Hi @John.He
I have already provided the executable sample code in my post no. 7 i.e. pivot-format-issue.zip and you mentioned that on new version i.e. v24.8 this issue is not occurring. But its a management decision to migrate to v24.8
Here the issue is setting the font size in pivot table. Can you please let me know different approaches available to set the font size in pivot table. It may give some more details to overcome from this issue.
And is there any way to set the font color of only pivot rowHeaders, columnHeaders and subtotals?
Have you not upgraded to or tried Aspose.Cells for Java v24.8 yet? Please note that we have not reproduced the issue using the latest version, so kindly upgrade to and try it. I tested your scenario/case with v24.8 using the following sample code with your template XLSX file, and the output XLSX (attached) is OK. The font size (8) is correctly applied to the pivot table report in the output XLSX file.
e.g.,
Sample code:
Workbook workbook = new Workbook("2.xlsx");
workbook.getWorksheets().add();
Worksheet sheet = workbook.getWorksheets().get(1);
sheet.getPivotTables().add("Data!A1:AL1419", "A15", "PivotTable1");
PivotTable pt = sheet.getPivotTables().get(0);
pt.addFieldToArea(PivotFieldType.ROW, "Organization Name");
pt.addFieldToArea(PivotFieldType.DATA, "Annual Salary");
pt.setAutoFormatType(PivotTableAutoFormatType.REPORT_1);
PivotTable pt1 = sheet.getPivotTables().get(0);
Style dxfStyle = workbook.createStyle();
com.aspose.cells.Font font = dxfStyle.getFont();
font.setSize(8);
pt1.formatAll(dxfStyle);
workbook.save("2_out1.xlsx");
2_out1.zip (297.2 KB)
We apologize, but we cannot address your issue in older versions. Additionally, we cannot provide fixes for older versions. All the fixes and enhancements are based on the latest APIs. Over time, we have made numerous improvements and other fixes to manipulate and render pivot tables feature for advanced MS Excel formats, so upgrading to the latest versions is recommended.