При работе с системой 1С часто возникает необходимость выгружать данные в формате Excel, чтобы в дальнейшем анализировать их или использовать вне системы. Стандартная кнопка «Выгрузить список» не всегда подходит, когда требуется сложное форматирование, формирование красивой шапки отчета, объединение ячеек или распределение данных по разным листам (закладкам) в одном файле Excel. Рассмотрим подробно, как решить эту задачу, используя встроенные возможности платформы 1С и механизм COM-объектов.
Мы выясним, как программно сформировать табличный документ, применить к нему необходимое оформление, а затем сохранить его в файл Excel. Также разберем, как создать отчет с несколькими закладками в одном файле, что дает полный контроль над структурой и содержанием выгружаемых данных.
ТабличныйДокументОбъект ТабличныйДокумент в 1С является мощным инструментом для формирования различных отчетов и печатных форм. Он позволяет нам программно создавать табличное представление данных, применять к нему разнообразные стили форматирования, объединять ячейки и многое другое. Далее мы можем сохранить этот ТабличныйДокумент в различных форматах, включая XLSX, который является основным форматом файлов Excel.
Для начала, мы создадим новый экземпляр объекта ТабличныйДокумент:
ТабДок = Новый ТабличныйДокумент;
Далее мы приступим к формированию содержимого отчета. Первым шагом будет создание заголовка. Мы хотим, чтобы заголовок был отформатирован особым образом, располагался по центру и, возможно, занимал несколько колонок.
Объединить() для создания единой области из нескольких ячеек.Шрифт и применяем его к заголовку, задавая гарнитуру, размер и начертание (например, жирный).Вывести() для добавления сформированной области в наш ТабличныйДокумент.
// Формируем шапку отчета
ОбластьЗаголовок = ТабДок.Области.Добавить("Заголовок", 1, 1, 1, 5); // Пример, где 5 колонок
ОбластьЗаголовок.Текст = "Отчет по реализации товаров и услуг";
ОбластьЗаголовок.Объединить(1, 1, 1, 5); // Объединяем ячейки с 1-й по 5-ю в 1-й строке
ОбластьЗаголовок.ГоризонтальноеПоложение = ГоризонтальноеПоложение.Центр;
ОбластьЗаголовок.ВертикальноеПоложение = ВертикальноеПоложение.Центр;
ОбластьЗаголовок.Шрифт = Новый Шрифт("Arial", 12, Истина); // Жирный шрифт, 12pt
ТабДок.Вывести(ОбластьЗаголовок);
// Добавим строку с заголовками колонок
ОбластьШапкаКолонок = ТабДок.Области.Добавить("ШапкаКолонок", 2, 1, 2, 5);
ОбластьШапкаКолонок.Область("R2C1").Текст = "Номенклатура";
ОбластьШапкаКолонок.Область("R2C2").Текст = "Количество";
ОбластьШапкаКолонок.Область("R2C3").Текст = "Цена";
ОбластьШапкаКолонок.Область("R2C4").Текст = "Сумма";
ОбластьШапкаКолонок.Область("R2C5").Текст = "Склад";
ОбластьШапкаКолонок.Шрифт = Новый Шрифт("Arial", 10, Истина); // Жирный шрифт для заголовков
ОбластьШапкаКолонок.ГоризонтальноеПоложение = ГоризонтальноеПоложение.Центр;
ТабДок.Вывести(ОбластьШапкаКолонок);
Теперь мы перейдем к выводу основной табличной части документа. Предположим, у нас есть табличная часть ТЧДокумента, которую мы хотим выгрузить. Для более гибкого управления форматом каждой строки данных рекомендуется использовать макеты. Макет позволяет заранее определить структуру строки, ее параметры и стили. Если макет не используется, нам придется заполнять каждую ячейку вручную, что может быть менее удобно для сложных таблиц.
ОбластьСтрока.Параметры.Номенклатура = СтрокаТЧ.Номенклатура;). Если макет не используется, мы напрямую устанавливаем текст для каждой ячейки внутри области.ТабличныйДокумент.
// Предположим, у нас есть табличная часть документа:
// ИсточникДанных = Документы.РеализацияТоваровУслуг.НайтиПоНомеру("0000-000001", Дата("20230101")).Товары;
// Или любая другая табличная часть/результат запроса.
// Для примера создадим тестовую табличную часть
ИсточникДанных = Новый ТаблицаЗначений;
ИсточникДанных.Колонки.Добавить("Номенклатура");
ИсточникДанных.Колонки.Добавить("Количество");
ИсточникДанки.Колонки.Добавить("Цена");
ИсточникДанных.Колонки.Добавить("Сумма");
ИсточникДанных.Колонки.Добавить("Склад");
СтрокаТЗ = ИсточникДанных.Добавить();
СтрокаТЗ.Номенклатура = "Монитор AOC";
СтрокаТЗ.Количество = 2;
СтрокаТЗ.Цена = 15000;
СтрокаТЗ.Сумма = 30000;
СтрокаТЗ.Склад = "Основной склад";
СтрокаТЗ = ИсточникДанных.Добавить();
СтрокаТЗ.Номенклатура = "Клавиатура Logitech";
СтрокаТЗ.Количество = 5;
СтрокаТЗ.Цена = 3000;
СтрокаТЗ.Сумма = 15000;
СтрокаТЗ.Склад = "Склад электроники";
// Выводим данные табличной части
Для Каждого СтрокаТЧ Из ИсточникДанных Цикл
ТекущаяСтрока = ТабДок.Области.Добавить("СтрокаДанных", ТабДок.ВысотаТаблицы + 1, 1, ТабДок.ВысотаТаблицы + 1, 5);
ТекущаяСтрока.Область("R" + (ТабДок.ВысотаТаблицы) + "C1").Текст = СтрокаТЧ.Номенклатура;
ТекущаяСтрока.Область("R" + (ТабДок.ВысотаТаблицы) + "C2").Текст = СтрокаТЧ.Количество;
ТекущаяСтрока.Область("R" + (ТабДок.ВысотаТаблицы) + "C3").Текст = СтрокаТЧ.Цена;
ТекущаяСтрока.Область("R" + (ТабДок.ВысотаТаблицы) + "C4").Текст = СтрокаТЧ.Сумма;
ТекущаяСтрока.Область("R" + (ТабДок.ВысотаТаблицы) + "C5").Текст = СтрокаТЧ.Склад;
// Применяем границы для каждой ячейки строки
Для Колонка = 1 По 5 Цикл
ТекущаяСтрока.Область("R" + (ТабДок.ВысотаТаблицы) + "C" + Колонка).ОбластьЯчеек.ЛеваяГраница = Новый Линия(ТипЛинииТабличногоДокумента.Тонкая);
ТекущаяСтрока.Область("R" + (ТабДок.ВысотаТаблицы) + "C" + Колонка).ОбластьЯчеек.ВерхняяГраница = Новый Линия(ТипЛинииТабличногоДокумента.Тонкая);
ТекущаяСтрока.Область("R" + (ТабДок.ВысотаТаблицы) + "C" + Колонка).ОбластьЯчеек.ПраваяГраница = Новый Линия(ТипЛинииТабличногоДокумента.Тонкая);
ТекущаяСтрока.Область("R" + (ТабДок.ВысотаТаблицы) + "C" + Колонка).ОбластьЯчеек.НижняяГраница = Новый Линия(ТипЛинииТабличногоДокумента.Тонкая);
КонецЦикла;
ТабДок.Вывести(ТекущаяСтрока);
КонецЦикла;
Для создания нескольких листов (закладок) в одном файле Excel с помощью объекта ТабличныйДокумент, нам нужно формировать несколько таких документов. Однако, ТабличныйДокумент по своей природе сохраняется как единый лист. Если вы хотите иметь несколько листов, каждый из которых является отдельным ТабличнымДокументом, вам потребуется сохранять их в отдельные файлы, а затем объединять вручную, либо использовать COM-объект Excel, о чем будет рассказано ниже. Для простого разделения на страницы при печати или просмотре можно использовать ТабДок.ВывестиГоризонтальныйРазделительСтраниц(), но это не создает отдельные листы в Excel.
После того как наш ТабличныйДокумент полностью сформирован и отформатирован, нам нужно сохранить его в файл Excel. Для этого мы используем метод Записать(). Мы укажем имя файла и тип файла (ТипФайлаТабличногоДокумента.XLSX для современного формата Excel).
ИмяФайла = ПолучитьИмяВременногоФайла("xlsx"); // Создаем временный файл
ТабДок.Записать(ИмяФайла, ТипФайлаТабличногоДокумента.XLSX);
// Открываем созданный файл Excel
Попытка
ЗапуститьПриложение(ИмяФайла);
Исключение
Сообщить("Не удалось открыть файл Excel: " + ОписаниеОшибки(), СтатусСообщения.Важное);
КонецПопытки;
Этот метод является относительно простым и хорошо подходит для большинства задач по выгрузке данных в Excel с форматированием. Однако, он не предоставляет прямого способа создания нескольких закладок в одном файле. Для такой функциональности нам потребуется более продвинутый инструмент.
COM-объект ExcelКогда требуется полный контроль над файлом Excel, включая создание нескольких листов, именование закладок, сложную настройку колонок, строк, применение формул и других специфических функций Excel, мы обращаемся к COM-объектам. Этот подход предполагает, что на компьютере пользователя установлен Microsoft Excel, и мы будем программно управлять им из 1С, как если бы пользователь работал с ним вручную.
Этот метод является более сложным, но он предоставляет максимальную гибкость. Разберем его пошагово:
COM-объект Excel: Мы создаем экземпляр приложения Excel. Важно понимать, что это запускает реальное приложение Excel на сервере или клиентской машине.Excel.Visible = Истина) или выполнять все операции в фоновом режиме (Excel.Visible = Ложь).Лист.Cells(строка, колонка).Value = Значение;), диапазонам ячеек (Лист.Range("A1:B2").Font.Bold = True;) и применять к ним все доступные свойства и методы Excel.Рассмотрим пример кода, демонстрирующий создание двух листов ("Товары" и "Услуги") и заполнение их данными.
// Создадим два ТабличныхДокумента для примера, которые будут содержать данные для разных листов Excel
ТабДокТовары = Новый ТабличныйДокумент;
ТабДокУслуги = Новый ТабличныйДокумент;
// Заполним ТабДокТовары (аналогично примеру выше)
ТабДокТовары.Области.Добавить("ЗаголовокТовары", 1, 1, 1, 4).Текст = "Список товаров";
ТабДокТовары.Области.Добавить("ЗаголовокТовары").Объединить(1,1,1,4);
ТабДокТовары.Вывести(ТабДокТовары.Области.ЗаголовокТовары);
ТабДокТовары.Области.Добавить("ШапкаТовары", 2, 1, 2, 4);
ТабДокТовары.Области.ШапкаТовары.Область("R2C1").Текст = "Номенклатура";
ТабДокТовары.Области.ШапкаТовары.Область("R2C2").Текст = "Количество";
ТабДокТовары.Области.ШапкаТовары.Область("R2C3").Текст = "Цена";
ТабДокТовары.Области.ШапкаТовары.Область("R2C4").Текст = "Сумма";
ТабДокТовары.Вывести(ТабДокТовары.Области.ШапкаТовары);
// Добавим тестовые данные для товаров
ИсточникДанныхТовары = Новый ТаблицаЗначений;
ИсточникДанныхТовары.Колонки.Добавить("Номенклатура");
ИсточникДанныхТовары.Колонки.Добавить("Количество", Новый ОписаниеТипов("Число", , , 2));
ИсточникДанныхТовары.Колонки.Добавить("Цена", Новый ОписаниеТипов("Число", , , 2));
ИсточникДанныхТовары.Колонки.Добавить("Сумма", Новый ОписаниеТипов("Число", , , 2));
СтрокаТЗ = ИсточникДанныхТовары.Добавить();
СтрокаТЗ.Номенклатура = "Монитор AOC";
СтрокаТЗ.Количество = 2;
СтрокаТЗ.Цена = 15000;
СтрокаТЗ.Сумма = 30000;
СтрокаТЗ = ИсточникДанныхТовары.Добавить();
СтрокаТЗ.Номенклатура = "Мышь A4Tech";
СтрокаТЗ.Количество = 10;
СтрокаТЗ.Цена = 1000;
СтрокаТЗ.Сумма = 10000;
Для Каждого СтрокаТЧ Из ИсточникДанныхТовары Цикл
ТекущаяСтрока = ТабДокТовары.Области.Добавить("СтрокаТоваров", ТабДокТовары.ВысотаТаблицы + 1, 1, ТабДокТовары.ВысотаТаблицы + 1, 4);
ТекущаяСтрока.Область("R" + (ТабДокТовары.ВысотаТаблицы) + "C1").Текст = СтрокаТЧ.Номенклатура;
ТекущаяСтрока.Область("R" + (ТабДокТовары.ВысотаТаблицы) + "C2").Текст = СтрокаТЧ.Количество;
ТекущаяСтрока.Область("R" + (ТабДокТовары.ВысотаТаблицы) + "C3").Текст = СтрокаТЧ.Цена;
ТекущаяСтрока.Область("R" + (ТабДокТовары.ВысотаТаблицы) + "C4").Текст = СтрокаТЧ.Сумма;
ТабДокТовары.Вывести(ТекущаяСтрока);
КонецЦикла;
// Заполним ТабДокУслуги
ТабДокУслуги.Области.Добавить("ЗаголовокУслуги", 1, 1, 1, 3).Текст = "Список услуг";
ТабДокУслуги.Области.Добавить("ЗаголовокУслуги").Объединить(1,1,1,3);
ТабДокУслуги.Вывести(ТабДокУслуги.Области.ЗаголовокУслуги);
ТабДокУслуги.Области.Добавить("ШапкаУслуги", 2, 1, 2, 3);
ТабДокУслуги.Области.ШапкаУслуги.Область("R2C1").Текст = "Наименование услуги";
ТабДокУслуги.Области.ШапкаУслуги.Область("R2C2").Текст = "Количество";
ТабДокУслуги.Области.ШапкаУслуги.Область("R2C3").Текст = "Стоимость";
ТабДокУслуги.Вывести(ТабДокУслуги.Области.ШапкаУслуги);
// Добавим тестовые данные для услуг
ИсточникДанныхУслуги = Новый ТаблицаЗначений;
ИсточникДанныхУслуги.Колонки.Добавить("НаименованиеУслуги");
ИсточникДанныхУслуги.Колонки.Добавить("Количество", Новый ОписаниеТипов("Число", , , 2));
ИсточникДанныхУслуги.Колонки.Добавить("Стоимость", Новый ОписаниеТипов("Число", , , 2));
СтрокаТЗ = ИсточникДанныхУслуги.Добавить();
СтрокаТЗ.НаименованиеУслуги = "Доставка";
СтрокаТЗ.Количество = 1;
СтрокаТЗ.Стоимость = 500;
СтрокаТЗ = ИсточникДанныхУслуги.Добавить();
СтрокаТЗ.НаименованиеУслуги = "Настройка ПО";
СтрокаТЗ.Количество = 2;
СтрокаТЗ.Стоимость = 2500;
Для Каждого СтрокаТЧ Из ИсточникДанныхУслуги Цикл
ТекущаяСтрока = ТабДокУслуги.Области.Добавить("СтрокаУслуг", ТабДокУслуги.ВысотаТаблицы + 1, 1, ТабДокУслуги.ВысотаТаблицы + 1, 3);
ТекущаяСтрока.Область("R" + (ТабДокУслуги.ВысотаТаблицы) + "C1").Текст = СтрокаТЧ.НаименованиеУслуги;
ТекущаяСтрока.Область("R" + (ТабДокУслуги.ВысотаТаблицы) + "C2").Текст = СтрокаТЧ.Количество;
ТекущаяСтрока.Область("R" + (ТабДокУслуги.ВысотаТаблицы) + "C3").Текст = СтрокаТЧ.Стоимость;
ТабДокУслуги.Вывести(ТекущаяСтрока);
КонецЦикла;
ИмяФайлаExcel = ПолучитьИмяВременногоФайла("xlsx");
Попытка
Excel = Новый COMОбъект("Excel.Application");
Excel.Visible = Истина; // Сделать Excel видимым для отладки, в рабочей среде лучше Ложь
Книга = Excel.Workbooks.Add(); // Добавляем новую книгу
// *** Работа с первым листом (Товары) ***
Лист1 = Книга.Worksheets(1); // Получаем первый лист
Лист1.Name = "Товары"; // Присваиваем имя закладке
// Для переноса ТабличногоДокумента в COM-объект Excel,
// сначала сохраним ТабличныйДокумент во временный файл,
// а затем откроем его в Excel и скопируем содержимое.
ИмяВременногоФайлаТовары = ПолучитьИмяВременногоФайла("xlsx");
ТабДокТовары.Записать(ИмяВременногоФайлаТовары, ТипФайлаТабличногоДокумента.XLSX);
ВременнаяКнигаТовары = Excel.Workbooks.Open(ИмяВременногоФайлаТовары);
ВременнаяКнигаТовары.Sheets(1).Cells.Copy(); // Копируем все ячейки
Лист1.Paste(Лист1.Cells(1,1)); // Вставляем на наш лист
ВременнаяКнигаТовары.Close(0); // Закрываем временную книгу без сохранения
УдалитьФайлы(ИмяВременногоФайлаТовары); // Удаляем временный файл
// Автоподбор ширины колонок для первого листа
Лист1.Columns.AutoFit();
Лист1.Range("A1").Activate(); // Активируем ячейку A1
// *** Работа со вторым листом (Услуги) ***
// Добавляем новый лист
Лист2 = Книга.Worksheets.Add();
Лист2.Name = "Услуги"; // Присваиваем имя закладке
ИмяВременногоФайлаУслуги = ПолучитьИмяВременногоФайла("xlsx");
ТабДокУслуги.Записать(ИмяВременногоФайлаУслуги, ТипФайлаТабличногоДокумента.XLSX);
ВременнаяКнигаУслуги = Excel.Workbooks.Open(ИмяВременногоФайлаУслуги);
ВременнаяКнигаУслуги.Sheets(1).Cells.Copy();
Лист2.Paste(Лист2.Cells(1,1));
ВременнаяКнигаУслуги.Close(0);
УдалитьФайлы(ИмяВременногоФайлаУслуги);
// Автоподбор ширины колонок для второго листа
Лист2.Columns.AutoFit();
Лист2.Range("A1").Activate();
// *** Сохранение и закрытие ***
Книга.SaveAs(ИмяФайлаExcel); // Сохраняем готовую книгу Excel
Excel.Quit(); // Закрываем приложение Excel
// Открываем итоговый файл
ЗапуститьПриложение(ИмяФайлаExcel);
Исключение
Сообщить("Произошла ошибка при работе с Excel: " + ОписаниеОшибки(), СтатусСообщения.Важное);
Если Excel <> Неопределено Тогда
Excel.Quit(); // Попытаемся закрыть Excel в случае ошибки
КонецЕсли;
КонецПопытки;
Важные моменты при работе с COM-объектом Excel:
COM-объектом может быть медленным, особенно при больших объемах данных или большом количестве операций форматирования.Попытка...Исключение...КонецПопытки и корректно закрывать COM-объект Excel (Excel.Quit()) даже в случае ошибки, чтобы предотвратить зависание процессов Excel в фоновом режиме.ТабличногоДокумента в COM-объект Excel: Мы рассмотрели эффективный способ переноса данных и форматирования из уже сформированного ТабличногоДокумента в COM-объект Excel. Для этого ТабличныйДокумент временно сохраняется в файл, который затем открывается Excel'ем, данные копируются, и временный файл удаляется. Это позволяет избежать ручного построения таблицы в Excel, используя привычные средства 1С для формирования содержимого, а затем COM-объект для организации листов и финальной сборки.При выборе метода выгрузки данных в Excel, мы рекомендуем учитывать следующие факторы:
ТабличныйДокумент. Это проще и надежнее.COM-объект Excel.COM-объект Excel может быть не самым быстрым решением. В таких случаях иногда рассматривают выгрузку в CSV или другие специализированные форматы, а затем импорт в Excel.COM-объект Excel.ТабличнымДокументом мы настоятельно рекомендуем использовать макеты (Объекты.Макет). Макеты значительно упрощают форматирование и вывод повторяющихся строк или блоков данных, делая код более читаемым и поддерживаемым. Макеты позволяют визуально настроить внешний вид областей (шапок, строк данных, итогов), задать параметры, а затем программно заполнять эти параметры. Это особенно полезно, когда структура отчета сложная, содержит разные типы строк или динамически изменяющееся количество колонок.Таким образом, мы видим, что платформа 1С предоставляет гибкие инструменты для решения задач по экспорту данных в Excel, позволяя нам выбрать оптимальный подход в зависимости от конкретных требований к отчету.