Weird Behavior with empty cells

Aspose.Cells Product Family
1

Hello,

I have some difficulties to understand the way Aspose (for java) handles empty cells. Sometimes, when calculating formulas, it seems to replace empty values with 0.

Lets take the following example:

  • A1 contains 1
  • A2 is empty
  • A3 contains 2
  • B1 has the following formula "=MATCH(0,A1:A3,0)" (exact match of value 0 between A1, A2 and A3).

Test 0 with Excel:

If I am using this in Excel, B1 will display "#N/A" (as zero is not found).

Test 1 with Aspose:

Workbook wb = new Workbook();
Worksheet sheet=wb.getSheet(0);
Cell a1 =sheet.getCell("A1");
a1.setValue(1);
Cell a3 =sheet.getCell("A3");
a3.setValue(3);

Cell b1=sheet.getCell("B1");
b1.setFormula("=MATCH(0,A1:A3,0)");
wb.calculateFormula();
System.out.println("B1="+b1.getValue());

This code will produce: B1=#N/A!

Test 2 with Aspose: The only difference is the line in bold: I am getting cell A2, but do nothing with it.

Workbook wb = new Workbook();
Worksheet sheet=wb.getSheet(0);
Cell a1 =sheet.getCell("A1");
a1.setValue(1);
Cell a2 =sheet.getCell("A2");
 Cell a3 =sheet.getCell("A3");
a3.setValue(3);

Cell b1=sheet.getCell("B1");
b1.setFormula("=MATCH(0,A1:A3,0)");
wb.calculateFormula();
System.out.println("B1="+b1.getValue());

This code will produce: B1=2

It seems that Aspose replaced A2 content with 0 only because I tried to get the cell object

Test 3 with Aspose and a template spreadsheet: I put the data and formula in the spreadsheet and simply try to get the final result:

Workbook wb = new Workbook();
InputStream in=new FileInputStream("testMATCH.xls");
wb.open(in);
Worksheet sheet=wb.getSheet(0);
Cell b1=sheet.getCell("B1");
wb.calculateFormula();
System.out.println("B1="+b1.getValue());
in.close();

This code will produce: B1=#N/A!


Test 4 with Aspose and a template spreadsheet: This time, instead of letting A2 empty in the template spreadsheet, I will put a formula that always return an empty string: =IF(TRUE,"","")

I am running the same code as above, it will produce: B1=2

Any idea ?

2

Hi,

Thanks for considering Aspose.

Well, Our developers are on their leave for Chinese National Day, So this issue will be sort out at the start of next week.

Thanks for your patience.

Regards

Amjad Sahi

Aspose Nanjing Team

3

Hello,

Thanks for this fix. It works on the specified test case, but I still have issues with other Excel functions.

For example, please consider the following example:

Workbook wb = new Workbook();
Worksheet sheet=wb.getSheet(0);
Cell a1 =sheet.getCell("A1");
a1.setValue(5);
Cell a2 =sheet.getCell("A2");
a2.setValue("");
Cell a3 =sheet.getCell("A3");
a3.setValue(20);
Cell b1 =sheet.getCell("B1");
b1.setValue(1);
Cell b2 =sheet.getCell("B2");
b2.setValue(2);
Cell b3 =sheet.getCell("B3");
b3.setFormula("=VLOOKUP(A3,A1:B2,2,1)");
wb.calculateFormula();
System.out.println("B3="+b3.getValue());

If you run this code, you will have the following output:

B3=2

However, if you do the same thing directly in Excel, you will have B3=1

This means that Aspose considers that the empty cell (A2) is the closest cell to 20, whereas Excel does ignore this empty cell.

Is it possible to fix this behavior also ?

Thanks in advance,

Yann.

4

Thanks for this fix. The test case is now working.

However I found another issue, which I described in post https://forum.aspose.com/t/105968

Regards,

Yann.