Resizing column not working on a protected sheet

Aspose.Cells Product Family
1

Below is the code I use

Workbook workbook = new Workbook();
WorksheetCollection worksheets = workbook.Worksheets;
Worksheet testCodeSheet = workbook.Worksheets[“Test Sheet”];

testCodeSheet.Protection.AllowFiltering = true;
testCodeSheet.Protection.AllowSorting = true;

testCodeSheet.Protect(ProtectionType.All, “test”, null);

It generates an excel file, but the column is not resizable.
Can you please let me know, if this can be done in Aspose.Cells

2

@mjjagan

To allow resizing of columns in a protected worksheet using Aspose.Cells, you need to ensure that the protection settings permit formatting of rows and columns. In your current implementation, you have set the worksheet to be protected with ProtectionType.All, which restricts many actions, including resizing columns.

To enable column resizing, you should allow formatting of rows and columns by setting the appropriate properties in the protection settings. Here’s how you can modify your code:

Workbook workbook = new Workbook();
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet testCodeSheet = worksheets.get("Test Sheet");

// Allow filtering and sorting
testCodeSheet.getProtection().setAllowFiltering(true);
testCodeSheet.getProtection().setAllowSorting(true);

// Allow formatting of rows and columns
testCodeSheet.getProtection().setAllowFormattingRow(true);
testCodeSheet.getProtection().setAllowFormattingColumn(true);

// Protect the worksheet
testCodeSheet.protect(ProtectionType.All, "test", null);

In this code, the key changes are the addition of setAllowFormattingRow(true) and setAllowFormattingColumn(true), which will enable users to resize the columns even when the sheet is protected.

Make sure to test this implementation to confirm that it meets your requirements for column resizing on the protected sheet.

3

@mjjagan,

I tested your scenario/case using the following sample code and it works fine. Please refer to the following code using Aspose.Cells for .NET and write/update your code accordingly for your requirements.
e.g.,
Sample code:

Workbook workbook = new Workbook();
WorksheetCollection worksheets = workbook.Worksheets;
//Set the name of the default (first) sheet
worksheets[0].Name = "Test Sheet";
Worksheet testCodeSheet = worksheets["Test Sheet"];

//Allow filtering and sorting
testCodeSheet.Protection.AllowFiltering = true;
testCodeSheet.Protection.AllowSorting = true;

//Allow formatting of rows and columns and cells
testCodeSheet.Protection.AllowFormattingRow = true;
testCodeSheet.Protection.AllowFormattingColumn = true;
testCodeSheet.Protection.AllowFormattingCell = true;

// Protect the worksheet
testCodeSheet.Protect(ProtectionType.All, "test", null);

workbook.Save("e:\\test2\\ou1.xlsx");

Please find attached the output Excel file for your reference.
out1.zip (5.9 KB)

Open the file into MS Excel and you may resize column widths, resize row heights and format cells in the protected (“Test Sheet”) without any issue. Moreover, you may filter and sort data.

If you still experience the issue, please share your full sample code and the sample Excel file to illustrate the problem, and we will review it promptly.

4

Thanks for the code.
It is working as expected

5

@mjjagan,

It’s good to hear that the suggested code segment meets your requirements. Don’t hesitate to reach out if you have any additional questions or feedback.