Excel Pivot Table is not automatically refreshed while the file first get open

jim_liao · February 17, 2020, 9:22am

Hi Support,

I have 2 spreadsheets in an excel file like the attached zip, the first one has some data and the second one has a pivot table that refers to the first one. I use Aspose to write data into the first one and use the following code to refresh the whole file before taking an excel screenshot of the pivot table, however, the pivot is not refreshed when I first open it, but refresh after I click the ‘Enable Editing’ button of Excel.
image.png (10.4 KB)

could help to identify the root cause? thank you in advance.

pivot_refresh-2020-02-16.zip (44.8 KB)

public static void refreshFormulaAndPivotTables(com.aspose.cells.Workbook workbook)
throws Exception {
// calculate formulas
workbook.calculateFormula();
// refresh pivot tables
Iterator it = workbook.getWorksheets().iterator();
while (it.hasNext()) {
Worksheet sheet = (Worksheet) it.next();
Iterator pivotTables = sheet.getPivotTables().iterator();
Iterator pivotChart = sheet.getCharts().iterator();
while (pivotTables.hasNext()) {
PivotTable table = (PivotTable) pivotTables.next();
table.setRefreshDataOnOpeningFile(true);
table.setRefreshDataFlag(true);
table.refreshData();
table.calculateData();
table.setRefreshDataFlag(false);
}
while (pivotChart.hasNext()) {
Chart chart = (Chart) pivotChart.next();
chart.refreshPivotData();
}
}
}

Amjad_Sahi · February 17, 2020, 11:06am

@jim_liao,

Thanks for the template file, sample code and screenshot.

Please try our latest version/fix: Aspose.Cells for Java v20.1.11 (attached)
aspose-cells-20.1.11-java.zip (6.8 MB)

I tested your scenario/ case using Aspose.Cells for Java v20.1.11, it works fine and I do not get any exception or pivot table’s refresh issue. I used the following sample code and find attached the output file for your reference:
e.g
Sample code:

Workbook workbook = new Workbook("f:\\files\\pivot_refresh-2020-02-16.xlsx");
		// calculate formulas
		workbook.calculateFormula();
		// refresh pivot tables
		Iterator it = workbook.getWorksheets().iterator();
		while (it.hasNext()) {
		Worksheet sheet = (Worksheet) it.next();
		Iterator pivotTables = sheet.getPivotTables().iterator();
		Iterator pivotChart = sheet.getCharts().iterator();
		while (pivotTables.hasNext()) {
		PivotTable table = (PivotTable) pivotTables.next();
		table.setRefreshDataOnOpeningFile(true);
		table.setRefreshDataFlag(true);
		table.refreshData();
		table.calculateData();
		table.setRefreshDataFlag(false);
		}
		while (pivotChart.hasNext()) {
		Chart chart = (Chart) pivotChart.next();
		chart.refreshPivotData();
		}
		}
		workbook.save("f:\\files\\out1.xlsx");

Let us know if you still find any issue with latest fix/version, please provide details, sample code, output file, etc., we will check it soon.
files1.zip (45.2 KB)

jim_liao · February 18, 2020, 6:45am

Hello Amjad,

Thanks for your prompt response, however, I found out that your out1.xlsx still doesn’t work, the correct result of (New) Buyer per category of pivot table should be:

image.png (358.7 KB)

but your is:
image.png (350.4 KB)

Besides, I also tried your attached latest version java, but got the same outcome,could you please help me take a further look? Thank you in advance!

Amjad_Sahi · February 18, 2020, 8:44am

@jim_liao,

Thanks for the screenshots.

I am sorry but I still could not find the issue with my output file. I opened the output file into MS Excel 2007 and 2013 and could not notice your mentioned issue with the pivot table, see the screenshot for your reference:
sc_shot_Excel2013.png (81.3 KB)

jim_liao · February 18, 2020, 9:47am

Hi Support,

Could you help me confirm whether your excel has enabled protected view

Step 1: Start Excel, click File and then choose Options.
Step 2: Click Trust Center and then Trust Center Settings.
Step 3: Click Protected View on the left sidebar, then enable (check) all three of the options listed there.

when the protected view function is enabled, you are able to the un-refreshed pivot table before clicking the editing view button.

when the protected view function is disabled, the excel will automatically get refreshed once you open it

thanks in advance

Amjad_Sahi · February 18, 2020, 10:37am

@jim_liao,

Yes, all three check boxes are checked.

Even I open the output file into MS Excel (2013) and then try to manually refresh in MS Excel (Analyze|Refresh All) but nothing is changed, I still get the expected (good) view of Pivot table.

jim_liao · February 19, 2020, 2:51am

Hi Support,

I doubt that your excel did the automatically refresh for you, I think we can verify it by taking screenshot, could you rerun your script to generate a brand new file out1.xlsx file, and run the following script to directly take a screenshot from the file (don’t manually open out1.xlsx file before taking a screenshot). String range = D6:P26 of the spreadsheet (New) Buyer per category

public static File excel2PNG(com.aspose.cells.Workbook workbook, String range, String pngFilePath) throws Exception {
ExcelUtils.refreshFormulaAndPivotTables(workbook);
WorksheetCollection worksheetCollection = workbook.getWorksheets();
Worksheet sheet = null;
// check sheet and range
// 1. “sheet1” -> “sheet1” + all values ranges
// 2. “sheet1!A1:B1” -> “sheet1” + “A1:B1”
String sheetName = range.split("!")[0];
for (int i = 0; i < worksheetCollection.getCount(); i++) {
if (worksheetCollection.get(i).getName().equals(sheetName)) {
sheet = worksheetCollection.get(i);
break;
}
}
if (sheet == null) {
throw new IllegalArgumentException(
String.format(“Sheet %s is not found in the excel template.”, sheetName));
}
if (range.split("!").length == 1) {
range = ExcelUtils.findNonEmptyCellRange(sheet);
} else {
range = range.split("!")[1];
}
System.out.println(String.format(“Screenshot on range %s”, range));
sheet.getPageSetup().setPrintArea(range);
sheet.getPageSetup().setLeftMargin(0);
sheet.getPageSetup().setRightMargin(0);
sheet.getPageSetup().setTopMargin(0);
sheet.getPageSetup().setBottomMargin(0);
ImageOrPrintOptions orPrintOptions = new ImageOrPrintOptions();
orPrintOptions.setVerticalResolution(100);
orPrintOptions.setHorizontalResolution(100);
orPrintOptions.setRenderingHint(RenderingHints.KEY_COLOR_RENDERING,
RenderingHints.VALUE_COLOR_RENDER_SPEED);
orPrintOptions.setRenderingHint(RenderingHints.KEY_FRACTIONALMETRICS,
RenderingHints.VALUE_FRACTIONALMETRICS_ON);
orPrintOptions.setRenderingHint(RenderingHints.KEY_ALPHA_INTERPOLATION,
RenderingHints.VALUE_ALPHA_INTERPOLATION_SPEED);
orPrintOptions
.setRenderingHint(RenderingHints.KEY_DITHERING, RenderingHints.VALUE_DITHER_ENABLE);
orPrintOptions
.setRenderingHint(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_ON);
orPrintOptions.setRenderingHint(RenderingHints.KEY_TEXT_ANTIALIASING,
RenderingHints.VALUE_TEXT_ANTIALIAS_ON);
orPrintOptions.setCheckWorkbookDefaultFont(false);
orPrintOptions.setDefaultFont(“Microsoft YaHei”);
orPrintOptions.setQuality(100);
orPrintOptions.setOnePagePerSheet(true);
orPrintOptions.setImageType(ImageType.PNG);
orPrintOptions.setOnlyArea(true);
orPrintOptions.setGridlineType(GridlineType.HAIR);
SheetRender sr = new SheetRender(sheet, orPrintOptions);
sr.toImage(0, pngFilePath);
return new File(pngFilePath);
}

please kindly let me know what screenshot outcome you get, thanks in advance!

Amjad_Sahi · February 19, 2020, 11:06am

@jim_liao,

Thanks for the code segment and details.

I could not compile your code segment as I am not sure about ExcelUtils class and its static methods. Either update your code segment or provide more details on ExcelUtils and methods, so we could execute your code precisely and get the output file.

By the way, in the mean time, we will also try your scenario on some other machines if we could reproduce it.

Amjad_Sahi · February 19, 2020, 11:29am

@jim_liao,

After further evaluation, I am able to reproduce the issue as you mentioned by using the following sample code (I add a line of code to the existing code segment) with your template file. I found Pivot Table is not refreshed properly when the file is opened into MS Excel. If we manually refresh the Pivot table in MS Excel, it works as expected.
e.g
Sample code:

Workbook workbook = new Workbook("f:\\files\\pivot_refresh-2020-02-16.xlsx");
        // calculate formulas
        workbook.calculateFormula();
        // refresh pivot tables
        Iterator it = workbook.getWorksheets().iterator();
        while (it.hasNext()) {
        Worksheet sheet = (Worksheet) it.next();
        Iterator pivotTables = sheet.getPivotTables().iterator();
        Iterator pivotChart = sheet.getCharts().iterator();
        while (pivotTables.hasNext()) {
        PivotTable table = (PivotTable) pivotTables.next();
        table.setRefreshDataOnOpeningFile(true);
        table.setRefreshDataFlag(true);
        table.refreshData();
        table.calculateData();
        table.setRefreshDataFlag(false);

table.setRefreshDataOnOpeningFile(false);//I added this line.

        }
        while (pivotChart.hasNext()) {
        Chart chart = (Chart) pivotChart.next();
        chart.refreshPivotData();
        }
        }
        workbook.save("f:\\files\\out1.xlsx");
        workbook.save("f:\\files\\out12.pdf");//the output PDF is not ok either.

I have logged a ticket with an id “CELLSJAVA-43127” for your issue. We will look into it soon.

Once we have an update on it, we will let you know.

jim_liao · February 21, 2020, 3:27am

Hi Support,

thanks for timely reply, may I know if you have an estimated schedule for this bug fix? thanks

Amjad_Sahi · February 21, 2020, 8:29am

@jim_liao,

We evaluated your issue further. Well, if you need to save the file in MS Excel file format, you can sort out your issue easily. See the added/updated line of code (in bold) that you may add to your code segment to fix your issue:
e.g
Sample code:

public static void refreshFormulaAndPivotTables(com.aspose.cells.Workbook workbook)
throws Exception {
// calculate formulas
workbook.calculateFormula();
// refresh pivot tables
Iterator it = workbook.getWorksheets().iterator();
while (it.hasNext()) {
Worksheet sheet = (Worksheet) it.next();
Iterator pivotTables = sheet.getPivotTables().iterator();
Iterator pivotChart = sheet.getCharts().iterator();
while (pivotTables.hasNext()) {
PivotTable table = (PivotTable) pivotTables.next();
table.setRefreshDataOnOpeningFile(true);
table.setRefreshDataFlag(true);
table.refreshData();
table.calculateData();
table.setRefreshDataFlag(false);
table.setRefreshDataOnOpeningFile(true);
}
while (pivotChart.hasNext()) {
Chart chart = (Chart) pivotChart.next();
chart.refreshPivotData();
}
}
}

Let us know if you still have any issue.

jim_liao · February 21, 2020, 11:08am

Hi Support, the function setRefreshDataOnOpeningFile doesn’t work, and we also tried it before reaching your help, I think we need a solution that can take a screenshot(excel2PNG) of the refreshed pivot table. thank you.

Amjad_Sahi · February 21, 2020, 1:19pm

@jim_liao,

Well, my solution will only work if you are saving to MS Excel file format. Since you are rendering to image file/PDF, so it won’t help much. I have reopened your issue and we will look into it soon.

jim_liao · February 24, 2020, 6:36am

Hi Support,

Appreciate that, please update the status here once you find a solution, thanks

ahsaniqbalsidiqui · February 24, 2020, 8:30am

@jim_liao,
Sure, we will update you in this topic once we have some news for you.

Amjad_Sahi · March 5, 2020, 8:48am

@jim_liao,

This is to inform you that we have fixed your issue now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

jim_liao · March 5, 2020, 9:03am

Hi Support,

Thanks for the prompt fix, we look forward to receiving it!

ahsaniqbalsidiqui · March 10, 2020, 10:17am

@jim_liao,
Please try our latest version/fix: Aspose.Cells for Java v20.2.4:
aspose-cells-20.2.4-java.zip (7.0 MB)

Your issue should be fixed in it.

Let us know your feedback.

jim_liao · March 11, 2020, 6:38am

Hi Support,

I have tried, the 20.2.4 release exactly solved the bug, appreciate your help!

Amjad_Sahi · March 11, 2020, 9:01am

@jim_liao,

Good to know that your issue is sorted out by the new fix/version. 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.