XLSB formula returns different value than XLSX formula

Aspose.Cells Product Family
1

Aspose Team,


The following formula is returning a different value in XSLB vs. XLSX:

= IF(ISBLANK(‘Sheet1’!A1, “”, ‘Sheet1’!A1)

In XLSX, when cell Sheet1!A1 is blank, it returns blank (as expected). However when pulled from an XLSB, it’s returning 0 (zero).

I have attached a program that recreates the issue. It generates a sheet that references blank cells in another sheet, re-loads the workbook from disk, then pulls data from the sheet as a DataTable and outputs the XML to the file system. In the XML you can clearly see the difference between XLSB andXLSX.

FYI - I’m using v5.3.0.1, which was a build generated as a result of this bug: Opening XLSB with multiple auto-filters throws null reference exception. I haven’t tried your latest v5.3.2 build because there’s been no indication that this fix has been rolled into the main build yet.

Could you take a look at this and advise?

Thanks,
Jim

2

Hi Jim,

I have tested your project with latest version v5.3.2.2 and found the issue as my initial test. I did see the difference in the generated xlsx.xml and xlsb.xml file as you have
mentioned. The xlsx file has blank data whereas the xlsb has “0” values. We need to investigate your issue further whether it is an issue or something else. I have logged an issue with an id: CELLSNET-26308. We will figure it out soon.

Thank you.

3

Hi Amjad,


I’ve done a little more checking, and have figured out that if you call Workbook.CalculateFormula() immediately after loading the workbook, this issue goes away.

However when you do not call CalculateFormula(), XSLB returns 0, and XLSX returns empty. This also appears to be true for the simple formula of “= A1 + B1”.

Run my program once as is, check the files, then re-run the program after adding .CalculateFormula() to the mix and you’ll see what I’m talking about.

Based on this, I think I can move forward by simply adding the .CalculateFormula() method call after I load XLSB workbooks.

Hope this helps!

Thx,
Jim


4

Hi,

Thanks for looking this issue further and providing us your feedback. It will be helpful for us to sort out the problem.

I have logged this information now.

5

Hi,

I think you are right. I tested it. If we add a line i.e. wb.CalculateFormula(),
then both xlsx.xml and xlsb.xml files are created fine. Both files are identical.


e.g

public static void WriteHiddenSheetData(string fileType, Workbook wb)

{

wb.CalculateFormula();

Cells cells = wb.Worksheets[“Hidden Sheet”].Cells;

DataTable tbl = cells.ExportDataTable(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1);

tbl.TableName = fileType;

tbl.WriteXml(@“e:\test2” + fileType + “.xml”);

}


I think you may add this line to your code at the moment and we will continue to look into your issue.
Once we have any update, we will let you know.

Thank you.

6

Hi,

We have fixed your issue now.

Kindly try Aspose.Cells for .NET v5.3.2.4.

If you still find the issue, let us know.

Thanks,

7

Hi Amjad,


Looks like this has done the trick.

Thx!
Jim
8

The issues you have found earlier (filed as 26308) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.