Is there a programmatically way for Excels "Protect Workbook"?

Question

I'm moving some legacy code using Office Interop libraries to epplus, one thing I can't figure out is how to set a Workbook to ask the user on open the file to open it read only. Like if the user clicks on File -> Info -> Protect Workbook -> Always Open Read-Only

I've tried to set the DocSecurity property stated here (https://sno.phy.queensu.ca/~phil/exiftool/TagNames/OOXML.html), but to no success. excelWorkBook.Properties.SetExtendedPropertyValue("DocSecurity", "2");

I also tried to add the following node to the workbookxml <fileSharing readOnlyRecommended="1"/>

I even tried to compare the unzipped excel files protected, non protected, but there were too many changes.

Answer 1

It can be done but it is not really straightforward. Setting DocSecurity can be done by generating the Workbook.Properties object. But that is only half of it. You also need to set the flag inside the Workbook itself which can only be done via XML manipulation.

[TestMethod]
public void DocSecurity_Test()
{
    //https://stackoverflow.com/questions/58335624/is-there-a-programmatically-way-for-excels-protect-workbook
    var fi = new FileInfo(@"c:\temp\DocSecurity_Test.xlsx");
    if (fi.Exists)
        fi.Delete();

    using (var package = new ExcelPackage(fi))
    {
        //Create a simple workbook
        var workbook = package.Workbook;
        var worksheet = workbook.Worksheets.Add("Sheet1");
        worksheet.Cells["A1"].Value = "Test";

        //Extended properties is a singleton so reference it to generate the app.xml file
        //needed and add the security setting
        var extProps = workbook.Properties;
        extProps.SetExtendedPropertyValue("DocSecurity", "2");

        //Also need to tell the workbook itself but can only do it via XML
        var xml  = workbook.WorkbookXml;
        var att = xml.CreateAttribute("readOnlyRecommended");
        att.Value = "1";

        const string mainNs = @"http://schemas.openxmlformats.org/spreadsheetml/2006/main";
        var fileSharing = xml.CreateElement("fileSharing", mainNs);
        fileSharing.Attributes.Append(att);

        //Element must be at the beginning of the tree
        xml.DocumentElement.PrependChild(fileSharing);
        package.Save();
    }
}

Which will look like this: