This is a migrated thread and some comments may be shown as answers.

Iterate through all selected cells?

1 Answer 77 Views
Spreadsheet
This is a migrated thread and some comments may be shown as answers.
David
Top achievements
Rank 1
David asked on 27 Oct 2020, 07:18 PM

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!

1 Answer, 1 is accepted

Sort by
0
Peter Milchev
Telerik team
answered on 30 Oct 2020, 11:56 AM

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:

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

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:

 

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

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

Tags
Spreadsheet
Asked by
David
Top achievements
Rank 1
Answers by
Peter Milchev
Telerik team
Share this question
or