Not able to get value from pivot sheet having formula in cell

Hi ,

I am having formula in cell in my pivot sheet. This formula is calculated once pivot sheet will refresh. Pivot table is refreshing and I can see value on cell. But when I was trying to get the value using cell.getStringValue(), I am not getting actual value from that cell. Need help to close this issue ASAP.

Note: When I open the excel after complete operation in aspose, and trying to close the file, it ask for saving. When I save the file and run the program again, I used to get the value from that cell.

My sample code for refresh and get value:

Refresh Pivot:
LoadOptions options = new LoadOptions();
options.setParsingFormulaOnOpen(true);
Workbook workbook = new Workbook(filePath,options);

	workbook.calculateFormula(true);
	
	Worksheet worksheet = workbook.getWorksheets().get(sheetName.trim());
	PivotTable pivotTable = worksheet.getPivotTables().get(pivotTableName);

	if (worksheet != null) {
		if (worksheet.getPivotTables().getCount() > 0) {
			if (pivotTable != null) {

				pivotTable.setRefreshDataFlag(true);                    
                try {
					pivotTable.calculateData();
				}catch(Exception e) {
					System.out.println("Error calulating pivot table data");
				}
                //pivotTable.setPreserveFormatting(true);
                //pivotTable.setEnableDrilldown(true);
                //pivotTable.setRefreshDataOnOpeningFile(true);
			}
		}
	}
	workbook.calculateFormula(true);
	
	workbook.save(filePath);
	workbook.dispose();

Get value from formula based cell:

Cell cell = cells.get(“Col” + i);
System.out.println(" Cell Name:: " + cell.getName() + " value " + cell.getStringValue());

Please look into it ASAP.

public static void readData(String file, String sheetName) throws Exception {

	Workbook workbook = new Workbook(file);
	Worksheet sheet = workbook.getWorksheets().get(sheetName);

	Cells cells = sheet.getCells();
	int maxrowcount = 10;
	int colcount = sheet.getCells().getMaxColumn();

	for (int rowIndex = 6; rowIndex <= maxrowcount; rowIndex++) {
		Row row = cells.getRow(rowIndex);
		for (int colIndex = 0; colIndex <= colcount; colIndex++) {
			Cell cell = cells.getCell(rowIndex, colIndex);
			if (cell.getName().contains("Y"))
				System.out.println(cell.getName() + " - " + cell.getStringValue());
		}
	}
}

Thanks
KishoreRefreshPivot.zip (1.0 MB)

@kishore007008,

Please make sure that your refresh and calculate pivot table in code. Also make sure you calculate the formulas in the workbook. Then you may get the cell’s value. See the following three lines:
e.g
Sample code:

......
pivotTable.refreshData();
pivotTable.calculateData();

 workbook.calculateFormula(); 

//......
//your code goes here.
//.....

If you still find any issue, kindly provide us a sample Java program/sample code (runnable) to reproduce the issue. Also zip your template file and attach it here, we will check it soon.

Hi Amjad,

Thanks for your quick reply.
pivotTable.calculateData(); throws null pointer exception for this pivot sheet. For other pivot it is not a problem. I already pasted my sample code in my first post. I am calculating formula inside workbook already.

Thanks

Getting below error while doing pivotTable.calculateData();
java.lang.NullPointerException
at com.aspose.cells.b.a.zy.a(Unknown Source)
at com.aspose.cells.b.a.zy.d(Unknown Source)
at com.aspose.cells.PivotTable.a(Unknown Source)
at com.aspose.cells.PivotTable.a(Unknown Source)
at com.aspose.cells.PivotTable.calculateData(Unknown Source)

Attached zip file, please revert ASAP

Thanks

@kishore007008,

Please try our latest version/fix: Aspose.Cells for Java v20.8.1 (attached)
aspose-cells-20.8.1-java.zip (7.2 MB)

Which Aspose.Cells version you are using? I used the latest version/fix (Aspose.Cells for Java v20.8.1 (attached)) using the following simplest code with your provided file and it works fine:
e.g
Sample code:

com.aspose.cells.LoadOptions options = new com.aspose.cells.LoadOptions();
		options.setParsingFormulaOnOpen(true);
		Workbook workbook = new Workbook("f:\\files\\RefreshPivot.xlsx",options);

			workbook.calculateFormula(true);
			
			Worksheet worksheet = workbook.getWorksheets().get("IPB WF");
			PivotTable pivotTable = worksheet.getPivotTables().get(0);
			pivotTable.refreshData();
			pivotTable.calculateData();
			
			
			Worksheet worksheet1 = workbook.getWorksheets().get("$50K Validation");
			PivotTable pivotTable1 = worksheet1.getPivotTables().get(0);
			pivotTable1.refreshData();
			pivotTable1.calculateData();
			
			workbook.calculateFormula(true);
			
			workbook.save("f:\\files\\out1.xlsx");

Hi Amjad,

Thanks for your quick reply. It is not working with 20.8.1 version, which you attached.
I am getting below nullpinter exception.

Try refreshing pivot for “Sales” tab not for “$50K Validation”

Worksheet worksheet1 = workbook.getWorksheets().get(“Sales”);
PivotTable pivotTable1 = worksheet1.getPivotTables().get(0);
pivotTable1.refreshData();
pivotTable1.calculateData();

java.lang.NullPointerException
at com.aspose.cells.b.a.zy.a(Unknown Source)
at com.aspose.cells.b.a.zy.d(Unknown Source)
at com.aspose.cells.PivotTable.a(Unknown Source)
at com.aspose.cells.PivotTable.a(Unknown Source)
at com.aspose.cells.PivotTable.calculateData(Unknown Source)

@kishore007008,

Thanks for providing further details.

I found the issue as you mentioned. I got an exception “java.lang.NullPointerException” when calculating pivot table in the “Sales” sheet in the workbook. I have logged a ticket with an id “CELLSJAVA-43267” for your issue. We will look into it soon.

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

Any update on this? It is an important features which we need to complete. Please provide a solution ASAP.

Note: When I do a pivotTable.setRefreshDataOnOpeningFile(true); and open the excel I can see formula got calculated but it is without pivotTable.calculateData(). But I cannot read values from the cell.
And one more thing I noticed, if I close the excel manually, it will ask me to save and if I save that, and run again the program it used to work. It is a strange behavior.

Thanks

@kishore007008,

We have a good news for you. We have fixed your original issue regarding exception “java.lang.NullPointerException” and while preparing the fix we found an issue with rendering results for the pivot table (e.g in the output PDF, we found some data is not in order). Hopefully we will provide the final fix in the next few days (within 1-3 days).

When the pivot table is not refreshed/calculated via Aspose.Cells properly in code, you cannot get the cell’s value in code at runtime.

When you close the Excel file after manually re-saving it, MS Excel writes the calculated/cached value for cell(s) in the source xml file(s) of the workbook. That’s why you are getting the value after re-loading it via Aspose.Cells APIs.

Hi Amjad,

Thanks for your quick reply.
Yes we are waiting for complete fix ASAP.
Time being can you provide me the fix for NullPointerEception ?

Thanks

@kishore007008,

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

Your issue should be fixed in it.

Let us know your feedback.
aspose-cells-20.8.2-java.zip (7.2 MB)

Thanks Amjad, It is working as expected. Appreciate your quick resolution.

Thanks

@kishore007008,

You are welcome. Good to know that your issue is resolved by the new fix.

Hi Amjad,

I am facing one more issue with different pivot sheet.
I was able to refresh the “PIVOT2” sheet but I was not able to read any value(with or without formula) from that sheet. Attached excel and my code to read.
Can you have a look and revert ASAP.

It might be problem with pivotTable.setRefreshDataOnOpeningFile(true);
Without that I was not able to get refresh “PIVOT2” sheet.

My code:

public class Test {
public static void main(String[] args) {
String fileName = “RefreshAndReadPivot.xlsx”;
try {
refreshPivotTable(fileName, “PivotTable4”, “PIVOT1”);
refreshPivotTable(fileName, “PivotTable5”, “PIVOT2”);
readData(fileName, “PIVOT2”);RefreshAndReadPivot.zip (327.3 KB)

	} catch (Exception e) {
		e.printStackTrace();
	}
}

public static void refreshPivotTable(String filePath, String pivotTableName, String sheetName) throws Exception {

	com.aspose.cells.LoadOptions options = new com.aspose.cells.LoadOptions();
	options.setParsingFormulaOnOpen(true);
	Workbook workbook = new Workbook(filePath, options);
	Worksheet worksheet = workbook.getWorksheets().get(sheetName);
	workbook.calculateFormula(true);
	System.out.println("Refresh started..");
	
	PivotTable pivotTable = worksheet.getPivotTables().get(pivotTableName);
	if (pivotTable != null) {
		pivotTable.setRefreshDataFlag(true);
		pivotTable.refreshData();
		pivotTable.calculateData();

		for(int i=0; i<pivotTable.getPageFields().getCount(); i++) {
			PivotField pf = pivotTable.getPageFields().get(i);
			for(int j=0; j<pf.getPivotItems().getCount(); j++) {
				PivotItem pi = pf.getPivotItems().get(j);
				if(pi!=null) {
					pi.setPosition(0); 
				} else {
					System.out.println("PivotItem is null");
				}
			}
			pf.setShowAllItems(true);
			System.out.println("Field- "+pf.getName());
		}						
		pivotTable.setRefreshDataOnOpeningFile(true);
	}

	workbook.calculateFormula(true);
	workbook.save(filePath);
}




public static void readData(String file, String sheetName) throws Exception {

	Workbook workbook = new Workbook(file);
	Worksheet sheet = workbook.getWorksheets().get(sheetName);

	Cells cells = sheet.getCells();
	int maxrowcount = 6;
	int colcount = sheet.getCells().getMaxColumn();

	for (int rowIndex = 4; rowIndex <= maxrowcount; rowIndex++) {
		Row row = cells.getRow(rowIndex);
		for (int colIndex = 0; colIndex <= colcount; colIndex++) {
			Cell cell = cells.getCell(rowIndex, colIndex);
			if (cell.getName().contains("A") || cell.getName().contains("B"))
				System.out.println(cell.getName() + " - " + cell.getDisplayStringValue());
		}
	}
}

}

Thanks
Kishore

@kishore007008,

We will check your issue and get back to you soon.

Any update? We have similar issue in other pivot sheet too.
Can you revert back soon.

@kishore007008,

We checked and found the issue as you mentioned. We were not able to to get value from pivot sheet as you described by your new code segment. I have logged a ticket with an id “CELLSJAVA-43269” for your issue. We will look into it soon.

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

@kishore007008,

Please try our latest version/fix: Aspose.Cells for Java v20.8.3 (attached)
aspose-cells-20.8.3-java.zip (7.2 MB)
Your issue (logged as “CELLSJAVA-43269”) should be resolved in it.

Please note:

  1. From your code, we can know that you want refreshing and calculating the PivotTable named “PivotTable4” in the worksheet named “PIVOT1” firstly. And, then refreshing and calculating the PivotTable named “PivotTable5” in the worksheet named “PIVOT2”.

  2. After refreshing and calculating the two PivotTables, reading data from the worksheet named “PIVOT2”.

  3. You can try/use the following code to get correct results:

    static void JAVA43267() throws Exception
        {
            String filePath = "D:\\CellsJavaTickets\\JAVA43267\\";
    
            String fileName = "RefreshAndReadPivot.xlsx";
            try
            {
                    LoadOptions options = new LoadOptions();
                    options.setParsingFormulaOnOpen(true);
                    Workbook workbook = new Workbook(filePath + fileName, options);          
                    workbook.calculateFormula(true);
    
                refreshPivotTable(workbook, "PivotTable4", "PIVOT1");
                refreshPivotTable(workbook, "PivotTable5", "PIVOT2");
    
                workbook.save(filePath + "out_java.xlsx");
    
                readData(filePath + "out_java.xlsx", "PIVOT2");
                
            }catch (Exception e) {
                                e.printStackTrace();
                      }
            
        }
             
        
        public static void refreshPivotTable(Workbook workbook, String pivotTableName, String sheetName) throws Exception
        {           
                 Worksheet worksheet = workbook.getWorksheets().get(sheetName);
                 System.out.println("Refresh started..");
                 
                 PivotTable pivotTable = worksheet.getPivotTables().get(pivotTableName);
                 if (pivotTable != null) 
                 {
                          for(int i=0; i<pivotTable.getPageFields().getCount(); i++)
                          {
                                   PivotField pf = pivotTable.getPageFields().get(i);
                                   for(int j=0; j<pf.getPivotItems().getCount(); j++) 
                                   {
                                             PivotItem pi = pf.getPivotItems().get(j);
                                             if(pi !=null) 
                                             {
                                                      pi.setPosition(0);
                                             } else 
                                             {
                                                      System.out.println("PivotItem is null");
                                             }
                                   }
                                   pf.setShowAllItems(true);
                                   System.out.println("Field- "+pf.getName());
                          }                 
                          
                          pivotTable.setRefreshDataFlag(true);
                          pivotTable.refreshData();
                          pivotTable.calculateData();
    
                                                               
                          pivotTable.setRefreshDataOnOpeningFile(true);
                 }
    
                 workbook.calculateFormula(true);
        }
    
    
    
    
        public static void readData(String file, String sheetName) throws Exception 
        {
    
                 Workbook workbook = new Workbook(file);
                 Worksheet sheet = workbook.getWorksheets().get(sheetName);
    
                 Cells cells = sheet.getCells();
                 int maxrowcount = 6;
                 int colcount = sheet.getCells().getMaxColumn();
    
                 for (int rowIndex = 4; rowIndex <= maxrowcount; rowIndex++)
                 {
                          Row row = cells.getRow(rowIndex);
                          for (int colIndex = 0; colIndex <= colcount; colIndex++)
                          {
                                   Cell cell = cells.getCell(rowIndex, colIndex);
                                   if (cell.getName().contains("A") || cell.getName().contains("B"))
                                   {
                                             System.out.println(cell.getName() + " - " + cell.getDisplayStringValue());
                                   }
                          }
                 }
        }
    

Let us know your feedback.

Thank you Amjad, This is working as expected.
Once again appreciate your quick support and resolution.

Thanks
Kishore