Protect/lock a specfic column in excel worksheet

Question

User-707830680 posted

I am doing this. But its getting locked entire worksheet. I want to lock only B1 column.  How can I do it.

 

Excel.Worksheet oOTDDataSheet = (Excel.Worksheet)app.ActiveSheet;

Excel.Range r1 = oOTDDataSheet.get_Range("B1", "B1");

r1.Locked =true;

object missing = System.Reflection.Missing.Value;

oOTDDataSheet.Protect(missing, missing,true, false, false, false, false, false, false,false, false, false, false, true, false, false);

 

Answer 1

User-1694870838 posted

Hi,

Please check the code following:

  public static void SaveToExcel(object[,] data) 
    { 
        Excel = Microsoft.VisualBasic.Interaction.CreateObject("Excel.Application", String.Empty); 
        Excel.ScreenUpdating = false; 
        dynamic workbook = Excel.workbooks; 
        workbook.Add(); 
 
        dynamic worksheet = Excel.ActiveSheet; 

 worksheet.Activate(); 

      worksheet.Application.ActiveWindow.SplitColumn = 1;

      worksheet.Application.ActiveWindow.FreezePanes = true;  
        const int left = 1; 
        const int top = 1; 
        int height = data.GetLength(0); 
        int width = data.GetLength(1); 
        int bottom = top + height - 1; 
        int right = left + width - 1; 
 
        if (height == 0 || width == 0) 
            return; 
 
        dynamic rg = worksheet.Range[worksheet.Cells[top, left], worksheet.Cells[bottom, right]]; 
        rg.Value = data; 
 
        // Set borders 
        for (var i = 1; i <= 4; i++) 
            rg.Borders[i].LineStyle = 1; 
 
        // Set header view 
        dynamic rgHeader = worksheet.Range[worksheet.Cells[top, left], worksheet.Cells[top, right]]; 
        rgHeader.Font.Bold = true; 
        rgHeader.Interior.Color = 189 * (int)Math.Pow(16, 4) + 129 * (int)Math.Pow(16, 2) + 78; 
        rg.EntireColumn.AutoFit(); 
 
        // Show excel app 
        Excel.ScreenUpdating = true; 
        Excel.Visible = true; 
    } 

Best Regards,

Damon