How to password protect an Excel document with Open XML

Question

At the moment, I am creating a new Excel document with Open XML's SpreadsheetDocument class by passing a MemoryStream parameter. I now need to set a password on this SpreadsheetDocument object, but what I have attempted does not seem to work. The Excel document open's up without asking for a password. Below is what I have tried so far (mem being the MemoryStream parameter):

using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(mem, true))
{
    foreach (var sheet in spreadsheet.WorkbookPart.WorksheetParts)
    {
        sheet.Worksheet.Append(new SheetProtection() { Password = "test" });
    }
}

I have also attempted the following with no success:

using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(mem, true))
{
    spreadsheet.WorkbookPart.Workbook.WorkbookProtection = new WorkbookProtection
    {
        LockStructure = true,
        LockWindows = true,
        WorkbookPassword = "test"
    }
}

What am I missing please?

Possible duplicate of [Excel File Password Protection with Open XML SDK](http://stackoverflow.com/questions/15168011/excel-file-password-protection-with-open-xml-sdk) — M O'Connell, Sep 12 '16 at 11:56
Yeah, this is where I got my first attempt's code sample from, but with no success. — Rudolf Lamprecht, Sep 12 '16 at 11:58
And did you noticed, that other [SO post answer](http://stackoverflow.com/a/15281182/997668) suggests to make a call to `Save()` method in case it doesn't work? `// add this in case it still doesn’t work. This makes sure the data is saved. //worksheet.Worksheet.Save();` Did you tried? This also didn't help? — Michael, Sep 12 '16 at 12:04
Yes, I have indeed tried implementing the `Save()` method for each worksheetpart. The only effect it had was to corrupt the Excel document — Rudolf Lamprecht, Sep 12 '16 at 12:06

score 9 · Answer 1 · answered Apr 06 '17 at 10:00

Openxml sheet protect Password has input Data type of "HexBinaryValue". so the input password as to be converted from string to hexa binary.

foreach (var worksheetPart in spreadsheet.WorkbookPart.WorksheetParts)
     {
         //Call the method to convert the Password string "MyPasswordfor sheet" to hexbinary type
         string hexConvertedPassword =  HexPasswordConversion("MyPasswordfor sheet");
//passing the Converted password to sheet protection
          SheetProtection sheetProt = new SheetProtection() { Sheet = true, Objects = true, Scenarios = true, Password = hexConvertedPassword };
          worksheetPart.Worksheet.InsertAfter(sheetProt,worksheetPart.Worksheet.Descendants<SheetData>().LastOrDefault());
worksheetPart.Worksheet.Save();
     }


/* This method will convert the string password to hexabinary value */
 protected string HexPasswordConversion(string password)
        {
            byte[] passwordCharacters = System.Text.Encoding.ASCII.GetBytes(password);
            int hash = 0;
            if (passwordCharacters.Length > 0)
            {
                int charIndex = passwordCharacters.Length;

                while (charIndex-- > 0)
                {
                    hash = ((hash >> 14) & 0x01) | ((hash << 1) & 0x7fff);
                    hash ^= passwordCharacters[charIndex];
                }
                // Main difference from spec, also hash with charcount
                hash = ((hash >> 14) & 0x01) | ((hash << 1) & 0x7fff);
                hash ^= passwordCharacters.Length;
                hash ^= (0x8000 | ('N' << 8) | 'K');
            }

            return Convert.ToString(hash, 16).ToUpperInvariant();
        }

Where did you get the algorithm to calculate the hashed password? — Francois Botha, Jun 22 '17 at 13:56

score 0 · Answer 2 · answered Sep 13 '16 at 06:20

Okay, so not entirely what I wanted to do, but I ended up dropping Open XML SDK and using Office.Interop assemblies to protect the document. Reason for using Open XML in the first place, was because it seems that an Interop workbook cannot be opened with a stream, it requires an actual file.

score 0 · Answer 3 · answered Mar 17 '17 at 11:29

You can try this:

using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(mem, true))
{
     foreach (var worksheetPart in spreadsheet.WorkbookPart.WorksheetParts)
     {
          SheetProtection sheetProt = new SheetProtection() { Sheet = true, Objects = true, Scenarios = true, Password = "test" };
          worksheetPart.Worksheet.InsertAfter(sheetProt, worksheetPart.Worksheet.Descendants<SheetData>().LastOrDefault());
     }
}

How to password protect an Excel document with Open XML

3 Answers3