CODEWITHSUNDEEP
c#openxml
Rudolf Lamprecht
Rudolf Lamprecht

Reputation: 1070

How to password protect an Excel document with Open XML

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?

Upvotes: 6

Views: 11874

Answers (3)

Dhineshbabu
Dhineshbabu

Reputation: 111

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();
        }

Upvotes: 11

daniell89
daniell89

Reputation: 2272

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());
     }
}

Upvotes: 1

Rudolf Lamprecht
Rudolf Lamprecht

Reputation: 1070

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.

Upvotes: 0

Related Questions