C# excel hide and lock rows

Question

Recently I have been converting a project from VBA to C# but I have run across an issue with .Hidden(), .Locked(), and .Protect()

In the VBA implementation if I hide(rows)->lock->protect then I cannot unhide the rows (as intended), but in the C# implementation if I hide(rows)->lock->protect the rows can be unhidden (highlight rows, right click, unhide)

Is there something I am missing, or is there a different way that the C# version needs to be written to produce the same result (rows cannot be unhidden) as the VBA version?

I have simplified the code to these short snippets that reproduce the results. Both versions create a new workbook, modify a cell, hide-lock-protect rows, and save/close the workbook.

C# version:

using Excel = Microsoft.Office.Interop.Excel;
...

private void button1_Click(object sender, EventArgs e)
{

    Excel.Application ex = new Excel.Application();
    Excel.Workbooks Books = ex.Workbooks;


    //create and save the output workbook (so only .save() needs to be called later)
    Excel.Workbook OutputBook = Books.Add();
    OutputBook.SaveAs("C:\\TestingFolder\\Outputbook.xlsm", Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled);

    //write secret stuff
    OutputBook.Sheets[1].Cells[15,15] = "Stuff";

    //hide and lock rows around secret stuff
    OutputBook.Sheets[1].Range["10:20"].EntireRow.Hidden = true;
    OutputBook.Sheets[1].Range["10:20"].EntireRow.Locked = true;


    //protect the sheet with a bad password
    OutputBook.Sheets[1].Protect(
                                    "SomePassword123",//password
                                    false,  //drawing objects
                                    true,   //Contents
                                    false,  //scenarios
                                    false,  //user interface
                                    true,   //format cells
                                    true,   //format columns
                                    true,   //format rows
                                    false,  //insert columns
                                    false,  //insert rows
                                    true,   //insert hyperlinks
                                    false,  //delete columns
                                    false,  //delete rows
                                    true,   //allow sorting
                                    true,   //allow filtering
                                    true    //allow pivot tables
                                );


    //save and close output workbook
    OutputBook.Save();
    OutputBook.Close(false);


    //-----general cleanup start-----
    Books.Close();
    ex.Quit();

    System.Runtime.InteropServices.Marshal.ReleaseComObject(OutputBook);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(Books);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(ex);

    OutputBook = null;
    Books = null;
    ex = null;

    GC.Collect();
    //-----general cleanup end-----


    //show message that the task completed
    MessageBox.Show("done");

}

and the VBA version:

Private Sub CommandButton1_Click()

    'create and save the output workbook (so only .save() needs to be called later)
    Dim OutputBook As Workbook
    Set OutputBook = Workbooks.Add
    Call OutputBook.SaveAs("C:\TestingFolder\Outputbook.xlsm", ThisWorkbook.FileFormat)

    'write secret stuff
    OutputBook.Sheets(1).Cells(15, 15) = "Stuff"

    'hide and lock rows around secret stuff
    OutputBook.Sheets(1).Range("10:20").EntireRow.Hidden = True
    OutputBook.Sheets(1).Range("10:20").EntireRow.Locked = True

    'protect the sheet with a bad password
    OutputBook.Sheets(1).Protect Password:="SomePassword123", _
                                DrawingObjects:=False, _
                                Contents:=True, _
                                Scenarios:=False, _
                                AllowFormattingCells:=True, _
                                AllowInsertingHyperlinks:=True, _
                                AllowSorting:=True, _
                                AllowFiltering:=True, _
                                AllowUsingPivotTables:=True

    'save and close output workbook
    Call OutputBook.Save
    Call OutputBook.Close

    'show message that the task completed
    MsgBox "done"

End Sub

Answer 1

Here is the code to Protect and Hide an Excel sheet. Use the required Namespaces as below

using System;
using System.Data;
using Microsoft.CSharp;
using System.Collections;
using Excel=Microsoft.Office.Interop.Excel;

Initialize the Excel Application, Filepath is an string variable containing password

string FilePath = @"C:\Filename.xlsx";
string Password = "12345";
Excel.Application ExcelApp = new Excel.Application();   // Initialize Excel Application
ExcelApp.DisplayAlerts = false;            
Excel.Workbook WB = ExcelApp.Workbooks.Open(FilePath);  // Initialize Excel Workbook

Then hide the sheet by using the below code, toHide in this code is an Arraylist which contains the list of sheets that are required to Hide.

 foreach (Excel.Worksheet Worksheet in WB.Worksheets)
        {
        if (toHide.Contains(Worksheet.Name))
            {
                ((Excel.Worksheet)WB.Worksheets[Worksheet.Name]).Visible = Excel.XlSheetVisibility.xlSheetHidden;
            }
        }

To protect the sheet, Here is the code; toProtect here is an Arraylist that contains the sheetnames that are required to protect.

 ExcelApp.Visible = true;
        foreach (Excel.Worksheet Worksheet in WB.Worksheets)
        {
            if (toProtect.Contains(Worksheet.Name))
            {
                ((Excel.Worksheet)WB.Worksheets[Worksheet.Name]).Protect(Password);
            }
        }
        //WB.Save();
        ExcelApp.Visible = false;

Please let me know if this helps.

Answer 2

In your Protect method, the format rows argument must be set to false and not true.