Junk value displayed in Calculated section in excel. Is this due to file format and its extension mismatch?

[Elakiyah]

Hi…
Weblogic Server in Solaris OS

When Save format is set to AUTO with file extension .xls, there is a mismatch between the file format and the excel extension.
So the paging sheet generated is empty. But the data displayed in UI which is retrieved from the paging sheet(Aspose object) is correct. How this is possible?

JBOSS Server in Linux OS
The same application when run in JBOSS server in Linux OS, both paging and UI has junk values wherever calculation takes place.
This issue occurs inconsistently and was resolved by changing the file format to EXCEL_97_TO_2003 (5) from AUTO (0)

Has the calculation been affected by the mismatch in file format and its extension? If So why the same issue has not occurred in Weblogic server in Solaris OS?

[shakeel.faiz]

@Elakiyah

You have done the right thing by specifying explicit format instead of relying on auto format.

For XLS format, you will use

SaveFormat.EXCEL_97_TO_2003

For XLSX format, you will use

SaveFormat.XLSX

And if you want to detect the file format on run time, you can use

FileFormatInfo finfo = FileFormatUtil.detectFileFormat()

And once, you detect the file format on runtime, you can load it like

LoadOptions ldOpts = new LoadOptions(finfo.getLoadFormat());

Workbook wb = new Workbook(fileName, ldOpts);

All this information should help you dealing with this issue properly.

[Elakiyah]

Hi Shakeel. Thanks for the info… The file format change was done and after that we are not facing the issue in JBOSS server in Linux OS. But we need clarification whether the file format mismatch has affected the calculated column in excel. if it has affected then why this issue has not occurred in weblogic in Solaris OS?. TIA

[shakeel.faiz]

@Elakiyah

Once, the Workbook object is created, then it has no relation with the Excel file, be it is XLS or XLSX. And when you calculate Workbook using Workbook.calculateFormula() method, it only calculates the formulas inside the Workbook object.

But when you save the Workbook object in XLS or XLSX format, then some limitations of XLS or XLSX occurs. For example, XLS format only supports up to 255 columns while XLSX supports many columns.

In short, Workbook.calculateFormula() method does not depend upon any format during calculation.