none
How to setup the password property in XML <SheetProtection>? RRS feed

  • Question

  • Hi, I am using the open xml sdk2.0 to create a excel spreadsheet. I want to add the <SheetProtection> to avoid user change some cells.

    Becuase the password is different for each excel file creating from system. So I wil dynamic assign password when create SpreadSheet XML.

    How can I get the correct MICROSOFT EXCEL encrypt password.

    ex: In Excel when user set password: ABCD , in XML wll be <sheetProtection password="C99D">

    Is any way to follow the EXCEL algorithm to give the correct password?

    This my c# code:
                        SheetProtection addProtection = new SheetProtection()
                        {                
                            Password = "C99D",
                            //AlgorithmName = "SHA-1",
                            //HashValue = "85ce6b4df042c1025e694c47c30f498e34ac75f2",
                            Sheet = true,
                            Objects = true,
                            Scenarios = true,
                            AutoFilter = false
                        };

    Thanks.

    BR

           Tom

     

    ex:

     

    Wednesday, January 26, 2011 6:30 AM

Answers

  •     public String GetSheetPassword(String password)
        {
          Int32 pLength = password.Length;
          Int32 hash = 0;
          if (pLength == 0) return hash.ToString("X");
    
          for (Int32 i = pLength - 1; i >= 0; i--)
          {
            hash = hash >> 14 & 0x01 | hash << 1 & 0x7fff;
            hash ^= password[i];
          }
          hash = hash >> 14 & 0x01 | hash << 1 & 0x7fff;
          hash ^= 0x8000 | 'N' << 8 | 'K';
          hash ^= pLength;
          return hash.ToString("X");
        }
    

    Using the function:

          String pwd = "ABCD";
          Console.WriteLine("{0} = {1}", pwd, GetSheetPassword(pwd));
    
    Output:
    ABCD = C99D


    ClosedXML - Create Excel files in .Net
    • Marked as answer by Bessie Zhao Tuesday, February 1, 2011 8:21 AM
    Saturday, January 29, 2011 6:36 PM