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

Question

Сортировка таблиц Excel (ListObjects) на защищенных листах запрещена. Вы увидите следующее сообщение об ошибке:

Я потратил недели на поиски решения безуспешно. Все там устарело с примерами кода Excel 2007. Там нет учебников или руководств о том, как обойти это ограничение.

Вот как мне удалось окончательно победить..

Answer 1

При сортировке из выпадающего меню фильтра 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 снимает защиту с листа при нажатии на кнопки вверх или вниз. Но если пользователь выберет "Пользовательская сортировка" (вкладка "Главная") или - "Сортировка" (вкладка "Данные"), появится диалоговое окно, в котором он обязательно снимет защиту листа и защитит его обратно, если нажата кнопка "ОК", но если пользователь отменяет, ThisWorkbook_SheetCalculate никогда не сработает, оставляя лист незащищенным. Итак, мы добавляем SortDialogNoAlerts который снимает защиту листа, но также запускает таймер, который использует p/Invoke FindWindow искать диалоговое окно сортировки. Когда окно больше не найдено, оно защищает его, если оно еще не защищено.

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).