Formulas using intersected ranges don't work

Aspose.Cells Product Family
1

Hello,

I'm having trouble with some formulas that are valid in Excel, which don't seem to work in Aspose. I'm using Aspose.Cells for .NET (just got the latest version 7.0.1.0).

Some Excel formulas allow me to specify a range as the intersection of two ranges. For example, SUM(D:F 8:8) is equivalent to SUM(D8:F8). The first syntax is especially useful when dealing with named ranges; e.g. if Name1 = D:F, then I can use SUM(Name1 8:8).

Aspose does not seem to handle these formulas correctly. Please see the attached Intersections.xls, with formulas in columns B and G. IntersectionsTests.cs has code which shows that these formulas are not correct when the grid is manipulated, although formulas that do not use this syntax (B9 and G9) work correctly. Is it possible to make these formulas work correctly in Aspose?

Additionally, when I save the Intersections.xls as SpreadsheetML format (Intersections.xml) Aspose can't even open the file. This seems to be because the formulas are converted to R1C1 notation, and references to whole rows are written as R. For example, =SUM(C[-3]:C[-1] R). Aspose seems to be unable to process the R. Is there any way this could be made to work as well?

Mishelle

2

Hi,


Thanks for sharing the sample files with code.

After an initial test, I can find the issue as you have mentioned regarding calculating formulas having intersected ranges. I have logged a ticket for the issue with an id: CELLSNET-30843. We will figure it out soon.

Thank you.
3

Hi,

Please try the latest version: Aspose.Cells for .NET v7.0.1.3

4

This version seems to resolve the problem. Thanks!

5

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

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