Data binding using SpreadSheet MVVM

[Psyduck]

Hello.

 

I saw the post in 2013. At that time, it was impossible to implement.

Is it possible now? Putting something like "ItemsSource={bindg WorkBook}" in telerik:RadSpreadsheet and binding it with MVVM?

Even when called like this, a workbook and a worksheet are created and bound in the viewmodel, and modifications should be possible afterwards.

About 8years have passed. But there are not many posts.

 

If possible, ask for examples. Thanks.

 

<Like this>

View(*.xaml)
<telerik:RadSpreadsheet ItemsSource={binding WorkBookItem} />
...
...
...

ViewModel(*.cs)
public WorkBook WorkBookItem {get;set;}~
....
var workbook = new Workbook();
Worksheet workSheet;
workSheet = workbook .Worksheets.Add();
workSheet.Name = "Name";

WorkBookItem= workbook ;

 

[Dimitar]

Hi Kim,

I have attached a small example that shows a similar example. 

Let me know how this works for your case.

Regards,
Dimitar
Progress Telerik

[Psyduck]

Hello.

Thanks for the quick reply. So I tested it.

Data binding and values came in perfectly.

However, after entering the cell, an error occurs. And when I add a sheet, an error occurs.

 

private void Workbook_WorkbookContentChanged(object sender, EventArgs e)
        {
            var worksheet = _workBookMain.Worksheets[0];

            for (int col = 0; col < worksheet.UsedCellRange.ColumnCount; col++)
            {
                string propName = worksheet.Cells[0, col].GetValue().Value.RawValue;

                for (int row = 1; row < worksheet.UsedCellRange.RowCount; row++)
                {
                    var value = worksheet.Cells[row, col].GetValue().Value;
                    var emp = this.employees[row - 1]; ★ 1

                    if (value is NumberCellValue)
                    {
                        var nuberValue = value as NumberCellValue;
                        typeof(Employee).GetProperty(propName).SetValue(emp, nuberValue.Value);
                    }
                    else
                    {
                        typeof(Employee).GetProperty(propName).SetValue(emp, value.RawValue); ★ 2
                    }
                }
            }
            this.PrintData();
        }

 

★ 1  : When I type, an error appears.  ( Error - System.ArgumentOutOfRangeException )

★ 2 : An error appears when adding a sheet.  (Error - System.ArgumentException )

An error appears, input and sheet creation are performed, and when an event occurs once more, it ends.

 

Q1. Do I have to use the event handler when entering values in the sheet?

When I used the behind code, I didn't use the change handler when entering values in the sheet. Is the case different?

Q2. When there are 2 sheets, the sheet movement button does not work. Are there separate events?

 

Thanks.

 

[Dimitar]

Hello Kim,

You can check if you are on the proper worksheet and check if the row index is correct. Here is an example for this (in your real application you may handle this differently, show a message, add a new row, or make the cells read-only): 

private void Workbook_WorkbookContentChanged(object sender, EventArgs e)
{
    var worksheet = this.workbook.Worksheets[0];
    if (worksheet != this.workbook.ActiveWorksheet)
    {
        return;
    }

    for (int col = 0; col < worksheet.UsedCellRange.ColumnCount; col++)
    {
        string propName = worksheet.Cells[0, col].GetValue().Value.RawValue;

        for (int row = 1; row < worksheet.UsedCellRange.RowCount; row++)
        {
            if (row >= employees.Count)
            {
                return;
            }
            var value = worksheet.Cells[row, col].GetValue().Value;
            var emp = this.employees[row - 1];

            if (value is NumberCellValue)
            {
                var nuberValue = value as NumberCellValue;
                typeof(Employee).GetProperty(propName).SetValue(emp, nuberValue.Value);
            }
            else
            {
                typeof(Employee).GetProperty(propName).SetValue(emp, value.RawValue);
            }
        }
    }
    this.PrintData();
}

The example indeed has limited functionality and there are cases that should be handled in a real application. Please note that this example is not meant to be a complete application. It can be used as a starting point for binding the spreadsheet with the model. 

Let me know if I can assist you further.

Regards,
Dimitar
Progress Telerik