При автоматизации работы с Excel из 1С 7.7 разработчики часто сталкиваются с проблемой: стандартный метод AutoFilter отлично работает с одним или двумя значениями, но при попытке передать список (массив) из трех и более значений возникает ошибка. Это происходит из-за того, что «семерка» не умеет нативно передавать массивы типа SAFEARRAY, которые ожидает Excel для оператора xlFilterValues.
В этой статье мы подробно разберем, почему возникает эта сложность, и проанализируем несколько проверенных способов её решения.
В объектной модели Excel макрос для фильтрации по списку выглядит так:
ActiveSheet.Range("$A$1:$G$100").AutoFilter Field:=1, Criteria1:=Array("123", "456", "789"), Operator:=7
Здесь Operator:=7 соответствует константе xlFilterValues. Проблема заключается в том, что в 1С 7.7 объект СписокЗначений не преобразуется автоматически в Array для COM-соединения. Попытка передать туда любые встроенные типы данных 1С заканчивается неудачей.
Рассмотрим наиболее элегантный программный способ. Мы можем использовать вспомогательный объект MSScriptControl.ScriptControl, который понимает типы данных массивов, совместимые с Excel. Выясним, как это реализовать:
AutoFilter.Пример реализации:
// Создаем объект для выполнения VBScript
Скрипт = СоздатьОбъект("MSScriptControl.ScriptControl");
Скрипт.Language = "VBScript";
// Формируем массив строк для фильтра
Скрипт.AddCode("
|Function GetArray()
| GetArray = Array(""45313"", ""67950"", ""84861"")
|End Function");
МассивДляExcel = Скрипт.Run("GetArray");
// Применяем фильтр (7 - это xlFilterValues)
ExcelApp.Selection.AutoFilter(1, МассивДляExcel, 7);
Проанализируем альтернативный подход, который не требует работы с массивами в памяти. Метод AdvancedFilter использует диапазон ячеек на листе в качестве критериев.
Разберем по шагам:
Этот способ надежен, так как 1С 7.7 легко записывает данные в ячейки Excel построчно.
Посмотрим на ситуацию с другой стороны. Если нам нужно отфильтровать данные по очень длинному списку, мы можем программно добавить в таблицу Excel временную колонку с формулой типа ВПР (VLOOKUP) или СЧЁТЕСЛИ (COUNTIF). Эта формула будет проверять, входит ли значение текущей строки в наш список разрешенных значений.
Затем мы просто применяем обычный AutoFilter к этой технической колонке, фильтруя только по одному значению (например, «1» или «Истина»). Это позволяет обойти любые ограничения на количество элементов в массиве.
Если в вашей базе используется расширение 1CPP, мы можем воспользоваться специальным объектом COMSafeArray. Он предназначен именно для создания массивов, которые корректно воспринимаются COM-объектами сторонних приложений.
// Пример использования COMSafeArray из 1CPP
врап = СоздатьОбъект("ПоставщикДанных"); // или другой способ доступа к COMSafeArray
Массив = СоздатьОбъект("COMSafeArray");
Массив.Создать("VT_VARIANT", 3); // Массив из 3-х элементов
Массив.Установить(0, "45313");
Массив.Установить(1, "67950");
Массив.Установить(2, "84861");
Диапазон.AutoFilter(1, Массив, 7);
Внимание: при передаче значений для фильтрации (будь то через VBScript или вспомогательные ячейки) крайне важно соблюдать тип данных. Если в колонке Excel хранятся числа, то и в массив критериев нужно передавать числа. Если в колонке текст (даже если он выглядит как число), передавайте строки. Несоответствие типов — самая частая причина того, что фильтр применяется, но скрывает все строки.