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

Aspose.Cells Product Family
#1

Hi,

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?

Thanks.

#2

@casey.chen,

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

#3

@ahsaniqbalsidiqui,

I’m using this Range.Address Property https://apireference.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.

#4

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

#5

@casey.chen,

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

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

Hope, this helps.

#6

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);
MessageBox.Show(range.Address);

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"];            
MessageBox.Show(name.RefersTo);

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.

#7

@casey.chen,

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.