Remove From My Forums

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

Question
0

Sign in to vote

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

Answered by:

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

Question

Answers