XLS file reports spurious circularity error- XLSX does not

Aspose.Cells Product Family
1

In the attached ZIP file there is an XLS and an XLSX file. The XLS file is produced by Apose.Cells 5.1.3.0 though it has been opened by Excel 2003 and then saved again. The XLSX file was produced by saving this file as XLSX from Excel 2003.

When the XLS is opened, Excel 2003 complains that cell (Tables)!E1701 has a circular reference. While it has an INDEX expression that COULD be circular, it is not circular, and there is an IF statement to prevent the circularity. In fact, you can see a correct numeric value in that cell. If I just resave and reopen the file, I get the error again. However, if I select E1701 and press F2 followed by Enter, then resave and reopen, the warning moves to a new cell. This has been done to the cells with the orange background.

If I save the entire file as XLSX and open it, there are no complaints. Also, if we generate an XLSX directly from Aspose.Cells, there is no complaint, it opens fine, with no circular errors. If I open the XLSX and then resave it as an XLS, the new XLS opens fine.

I am convinced, on the basis of these experiments, that the formulas are all correct and that for some reason Aspose's XLS triggers this false detection in Excel. Can this be fixed?

Thanks,
--Howard

2

Hi Howard,

Thanks a lot for the detailed information. We are working on the issue. We will update you about the findings and the solution as soon as possible.

Thanks,

3

Hi Howard,

We are working on the issue mentioned by you. Please forward us the code in a sample application through which you have generated this file. This will help us to find out the root cause of the issue.

Thanks,

4

Salman, I wish I had a simple sample application for you but I don't. Our large application creates those formulas, stores them in cells, then saves the XLS file. While there's a lot of complexity in our system, the use of .Cells is fairly straightforward in this case. How can I help you more?

--Howard

5

Hi Howard,

Please check why the formula of the Cell “E1701” is

=E573*E721+IF(ROUND(F1714-4*B595,0)<1,0,E584*INDEX(B1701:Q1701,,ROUND(F1714-4*B595,0)))

Please change your formula. It should not be the issue of Aspose.Cells. We think there are different calculating engines in between MS Excel 2007 to MS Excel 2003.

Thanks,

6

Salman, our formulas are fine as they work in XLSX files in Excel 2003. Also, if you F2/Enter on the formulas then resave, those formulas load correctly and the error moves to the next formula. In addition, I can load the XLSX into Excel 2003 and save it as an XLS and that XLS loads and operates correctly. I've attached the resulting XLS and you can easily repeat that experiment with the XLSX I provided in the original post.

--Howard

7

Hi,

Please try the attached version, let us know if it fixes your issue now.

Thank you.

8

Amjad, our testing shows this problem is fixed. Thank you very much!

--Howard