We have a requirement to lock all the cells in a worksheet first. Then we need to unlock a selective range. How can we achieve this in Aspose.cells. Is there any methods to lock all cells atonce other than looping thru cells.
This message was posted using Banckle Live Chat 2 Forum
Hi Anish,
Thanks for your query. As your query relates to Aspose.Cells, so I’m moving your request to the related forum. There associated support developer will guide you properly.
Best Regards,
Hi,
Sure, it is possible. Please see the document for your complete reference:
Note: Sub-topics in the document of your special interest can be:
Allow Users to Edit Ranges, Protect a few Cells in the Worksheet etc.
Hi,
I am unable to achieve the locking functionality completely as mentioned . Please find my requirement.
1. I have a base template in which I need to do some manipulations and create it as a new file.
2. I have few ranges which I need to make editable (In the attached file I have create only 1 for your reference .In live scenario there will be multiple dynamic ranges).
3. I need to lock all the cells except few of the selected ranges.
I have implemented the code as mentioned below but the locking is working only partially. I am still able to edit the other cells (From Cell C13). and few are non-editable. I am attaching the file and code for your reference. Let me know what is the issue here
Private Sub ProtectSheet()
Dim workbook As New Workbook("D:\AsposeTest\AsposeSample\AsposeSample\ExcelSource\RPT1.xls")
'Get the first (default) worksheet
Dim sheet As Worksheet = workbook.Worksheets(0)
'Get the Allow Edit Ranges
Dim allowRanges As ProtectedRangeCollection = sheet.AllowEditRanges
'Define ProtectedRange
Dim proteced_range As ProtectedRange
Dim ntRange As Aspose.Cells.Range
ntRange = workbook.Worksheets.GetRangeByName("Range_Notes")
' ''Create the range
Dim idx As Integer = allowRanges.Add(ntRange.Name, ntRange.Item(0, 0).Row, ntRange.Item(0, 0).Column, ntRange.Item(ntRange.RowCount - 1, ntRange.ColumnCount - 1).Row, ntRange.Item(ntRange.RowCount - 1, ntRange.ColumnCount - 1).Column)
proteced_range = allowRanges(idx)
'Protect the sheet
sheet.Protect(ProtectionType.All)
workbook.Save("D:\AsposeTest\AsposeSample\AsposeSample\ExcelSource\RPT1_Protected.xls")
End Sub
Hi,
Well, I have tested your scenario with your file and code, the AllowEditRanges feature does work the same way as MS Excel, for confirmation, you may do your task manually in MS Excel, you will see the similar results as per your output file(by Aspose.Cells APIs). Please create your desired/expected output file (with your desired locking for specific range of cells only) using your template input file “RPT1.xls” by manually creating it in MS Excel and attach it here for demonstration, we will check it and tell you how to do it by Aspose.Cells APIs.
Thanks,
Hi
Thanks. I have modifed & attached my output file as per my requirement for your reference. In that the Green colour is getting locked as per my requirement using Aspose.Cells. The cells which are not getting locked is colored in Orange. The cells which needs to be editable are colored in Yellow (Please note the editable ranges are dynamic). Let me know how I can achieve this.
Limitation in my Scenario: - We dont have control on the base template. We are not supposed to modify the base template. We can do modificationsrequired in the newely generated file :-)
Thanks
Anish
Hi Anish,
I will be working over your query and will try to provide some code snippet to accomplish your task by Aspose.Cells APIs. I will get back to you soon.
Thank you.
Hi Anish,
Please see the sample code below with comments, it will fulfill your needs. You may also amend the code (add/update the lines) accordingly. I used your previous template file “RPT1.xls” as input file.
Sample code:
Dim wb As New Workbook(“e:\test2\RPT1.xls”)
Dim sheet As Worksheet = wb.Worksheets(0)
Dim style As Style
Dim flag As StyleFlag
'I checked your template file, Actually all the cells are not set with locked attribute, so when we protect a sheet, all the cells are not included
'So, we need to make sure that we should set all the cells locked first for all the columns i.e. A to IV.
For i As Integer = 0 To 255
style = sheet.Cells.Columns(CByte(i)).Style
style.IsLocked = True
flag = New StyleFlag()
flag.Locked = True
sheet.Cells.Columns(CByte(i)).ApplyStyle(style, flag)
Next i
'Suppose, now we only need to unlock B10 and C15 cells. So, you will do, e.g
’ Off the Lock from the cell
style = sheet.Cells(“B10”).GetStyle()
style.IsLocked = False
sheet.Cells(“B10”).SetStyle(style)
'Similarly off the lock from C15 cell.
style = sheet.Cells(“C15”).GetStyle()
style.IsLocked = False
sheet.Cells(“C15”).SetStyle(style)
'…
'You may even create a range for your desired editable area (A13:G194) and set its style’s IsLocked to false accordinlgy.
'…
'Now Protect the sheet now
sheet.Protect(ProtectionType.All)
'Save the Excel file
wb.Save(“e:\test2\myprotectedrange2.xls”)
The output file is also attached here.
If you still have any confusion or issue, let me know.
Thank you.
Hi,
Thanks its working.For single Cells I am able to get the style and modify the same to lock it. But I have a issue to set the locking for a range of multiple cells. How to do this for the below scenario
style = sheet.Cells("A13:G194").GetStyle() ' This throws an error like invalid Cell address
style .IsLocked = true
Thanks
Anish
Hi Anish,
You are doing wrong while getting/setting styles for a range in your code. This is not the way to get/set style for a range. See the sample code below for your reference:
Dim style As Style = wb.Styles(wb.Styles.Add())
style.IsLocked= True
sheet.Cells.CreateRange(“A13:G194”).SetStyle(style)
Hope, this helps you. Also, we recommend you to kindly see our online documentation, especially the topic:
Creating a Named Range with Aspose.Cells for .NET
Let me know if you still have any other issue.
Thank you.
Works like charm!!!. Thanks a lot
One more doubt "will locking of the multiple cells contribute to the size of the excel?" Seems like the file size is increased compared to unlocked one.
Anish
Hi Anish,
Good to know that your issue is resolved now.
Anishc:
One more doubt "will locking of the multiple cells contribute to the size of the excel?" Seems like the file size is increased compared to unlocked one.
Well, I think it may affect the overall size of the file a bit as we are applying styles (with Locked options on etc.) to those cells/range of cells.
Thank you.