Problem when saving an Excel file in 2003 format with Aspose Cells 2.5.3.x versions

Aspose.Cells Product Family
1

Hello,

It seems that the version 2.5.3 has a problem when saving an Excel 2003 file.

I have a really simple Excel file (called “test-2003.xls”), which contains in the cell C2 the value “=B2” (i.e. the C2 cell contains the same thing as the cell B2).
In my code, I put a value in B2 cell, using the following code:

Workbook book2003 = new Workbook();
book2003.open(TestAspose.class.getResourceAsStream("/test-2003.xls"));
book2003.getWorksheets().getSheet(0).getCells().getCell(1, 1).setValue(“foo”);
book2003.save(“output.xls”);

Then, I open the “output.xls” file, and I see “foo” in B2, but I see “0” in C2, which means that the C2 cell has not been “refreshed” during the save process.

Some notes:

  • If I define the file format type (book2003.save(“output.xls”, FileFormatType.EXCEL97TO2003);), it does not work neither.
  • If I use the 2007 Excel format (.xlsx), the problem does not occur.
  • If I use Apose Cells 2.5.0.4, the Excel 2003 format is correctly handled.
What is the problem?


2

Hi,

Thanks for your interest in Aspose.Cells for Java.

Thanks for your code example, please also attach your source file test-2003.xls so that we could test this issue and report it if it is a bug.

3

Hello,

Thanks for your quick reply.
I’ve attached the Excel file, but you will see it is really simple.

I’ve done another test, by forcing the calculation of formula before saving the workbook:

Workbook book2003 = new Workbook();
book2003.open(TestAspose.class.getResourceAsStream("/test-2003.xls"));
book2003.getWorksheets().getSheet(0).getCells().getCell(1, 1).setValue(“foo”);
// Force the calculation of formula…
book2003.calculateFormula();
book2003.save(“output-1.xls”);

Doing things like that, the file is now correct. However, I don’t understand why I have to manually calculate the formula, which was not the case before (v. 2.5.0.4).

4

Hi,


Thank you for sharing your sample file.
I have tested your said issue with latest fix version of Aspose.Cells JAVA v2.5.3.8 [attached] and found no problem in calculating the formulae. Please download this version and test your scenario with latest JAR. Also, attached is my output file for your reference.

If you still face any difficulties, please do not hesitate to write back.
5

Unfortunately, it didn’t help.

I’ve attached the output file in this post (generated with 2.5.3.8).
Does the cell C2 evaluates to “foo” or “0” when you open it?

When I open your output-1.xls, I see “foo” in both cells, which is correct, but not with my generated files.

6

Hi,


Thanks for reporting.
I have verified with MS Excel 2003 that unless you click on C2 cell, it does not show “foo”. Where as in Excel 2010, there is no such issue. I will confirm this issue and then log it in our Bug Tracking System.
7

Hi,

We are using Excel 2007 SP2 here, maybe the issue is indeed not present with newer version of Ms Office…

Thanks.

8

Hi,


I have verified your said scenario again with latest fix version of Aspose.Cells JAVA 2.5.3.8 [attached]. The formula in output file is evaluating fine when opened in MS Excel 2003, 2007 and Excel 2010. Out.xls is also attached for your reference.
Please try this latest JAR with below source code and let us know of your feedback.

JAVA

Workbook book = new Workbook();
book.open(“C:\temp\test-2003-1.xls”);
book.getWorksheets().getSheet(0).getCells().getCell(1, 1).setValue(“foo”);
// Force the calculation of formula…
book.calculateFormula();
book.save(“c:\temp\out.xls”,FileFormatType.EXCEL97TO2003);
9

Hello,

With this code, there is no problem, as we force the calulation of formula (book.calculateFormula();).

My problem occurs when this line is not present, the formula is not updated automatically with recent versions of Aspose (2.5.2.x or 2.5.3.x), while it was the case with 2.5.0.x version.

10

Hi,


In recent versions, we have made a lot of enhancements to Formula Engine. So if your spreadsheet have formulae in it, you need to explicitly call Workbook.CalculateFormula() every time before saving the spreadsheet to disk.
Thank you for understanding.
11

Ok, so if I understand correctly, we should explicitely call the calculateFormula() before saving now?

If yes, there is still something that I don’t understand: why does my code (without the calculateFormula()) work perfectly when saving my file in XLSX format? I would expect the same behavior as for XLS format, not a different one…

12
Hi,

According to some other user's requirement, we made some enhancements for reading and re-saving template files. For old version, some properties of formula will be lost when reading the template file such as the flag that denotes whether re-calculate cell formula when open file in MS Excel. In you template file, the re-calcuate flag is false for cell C2. In old versions we always reset this property as true, so your case can work with those old versions. With newer versions, to get the result you desired, please add following code before save the workbook:
workbook.getWorkbookSettings().setCalculateOnOpen(true);
Thus the formula will be re-calcualted when you open the generated file in MS Excel.
Thank you.