Free Support Forum - aspose.com

Autofill on protected worksheet

Hi,

I have a protected worksheet with some cells unprotected:

// allow users to edit unlocked cells only
m_WorkSheet.Protection.AllowSelectingUnlockedCell = true;

        // protect worksheet
        m_WorkSheet.Protect(ProtectionType.All, ConfigurationManager.AppSettings["ExcelExportPassword"], string.Empty);

The issue I’m having is that autofill is not allowed on the unlocked cells. So, the user can’t select the bottom right hand corner of an unlocked cell and drag to copy content down to other unlocked cells directly below.

I’m not able to find any reference in the Protection class to allow this.

Tia
JC

@bonson,

I have tested your scenario/case a bit using the following sample code, it works fine and I do not find any issue. Please also find attached the output file for your reference. I can use auto-fill handle to expand on the unlocked cells, see the screenshot for your reference:

e.g
Sample code:

    //Create workbook
    Workbook wb = new Workbook();

    //Access first worksheet
    Worksheet ws = wb.Worksheets[0];

    //Put some values in cells
    ws.Cells["B5"].PutValue("Unlocked 1");
    ws.Cells["B6"].PutValue("Unlocked 11");
    ws.Cells["B7"].PutValue("Unlocked 111");

    //Unlock the cells B5, C5, D5
    Cell c = ws.Cells["B5"];
    Style s = c.GetStyle();
    s.IsLocked = false;

    //Unlocking
    ws.Cells["B5"].SetStyle(s);
    ws.Cells["B6"].SetStyle(s);
    ws.Cells["B7"].SetStyle(s);

    ws.AutoFitColumn(1);

    //Set protection that we allow selecting unlocked cells only
    ws.Protection.AllowSelectingUnlockedCell = true;

    //Protect the worksheet
    ws.Protect(ProtectionType.All, "abcd", string.Empty);

    //Save the output Excel file
    wb.Save("e:\\test2\\output.xlsx");

If you still find the issue, kindly do paste your sample code (runnable) same as above to reproduce the issue on our end. Also provide some screenshots to highlight the issue. We will check it soon.
file1.zip (6.3 KB)

Many thanks for this, being going round and realised the problem is infact Excel. I have an image on the next cell which was locked.

When the sheet was protected and the user moved the mouse to the autofill position the auto fill cross hair appeared but did nothing. When you unlocked the sheet and moved the mouse to the same position the image move cursor appeared.

Just slightly confusing, issue was solved by nudging the image a few pixels.

Thanks for assistance.

@bonson,

Good to know that your have sort it out now. Feel free to write us back if you have further comments or queries, we will be happy to assist you soon.