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?
Protecting an Excel File You have two options with this type of protection – locking it down with a password and making the file Read-Only for certain specified groups. To protect your workbook at the file level, go to File > Info. Choose Protect Workbook and select Encrypt with Password.
To set it up, open your Excel file and head to the File menu. You'll see the “Info” category by default. Click the “Protect Workbook” button and then choose “Encrypt with Password” from the dropdown menu. Type your password and click “OK.”
As Microsoft Office now uses AES-256, you can rest assured that the password protection on your Excel spreadsheet is trustworthy and impossible to break using current technology. As the integrated password protection only handles one file at a time, you might also consider the 7-Zip encryption option.
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();
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With