Protecting Worksheets in Excel 2007 using SDK 2.0 RRS feed

  • Question

  • I am using Open XML SDK 2.0 to modify an EXCEL 2007 Workbook. I need to password protect one of the work sheets from being viewed or changed...Also I intend to add functionality using VSTO; I need to protect that also. If anybody knows of any samples it would be greatly appreciated.
    Thanks in advance...
    Monday, February 8, 2010 2:07 AM

All replies

  • Hi PerryHall!

    If you want to protect a worksheet, you simply have to add a SheetProtection element under the Worksheet element:

    Worksheet worksheet;

        new SheetProtection() { Password = new HexBinaryValue() { Value = "CC1A" }, Sheet = true, Objects = true, Scenarios = true }

    Not sure though how to come up with the HexBinaryValue of the password.

    Tuesday, February 16, 2010 4:04 AM
  • Hi Jose..I wanted to thank you for your reply. I will give it a try and see what gives. Thanks again...PerryHall 
    Saturday, February 20, 2010 3:32 PM
  • Hi PerryHall,

    Thanks for your question.

    Open XML SDK doesn't support file encrytion/decrytion and digital signature. If you have this need, please refer to System.IO.Packaging.

    As for Jose Anton Bautista's code, it can protect the sheet from being edited but not being viewed in UI, so you could still open the sheet but cannot edit it after running this code.

    Hope this helps. If you have any question, please let me know.


    Monday, February 22, 2010 8:54 AM
  • Hi,

    Does anyone know how to do this for a presentation?



    Christer Ogenstad @ http://www.slideexecutive.com
    Sunday, April 4, 2010 5:32 PM
  • @ Christer Ogenstad

    Did you find a solution to your issue? I'm doing the same thing. If I find an answer I'll give you a shout.


    EDIT: I found a blog that looks promising and I replaced a few values in it to match PowerPoint... I'm not getting the right hash from it though it seems. The password I enter is never correct. Any suggestions?


    Thursday, August 5, 2010 1:00 AM
  • Hi,

    There is comment added in the same blog


    Hi Beowulf,

    It seems the problem is with the statement sb.Append(Convert.ToString(generatedKey[intTemp], 16));

    If the top nibble of one of the key bytes is 0, then Convert.ToString() will only output one character, creating an incorrect hex representation of the key.  If you replace this line with sb.Append(generatedKey[intTemp].ToString("X2"));, then it should work.


    Have you tried this change as well?


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Ankush Microsoft Online Community Support
    Thursday, August 5, 2010 1:53 PM
  • Thanks for the quick response,

    Actually, yeah... I have used both methods and tried it out. I got the same results... a document that has a password that I don't know. I modified the code to look as so:

    #region "Encryption"
      int[] InitialCodeArray = { 0xE1F0, 0x1D0F, 0xCC9C, 0x84C0, 0x110C, 0x0E10, 0xF1CE, 0x313E, 0x1872, 0xE139, 0xD40F, 0x84F9, 0x280C, 0xA96A, 0x4EC3 };
      int[,] EncryptionMatrix = new int[15, 7]
          /* char 1 */ {0xAEFC, 0x4DD9, 0x9BB2, 0x2745, 0x4E8A, 0x9D14, 0x2A09},
          /* char 2 */ {0x7B61, 0xF6C2, 0xFDA5, 0xEB6B, 0xC6F7, 0x9DCF, 0x2BBF},
          /* char 3 */ {0x4563, 0x8AC6, 0x05AD, 0x0B5A, 0x16B4, 0x2D68, 0x5AD0},
          /* char 4 */ {0x0375, 0x06EA, 0x0DD4, 0x1BA8, 0x3750, 0x6EA0, 0xDD40},
          /* char 5 */ {0xD849, 0xA0B3, 0x5147, 0xA28E, 0x553D, 0xAA7A, 0x44D5},
          /* char 6 */ {0x6F45, 0xDE8A, 0xAD35, 0x4A4B, 0x9496, 0x390D, 0x721A},
          /* char 7 */ {0xEB23, 0xC667, 0x9CEF, 0x29FF, 0x53FE, 0xA7FC, 0x5FD9},
          /* char 8 */ {0x47D3, 0x8FA6, 0x0F6D, 0x1EDA, 0x3DB4, 0x7B68, 0xF6D0},
          /* char 9 */ {0xB861, 0x60E3, 0xC1C6, 0x93AD, 0x377B, 0x6EF6, 0xDDEC},
          /* char 10 */ {0x45A0, 0x8B40, 0x06A1, 0x0D42, 0x1A84, 0x3508, 0x6A10},
          /* char 11 */ {0xAA51, 0x4483, 0x8906, 0x022D, 0x045A, 0x08B4, 0x1168},
          /* char 12 */ {0x76B4, 0xED68, 0xCAF1, 0x85C3, 0x1BA7, 0x374E, 0x6E9C},
          /* char 13 */ {0x3730, 0x6E60, 0xDCC0, 0xA9A1, 0x4363, 0x86C6, 0x1DAD},
          /* char 14 */ {0x3331, 0x6662, 0xCCC4, 0x89A9, 0x0373, 0x06E6, 0x0DCC},
          /* char 15 */ {0x1021, 0x2042, 0x4084, 0x8108, 0x1231, 0x2462, 0x48C4}
        private byte[] concatByteArrays(byte[] array1, byte[] array2)
          byte[] result = new byte[array1.Length + array2.Length];
          Buffer.BlockCopy(array2, 0, result, 0, array2.Length);
          Buffer.BlockCopy(array1, 0, result, array2.Length, array1.Length);
          return result;
        // Main implementation
        public void ApplyDocumentProtection(PresentationDocument wdDocument, string strPassword)
          // Generate the Salt
          byte[] arrSalt = new byte[16];     
          System.Security.Cryptography.RandomNumberGenerator rand = new System.Security.Cryptography.RNGCryptoServiceProvider();
          //Array to hold Key Values
          byte[] generatedKey = new byte[4];
          //Maximum length of the password is 15 chars.
          int intMaxPasswordLength = 15; 
          if (!String.IsNullOrEmpty(strPassword))
            // Truncate the password to 15 characters
            strPassword = strPassword.Substring(0, Math.Min(strPassword.Length, intMaxPasswordLength));
            // Construct a new NULL-terminated string consisting of single-byte characters:
            // -- > Get the single-byte values by iterating through the Unicode characters of the truncated Password.
            //  --> For each character, if the low byte is not equal to 0, take it. Otherwise, take the high byte.
            byte[] arrByteChars = new byte[strPassword.Length];
            for (int intLoop = 0; intLoop < strPassword.Length; intLoop++)
              int intTemp = Convert.ToInt32(strPassword[intLoop]);
              arrByteChars[intLoop] = Convert.ToByte(intTemp & 0x00FF);
              if (arrByteChars[intLoop] == 0)
                arrByteChars[intLoop] = Convert.ToByte((intTemp & 0xFF00) >> 8);
            // Compute the high-order word of the new key:
            // --> Initialize from the initial code array (see below), depending on the strPassword’s length. 
            int intHighOrderWord = InitialCodeArray[arrByteChars.Length - 1];
            // --> For each character in the strPassword:
            //   --> For every bit in the character, starting with the least significant and progressing to (but excluding) 
            //     the most significant, if the bit is set, XOR the key’s high-order word with the corresponding word from 
            //     the Encryption Matrix
            for (int intLoop = 0; intLoop < arrByteChars.Length; intLoop++)
              int tmp = intMaxPasswordLength - arrByteChars.Length + intLoop;
              for (int intBit = 0; intBit < 7; intBit++)
                if ((arrByteChars[intLoop] & (0x0001 << intBit)) != 0)
                  intHighOrderWord ^= EncryptionMatrix[tmp, intBit];
            // Compute the low-order word of the new key:
            // Initialize with 0
            int intLowOrderWord = 0;
            // For each character in the strPassword, going backwards
            for (int intLoopChar = arrByteChars.Length - 1; intLoopChar >= 0; intLoopChar--)
              // low-order word = (((low-order word SHR 14) AND 0x0001) OR (low-order word SHL 1) AND 0x7FFF)) XOR character
              intLowOrderWord = (((intLowOrderWord >> 14) & 0x0001) | ((intLowOrderWord << 1) & 0x7FFF)) ^ arrByteChars[intLoopChar];
            // Lastly,low-order word = (((low-order word SHR 14) AND 0x0001) OR (low-order word SHL 1) AND 0x7FFF)) XOR strPassword length XOR 0xCE4B.
            intLowOrderWord = (((intLowOrderWord >> 14) & 0x0001) | ((intLowOrderWord << 1) & 0x7FFF)) ^ arrByteChars.Length ^ 0xCE4B;
            // Combine the Low and High Order Word
            int intCombinedkey = (intHighOrderWord << 16) + intLowOrderWord;
            // The byte order of the result shall be reversed [Example: 0x64CEED7E becomes 7EEDCE64. end example],
            // and that value shall be hashed as defined by the attribute values.
            for (int intTemp = 0; intTemp < 4; intTemp++)
              generatedKey[intTemp] = Convert.ToByte(((uint)(intCombinedkey & (0x000000FF << (intTemp * 8)))) >> (intTemp * 8));
          // Implementation Notes List:
          // --> In this third stage, the reversed byte order legacy hash from the second stage shall be converted to Unicode hex 
          // --> string representation 
          StringBuilder sb = new StringBuilder();
          for (int intTemp = 0; intTemp < 4; intTemp++)
            //sb.Append(Convert.ToString(generatedKey[intTemp], 16));
          generatedKey = Encoding.Unicode.GetBytes(sb.ToString().ToUpper());
          // Implementation Notes List:
          //Word appends the binary form of the salt attribute and not the base64 string representation when hashing
          // Before calculating the initial hash, you are supposed to prepend (not append) the salt to the key
          byte[] tmpArray1 = generatedKey;
          byte[] tmpArray2 = arrSalt;
          byte[] tempKey = new byte[tmpArray1.Length + tmpArray2.Length];
          Buffer.BlockCopy(tmpArray2, 0, tempKey, 0, tmpArray2.Length);
          Buffer.BlockCopy(tmpArray1, 0, tempKey, tmpArray2.Length, tmpArray1.Length);
          generatedKey = tempKey;
          // Iterations specifies the number of times the hashing function shall be iteratively run (using each
          // iteration's result as the input for the next iteration).
          int iterations = 50000;
          // Implementation Notes List:
          //Word requires that the initial hash of the password with the salt not be considered in the count.
          //  The initial hash of salt + key is not included in the iteration count.
          System.Security.Cryptography.HashAlgorithm sha1 = new System.Security.Cryptography.SHA1Managed();
          generatedKey = sha1.ComputeHash(generatedKey);
          byte[] iterator = new byte[4];
          for (int intTmp = 0; intTmp < iterations; intTmp++)
            //When iterating on the hash, you are supposed to append the current iteration number.
            iterator[0] = Convert.ToByte((intTmp & 0x000000FF) >> 0);
            iterator[1] = Convert.ToByte((intTmp & 0x0000FF00) >> 8);
            iterator[2] = Convert.ToByte((intTmp & 0x00FF0000) >> 16);
            iterator[3] = Convert.ToByte((intTmp & 0xFF000000) >> 24);
            generatedKey = concatByteArrays(iterator, generatedKey);
            generatedKey = sha1.ComputeHash(generatedKey);
          // Apply the element
          ModificationVerifier documentProtection = new ModificationVerifier();
          documentProtection.CryptographicAlgorithmClass = CryptAlgorithmClassValues.Hash;
          documentProtection.CryptographicProviderType = CryptProviderValues.RsaFull;
          documentProtection.CryptographicAlgorithmType = CryptAlgorithmValues.TypeAny;
          documentProtection.CryptographicAlgorithmSid = 4; // SHA1
          //  The iteration count is unsigned
          UInt32Value uintVal = new UInt32Value();
          uintVal.Value = (uint)iterations;
          documentProtection.SpinCount = uintVal;
          documentProtection.HashData = Convert.ToBase64String(generatedKey);
          documentProtection.SaltData = Convert.ToBase64String(arrSalt);

    I'm using it like this:

    using (PresentationDocument pptdoc = PresentationDocument.Open(newDocumentName, true))
       ApplyDocumentProtection(pptdoc, "Example");

    I haven't got a chance to really debug the function and see what it is doing... I've read the documentation and the comments through the code both, and from what I know, it looks correct. There are a few inconsistancies with the documentation that make me worried though... it's like they copied and pasted the documentation form the Word portion of the documentation to the Power Point portion except they do not talk about the encryption as much. Additionally, in the documentation is says to use (see § in part4):

      <p:documentProtection ...
        p:cryptAlgorithmClass="hash" p:cryptAlgorithmType="typeAny"
        p:cryptAlgorithmSid="1" p:hashData="9oN7nWkCAyEZib1RomSJTjmPpCY=" ... />

    However, the SDK makes the tag

        cryptProviderType="rsaFull" cryptAlgorithmClass="hash" 
        cryptAlgorithmType="typeAny" cryptAlgorithmSid="4" 
        spinCount="50000" saltData="pPLPUjTn3d29jlsESspE8A==" 
    At this point, I don't know that I should trust the documentation for this particular function because of that. I plan on playing with it some more. In the mean time, if you or anyone else has answers, I appreciate any support. 
    Thursday, August 5, 2010 4:58 PM