Locking column in excel sheet with ClosedXml

Question

I'm getting to grips with using ClosedXml and trying to get it to lock a whole column. I've set it up like so:

void addingDataTable()
{
    using (XLWorkbook workbook = new XLWorkbook())
    {
        DataTable dt = GetTable("Information");

        var worksheet = workbook.Worksheets.Add(dt);

        ProtectColumn(worksheet, "Drug");
        workbook.SaveAs("C:\\Training\\excel sheet examples\\AddingDataTableAsWorksheet.xlsx");
    }
}

DataTable GetTable(string tableName)
{
        DataTable table = new DataTable();
        table.TableName = tableName;
        table.Columns.Add("Dosage", typeof(int));
        table.Columns.Add("Drug", typeof(string));
        table.Columns.Add("Patient", typeof(string));
        table.Columns.Add("Date", typeof(DateTime));
    
        table.Rows.Add(25, "Indocin", "David", DateTime.Now);
        table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
        table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
        table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
        table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
        return table;
}

static void ProtectColumn(IXLWorksheet worksheet, string columnName)
{
    // Protect the entire column
    worksheet.Column("Drug").Style.Protection.SetLocked(true);
}

However, when I run the application I get this error:

System.ArgumentOutOfRangeException: 'Specified argument was out of the range of valid values. Arg_ParamName_Name'

when it reaches the ProtectColumn method. I'm not sure where it is I am going wrong?

Answer 1

Realised I forgot to come back to this, but have now learned I can do something like this:

static void ProtectColumn(IXLWorksheet worksheet, string columnName)
{
    worksheet.Protect();

    IXLTable worksheetTable = worksheet.Table("Table1");
    IXLCell drugColumnHeader = worksheetTable.HeadersRow().CellsUsed(c => c.Value.ToString() == "Drug").Single();

    IXLCells drugColumnCells = worksheetTable.Column(drugColumnHeader.WorksheetColumn().ColumnNumber()).CellsUsed(c => c.Address.RowNumber > 1);


    foreach (IXLCell? cell in drugColumnCells)
    {
        cell.Style.Protection.SetLocked(false);
    }
}

This allows me to search for the specific column by its name and then go through each cell within that column and unlocking them whilst keeping the header cell locked

Answer 2

The error you are receiving indicates that there's an issue with identifying the column by the name "Drug". This could potentially be happening because the column name is not being recognized correctly by ClosedXML, or perhaps because the worksheet protection has not been correctly configured.

In ClosedXML, to protect cells in a column, you actually need to do two things:

1. Set the Locked property of the cells to true in the column you want to protect.
2. Enable sheet protection.

Here is a revised version of your ProtectColumn method that should do both:

static void ProtectColumn(IXLWorksheet worksheet, string columnName)
{
    // Assuming you want to lock the entire column, which will likely be from row 1 onwards.
    // Find the index of the column with the specified name.
    int columnIndex = worksheet.Column(columnName).ColumnNumber();
    
    // Lock the cells in this column.
    worksheet.Column(columnIndex).Style.Protection.Locked = true;

    // Enable sheet protection.
    worksheet.Protect() // You can also set a password here if necessary.
        .SetFormatCells(false)
        .SetFormatColumns(false)
        .SetFormatRows(false)
        .SetInsertColumns(false)
        .SetInsertRows(false)
        .SetInsertHyperlinks(false)
        .SetDeleteColumns(false)
        .SetDeleteRows(false)
        .SetSort(false)
        .SetAutoFilter(false)
        .SetPivotTables(false);
}

With this, now the whole column specified should be locked when the worksheet is protected. Once protection is enabled, the locked cells cannot be modified until the protection is disabled.

Another important note is when you apply column protection through .Style.Protection.Locked = true;, it would apply to all cells in the column. But this protection is only enforced once sheet protection is enabled, as you can see in the worksheet.Protect() call.

Please use this revised method in your code and check whether the exception is resolved and the column protection works as expected. Ensure the column name exists in the spreadsheet, or an ArgumentException will be thrown stating “The specified column name is not valid because it does not exist in the current context.” and column names are case sensitive in ClosedXML when referenced by name.