New to Telerik Document Processing? Download free 30-day trial

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); 
                } 
            } 
        } 
    } 
} 
There is an item logged in our backlog to provide an API to make this easier: SpreadProcessing: Add API to get the list of ranges to which a defined name refers. You can cast your vote for the implementation as well as subscribe to the task by clicking the Follow button to receive updates when its status changes.
In this article
Not finding the help you need? Improve this article