| public static GetFullAddress ( string worksheetName, string address ) : string | ||
| worksheetName | string | The name of the worksheet |
| address | string | The address |
| return | string | |
/// <summary>
/// Gets the formula of a named range relative to the specified <paramref name="relativeRow"/> and <paramref name="relativeColumn"/>.
/// </summary>
/// <param name="relativeRow">The row from which the named range is referenced.</param>
/// <param name="relativeColumn">The column from which the named range is referenced.</param>
/// <returns>The updated formula relative to the specified <paramref name="relativeRow"/> and <paramref name="relativeColumn"/>.</returns>
public IEnumerable <Token> GetRelativeNameFormula(int relativeRow, int relativeColumn)
{
var tokens = this.Workbook.FormulaParser.Lexer.Tokenize(this.NameFormula);
foreach (var token in tokens)
{
if (token.TokenType == TokenType.ExcelAddress)
{
var address = new ExcelAddress(token.Value);
// Do not update external references.
if (!string.IsNullOrEmpty(address?.Workbook))
{
continue;
}
int fromRow = address._fromRow;
int fromColumn = address._fromCol;
int toRow = address._toRow;
int toColumn = address._toCol;
if (!address._isFullColumn)
{
fromRow = this.GetRelativeLocation(address._fromRowFixed, address._fromRow, relativeRow, ExcelPackage.MaxRows);
toRow = this.GetRelativeLocation(address._toRowFixed, address._toRow, relativeRow, ExcelPackage.MaxRows);
}
if (!address._isFullRow)
{
fromColumn = this.GetRelativeLocation(address._fromColFixed, address._fromCol, relativeColumn, ExcelPackage.MaxColumns);
toColumn = this.GetRelativeLocation(address._toColFixed, address._toCol, relativeColumn, ExcelPackage.MaxColumns);
}
var updatedAddress = ExcelCellBase.GetAddress(fromRow, fromColumn, toRow, toColumn, address._fromRowFixed, address._fromColFixed, address._toRowFixed, address._toColFixed);
token.Value = ExcelCellBase.GetFullAddress(address.WorkSheet, updatedAddress);
}
}
return(tokens);
}
private static string BuildNewAddress(ExcelNamedRange namedRange, string newAddress)
{
if (namedRange.FullAddress.Contains("!"))
{
var worksheet = namedRange.FullAddress.Split('!')[0];
worksheet = worksheet.Trim('\'');
newAddress = ExcelCellBase.GetFullAddress(worksheet, newAddress);
}
return(newAddress);
}
