При автоматизации работы с файлами Microsoft Excel из среды 1С:Предприятие разработчики часто сталкиваются с неприятным эффектом. После программного изменения и сохранения существующего файла формата .xlsx пользователь открывает его стандартным путем в Excel и видит лишь пустой серый экран. При этом сетка, листы и данные отсутствуют, хотя сам файл весит несколько мегабайт, а панели инструментов программы остаются неактивными.
В этой статье мы подробно разберем причины возникновения этой проблемы, найдем решение для исправления кода 1С, а также рассмотрим современную и более надежную альтернативу использованию COM-технологий.
Для записи данных в существующий файл разработчики часто используют метод ПолучитьCOMОбъект(ИмяФайла). Этот способ является аналогом системного вызова GetObject в среде Windows. Его преимущество заключается в том, что он позволяет быстро получить доступ к структуре книги, минуя явный запуск нового процесса через Новый COMОбъект("Excel.Application").
Однако здесь кроется важный нюанс работы операционной системы и пакета MS Office:
ПолучитьCOMОбъект, операционная система запускает Excel в фоновом (скрытом) режиме. При этом свойство видимости окна созданной книги по умолчанию устанавливается в значение Ложь (Visible = False).Save() или SaveAs() приложение Excel фиксирует текущие параметры интерфейса — в том числе и флаг скрытого окна — непосредственно внутрь структуры самого файла .xlsx.Данные в файле при этом не повреждены и полностью сохранены. Чтобы вернуть отображение вручную в самом Excel, пользователю достаточно перейти на вкладку «Вид» и нажать кнопку «Отобразить» (Unhide), после чего пересохранить документ. Но для автоматической работы такое поведение системы недопустимо, поэтому исправим логику в коде 1С.
Давайте проанализируем, как решить эту задачу программно. Чтобы Excel при сохранении записывал корректный статус отображения, нам необходимо принудительно сделать окно книги видимым перед записью и закрытием файла.
Для этого используется обращение к коллекции окон книги: Книга.Windows(1).Visible = Истина. Посмотрим на практический пример реализации этого метода:
&НаКлиенте
Процедура ЗаписатьВExcelРекомендуемыйПуть(ИмяФайла)
Попытка
// Получаем доступ к файлу через COM
Книга = ПолучитьCOMОбъект(ИмяФайла);
Исключение
Предупреждение("Не удалось открыть файл. Возможно, Excel не установлен: " + ОписаниеОшибки());
Возврат;
КонецПопытки;
// Получаем первый рабочий лист
Лист = Книга.Worksheets(1);
// Выполняем запись данных в ячейку (строка 1, колонка 1)
Лист.Cells(1, 1).Value = 99;
// КРИТИЧЕСКИ ВАЖНО: Делаем окно книги видимым в метаданных файла перед сохранением
Попытка
Книга.Windows(1).Visible = Истина;
Исключение
// Игнорируем возможные системные ошибки обращения к окнам
КонецПопытки;
// Сохраняем изменения и закрываем книгу
// Метод Close с параметром Истина (True) сохраняет файл перед закрытием
Книга.Close(Истина);
// Обязательно освобождаем COM-объекты для исключения утечек памяти
Лист = Неопределено;
Книга = Неопределено;
ПоказатьПредупреждение(, "Файл успешно обновлен!");
КонецПроцедуры
Использование метода Книга.Close(Истина) избавляет от необходимости вызывать Save() отдельно, что делает код более лаконичным и предотвращает зависание фонового процесса Excel в диспетчере задач.
Зачастую разработчики уходят от стандартного создания объекта Новый COMОбъект("Excel.Application") к методу ПолучитьCOMОбъект из-за системной ошибки конфликта имен, связанной с _FilterDatabase. Выясним причину этой ошибки.
Имя _FilterDatabase — это скрытый системный диапазон ячеек, создаваемый Excel автоматически при активации автофильтров на листах. Если листы в книге копировались, переносились или структура файла была нарушена сторонними программами записи, Excel при программном открытии через Workbooks.Open() пытается разрешить конфликт имен и выбрасывает скрытое диалоговое окно с запросом нового имени диапазона.
Поскольку 1С работает в невидимом режиме, это диалоговое окно «вешает» выполнение кода намертво. При этом установка флага DisplayAlerts = Ложь зачастую не помогает, так как Excel считает этот конфликт критической ошибкой метаданных.
Если вы всё же хотите использовать классический метод работы через Excel.Application, решить проблему с автофильтрами можно программным удалением конфликтующих системных имен сразу после открытия файла:
// Пример работы через создание нового приложения Excel
ExcelПриложение = Новый COMОбъект("Excel.Application");
ExcelПриложение.DisplayAlerts = Ложь; // Отключаем предупреждения
Книга = ExcelПриложение.Workbooks.Open(ИмяФайла);
// Пробегаемся по коллекции имен и удаляем проблемные фильтры
Для Каждого ТекущееИмя Из Книга.Names Цикл
Если Найти(ТекущееИмя.Name, "_FilterDatabase") > 0 Тогда
Попытка
ТекущееИмя.Delete();
Исключение
// Имя может быть защищено, игнорируем ошибку удаления
КонецПопытки;
КонецЕсли;
КонецЦикла;
// ... дальнейшая работа с заполнением ячеек ...
Использование COM-объектов MS Excel в среде 1С считается устаревшим подходом. COM-соединение требует обязательной установки Microsoft Office непосредственно на рабочей станции (или сервере 1С), работает медленно, потребляет много оперативной памяти и подвержено сбоям при обновлении операционной системы.
Начиная с современных версий платформы 8.3, 1С:Предприятие умеет нативно (на уровне бинарных данных) читать и записывать файлы Excel формата .xlsx с помощью объекта ТабличныйДокумент. Этот способ полностью избавлен от проблем с серыми экранами, зависшими процессами EXCEL.EXE и конфликтами автофильтров.
Рассмотрим по шагам, как переписать процедуру заполнения ячеек файла без использования Excel вообще:
&НаСервере
Процедура ЗаписатьВExcelЧерезТабДок(ИмяФайла)
// Создаем новый табличный документ в памяти 1С
ТабДок = Новый ТабличныйДокумент;
// Читаем существующий файл Excel напрямую
Попытка
ТабДок.Прочитать(ИмяФайла, СпособЧтенияЗначенийТабличногоДокумента.Значение);
Исключение
ЗаписьЖурналаРегистрации("Чтение Excel", УровеньЖурналаРегистрации.Ошибка,,, ОписаниеОшибки());
ВызватьИсключение "Не удалось прочитать файл Excel. Возможно, он поврежден или заблокирован.";
КонецПопытки;
// Модифицируем ячейки табличного документа.
// Обратите внимание: адресация в 1С идет через метод Область(Строка, Колонка)
НомерСтроки = 1;
НомерКолонки = 1;
ТабДок.Область(НомерСтроки, НомерКолонки).Текст = "99";
// Перезаписываем исходный файл в формате XLSX
Попытка
ТабДок.Записать(ИмяФайла, ТипФайлаТабличногоДокумента.XLSX);
Исключение
ВызватьИсключение "Не удалось сохранить изменения. Убедитесь, что файл закрыт у всех пользователей.";
КонецПопытки;
КонецПроцедуры
Преимущества работы через Табличный Документ:
Таким образом, если вы вынуждены использовать COM-технологию, всегда помните о необходимости делать окно книги видимым перед сохранением (Книга.Windows(1).Visible = Истина). В новых же разработках мы настоятельно рекомендуем переходить на использование стандартных табличных документов 1С.