How to Unlock Cells and Protect Worksheet in WinForms XlsIO?
Syncfusion® Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. It also converts Excel documents to PDF files. Using this library, you can unlock a range of cells in a worksheet and then protect it for formatting cells.
Locking and unlocking cells will be effective only when the worksheet is protected. To unlock the cells with protection for formatting columns, the range of cells must first be unlocked, and then the sheet must be protected with the "Select locked cells," "Select unlocked cells" options along with the "Format columns" option in the WinForms DocIo feature tour page.
Steps to unlock a range of cells in which the sheet is protected for formatting columns, programmatically:
Step 1: Create a new C# console application project.
Create a new C# console application project
Step 2: Install the Syncfusion.XlsIO.WinForms NuGet package as reference to your .NET Framework application from NuGet.org.
Install NuGet package to the project
Step 3: You can unlock the cells in a worksheet by disabling the Locked property available under CellStyle.
C#
// Unlock a range of cells in a worksheet worksheet.Range["A1:A10"].CellStyle.Locked = false;
VB.NET
'Unlock a range of cells in a worksheet worksheet.Range("A1:A10").CellStyle.Locked = False
Step 4: You can protect a worksheet using the Protect method, with different sheet protection options available under ExcelSheetProtection enum.
C#
// Protect the worksheet worksheet.Protect("syncfusion", ExcelSheetProtection.LockedCells | ExcelSheetProtection.UnLockedCells | ExcelSheetProtection.FormattingColumns);
VB.NET
'Protect the worksheet worksheet.Protect("syncfusion", (ExcelSheetProtection.LockedCells Or (ExcelSheetProtection.UnLockedCells Or ExcelSheetProtection.FormattingColumns)))
Step 5: Include the following namespaces in the Program.cs file.
C#
using Syncfusion.XlsIO;
VB.NET
Imports Syncfusion.XlsIO
Step 6: Include the following code snippet in main method of Program.cs file to unlock cells in a worksheet and then protect it for formatting columns.
C#
using (ExcelEngine excelEngine = new ExcelEngine()) { // Instantiate Excel application object IApplication application = excelEngine.Excel; // Create a new Excel workbook IWorkbook workbook = application.Workbooks.Create(1); // Get the first worksheet in the workbook into IWorksheet IWorksheet worksheet = workbook.Worksheets[0]; // Unlock a range of cells in the worksheet worksheet.Range["A1:A10"].CellStyle.Locked = false; // Protect the worksheet for formatting columns worksheet.Protect("syncfusion", ExcelSheetProtection.LockedCells | ExcelSheetProtection.UnLockedCells | ExcelSheetProtection.FormattingColumns); // Save the Excel workbook workbook.SaveAs("Output.xlsx"); }
VB.NET
Using excelEngine As ExcelEngine = New ExcelEngine() 'Instantiate Excel application object Dim application As IApplication = excelEngine.Excel 'Create a new Excel workbook Dim workbook As IWorkbook = application.Workbooks.Create(1) 'Get the first worksheet in the workbook into IWorksheet Dim worksheet As IWorksheet = workbook.Worksheets(0) 'Unlock a range of cells in the worksheet worksheet.Range("A1:A10").CellStyle.Locked = False 'Protect the worksheet for formatting columns worksheet.Protect("syncfusion", (ExcelSheetProtection.LockedCells Or (ExcelSheetProtection.UnLockedCells Or ExcelSheetProtection.FormattingColumns))) 'Save the Excel workbook workbook.SaveAs("Output.xlsx") End Using
A complete working sample to unlock cells in a worksheet and then protect it for formatting columns can be downloaded from Unlock-Cells.zip.
By executing the program, you will get the output Excel file with an editable range from A1 to A10 and the formatting option available for any column in the worksheet.
Output Excel document
Take a moment to peruse the documentation where you can find basic Excel document processing options along with the features like import and export data, chart, formulas, conditional formatting, data validation, tables, pivot tables and protect the Excel documents, and most importantly, the PDF, CSV and Image conversions with code examples.
Click here to explore the rich set of Syncfusion Excel (XlsIO) library features.
Starting with v16.2.0.x, if you reference Syncfusion® assemblies from a trial setup or from the NuGet feed, include a license key in your projects. Refer to the link to learn about generating and registering the Syncfusion® license key in your application to use the components without a trial message.
Conclusion
I hope you enjoyed learning about how to unlock cells and protect worksheet in WinForms XlsIO.
You can refer to our XIsIO’s feature tour page to learn about its other groundbreaking features. Explore our UG documentation and online demos to understand how to manipulate data in Excel documents.
If you are an existing user, you can access our latest components from the License and Downloads page. For new users, you can try our 30-day free trial to check out XlsIO and other Syncfusion components.
If you have any queries or require clarification, please let us know in the comments below or contact us through our support forums, Support Tickets, or feedback portal. We are always happy to assist you!