none
How do you password protect a programtically generated OPEN XML Excel workbook ? RRS feed

  • Question

  • Firstly apologies if this is in the wrong thread/place - this is my first time asking a question via microsoft help forums.

    I've spent the last few weeks implementing dynamically generated excel 2007 worksheets from an ASP.net web application using Open XML.  I've found it very tough going but have muddled through so far.  Over the last 3 or 4 days I've been unsuccessfully attempting to add password protection to the workbooks that the application generates.

    I've read al of the MSDN documentation on the WorkbookProtection class here, I've also read this post and tried the suggestions in this blog, but so far the best that I can get to happen is for the Workbook to be created with no password protection.  The worst I can get to happen is for the Workbook to not be created correctly.

    I cannot find anywhere online that shows a good example of how to correctly implement the WorkbookProtection class so I'm at a complete loss as to how to progress any further.

    Here is a snippet of the code that I've been testing with:

                            //  Workbook Protection
                            workbookpart.Workbook.WorkbookProtection = new WorkbookProtection();
                            workbookpart.Workbook.WorkbookProtection.LockStructure = true;
                            workbookpart.Workbook.WorkbookProtection.LockWindows = true;
                            workbookpart.Workbook.WorkbookProtection.WorkbookAlgorithmName = "SHA-1";
    
                            string textToHash = "Test";
                            byte[] byteRepresentation = UnicodeEncoding.UTF8.GetBytes(textToHash);
                            byte[] hashedTextInBytes = null;
                            System.Security.Cryptography.SHA1 sha = new System.Security.Cryptography.SHA1CryptoServiceProvider();
                            hashedTextInBytes = sha.ComputeHash(byteRepresentation);
                            string hashedText = Convert.ToBase64String(hashedTextInBytes);
    
                            //workbookpart.Workbook.WorkbookProtection.WorkbookHashValue = hashedText;
                            workbookpart.Workbook.WorkbookProtection.WorkbookPassword = hashedText;

    the xml output ends up looking something like this:

      <?xml version="1.0" encoding="utf-8" ?> 
    - <x:workbook xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
      <x:workbookPr defaultThemeVersion="124226" /> 
      <x:workbookProtection lockStructure="1" lockWindows="1" workbookAlgorithmName="SHA-1" workbookPassword="ZAqyuuB77cTBY/Z5p0b3q3+10fo=" /> 
    - <x:bookViews>
      <x:workbookView xWindow="390" yWindow="510" /> 
      </x:bookViews>
    - <x:sheets>
      <x:sheet name="1511" sheetId="1" r:id="Rce6a14d0320d45fb" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" /> 
      </x:sheets>
      </x:workbook>

    I am completely new to Open XML so the above may be wrong in other places - but the key thing for me at the moment is how to get the workbook password protection implemented.

    Any assistance and preferably a good example to work through would be greatly appreciated.

    Thanks in advanced....

    Kieron

    Monday, June 11, 2012 8:59 AM

All replies

  • Hi Kieron,

    As far as I know that Openxml can't generate encrypted document.

    Best Regards,

    T.X.

    Tuesday, June 12, 2012 12:02 AM
  • Hi,


    Thanks for the reply.  In the blog I mentioned, it had the following XML snippet:


    <?xml version="1.0" encoding="utf-8" standalone="yes" ?>
    
    <x:workbook xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    
    <x:fileVersion appName="xl" lastEdited="4" lowestEdited="4" rupBuild="4505" />
    
    <x:workbookPr defaultThemeVersion="124226" />
    
    <x:workbookProtection workbookPassword="xsd:hexBinary data" lockStructure="1" lockWindows="1" />
    
    <x:bookViews>
    
    <x:workbookView xWindow="600" yWindow="525" windowWidth="17895" windowHeight="4560" activeTab="1" />
    
    </x:bookViews>
    
    <x:sheets>
    
    <x:sheet name="My Data" sheetId="1" r:id="rId1" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" />
    
    <x:sheet name="Chart" sheetId="3" r:id="rId2" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" />
    
    </x:sheets>
    
    <x:calcPr calcId="125725" />
    
    </x:workbook>

    out of the above, I'm only interested in how to programatically set the following:

    <x:workbookProtection workbookPassword="xsd:hexBinary data" lockStructure="1" lockWindows="1" />

    Any suggestions ?

    thanks in advanced...

    Kieron




    • Edited by blumunki Thursday, June 14, 2012 7:54 AM typo
    Thursday, June 14, 2012 7:02 AM
  • Hi Kieron,

    Thanks for posting in the MSDN Forum.

    It seems that you add the workbookProtection element manually. Is it right? If it isn't would you please provide me some snippet for further research?

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Monday, June 18, 2012 2:34 AM
    Moderator
  • Try this:

    doc.WorkbookPart.Workbook.WorkbookProtection = new WorkbookProtection
    {
       LockStructure    = true,
       LockWindows      = true,
       WorkbookPassword = hashPassword("sample")
    };

    and this (isn't my code, but I can't remember where I found it):

        private static string hashPassword(string password)
        {
            int hash = 0;
            byte[] passwordCharacters = Encoding.UTF8.GetBytes(password);
            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).ToUpper();
        }


    Wednesday, November 7, 2012 3:25 PM