How to protect the excel file created from a datatable using .net , but the protection is such that they can sort/filter the data based on partcular column/header column.
Hi,
Please refer to this document: Advanced Protection Settings since Excel XP
Let me know if it is useful for you.
This is quite a co-incidence as I was about to ask this exact same question! I have read over the Advanced protection settings, but there seems to be a problem applying these settings, in that, in excel (im using 2007), if I go to to one of the cells which is supposed to be locked, it does have the 'Locked' checkbox checked, but it also says underneath:
"Locking cells or hiding formilas has no effect until you protect the worksheet (Review tab, Changes group, Protect Sheet button)"
This is a bit of a contradiction to the advice given on the Advanced protection Aspose page, which reads:
"Note: Please don't call Protect method of the Worksheet class when you use Protection property. Moreover, please save the file as Excel97To2003 format because these advanced protection settings are only supported Excel versions since XP."
To sum up, if I use the method of:
opWorksheet.Protection.AllowFiltering = true;
opWorksheet.Protection.AllowSelectingLockedCell = false;
then my worksheet remains editable in the 'locked' cells, as per the sentence excel provides, and if I apply:
opWorksheet.Protect(ProtectionType.All)
Then the filtering is disabled, as the whole sheet is marked as protected.
Is there a specific ProtectionType we're supposed to use with 'Protection.AllowX'? or is there another way to allow filtering/sorting whilst maintaining the protection on the worksheet?
Hi,
Kindly provide source xls/xlsx file manually created by you using Ms-Excel with the requirements you specified. We will investigate how to create such a file and provide you a sample code here asap.
Hi mshakeel.faiz,
Thanks for the reply, I have attached a spreadsheet in the desired final format. It's in xlxs, but it would equally be in xls if that makes things easier your end, just let me know.
The rough summary of the sheet is that I would like certain columns to be effecively readonly, that is, locked and uneditable by the user. Column A is an example of this.
Column B, I would like to leave editable to the user, this could be a plain text field, or in some sitations this would be based around custom validation, but I assume that's not a problem as my previously protected sheet was ok with this.
The overall satisfactory goal is to allow auto filtering on the sheet, whilst also maintaining the 'protected' state. The overall 'perfect' situation would be for me to produce the sheet without the auto filter, (but retaining the protected state) and also allow the user to add in the auto filter if they wanted. If this is not possible, having the auto filter on may be enough for my client, I will clarify this with them.
Any pointers on this would be greatly appreciated,
Many thanks,
Doug
why ascending/descending is not working.when we apply properties as follows
Worksheet.Protection.AllowFiltering = true;
Worksheet.Protection.AllowDeletingColumn = false;
Worksheet.Protection.AllowDeletingRow = false;
Worksheet.Protection.AllowEditingContent = false;
Worksheet.Protection.AllowEditingObject = false;
Worksheet.Protection.AllowEditingScenario = false;
Worksheet.Protection.AllowFormattingCell = false;
Worksheet.Protection.AllowFormattingRow = false;
Worksheet.Protection.AllowFormattingColumn = false;
Worksheet.Protection.AllowInsertingHyperlink = false;
Worksheet.Protection.AllowInsertingRow = false;
Worksheet.Protection.AllowSelectingLockedCell = true;
Worksheet.Protection.AllowSelectingUnlockedCell = true;
Worksheet.Protection.AllowSorting = true;
Worksheet.Protection.AllowUsingPivotTable = false;
Worksheet.Protect(ProtectionType.None, “passs”, null);
and how to remove line breaks with in a cell
if a cell has a data like (one,
two,
three) with line breaks
Hi,
Please see the code below and the output file. The code is simple and fully commented, so you will not have a trouble understanding it
I have created a sample input file based on your file, then I protect the worksheet using the code below and create output file which is same as your desired file.
C#
string path = @“F:\Shak-Data-RW\Downloads\SheetProtection.xlsx”;
Workbook workbook = new Workbook(path);
Worksheet worksheet = workbook.Worksheets[0];
//Make all cells locked in this range
Range rng = worksheet.Cells.CreateRange(“B1:B7”);
Style style = workbook.CreateStyle();
style.IsLocked = false;
StyleFlag flag = new StyleFlag();
flag.All = true;
rng.ApplyStyle(style, flag);
//Set the protection
worksheet.Protection.AllowSelectingUnlockedCell = true;
worksheet.Protection.AllowSorting = true;
worksheet.Protection.AllowFiltering = true;
worksheet.Protection.AllowEditingObject = true;
//Protect the worksheet
worksheet.Protect(ProtectionType.All);
//Write the output
workbook.Save(path + “.out.xlsx”);
Hi there,
Thanks for the update, unfortunately that method is how I was previously attempting to perform the locking. Are there any issues with how I've put together this method? I've used your provided code, to apply specific formatting to columns I want, rather than a range:
//for each column index, lock the columns
for (int i = 0; i < opColumnIndexes.Length; i++)
{
Style opStyle = new Style();
opStyle = opWorksheet.Cells.Columns[opColumnIndexes[i]].Style;
opStyle.IsLocked = false;
opStyle.Font.Color = Color.DarkGray;
StyleFlag opStyleFlag = new StyleFlag();
opStyleFlag.All = true;
//apply the styles
opWorksheet.Cells.Columns[opColumnIndexes[i]].ApplyStyle(opStyle, opStyleFlag);
}
opWorksheet.Protection.AllowFiltering = true;
opWorksheet.Protection.AllowSelectingUnlockedCell = true;
opWorksheet.Protection.AllowSorting = true;
opWorksheet.Protection.AllowEditingObject = true;
//protect the sheet (currently using a GUID, so we can't really unlock it)
opWorksheet.Protect(ProtectionType.All,Guid.NewGuid().ToString(),String.Empty);
The sheet at the end of this process both allows me to edit the locked cells, and doesn't allow me to turn on the auto filter :(
Any ideas on the best way to tweak this method?
Cheers,
Doug
EDIT: As a test, I took the code posted here and ran that at my end, the sheet which is output is in the same format as the original code, in that the cells are protected when you'd expect, but the filtering is totally disabled for the uneditable column.
As a side note, I noticed you've used a template file, which might be what is being used to dictate whether or not the filtering is applied. If this is the case, I'm afraid that isn't an option I have available as both my columns and sheets are generated dynamically, so I'm not able to hard code the locations of columns I wish to apply filtering upon.
Hi,
Did you check the output xlsx file?
Attachment: SheetProtection.xlsx.out.xlsx
It is exactly same as you provided me your
Hi,
Please check the rectified code, it now uses column formatting instead of range. I found, you need to add one extra line.
worksheet.Protection.AllowSelectingLockedCell = false;
Please see the output xlsx file
C#
string path = @“F:\Shak-Data-RW\Downloads\SheetProtection.xlsx”;
Workbook workbook = new Workbook(path);
Worksheet worksheet = workbook.Worksheets[0];
Style style = workbook.CreateStyle();
style.IsLocked = false;
StyleFlag flag = new StyleFlag();
flag.All = true;
//Now apply it in on column
worksheet.Cells.Columns[1].ApplyStyle(style, flag);
//Set the protection
worksheet.Protection.AllowSelectingLockedCell = false;
worksheet.Protection.AllowSelectingUnlockedCell = true;
worksheet.Protection.AllowSorting = true;
worksheet.Protection.AllowFiltering = true;
//Protect the worksheet
worksheet.Protect(ProtectionType.All);
//Write the output
workbook.Save(path + “.out.xlsx”);
Hi mshakeel.faiz,
Thanks again for the feedback and code. I have checked the output file as you say, but the problem is that the routine I need to create cannot feasible have an input file to start with. As a result, I cannot apply the filter to the sheet as you have done (on the import sheet).
The main problem I have is that the option to apply a filter is removed when the sheet is locked with ProtectionType.All. Is there a mechanism in the Aspose suite to allow the auto filtering options to be switched on and off after the sheet is protected? Or to apply the auto filter to the sheet, then subsiquently apply the protection?
In addition, I had previously tried the following protection options:
opWorksheet.Protection.AllowFiltering = true;
opWorksheet.Protection.AllowSorting = true;
opWorksheet.Protection.AllowSelectingLockedCell = false;
opWorksheet.Protection.AllowSelectingUnlockedCell = true;
Which didn't seem to allow me to select any of the cells in the column I had applied formatting to, in fact, it didnt let me select any of the cells on the worksheet at all :(
I will try when I get a minute to strip my 'export' page into a standalone format and send a copy over, as it feels like I'm either applying something I shouldn't be, or not applying something I should!
Hi,
You do not need any input workbook. All you need is to create your workbook from scratch using your code and once you are done with it, use this code and then save your workbook.
If it does not work, then let me know your complete running example. I will check and report it to development team for a fix.
C#
Style style = workbook.CreateStyle();
style.IsLocked = false;
StyleFlag flag = new StyleFlag();
flag.All = true;
//Now apply it in on column
worksheet.Cells.Columns[1].ApplyStyle(style, flag);
//Set the protection
worksheet.Protection.AllowSelectingLockedCell = false;
worksheet.Protection.AllowSelectingUnlockedCell = true;
worksheet.Protection.AllowSorting = true;
worksheet.Protection.AllowFiltering = true;
//Protect the worksheet
worksheet.Protect(ProtectionType.All);