Сортировка таблицы Excel (ListObject) на защищенном листе с помощью Excel Interop

При сортировке из раскрывающегося меню фильтра Excel таблицы нет перехватываемого события. Однако вы можете перехватывать события, когда диалоговые команды по возрастанию, убыванию или сортировке вызываются на вкладках ленты «Главная» и «Данные».

Использование Excel 2016 Interop (настройка на уровне документа), Visual Studio 2015 и C#:

1. Щелкните правой кнопкой мыши свой проект -> Добавить -> Новый элемент -> Лента (XML)

2. На вашем Ribbon.xml:

<?xml version="1.0" encoding="UTF-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="Ribbon_Load">
  <commands>  
    <command idMso="SortAscendingExcel" onAction="SortNoAlerts" />
    <command idMso="SortDescendingExcel" onAction="SortNoAlerts" />
    <command idMso="SortCustomExcel" onAction="SortDialogNoAlerts" /><!--TabHome-->
    <command idMso="SortDialog" onAction="SortDialogNoAlerts" /><!--TabData-->
  </commands>
</customUI>

Затем добавьте функции обратного вызова событий. SortNoAlerts снимает защиту с листа при нажатии кнопок по возрастанию/убыванию. Но если пользователь выберет «Пользовательская сортировка» (вкладка «Главная») — или — «Сортировка» (вкладка «Данные»), появится диалоговое окно, которое обязательно снимет защиту листа и защитите его сразу же, если нажмете OK, но если пользователь отменит, ThisWorkbook_SheetCalculate никогда не сработает, оставив лист незащищенным. Поэтому мы добавляем SortDialogNoAlerts, который снимает защиту с листа, но также запускает таймер, который использует p/Invoke FindWindow для поиска диалогового окна Sort. Когда окно больше не найдено, оно защищает его, если оно еще не защищено.

3. В ваших обратных вызовах Ribbon.cs:

    public void SortNoAlerts(Office.IRibbonControl control, ref bool cancelDefault)
    {
        Excel.Worksheet ws = null;
        try
        {
            ws = (Excel.Worksheet)Globals.ThisWorkbook.ActiveSheet;
            ws.Unprotect("your password");
            cancelDefault = false;
        }
        catch (Exception) { }
        finally
        {
            if (ws != null) Marshal.ReleaseComObject(ws); ws = null;
        }
    }

    public void SortDialogNoAlerts(Office.IRibbonControl control, ref bool cancelDefault)
    {
        Excel.Worksheet ws = null;
        try
        {
            ws = (Excel.Worksheet)Globals.ThisWorkbook.ActiveSheet;
            ws.Unprotect("your password");
            Globals.ThisWorkbook._myActionPane.tmrWaitSortWinClose.Enabled = true;
            cancelDefault = false;
        }
        catch (Exception) {
            Globals.ThisWorkbook._myActionPane.tmrWaitSortWinClose.Enabled = false;
        }
        finally
        {
            if (ws != null) Marshal.ReleaseComObject(ws); ws = null;
        }
    }

4. В ThisWorkbook.cs -> InternalStartup() добавьте следующее:

this.SheetCalculate += new Excel.WorkbookEvents_SheetCalculateEventHandler(ThisWorkbook_SheetCalculate);

5. В ThisWorkbook.cs -> добавьте следующее:

public bool sortDialogVisible;

private void ThisWorkbook_SheetCalculate(object sh)
{
  Excel.Worksheet ws = (Excel.Worksheet)sh;
  ws.EnableOutlining = true;
  ws.Protect("your password", true, Type.Missing, Type.Missing, true, true, true, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, true, true, true, Type.Missing);
  Marshal.ReleaseComObject(ws); ws = null;
}

6. Добавьте таймер с именем tmrWaitSortWinClose и установите Interval = 750:

private void tmrWaitSortWinClose_Tick(object sender, EventArgs e)
{
    Globals.ThisWorkbook.sortDialogVisible = Native.FindWindow("NUIDialog", "Sort") == IntPtr.Zero;

    if (Globals.ThisWorkbook.sortDialogVisible)
    {
        Excel.Worksheet ws = null;
        try
        {
            ws = (Excel.Worksheet)Globals.ThisWorkbook.ActiveSheet;

            if (!ws.ProtectContents)
            {
               ws.Protect("your password", true, Type.Missing, Type.Missing, true, true, true, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, true, true, true, Type.Missing);
            }
            tmrWaitSortWinClose.Enabled = false;
        }
        catch (Exception) { tmrWaitSortWinClose.Enabled = false; }
        finally
        {
            if (ws != null) Marshal.ReleaseComObject(ws); ws = null;
        }
    }
}

7. Добавьте класс с именем Native.cs:

public class Native
{
    [DllImport("user32.dll", SetLastError = true)]
    public static extern IntPtr FindWindow(string lpClassName, string lpWindowName);
}

Это позволит сортировать таблицы на защищенных листах. Не путайте, опция AllowSort для worksheet.Protect() предназначена только для ячеек листа, которые не являются частью таблицы (ListObject).