Make few cells lock/readonly in excel format xls Or xlsx

Aspose.Cells Product Family
1

Hi,


I am trying make few cells readonly/locking using aspose.cell in Excel format for XLS or XLSX with below code. But when I open the particular file in Excel. It’s protecting complete sheet.
After unprotecting the sheet, I am able to edit the cell in the range of (A14:G20).

Is there any way to protect/lock/make read-only few cells in the worksheet. ( I am using Aspose.Cells.dll of version 7.4.2.3).
 public void ReadOnly(Aspose.Cells.Worksheet sheet)

{

        <span style="color:blue;">for</span> (<span style="color:blue;">int</span> i = 0; i < sheet.Cells.Columns.Count; i++)
        {
            Aspose.Cells.<span style="color:#2b91af;">Style</span> style = sheet.Cells.Columns[i].Style;
            style.IsLocked = <span style="color:blue;">false</span>;
            <span style="color:blue;">var</span> flag = <span style="color:blue;">new</span> Aspose.Cells.<span style="color:#2b91af;">StyleFlag</span>() { Locked = <span style="color:blue;">true</span> };
            sheet.Cells.Columns[i].ApplyStyle(style, flag);
        }
        <span style="color:blue;">var</span> range = sheet.Cells.CreateRange(<span style="color:#a31515;">"A14:G20"</span>);
        <span style="color:#2b91af;">IEnumerator</span> enumerator = range.GetEnumerator();
        <span style="color:blue;">while</span> (enumerator.MoveNext())
        {
            <span style="color:blue;">var</span> cell = enumerator.Current <span style="color:blue;">as</span> Aspose.Cells.<span style="color:#2b91af;">Cell</span>;
            Aspose.Cells.<span style="color:#2b91af;">Style</span> style = cell.GetStyle();
            style.IsLocked = <span style="color:blue;">true</span>;
            <span style="color:green;">//var flag = new Aspose.Cells.StyleFlag() { Locked = true };  </span></pre><pre style="font-family: Consolas; font-size: 13px; background: white;"><span style="color: green;">                </span><span style="color: green;">//cell.SetStyle(style, flag);</span></pre><pre style="font-family: Consolas; font-size: 13px; background: white;"><pre style="font-family: Consolas; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">                cell.SetStyle(style );</pre></pre><pre style="font-family: Consolas; font-size: 13px; background: white;">            }
        sheet.Protect(<span style="color:#2b91af;">ProtectionType</span>.All);
    }</pre></div><div><br></div><div>Thanks,</div><div>Ramesh</div>
2

Hi Ramesh,


Thank you for contacting Aspose support.

When you lock cells, columns/rows & ranges in a worksheet, the protection is applied at the worksheet level. Therefore when you un-protect the worksheet, all locked cells, columns/rows & ranges are no more protected.

If your requirement is to protect a range of cells in a worksheet while allowing the user to change the contents of that range upon providing the password, you may use the mechanism elaborated below. Please note, complete worksheet would be protected, however, only the specified range of cells will allow the editing after providing valid password.

C#

//Instantiate a new Workbook
Workbook book = new Workbook();

//Get the first (default) worksheet
Worksheet sheet = book.Worksheets[0];
//Get the Allow Edit Ranges
ProtectedRangeCollection allowRanges = sheet.AllowEditRanges;
//Define ProtectedRange
ProtectedRange proteced_range;

//Create the range
int idx = allowRanges.Add(“name”, 0, 0, 0, 0);
proteced_range = allowRanges[idx];
//Specify the passoword
proteced_range.Password = “123”;

//Protect the sheet
sheet.Protect(ProtectionType.All);

//Save the Excel file
book.Save(myDir + “protectedrange.xls”, SaveFormat.Excel97To2003);
book.Save(myDir + “protectedrange.xlsx”, SaveFormat.Xlsx);

In above code, the specified range is the cell A1. You acn specify any range by passing the start row & column indexes as well as ending row & column to the ProtectedRangeCollection.Add method.
3

Hi,


Please change your code segment i.e,
…
var range = sheet.Cells.CreateRange(“A14:G20”);
IEnumerator enumerator = range.GetEnumerator();
while (enumerator.MoveNext())
{
var cell = enumerator.Current as Aspose.Cells.Cell;
Aspose.Cells.Style style = cell.GetStyle();
style.IsLocked = true;
//var flag = new Aspose.Cells.StyleFlag() { Locked = true };
// cell.SetStyle(style, flag);
cell.SetStyle(style );
}
sheet.Protect(ProtectionType.All);
…

with:
…
var range = sheet.Cells.CreateRange(“A14:G20”);


for (int r = range.FirstRow; r < range.FirstRow + range.RowCount; r++)
{

for (int c = range.FirstColumn; c < range.FirstColumn + range.ColumnCount; c++)
{
Cell cell = sheet.Cells[r, c];
style = cell.GetStyle();
style.IsLocked = true;
cell.SetStyle(style);

}

}



sheet.Protect(ProtectionType.All);
…
it works fine as I tested for your needs.

Let us know if you still find the issue.

Thank you.