Convert Referred Area to a Range Address

Aspose.Cells Product Family
1

Hello,

The AbstractCalculationEngine Calculate method often provides ReferredArea objects when references are used in formulas. I’m wondering if there is any utility functions to convert a ReferredArea to a Range Address formula such as “Sheet1!A1:C5”.

Also, is there a way to tell if a ReferredArea is an entire row or entire column (e.g. Sheet1!C:C)? For an entire column it shows a very large row number and I can’t find what to compare that too to be sure it’s an entire column.

Thank You,
-Andy

2

I meant to provide my working example. This works for me, but I would prefer to use something from Aspose if it exists or at least to not hardcode max row and max column.

    public static bool IsEntireRow(this ReferredArea referredArea)
    {
        return referredArea.StartColumn == 0 && referredArea.EndColumn == 16383;
    }

    public static bool IsEntireColumn(this ReferredArea referredArea)
    {
        return referredArea.StartRow == 0 && referredArea.EndRow == 1048575;
    }

    public static string GetAddress(this ReferredArea referredArea)
    {
        var topLeft = CellsHelper.CellIndexToName(referredArea.StartRow, referredArea.StartColumn);
        var bottomRight = CellsHelper.CellIndexToName(referredArea.EndRow, referredArea.EndColumn);
        var sheetName = $"'{referredArea.SheetName}'";

        if(topLeft == bottomRight)
            return $"{sheetName}!{topLeft}";

        if(referredArea.IsEntireColumn())
            return $"{sheetName}!{GetExcelColumnName(referredArea.StartColumn + 1)}:{GetExcelColumnName(referredArea.EndColumn + 1)}";

        if(referredArea.IsEntireRow())
            return $"{sheetName}!{referredArea.StartRow + 1}:{referredArea.EndRow + 1}";

        return $"{sheetName}!{topLeft}:{bottomRight}";
    }
3

@weissa
Your judgment logic is correct. You can continue to use it, and we also plan to add APIs to make it more convenient for users to use the product.

We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-55494

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

4

@John.He,

Thank you for the quick response. And feel free to use the helper functions above if that’s helpful. I have not tested it properly yet, it was a work in progress.

Thanks!
-Andy

5

@weissa
You are welcome. Thank you for your feedback. Once there are updates, we will notify you promptly.

6

@weissa
After further research, you can also use ReferredArea.ToString() method to achieve the goal. Of course, we will also add apis for determining whether it is a entire row and entire column

1 Like
7

@weissa,

We are pleased to inform you that your issue (logged earlier as “CELLSNET-55494”) has been resolved. We have added new properties: ReferredArea.IsEntireRow/IsEntireColumn to indicate whether the reference is entire row or column. The enhancements will be included in our upcoming release (Aspose.Cells v24.5) that we plan to release in the first half of May 2024. You will be notified when the next version is released.

8

@amjad.sahi,

Thank you. We look forward to transitioning to these methods in the next release.

-Andy

9

@weissa,

You are welcome. We will keep you posted with updates on the new release.

10

The issues you have found earlier (filed as CELLSNET-55494) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi