Click here to Skip to main content
15,846,144 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I opened an Excel file with OpenXML. I need to lock some cells into sheet.

What I have tried:

I used the protection object, but I can only lock the entire sheet.
Posted
Updated 12-Oct-23 12:07pm
v2

1 solution

Yes, protection only works on the entire sheet, not cells. What you do is set the cells you want to protect as "locked" and all other cells as "unlocked", then you enable protection on the sheet to turn the protection of locked cells on and off.

You can see this in Excel by selecting a range of cells, then right-clicking on that range and pick "Format Cells". Click on the Protection tab in the dialog that shows up to get/set the Locked state for the selected cells.

When you get all the cells you want to protect setup as "locked", you then enable Protection on the sheet to enforce it.

If you're going to dig into this stuff, it helps to know how Excel works. They cover this kind of stuff in the documentation for Excel.

--- EDIT ---
By default, all cells are formatted "Locked = true" so you don't have to do anything to them unless you want to leave cells unprotected.

To actually turn on protection for the sheet, you have to create a SheetProtection object and add it to the parent WorksheetPart of the sheet.
C#
    // Add Sheet Protection
    ProtectSheet(worksheetPart, "P@ssword");

...

    static void ProtectSheet(WorksheetPart worksheetPart, string password)
    {
        // All passwords in Excel are expected to use Unicode encoding, not ASCII!
        byte[] passwordBytes = Encoding.Unicode.GetBytes(password);
        byte[] salt = new byte[16];
        RandomNumberGenerator.Fill(salt);
        uint spinCount = 10000;
        
        // Setup the SheetProtection object to use a password to unlock
        SheetProtection sheetProtection = new SheetProtection()
        {
            // When you protect a sheet, you can specify which functions to lock
            Sheet = true,
            Objects = true,
            Scenarios = true,
            SelectLockedCells = true,
            AlgorithmName = "SHA-512",
            HashValue = Convert.ToBase64String(GetPasswordHash(passwordBytes, salt, spinCount)),
            SaltValue = Convert.ToBase64String(salt),
            SpinCount = spinCount
        };

        worksheetPart.Worksheet.Append(sheetProtection);
    }

    static byte[] GetPasswordHash(byte[] password, byte[] salt, uint spinCount)
    {
        using (SHA512 sha512 = SHA512.Create())
        {
            byte[] buffer = new byte[salt.Length + password.Length];
            Array.Copy(salt, buffer, salt.Length);
            Array.Copy(password, 0, buffer, salt.Length, password.Length);
            byte[] hash = sha512.ComputeHash(buffer);
            buffer = new byte[hash.Length + 4];
            for (int i = 0; i < spinCount; i++)
            {
                Array.Copy(hash, buffer, hash.Length);
                Array.Copy(BitConverter.GetBytes(i), 0, buffer, hash.Length, 4);
                hash = sha512.ComputeHash(buffer);
            }

            return hash;
        }
    }
 
Share this answer
 
v2
Comments
chiaracat 11-Oct-23 7:04am    
I have set

CellFormat lockFormat = new CellFormat() {
ApplyProtection = true,
Protection = new Protection() {
Locked = true
}
};

and append this to WorkbookStylesPart

sp.Stylesheet.CellFormats.AppendChild <cellformat> (lockFormat);

but I dont know how lock cells

Thanks
Dave Kreskowiak 11-Oct-23 14:26pm    
OpenSDK is not easy to work with. I've updated my answer with a couple methods that will add a SheetProtection object to a specified WorksheetPart object. You have to supply the WorksheetPart associated with the Sheet you want to protect and a password to set.
chiaracat 16-Oct-23 6:41am    
Thanks you for you update but I don't find the property to set to lock a cell.
You write

... SelectLockedCells = true,...

but how can I lock into sheet?

Thanks
Dave Kreskowiak 16-Oct-23 9:20am    
SelectLockedCells is a property of the SheetProtection class.

Every cell is, by default, already formatted to be Locked. If you don't want a cell locked, you actually have to change the format of those cells. That is not covered in the code I posted.

The code I posted enables the protection of an entire Sheet. You just have to have to provide the WorksheetPart of the sheet you want to protect and the password to set.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900