Часто перед разработчиком 1С встает задача не просто выгрузить данные в файл, а заполнить уже готовый бизнес-шаблон в формате Excel. Обычные средства платформы, такие как метод Записать() объекта ТабличныйДокумент, позволяют быстро создать файл, но при этом часто теряются сложные формулы Excel, настройки защиты листов и специфическое форматирование. В таких ситуациях на помощь приходит использование технологии COM. Разберем подробно, как реализовать этот процесс правильно и эффективно.
Прежде чем приступать к выгрузке, нам необходимо получить данные. Рассмотрим стандартный пример формирования таблицы значений на основе запроса к документам реализации. Проанализируем ситуацию: нам нужно собрать данные о номенклатуре, направлениях деятельности и заказах клиентов в одну таблицу.
&НаСервере
Процедура ЗаполнитьНаСервере()
Запрос1 = Новый Запрос("ВЫБРАТЬ
| РеализацияТоваровУслуг.Ссылка КАК Ссылка,
| РеализацияТоваровУслуг.НаправлениеДеятельности КАК НаправлениеДеятельности,
| РеализацияТоваровУслуг.НаправлениеДеятельности.Комментарий КАК НаправлениеДеятельностиКомментарий,
| РеализацияТоваровУслуг.ЗаказКлиента.ксВидРабот КАК ЗаказКлиентаксВидРабот,
| ЗаказКлиентаТовары.Номенклатура КАК Номенклатура
|ИЗ
| Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
| ЛЕВОЕ СОЕДИНЕНИЕ Документ.ЗаказКлиента.Товары КАК ЗаказКлиентаТовары
| ПО РеализацияТоваровУслуг.ЗаказКлиента = ЗаказКлиентаТовары.Ссылка
|ГДЕ
| РеализацияТоваровУслуг.Проведен = &Проведен
| И РеализацияТоваровУслуг.Дата МЕЖДУ &НачалоПериода И &КонецПериода");
Запрос1.УстановитьПараметр("НачалоПериода", Объект.НачалоПериода);
Запрос1.УстановитьПараметр("КонецПериода", Объект.КонецПериода);
Запрос1.УстановитьПараметр("Проведен", Истина);
РезультатЗапроса = Запрос1.Выполнить();
// Выгружаем результат в таблицу значений на форме (ТЗ)
ТаблицаРезультат = РезультатЗапроса.Выгрузить();
ТЗ.Очистить();
Для каждого Стр Из ТаблицаРезультат Цикл
НоваяСтрока = ТЗ.Добавить();
НоваяСтрока.НаименованиеОбъекта = Стр.НаправлениеДеятельности;
НоваяСтрока.ПолноеНаименованиеОбъекта = Стр.НаправлениеДеятельностиКомментарий;
НоваяСтрока.ЗаказКлиента = Стр.Ссылка;
НоваяСтрока.ВидРабот = Стр.ЗаказКлиентаксВидРабот;
НоваяСтрока.Содержание = Стр.Номенклатура;
КонецЦикла;
КонецПроцедуры
Переходим к самому процессу выгрузки. Выясним причину, почему мы используем именно COMОбъект. Это позволяет нам напрямую управлять приложением Microsoft Excel, обращаясь к его внутренним методам. Начнем с создания объекта и открытия файла шаблона.
&НаКлиенте
Процедура ВыгрузитьВExcel(Команда)
Попытка
Excel = Новый COMОбъект("Excel.Application");
Исключение
Сообщить("Excel не установлен на данном компьютере!");
Возврат;
КонецПопытки;
// Делаем Excel невидимым на время заполнения для ускорения работы
Excel.Visible = Ложь;
Excel.DisplayAlerts = Ложь; // Отключаем лишние уведомления
ПутьКШаблону = "C:\Users\Admin\Desktop\Шаблон.xlsx";
Попытка
Книга = Excel.Workbooks.Open(ПутьКШаблону);
Лист = Книга.Worksheets(1); // Обращаемся к первому листу
Исключение
Сообщить("Не удалось открыть файл шаблона. Проверьте путь.");
Excel.Quit();
Возврат;
КонецПопытки;
// Здесь будет код заполнения...
КонецПроцедуры
Рассмотрим важный нюанс производительности. Если мы будем записывать данные в каждую ячейку по отдельности (используя Лист.Cells(r, c).Value = ...), то при большом объеме данных выгрузка займет очень много времени из-за постоянных обращений между 1С и COM-сервером. Чтобы ускорить процесс, проанализируем метод передачи данных через двумерный массив.
Мы создадим массив в 1С, заполним его всеми строками таблицы значений и одним махом передадим в диапазон Excel Range. Это сократит количество обращений к Excel до минимума.
// Определяем количество строк и колонок
КолСтрок = ТЗ.Количество();
КолКолонок = 5; // Укажите ваше количество колонок
// Создаем COMSafeArray (двумерный массив для передачи данных)
МассивДанных = Новый COMSafeArray("VT_VARIANT", КолСтрок, КолКолонок);
Для НомСтр = 0 По КолСтрок - 1 Цикл
ТекущаяСтрокаТЗ = ТЗ[НомСтр];
МассивДанных.SetValue(НомСтр, 0, ТекущаяСтрокаТЗ.НаименованиеОбъекта);
МассивДанных.SetValue(НомСтр, 1, ТекущаяСтрокаТЗ.ПолноеНаименованиеОбъекта);
МассивДанных.SetValue(НомСтр, 2, Строка(ТекущаяСтрокаТЗ.ЗаказКлиента));
МассивДанных.SetValue(НомСтр, 3, ТекущаяСтрокаТЗ.ВидРабот);
МассивДанных.SetValue(НомСтр, 4, ТекущаяСтрокаТЗ.Содержание);
КонецЦикла;
// Определяем область на листе, начиная, например, со второй строки
НачальнаяСтрока = 2;
КонечнаяСтрока = НачальнаяСтрока + КолСтрок - 1;
Область = Лист.Range(Лист.Cells(НачальнаяСтрока, 1), Лист.Cells(КонечнаяСтрока, КолКолонок));
// Выгружаем весь массив данных сразу
Область.Value = МассивДанных;
Посмотрим на ситуацию, когда в шаблоне Excel внизу таблицы уже есть строка «Итого» с прописанными формулами (например, =СУММ(E2:E10)). Если данных в 1С больше, чем строк в шаблоне, нам нужно «раздвинуть» таблицу. Выясним, как это сделать программно, чтобы Excel автоматически скорректировал диапазоны формул.
Вместо простого заполнения ячеек, мы можем вставлять новые строки методом Insert(). Рассмотрим по шагам:
// Если данных много, вставим строки перед строкой итогов
Если КолСтрок > 1 Тогда
Для и = 1 По КолСтрок - 1 Цикл
Лист.Rows(НачальнаяСтрока + 1).Insert(); // Вставляем пустую строку с сохранением форматирования
КонецЦикла;
КонецЕсли;
Если нам нужно программно прописать формулу, используйте свойство Formula (для английских имен функций, например SUM) или FormulaLocal (для локализованных, например СУММ). Проанализируем пример установки формулы суммы в ячейку под колонкой с данными:
СтрокаИтога = НачальнаяСтрока + КолСтрок;
Лист.Cells(СтрокаИтога, 5).Formula = "=SUM(E" + НачальнаяСтрока + ":E" + (СтрокаИтога - 1) + ")";
Выясним, как работать с защищенными листами. Если ваш шаблон заблокирован от изменений, перед записью данных защиту нужно снять, а после — вернуть обратно. Это делается методами Unprotect и Protect.
Важный момент: всегда корректно завершайте работу с процессом excel.exe. Если этого не сделать, на сервере или компьютере пользователя будут накапливаться «зависшие» процессы Excel, что приведет к сбоям системы.
// Снимаем защиту, если она есть (пароль указывать не обязательно, если его нет)
Лист.Unprotect();
// Выполняем заполнение данных...
// Возвращаем защиту
Лист.Protect();
// Сохраняем результат
Книга.SaveAs("C:\Users\Admin\Desktop\РезультатВыгрузки.xlsx");
// Обязательно закрываем приложение и очищаем ссылки
Excel.Quit();
Excel = Неопределено;
Рассмотрим несколько полезных советов, которые помогут избежать типичных ошибок при автоматизации Excel:
Лист.UsedRange.Rows.Count. Это позволит определить границы существующей таблицы.Строка(ТЗ.ЗаказКлиента) или ТЗ.Сумма.dcomcnfg).Таким образом, мы разобрали комплексный подход к выгрузке данных в Excel. Использование технологии COM в сочетании с передачей данных через массивы и управлением структурой листа (Insert) позволяет создавать профессиональные отчеты, которые полностью соответствуют корпоративным стандартам оформления.