We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Sheets protection

Hi,

I would like to restrict the user on my reports( excel files) .

To apply sheets protection in a way as the user will be able to:

  • change column/row width
  • select cells
  • filter out
  • apply autofit - if use select all cells and apply autofit then also hidden columns will be unhided!!!
  • hide / unhide columns / rows

can you please suggest me , is that possible to allow only some actions to user, if so how can i achieve that.

Thanks

Satish madadi.

Hi,


Aspose.Cells for .NET supports Worksheet protection options provided in MS Excel. You may try some advanced protection options that MS Excel supports according to your requirement. See the document for reference, you may utilize (on/off) appropriate options:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/advanced-protection-settings-since-excel-xp.html


Sample code:

Workbook workbook = new Workbook();
string templateFileName = “e:\test\MyFile.xls”;
workbook.Open(templateFileName);
//Get the first worksheet.
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Protection.IsFilteringAllowed = true;
worksheet.Protection.IsSelectingLockedCellsAllowed = true;
worksheet.IsGridlinesVisible = false;
worksheet.Protection.IsSelectingUnlockedCellsAllowed = true;
worksheet.Protection.IsDeletingColumnsAllowed = false;
worksheet.Protection.IsDeletingRowsAllowed = false;
worksheet.Protection.IsEditingContentsAllowed = false;
worksheet.Protection.IsEditingObjectsAllowed = false;
worksheet.Protection.IsEditingScenariosAllowed = false;
worksheet.Protection.IsFormattingCellsAllowed = false;
worksheet.Protection.IsFormattingColumnsAllowed = true;
worksheet.Protection.IsFormattingRowsAllowed = true;
worksheet.Protection.IsInsertingColumnsAllowed = false;
worksheet.Protection.IsInsertingHyperlinksAllowed = false;
worksheet.Protection.IsInsertingRowsAllowed = false;
worksheet.Protection.IsSortingAllowed = true;
worksheet.Protection.IsUsingPivotTablesAllowed = false;
worksheet.Protection.Password = “1234”;

workbook.Save(“e:\test\ProtectedSheet.xls”, FileFormatType.ExcelXP);


Thank you.

Hi,

Well, in my last reply, I was using Aspose.Cells for .NET component in the sample code. But, if you are using Aspose.Cells for Java, you can easily change my code accordingly, see the document from Aspose.Cells for Java documentation for your reference:
http://www.aspose.com/documentation/java-components/aspose.cells-for-java/advanced-protection-settings-since-excel-xp.html


Thank you.

Hi ,

Our FID Reports are went live last month. after taking the feedback from users we decide to do some more enhancements for open boundaries.

In theat one is sheet protection , that u gave some idea -- iwill try that.

one more thing is that right now we are hiding the sheets and columns in the report. now users want to delete the column/sheet.

I have already share my code with you any am attaching one main code. can you please sugest how can i delete without changing much code as not approved man hours .

Thanks is advance

satish madadi.

Hi Satish,

Please use Advanced protection types accordingly for your requirement.
I have pasted a sample code here, the code will restrict certain features and objects but allow the users to delete column, rows and other operations. Please get reference from it for your need. You may allow or restrict certain elements using “true” or “false” for their respective methods.

Sample code:

//Instantiating a Workbook object
Workbook excel = new Workbook();

//Accessing the first worksheet in the Excel file
excel.open(“e:\test\book1.xls”);
Worksheets worksheets = excel.getWorksheets();
Worksheet worksheet = worksheets.getSheet(0);

Protection protection = new Protection();
worksheet.protect(protection);

//Allowing users to delete columns of the worksheet
protection.setDeletingColumnsAllowed(true);

//Allowing users to delete row of the worksheet
protection.setDeletingRowsAllowed (true);

//Restrict users to edit contents of the worksheet
protection.setEditingContentsAllowed (false);

//Restrict users to edit objects of the worksheet
protection.setEditingObjectsAllowed (false);

//Restrict users to edit scenarios of the worksheet
protection.setEditingScenariosAllowed (false);

//Restricting users to filter
protection.setFilteringAllowed (false);

//Allowing users to format cells of the worksheet
protection.setFormattingCellsAllowed (true);

//Allowing users to format rows of the worksheet
protection.setFormattingRowsAllowed (true);

//Allowing users to insert columns in the worksheet
protection.setInsertingColumnsAllowed (true);

//Allowing users to insert hyperlinks in the worksheet
protection.setInsertingHyperlinksAllowed (true);

//Allowing users to insert rows in the worksheet
protection.setInsertingRowsAllowed(true);

//Allowing users to select locked cells of the worksheet
protection.setSelectingLockedCellsAllowed(true);

//Allowing users to select unlocked cells of the worksheet
protection.setSelectingUnlockedCellsAllowed(true);

//Allowing users to sort
protection.setSortingAllowed(true);

//Allowing users to use pivot tables in the worksheet
protection.setUsingPivotTablesAllowed (true);

//Saving the modified Excel file Excel XP format
excel.save(“d:\files\output.xls”,FileFormatType.EXCELXP);


Thank you.

Hi Amjad,

Thanks for quick responce. I will implement this one for protection. Can you please give some tips to delete the columns and sheets for programatically.

Can you please send me the latest API.

Thanks

Satish.

Hi Satish,

Please see the documents for your requirement:

1) To delete worksheet in the workbook:
http://www.aspose.com/documentation/java-components/aspose.cells-for-java/managing-worksheets.html

2) To Delete Rows/Columns in the worksheet:
http://www.aspose.com/documentation/java-components/aspose.cells-for-java/insertingdeleting-rows-and-columns.html


Thank you.