Iterate through all selected cells?

[David]

I'm working on a method of identifying and storing the location of certain datum in a spreadsheet.  For example, when a spreadsheet is displayed, the user is prompted to 'Select all cells containing names." My hope is that the user will select the appropriate worksheet in the workbook, then using Ctrl+Click select each cell containing a name.  Once they've done this, I want to iterate through each of the selected cells of the entire workbook and capture the worksheet index, column and row index, as well as the value for each of those cells from code-behind.  Ideally, I'll use this data to create a database table with column names based upon the values of the selected cells. I'll also add rows to another table that store the location of each selected name, i.e., the cell containing the name 'Jane Doe' is in column 'M', row '11', on worksheet '6'.

All of the database stuff I can handle without trouble, but I cannot find an effective way to capture the cell locations or values in an iterative sort of way across the whole workbook from code-behind.

I'm sorry if this turns out to be a trivially simple problem.  I only started using Telerik a few days ago. I look forward to your response!

[Peter Milchev]

Hello David,

For convenience and better visibility from the community, I am sharing my reply from the duplicate support thread:

The RadSpreadsheet for ASP.NET AJAX is a wrapper of the Kendo UI Spreadsheet widget, so in most cases, the resources you find for Kendo UI Spreadsheet would be applicable to RadSpreadsheet also. This might help you in future researches. 

On the server-side, things are similar to the Spreadsheet for ASP.NET MVC, which is also a wrapper of the Kendo UI suite. Both Spreadsheet for ASP.NET AJAX and Spreadsheet for ASP.NET MVC share the same Telerik.Web.Spreadsheet.dll assembly, so the resources about the server-side API should be applicable to both suites.

Another important thing is that the Telerik.Web.Spreadsheet is using internally the DocumentProcessingLibrary and for more information about that you can check this article:

• https://www.telerik.com/support/kb/aspnet-ajax/details/understanding-telerik-ajax-and-document-processing-library-spreadsheet-workbook-and-worksheets 

On the question itself, you might find useful this article:

• https://docs.telerik.com/devtools/aspnet-ajax/knowledge-base/spreadsheet-load-via-json-and-save-with-postback   

Once you get the Telerik sheet, you can convert it to a Document Processing one and iterate all the selected cells with the code below:

• https://docs.telerik.com/devtools/document-processing/libraries/radspreadprocessing/working-with-cells/get-set-clear-properties
• https://docs.telerik.com/devtools/document-processing/libraries/radspreadprocessing/working-with-cells/accessing-cells-of-worksheet 
• https://docs.telerik.com/devtools/document-processing/libraries/radspreadprocessing/working-with-cells/iterating-used-cells  
  

 

var dplWorkbook = workbook.ToDocument();
foreach (var sheet in dplWorkbook.Worksheets)
{
    var ranges = sheet.ViewState.SelectionState.SelectedRanges;
    foreach (var range in ranges)
    {
        for (int row = range.FromIndex.RowIndex; row <= range.ToIndex.RowIndex; row++)
        {
            for (int column = range.FromIndex.ColumnIndex; column <= range.ToIndex.ColumnIndex; column++)
            {
                ICellValue cellvalue = sheet.Cells[row, column].GetValue().Value;
                var rawvalue = cellvalue.RawValue;
            }
        }
    }
}

Regards,
Peter Milchev
Progress Telerik