Problem with excel recalculation (many #VALUE errors)

Aspose.Cells Product Family
1

Hello

I’m having a complex excel file which contains, among others, 63 cells with “#VALUE” error. After doing the recalculation, I end up with a file that contains 12355 “#VALUE” errors.

Here is the code I’m having (kotlin):

package com.layer

import com.aspose.cells.Workbook

fun main() {
    val workbook = Workbook("/tmp/bug.xlsx")
    workbook.calculateFormula()
    workbook.save("/tmp/bug-result.xlsx")
}

File: https://drive.google.com/file/d/1wsW0yhLDKmKi__kR9tdfku2OCNX2WErC/view?usp=sharing

If the “bug-result.xlsx” is opened in Microsoft excel it looks identical to “bug.xlsx” (probably microsoft is doing it’s own recalculation). Problematic cells can be spotted once the excel is unpacked:

unzip bug.xlsx -d bug
grep "#VALUE" -o bug/xl/worksheets/*.xml | wc -l

unzip bug-result.xlsx -d bug
grep "#VALUE" -o bug-result/xl/worksheets/*.xml | wc -l

First “grep” gives 63, second one 12355.

Seems like Aspose.Cells can’t recalculate broken file and is producing a lot more errors. Can I do the recalculation but not produce more errors? Am I missing something? It looks like a bug to me.

2

@rafalpiotrowskilayer,
There are a large number of sheets and it is a little difficult to compare Excel files updated by Aspose.Cells and MS Excel. Could you please identify some particular sheet which can be used for comparison? Also, make sure that you have used the latest version for testing at your end.

3

Thanks for the response. Unfortunately I can’t make the file simpler and can’t identify particular sheet. In that case I thing we can close it.

4

@rafalpiotrowskilayer,
We have observed the issue using the latest version and logged it in our database for further investigation. You will be notified here once any update is ready for sharing.

This issue is logged as:
CELLSJAVA-43399 - CalculateFormula() creates lot of error type values #VALUE

Following sample code was used to test the issue:

private static void Test() throws Exception
{
    Workbook workbook = new Workbook("bug.xlsx");
    System.out.println("Before calculate formula:" + TotalErrors(workbook));
    workbook.calculateFormula();
    System.out.println("After calculate formula:" + TotalErrors(workbook));
}
private static int TotalErrors(Workbook workbook)
{
    int count = 0;
    for(Object obj : workbook.getWorksheets())
    {
        Worksheet ws = (Worksheet)obj;
        for (Object item : ws.getCells())
        {
            Cell cell = (Cell)item;
            if (cell.getType() == CellValueType.IS_ERROR)
            {
                count++;
            }
        }
    }
    return count;
}

Following was the program output:

Before calculate formula:1428
After calculate formula:13710
5

@rafalpiotrowskilayer,
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.

6

Please try the latest fix v21.2.5:
aspose-cells-21.2.5-java.zip (7.2 MB)

7

The issues you have found earlier (filed as CELLSJAVA-43399) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi

8

Hi @ahsaniqbalsidiqui, @johnson.shi.
Thank you for fixing that. I’ve tested this and seems that everything is working fine.

9

@rafalpiotrowskilayer,

Good to know that your issue is resolved by the new version/fix. In the event of further queries or issue, feel free to write us back and we will be happy to assist you soon.