Set Value using Named Range
| Product Version | Product | Author |
|---|---|---|
| 2021.1.222 | SpreadProcessing | Martin Velikov |
Description
How to set the value to a CellSelection using its Named Range.
Solution
The solution would be to iterate the imported Workbook`s Names in order to find the desired one. Then to split its RefersTo property to use its elements to match the exact Worksheet and to get the name of the indexes describing the CellSelection. We are using the NameConverter.TryConvertCellNameToIndex() method to convert the already obtained cell name to an index.
Set Value using Named Range
string namedRange = "test";
string newValue = "edited";
Workbook workbook = ImportWorkbook();
foreach (var name in workbook.Names)
{
if (name.Name == namedRange)
{
string[] refersToElements = name.RefersTo.Split("!".ToCharArray());
foreach (Worksheet sheet in workbook.Sheets)
{
if (sheet.Name.ToUpper() == refersToElements[0].Replace("=", String.Empty).ToUpper())
{
string rangeName = refersToElements[1];
string fromIndexName = rangeName.Split(":".ToCharArray())[0];
string toIndexName = rangeName.Split(":".ToCharArray())[1];
bool nameRefersToIndexFrom = NameConverter.TryConvertCellNameToIndex(fromIndexName, out bool isRowFromAbsolute, out int fromRowIndex, out bool isColumnFromAbsolute, out int fromColumnIndex);
bool nameRefersToIndexTo = NameConverter.TryConvertCellNameToIndex(toIndexName, out bool isRowToAbsolute, out int toRowIndex, out bool isColumnToAbsolute, out int toColumnIndex);
if (nameRefersToIndexFrom && nameRefersToIndexTo)
{
CellRange cellRange = new CellRange(fromRowIndex, fromColumnIndex, toRowIndex, toColumnIndex);
sheet.Cells[cellRange].SetValue(newValue);
}
}
}
}
}