Get absolute address of range

Question

In old school Excel Interop, I can use the following code to generate an absolute address and use it inside a formula:

range.Formula = $"=sum({myRange.Address[false, true]})";

What is the EPPlus equivalent of this line, to get an absolute address (with an absolute row and/or column on demand)?

Answer 1

Well, there isn't a built in method, but you can do the following:

string GetAddress(ExcelRange rgn, bool absoluteRow, bool absoluteColumn,bool includeSheetName=false)
{
    string address = rgn.Address;
    if (absoluteColumn)
    {
        address = Regex.Replace(address, @"\b([A-Z])", @"$$$1");
    }
    if (absoluteRow)
    {
        address = Regex.Replace(address, @"([0-9]+)", @"$$$1");
    }
    if (includeSheetName)
    {
        address = $"'{rgn.Worksheet.Name}'!{address}";
    }
    return address;
}

Or as an extension method, so you can use like interop:

public static class EpplusExtensions
{
    public static string Address(this ExcelRange rgn, bool absoluteRow, bool absoluteColumn, bool includeSheetName=false)
    {
        string address = rgn.Address;
        if (absoluteColumn)
        {
            address = Regex.Replace(address, @"\b([A-Z])", @"$$$1");
        }
        if (absoluteRow)
        {
            address = Regex.Replace(address, @"([0-9]+)", @"$$$1");
        }
        if (includeSheetName)
        {
            address = $"'{rgn.Worksheet.Name}'!{address}";
        }
        return address;
    }
}

Usage:

using (var ep = new ExcelPackage(new FileInfo(file)))
{
    var sh = ep.Workbook.Worksheets.First();
    ExcelRange myRange = sh.Cells[1, 1, 26, 36];
    var absoluteColumn = myRange.Address(false, true);
    var absoluteRow = myRange.Address(true, false);
    var absolute = myRange.Address(true, true);
    var relative = myRange.Address(false, false);
    var withSheetName = myRange.Address(true, true, true);
}