Lock all columns in a worksheet

Aspose.Cells Product Family
1

Hello guys,

I need to block all columns in a worksheet. Today, we are using the documentation code for lock, but as there are many columns and we have many spreadsheets in a Workbook, the processing time is very bad.

We saw that there is a Range feature. Is it possible to use it to lock all columns at once?

Thanks!

2

@caiocanalli,

Could you please share your sample code (runnable) and template file to show the performance issue, we will check your issue soon. We might enhance your code a bit to cope with it.

PS. please zip the files prior attaching here.

3

@Amjad_Sahi

I can’t upload the example sheet. I also can’t upload the file to the cloud because my machine is blocked. But the source code we are using is below. Spreadsheets usually have around 70 tabs and we need to block all columns from all of them.

public Worksheet LockAllColumns(Worksheet worksheet, int colCount)
    {
        var locklimit = colCount; // 16384 columns

        locklimit--;
        if (locklimit <= 0)
            locklimit = 16383;

        for (var i = 0; i <= locklimit; i++)
        {
            var flag = new StyleFlag();
            var style = worksheet.Cells.Columns[i].Style;
            style.IsLocked = true;
            flag.Locked = true;

            worksheet.Cells.Columns[i].ApplyStyle(style, flag);
        }

        return worksheet;
    }

Thanks!

4

@caiocanalli,

To lock all the columns in a sheet means you want to lock the whole worksheet, so it is better to lock the worksheet using minimal code in one go instead of unnecessarily locking each column one by one. See the sample code for your reference on how to protect the whole sheet:
e.g.
Sample code:

// Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream("Book1.xlsx", FileMode.Open);

// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];

// Protecting the worksheet with a password
worksheet.Protect(ProtectionType.All, "aspose", null);

// Saving the modified Excel file in default format
workbook.Save("output.out.xlsx");

Also, see the document for your reference.

Let us know if you still have any issue or confusion.

5

@Amjad_Sahi,

Sorry, I think I forgot some information.

In this case, for each worksheet, I need to block following these rules:

1 - If you have data and it is not a formula, do not block;
2 - If it is formula or empty it blocks;
3 - Check from column 0 to 16500;

How we are doing:

We iterate over all the spreadsheets in the workbook and block all columns. Then, we check the cells that have data and it’s not a formula and we unlock it.

If we lock the entire spreadsheet, will we still be able to unlock the cells that meet rule 1?

Our current code:

foreach(var s in sheets)
{
    var colCount = s.Cells.MaxDataColumn;
    var rowCount = s.Cells.MaxDataRow;

    LockAllColumns(s);
    UnlockFewCells(s, colCount, rowCount);
    ProtectSheet(s);
}

public Worksheet UnlockFewCells(Worksheet worksheet, int col, int row)
{
    for (int i = 0; i <= col; i++)
    {
        for (int j = 0; j <= row; j++)
        {
            var cell = worksheet.Cells[j, i];
            if (cell != null && !string.IsNullOrEmpty(cell?.Value?.ToString()) && !cell.IsFormula)
            {
                var style = cell.GetStyle();
                style.IsLocked = false;
                worksheet.Cells[j, i].SetStyle(style);
            }
        }
    }
    return worksheet;
}

 public Worksheet ProtectSheet(Worksheet worksheet)
 {
     worksheet.Protect(ProtectionType.All, "password", null);
     return worksheet;
 }

If we lock the entire spreadsheet, calling ProtectSheet before the UnlockFewCells method, when we run UnlockFewCells, will unlocking work?

Thanks.

6

@caiocanalli,

You do not need to block all the columns (as you are using LockAllColumns(s);), it will waste time. Please note all cells’ locked attribute is true by default, so why iterating columns (to set IsLocked = true) at the first place. A good workflow can be:

  1. Unlock your desired range of cells (set Style.IsLocked to false) first.
  2. Now protect the sheet (e.g. via Worksheet.Protect() method).

Hope, this helps a bit.