Not Able to Read cells and gettign null values though there are values in the Excel

Aspose.Cells Product Family
1

Version :17.8
While reading the Excel document though there is evaluated data its reading as null. The Excel is getting generated in the app, for few customers it works fine for few customers and not working for other customers though they have data.
We tried lot of things to find but it’s still not working.
is there a way to debug this to find what formula is incorrect. We are using the below functions to read the data.
Double annualPremiumValue = excelReader.getDoubleByCustomName(annualPremium);
LOGGER.info(“Reading cell ‘{}’ from sheet ‘{}’”, annualPremium);
Object rawValue = excelReader.getValueByCustomName(annualPremium);
LOGGER.info(“Raw value: {}”, rawValue);
LOGGER.info(“Parsed double value: {}”, annualPremiumValue);

2

@s3005301

It appears you are experiencing issues with reading values from an Excel document using Aspose.Cells, where some cells return null despite containing data. This inconsistency may arise from various factors, such as differences in the Excel files or the evaluation of formulas.

To troubleshoot this issue, consider the following steps:

  1. Check for Formula Errors: Use the GetFormula method to retrieve the formula from the cell. This can help identify any issues with the formula itself.

    string formula = excelReader.GetFormulaByCustomName(annualPremium);
LOGGER.info("Formula in cell '{}': {}", annualPremium, formula);

  2. Evaluate Formulas: Ensure that the formulas are evaluated correctly. You can use the CalculateFormula method to force the evaluation of formulas in the workbook.

    workbook.CalculateFormula();

  3. Inspect Cell Formats: The format of the cells can affect how values are read. Verify that the formats of the cells in question are set correctly.

  4. Log Detailed Information: Continue logging detailed information about the cells, including their addresses, types, and any potential errors.

  5. Test with Different Files: If possible, compare the problematic files with those that work correctly to identify any discrepancies.

If these steps do not resolve the issue, consider reaching out to Aspose support with specific details about the problematic files and the formulas used.

3

@s3005301,

Please try using the Workbook.calculateFormula() method from the Aspose.Cells for Java API in your code before retrieving values from the cells. If the issue persists, please zip your template Excel file and share it along with your sample (runnable) code using the Aspose.Cells for Java API. We will check your issue soon.

4

i used this function and still no good results. is there any way we can connect on this and discuss. Instead of this long conversations.

5

Please let me know how can i share this Excl here?

6

@s3005301,

Our forum support is efficient and serves as a good platform for obtaining technical assistance. We encourage you to use it to discuss your concerns, along with relevant samples. Please compress the Excel file into a zipped archive and attach it here. Additionally, provide detailed information and sample code regarding the specific cells where you were unable to retrieve calculated values. Once we receive this information and resource files, we will promptly evaluate your issue assist you soon.

7

Thanks,. Here is atttachemnet and under that “Rate Screen Output” sheet the I47,I49,I51 not able to read the values.
Sample.zip (5.2 MB).

Code snippet:
Double annualPremiumValue = excelReader.getDoubleByCustomName(annualPremium);
LOGGER.info(“Reading cell ‘{}’ from sheet ‘{}’”, annualPremium);
Object rawValue = excelReader.getValueByCustomName(annualPremium);
LOGGER.info(“Raw value: {}”, rawValue);
LOGGER.info(“Parsed double value: {}”, annualPremiumValue);

8

@s3005301
Please call workbook.calculateFormula() to calculate formulas:

     Workbook workbook = new Workbook(dir + "sample.xlsx");
     workbook.calculateFormula();
    LOGGER.info(workbook.getWorksheets().get("Rate Screen Output").getCells().get("I47").getDoubleValue());
9

@s3005301,

It seems you are using an older version of Aspose.Cells for Java API as you mentioned in your first post:

We are sorry but we cannot evaluate your issue using the older versions. Neither we can include fixes to the older APIs. All the fixes and enhancements are based latest APIs set only. It might be an issue or limitation with the older version for formula calculations.Over the years we have enhanced Aspose.Cells formula calculation engine and other modules. So, we recommend you to kindly upgrade to and try latest version/fix: Aspose.Cells v25.9. We tested using your template Excel with Aspose.Cells v25.9 using the following sample code and it works fine and as expected:
e.g.,
Sample code:

Workbook workbook = new Workbook("d:\\files\\sample.xlsx");
workbook.calculateFormula();
System.out.println(workbook.getWorksheets().get("Rate Screen Output").getCells().get("I47").getDoubleValue());
System.out.println(workbook.getWorksheets().get("Rate Screen Output").getCells().get("I49").getDoubleValue());
System.out.println(workbook.getWorksheets().get("Rate Screen Output").getCells().get("I51").getDoubleValue());

console output:

87541.56
0.4
882.63

Let us know if we can be of any further help.

10

But will old version license works for new vesrion? if i upgrade .

11

@s3005301,

No, you need to upgrade your subscription and obtain a new license to use it with the latest versions of the Aspose.Cells for Java API.

12

is there anyway i can test locally if it works or not with upgrade?

13

@s3005301,

Sure, you may request a 30 days temporary license to test the APIs for your needs. Please refer to how to get a temporary license? for more information.

14

Thanks, can you please help me at least to find what is causing the problem. Like any tracers i can enable to see the issue.

15

@s3005301,

It appears that some advanced MS Excel formulas or functions are being utilized in the expressions for those cells in the worksheet, which may not be supported with the older version you are using. Please note that newer versions support the calculations of numerous formulas, including advanced Excel functions. For your reference, you may review the document on supported functions by the Aspose.Cells formula calculation engine.