We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Editing Multiple Cells of Spreadsheet which are protected.

Thread ID:

Created:

Updated:

Platform:

Replies:

128560 Jan 29,2017 01:58 PM UTC Jan 30,2017 11:34 AM UTC Windows Forms 1
loading
Tags: Spreadsheet
Hayoung Kim
Asked On January 29, 2017 01:58 PM UTC

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 [Syncfusion]
Replied On January 30, 2017 11:34 AM UTC

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,   
   
   
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,   
   
   
Please find the sample link for your reference,   
   
   
Regards   
Kanimozhi B   


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;