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