We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Getting a cell or range address with optional row and/or column absolute reference


Sometimes I need to get the address of a cell or range with row and/or column absolute reference. For example, $A1 or A$1 or $A$1. Right now the Cells.Range.Address property always return the address without absolute reference (e.g. A1). Is there a way around this?



Please share your sample file and code snippet with us for our testing. We will reproduce the problem and provide our feedback after analysis.


I’m using this Range.Address Property https://reference.aspose.com/net/cells/aspose.cells/range/properties/address and if you test it, it will always return the range’s address without any absolute sign (i.e. “$”), and there is no option for me to choose otherwise.

Whereas in Interop, there are other optional parameters as part of the Range.Address Property https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.range.address?view=excel-pia


You can work with Name.RefersTo property to get absolute addresses.

Workbook wb = new Workbook("Book.xlsx");            
Name name = wb.Worksheets.Names["MyRange1"];            

Hope, this helps.

Thanks @ahsaniqbalsidiqui
However, that only works if the name already exists. Here is my code snippet.

Workbook wb = new Workbook("Book.xlsx");
Worksheet ws = wb.Worksheets[0];
Range range = ws.Cells.CreateRange(
                firstRow: 1,
                firstColumn: 1,
                totalRows: 1,
                totalColumns: 1);

range.Address always returns A1 whereas I may want $A1 or A$1 or $A$1. If I name that range:

range.Name = "MyRange1";
Name name = wb.Worksheets.Names["MyRange1"];            

name.RefersTo does return =Sheet1!$A$1. But that means I need to name a range every time I want to get absolute address and clean up the = in front and also the extra sheet name.

Right now I’m manipulating the string myself:

string address = range.Address;
string column = new Regex(@"\$?[a-zA-Z]{1,3}").Match(address).Value;
address = address.Replace(column, $"${column}");
string row = new Regex(@"\$?[0-9]{1,7}").Match(address).Value;
address = address.Replace(row, $"${row}");
MessageBox.Show(address.Replace(range.Address, address));

to get $A$1.

I’m wondering if Aspose has something else innate so I don’t have to do the above string manipulations.


For your requirements, we think the easier solution with better performance should be that you build the address with method CellsHelper.ColumnIndexToName() or CellsHelper.CellIndexToName().

Example code:

            Range r = …;
            StringBuilder sb = new StringBuilder();
sb.Append('$').Append(CellsHelper.ColumnIndexToName(r.FirstColumn)).Append('$').Append(r.FirstRow + 1); //you can determine whether ‘$’ should exist for row and/or column here according to your requirements
            if (r.RowCount > 1 || r.ColumnCount > 1)
                sb.Append(":$").Append(CellsHelper.ColumnIndexToName(r.FirstColumn + r.ColumnCount - 1)).Append('$').Append(r.FirstRow + r.RowCount);
            string address = sb.ToString(); 

Hope, this helps.