Advanced Protection Settings For Excel Worksheet
In this tutorial, we will walk you through the steps to set advanced protection settings for an Excel spreadsheet using the Aspose.Cells library for .NET. Follow the instructions below to complete this task.
Step 1: Preparation
Make sure you have installed Aspose.Cells for .NET and created a C# project in your preferred integrated development environment (IDE).
Step 2: Set the document directory path
Declare a dataDir variable and initialize it with the path to your documents directory. For example :
string dataDir = "YOUR_DIRECTORY_OF_DOCUMENTS";
Be sure to replace "YOUR_DOCUMENTS_DIRECTORY" with the actual path to your directory.
Step 3: Create a file stream to open the Excel file
Create a FileStream object containing the Excel file to open:
FileStream fstream = new FileStream(dataDir + "book1.xls", FileMode.Open);
Make sure you have the Excel file book1.xls in your documents directory or specify the correct file name and location.
Step 4: Instantiate a Workbook object and open the Excel file
Use the Workbook class from Aspose.Cells to instantiate a Workbook object and open the specified Excel file via the file stream:
Workbook excel = new Workbook(fstream);
Step 5: Access the first worksheet
Navigate to the first worksheet of the Excel file:
Worksheet worksheet = excel.Worksheets[0];
Step 6: Set Worksheet Protection Settings
Use Worksheet object properties to set worksheet protection settings as needed. For example :
worksheet.Protection.AllowDeletingColumn = false;
worksheet.Protection.AllowDeletingRow = false;
worksheet.Protection.AllowEditingContent = false;
worksheet.Protection.AllowEditingObject = false;
// ... Set other protection settings as needed...
Step 7: Save the modified Excel file
Save the modified Excel file using the Save method of the Workbook object:
excel.Save(dataDir + "output.xls", SaveFormat.Excel97To2003);
Be sure to specify the desired path and filename for the output file.
Step 8: Close the file stream
Once saved, close the file stream to release all associated resources:
fstream.Close();
Sample source code for Advanced Protection Settings For Excel Worksheet using Aspose.Cells for .NET
// The path to the documents directory.
string dataDir = "YOUR DOCUMENT DIRECTORY";
// Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream(dataDir + "book1.xls", FileMode.Open);
// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook excel = new Workbook(fstream);
// Accessing the first worksheet in the Excel file
Worksheet worksheet = excel.Worksheets[0];
// Restricting users to delete columns of the worksheet
worksheet.Protection.AllowDeletingColumn = false;
// Restricting users to delete row of the worksheet
worksheet.Protection.AllowDeletingRow = false;
// Restricting users to edit contents of the worksheet
worksheet.Protection.AllowEditingContent = false;
// Restricting users to edit objects of the worksheet
worksheet.Protection.AllowEditingObject = false;
// Restricting users to edit scenarios of the worksheet
worksheet.Protection.AllowEditingScenario = false;
// Restricting users to filter
worksheet.Protection.AllowFiltering = false;
// Allowing users to format cells of the worksheet
worksheet.Protection.AllowFormattingCell = true;
// Allowing users to format rows of the worksheet
worksheet.Protection.AllowFormattingRow = true;
// Allowing users to insert columns in the worksheet
worksheet.Protection.AllowFormattingColumn = true;
// Allowing users to insert hyperlinks in the worksheet
worksheet.Protection.AllowInsertingHyperlink = true;
// Allowing users to insert rows in the worksheet
worksheet.Protection.AllowInsertingRow = true;
// Allowing users to select locked cells of the worksheet
worksheet.Protection.AllowSelectingLockedCell = true;
// Allowing users to select unlocked cells of the worksheet
worksheet.Protection.AllowSelectingUnlockedCell = true;
// Allowing users to sort
worksheet.Protection.AllowSorting = true;
// Allowing users to use pivot tables in the worksheet
worksheet.Protection.AllowUsingPivotTable = true;
// Saving the modified Excel file
excel.Save(dataDir + "output.xls", SaveFormat.Excel97To2003);
// Closing the file stream to free all resources
fstream.Close();
Conclusion
Congratulation ! You have now learned how to set advanced protection settings for an Excel spreadsheet using Aspose.Cells for .NET. Use this knowledge to secure your Excel files and restrict user actions.
FAQs
Q: How can I create a new C# project in my IDE?
A: The steps to create a new C# project may vary depending on the IDE you are using. Consult your IDE’s documentation for detailed instructions.
Q: Is it possible to set custom protection settings other than those mentioned in the tutorial?
A: Yes, Aspose.Cells offers a wide range of protection settings that you can customize to your specific needs. See the Aspose.Cells documentation for more details.
Q: What is the file format used to save the modified Excel file in the sample code?
A: In the sample code, the modified Excel file is saved in Excel 97-2003 (.xls) format. You can choose other formats supported by Aspose.Cells if needed.
Q: How can I access other worksheets in the Excel file?
A: You can access other worksheets using index or sheet name, for example: Worksheet worksheet = excel.Worksheets[1]; or Worksheet worksheet = excel.Worksheets[" SheetName"];.
