ConvertA1FormulaToR1C1 references whole column error

Aspose.Cells Product Family
1

When a formula references a whole column, calling ConvertA1FormulaToR1C1 doesn’t convert the formula.

i have a workbook with the following formulas:
2020-01-10_07-34-41.png (3.7 KB)

xl shows the formulas in RC as:
2020-01-10_07-30-21.png (3.9 KB)

when i run the following code:

        var file = "wb423.xlsx";
        var workbook = new Workbook(file);

        for (int row = 1; row < 5; row++)
        {
            var cell = workbook.Worksheets[0].Cells[row, 1];
            Console.WriteLine(CellsHelper.ConvertA1FormulaToR1C1(cell.Formula, cell.Row, cell.Column));
        }

only the first 2 formulas get converted :frowning:

2020-01-10_07-27-23.png (3.8 KB)

the attached zip file contains my workbook and the c# project
wb423.zip (14.2 KB)

Ron.

2

@ron,
Thank you for your query.
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-47084 – Error when whole column is referred in ConvertA1FormulaToR1C1

3

@ron,
For your case, please use Cell.R1C1Formula instead to get the R1C1 formula for those cells.

Methods CellsHelper.ConvertA1FormulaToR1C1 and ConvertR1C1FormulaToA1 can only process some simple formulas. To support all complicated formulas, we need global models such as Workbook/Worksheet. So we may consider to make those two methods obsolete later and investigate whether we can provide similar APIs at the level of Workbook or Worksheet, or require users to set and get formula on the based cell directly.