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.
@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.
@ahsaniqbalsidiqui,
I’m using this Range.Address Property Range.Address | Aspose.Cells for .NET API Reference 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.
@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.
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.
@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.