Editing Multiple Cells of Spreadsheet which are protected.

[Hayoung Kim]

Hi,

I am developing a spreadsheet application. In the application, spreadsheet control loads Excel file using the following lines of code in the loading event.

     this.spreadsheet1.Open(stream);

Loading event works fine.

Also the application doesn't allow a user to edit any cells. So I added a code like this (I had to loop through worksheets since the workbook has many worksheets):

       private void Spreadsheet1_WorkbookLoaded(object sender, WorkbookLoadedEventArgs args)

        {

            string keyName = string.Empty;

            for (int i = 0; i < spreadsheet1.Workbook.Worksheets.Count; i++)

            {

                spreadsheet1.ProtectSheet(spreadsheet1.Workbook.Worksheets[i], "", ExcelSheetProtection.All);

                keyName = spreadsheet1.Workbook.Worksheets[i].Name;

                spreadsheet1.GridCollection[keyName].AllowEditing = false;

            }

        }

But when I edit the cell values in the run-time, sometimes exception occurs saying object reference not set.

The code for editing is as following:

         

          spreadsheet1.ActiveGrid.CurrentCell.BeginEdit(true);

          for (int i = 0; i < 15; i++)

            {

                this.spreadsheet1.Workbook.Worksheets["Sheet3"].Range[i + 1, 1].Value2 = i;

            }

            spreadsheet1.ActiveGrid.CurrentCell.EndEdit(true);

I appreciate any opinion or suggestion.

[Kanimozhi Bharathi]

Hi Hayoung Kim,   

   

If you protect the sheet in Spreadsheet, then by default the users will not be able to edit the cells like MS Excel behavior. So please find the below code example for your reference,   

   

for (int i = 0; i < spreadsheet.Workbook.Worksheets.Count; i++)   

{   

   spreadsheet.ProtectSheet(spreadsheet.Workbook.Worksheets[i], "", ExcelSheetProtection.All);   

}   

  

Please find the UG link for your reference,   

   

UG: https://help.syncfusion.com/wpf/sfspreadsheet/worksheet-management#protection   

   

If the mentioned “Sheet3” is not present in the workbook, then the exception “object reference not set” will occur. Also, if you want to set the cell value at runtime in spreadsheet, use SetCellValue method like below code example   

   

for (int i = 0; i < 15; i++)   

{   

  var range = spreadsheet.Workbook.Worksheets["Sheet3"].Range[i + 1, 1];   

   

  spreadsheet.ActiveGrid.SetCellValue(range, i.ToString());   

   

  spreadsheet.ActiveGrid.InvalidateCell(i+1, 1);   

}   

  

   

Please find the UG link for your reference,   

   

UG: https://help.syncfusion.com/wpf/sfspreadsheet/working-with-sfspreadsheet#setting-the-value-or-formula-to-a-cell   

   

Please find the sample link for your reference,   

   

Sample: http://www.syncfusion.com/downloads/support/directtrac/general/ze/Windows-362165586   

   

Regards   

Kanimozhi B