При работе с 1С часто возникает задача выгрузки данных из табличных частей документов, регистров или результатов запросов в Excel. Стандартные средства 1С, такие как метод Записать() объекта ТабличныйДокумент, не всегда позволяют сохранить специфическое форматирование ячеек, из-за чего даты и числа могут импортироваться в Excel как текстовые строки. Хотя для простых задач может подойти сохранение данных табличных частей форм в Excel с помощью готовых подсистем, для обеспечения максимально корректного отображения типов данных и применения расширенного форматирования мы будем использовать механизм COM-объектов, взаимодействуя напрямую с Microsoft Excel.
Мы рассмотрим подробнее, как шаг за шагом реализовать такую выгрузку, обеспечивая правильное форматирование чисел, дат и текста, а также как подготовить данные из произвольных запросов для экспорта.
Для прямого управления содержимым и форматированием ячеек Excel мы будем использовать COM-объект Excel.Application. Этот подход обеспечивает максимальную гибкость и позволяет применить к выгруженным данным любые возможности Excel, доступные через его объектную модель.
Прежде всего, нам необходимо создать экземпляр приложения Excel. Важно помнить, что для работы этого метода на клиентской машине должен быть установлен Microsoft Excel.
Процедура ВыгрузитьДанныеВExcelКОМ(ТаблицаДляВыгрузки)
ИмяФайла = "C:\Temp\ОтчетПоДанным.xlsx"; // Укажите путь и имя файла для сохранения
Попытка
// Создаем COM-объект приложения Excel
Excel = Новый COMОбъект("Excel.Application");
// Установим видимость Excel. При Ложь - Excel не будет виден пользователю.
// При Истина - Excel будет открыт, что удобно для отладки.
Excel.Visible = Ложь;
// Добавляем новую рабочую книгу (файл Excel)
РабочаяКнига = Excel.Workbooks.Add();
// Получаем ссылку на первый лист в книге
Лист = РабочаяКнига.Sheets(1);
Мы начинаем с создания нового экземпляра COMОбъект("Excel.Application"). Свойство Excel.Visible позволяет нам контролировать, будет ли окно Excel отображаться на экране в процессе выполнения выгрузки. Для конечного пользователя обычно устанавливают Ложь, чтобы процесс был невидимым. Затем мы добавляем новую рабочую книгу и получаем ссылку на первый лист, с которым будем работать.
Далее мы последовательно заполняем ячейки Excel данными. Обычно сначала записываются заголовки колонок, а затем данные из каждой строки нашей таблицы.
// Пример заполнения заголовка отчета
Лист.Cells(1, 1).Value = "Отчет по товарам на складах"; Лист.Cells(1, 1).Font.Bold = Истина; // Делаем заголовок жирным
Лист.Cells(1, 1).Font.Size = 14; // Устанавливаем размер шрифта
Лист.Range(Лист.Cells(1, 1), Лист.Cells(1, 3)).MergeCells = Истина; // Объединяем ячейки
// Заголовки колонок, начинаем со второй строки
СтрокаЗаголовков = 2;
Колонка = 1;
Для Каждого КолонкаТЗ Из ТаблицаДляВыгрузки.Колонки Цикл
Лист.Cells(СтрокаЗаголовков, Колонка).Value = КолонкаТЗ.Имя;
Лист.Cells(СтрокаЗаголовков, Колонка).Font.Bold = Истина;
Колонка = Колонка + 1;
КонецЦикла;
// Заполнение данных из переданной ТаблицыЗначений
СтрокаНачалаДанных = СтрокаЗаголовков + 1;
Для Каждого СтрокаТЧ Из ТаблицаДляВыгрузки Цикл
КолонкаДанных = 1;
Для Каждого КолонкаТЗ Из ТаблицаДляВыгрузки.Колонки Цикл
ЗначениеЯчейки = СтрокаТЧ[КолонкаТЗ.Имя];
Лист.Cells(СтрокаНачалаДанных, КолонкаДанных).Value = ЗначениеЯчейки;
КолонкаДанных = КолонкаДанных + 1;
КонецЦикла;
СтрокаНачалаДанных = СтрокаНачалаДанных + 1;
КонецЦикла;
Мы видим, как можно установить общий заголовок отчета, сделать его жирным и объединить ячейки. Затем мы динамически формируем заголовки колонок, используя имена колонок из переданной ТаблицыЗначений. Данные из каждой строки ТаблицыЗначений переносятся в соответствующие ячейки листа Excel. Важно использовать свойство Value объекта Cells для присвоения значений.
Самый важный шаг для решения нашей проблемы – применение корректного форматирования. Объектная модель Excel позволяет устанавливать NumberFormat для целых колонок или диапазонов ячеек. В сложных случаях, если требуется массовая обработка стилей, может потребоваться утилита изменения всех ячеек книги Excel на текстовый.
// Пример форматирования колонок
// Предположим, вторая колонка содержит числа, третья - даты.
// Эти индексы могут варьироваться в зависимости от вашей ТаблицыДляВыгрузки.
// Для числовых колонок:
// Например, если колонка "Количество" является второй (индекс 2)
Лист.Columns(2).NumberFormat = "#,##0.00"; // Числовой формат с разделителем тысяч и двумя знаками после запятой
// Или для целых чисел:
// Лист.Columns(2).NumberFormat = "0";
// Для колонок с датами:
// Например, если колонка "ДатаОтгрузки" является третьей (индекс 3)
Лист.Columns(3).NumberFormat = "dd.mm.yyyy"; // Формат даты "день.месяц.год"
// Или с временем:
// Лист.Columns(3).NumberFormat = "dd.mm.yyyy hh:mm:ss";
// Пример для текста, можно явно указать формат "Текстовый"
Лист.Columns(1).NumberFormat = "@";
// Автоматическая подгонка ширины колонок для лучшей читаемости
Лист.Columns.AutoFit();
Здесь мы используем свойство NumberFormat для установки типа данных и формата отображения в ячейках. Для чисел мы применяем формат #,##0.00, который обеспечивает отображение числа с разделителями тысяч и двумя знаками после запятой. Для дат используется формат dd.mm.yyyy. Если данные в 1С уже имеют правильный тип (Число, Дата), то Excel автоматически корректно их интерпретирует при присвоении через .Value, и NumberFormat лишь задаст способ их отображения. Метод Лист.Columns.AutoFit() позволяет автоматически подобрать оптимальную ширину всех колонок по их содержимому, улучшая читаемость отчета.
После того как все данные заполнены и отформатированы, необходимо сохранить рабочую книгу и закрыть приложение Excel.
РабочаяКнига.SaveAs(ИмяФайла); // Сохраняем файл под указанным именем
РабочаяКнига.Close(0); // Закрываем рабочую книгу без запроса на сохранение
Excel.Quit(); // Завершаем работу приложения Excel
Сообщить("Данные успешно выгружены в файл: " + ИмяФайла);
Исключение
Сообщить("Ошибка при выгрузке в Excel: " + ОписаниеОшибки());
// В случае ошибки, стараемся закрыть Excel, если он был открыт
Если Не Excel = Неопределено Тогда
Excel.Quit();
КонецЕсли;
КонецПопытки;
КонецПроцедуры
Мы используем метод SaveAs() для сохранения файла по указанному пути, Close(0) для закрытия книги без сохранения изменений (поскольку мы уже сохранили) и Quit() для полного завершения процесса Excel. Блок Попытка...Исключение очень важен для обработки возможных ошибок, например, если файл уже открыт или нет прав на запись. В случае ошибки мы обязательно должны попытаться закрыть экземпляр Excel, чтобы избежать "подвисших" процессов.
Часто данные, которые мы хотим выгрузить, не находятся в готовой ТаблицеЗначений, а являются результатом выполнения запроса к базе данных 1С. Мы разберем, как получить эти данные и преобразовать их в формат, удобный для дальнейшей обработки COM-объектом Excel.
Используем объект Запрос для выборки необходимых данных. Полученный результат запроса мы выгрузим в ТаблицуЗначений.
// Предположим, нам нужно получить остатки товаров на складах
Функция ПолучитьДанныеДляВыгрузки()
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ТоварыНаСкладахОстатки.Номенклатура.Наименование КАК Номенклатура,
| ТоварыНаСкладахОстатки.ВНаличииОстаток КАК КоличествоНаСкладе,
| ТоварыНаСкладахОстатки.Склад.Наименование КАК Склад,
| ДАТАВРЕМЯ(2023, 10, 26, 15, 30, 0) КАК ДатаОтчета
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.Остатки КАК ТоварыНаСкладахОстатки
|ГДЕ
| ТоварыНаСкладахОстатки.ВНаличииОстаток > 0
|УПОРЯДОЧИТЬ ПО
| Номенклатура";
РезультатЗапроса = Запрос.Выполнить();
// Выгружаем результат запроса в ТаблицуЗначений
ТаблицаДляВыгрузки = РезультатЗапроса.Выгрузить();
Возврат ТаблицаДляВыгрузки;
КонецФункции
Мы создаем новый объект Запрос и формируем его текст, выбирая необходимые поля из регистров, справочников или других объектов конфигурации. Обратите внимание, что для удобства мы используем псевдонимы (КАК Номенклатура) для полей, что сделает имена колонок в результирующей ТаблицеЗначений более читаемыми. После выполнения запроса методом Выполнить(), мы используем метод Выгрузить() для получения ТаблицыЗначений, которая и будет являться источником данных для экспорта в Excel.
Полученную ТаблицуЗначений мы можем передать в ранее описанную процедуру ВыгрузитьДанныеВExcelКОМ().
// Пример вызова процедуры выгрузки
Процедура КомандаВыгрузитьВExcel(Команда)
ДанныеДляОтчета = ПолучитьДанныеДляВыгрузки();
Если ДанныеДляОтчета.Количество() > 0 Тогда
ВыгрузитьДанныеВExcelКОМ(ДанныеДляОтчета);
Иначе
Сообщить("Нет данных для выгрузки.");
КонецЕсли;
КонецПроцедуры
Таким образом, мы сначала формируем набор данных, а затем передаем его в универсальную процедуру выгрузки, которая позаботится о создании Excel-файла и его форматировании. Это позволяет разделить логику получения данных от логики их экспорта, делая код более модульным и понятным.
Критически важно понимать, что для работы с COMОбъект("Excel.Application") на клиентской машине должен быть установлен Microsoft Excel. Если Excel не установлен, создание COM-объекта завершится ошибкой. В тех случаях, когда требуется формирование файлов с формулами без использования тяжелых COM-объектов, иногда применяют альтернативное использование Python при выгрузке в Excel с формулами.
Если выгрузка предполагается на сервере 1С, то Microsoft Excel должен быть установлен на самом сервере. Однако, стоит отметить, что использование COM-объектов офисных приложений на сервере 1С не является рекомендуемой практикой. Это может привести к нестабильности сервера, утечкам памяти и проблемам с правами доступа. Для серверной выгрузки часто применяют библиотеки для работы с OpenXML-форматом (XLSX) напрямую.
При выгрузке очень больших объемов данных производительность через COM может быть низкой. В таких сценариях рекомендуется заполнять диапазоны ячеек сразу массивами значений, а не построчно. Если после выгрузки данные всё же отображаются некорректно, можно использовать набор инструментов Коррекция 1С для Excel, представляющий собой надстройку для исправления типичных недостатков в выгруженных данных.
// Пример: Присвоение массива диапазону ячеек
Если МассивВсехДанных.Количество() > 0 Тогда
Диапазон = Лист.Range(Лист.Cells(СтрокаНачалаДанных, 1), Лист.Cells(СтрокаНачалаДанных + МассивВсехДанных.Количество() - 1, ТаблицаДляВыгрузки.Колонки.Количество()));
Диапазон.Value = МассивВсехДанных;
КонецЕсли;
Такой подход значительно ускоряет выгрузку, поскольку минимизирует количество обращений к COM-объекту.
Объектная модель Excel предоставляет огромные возможности для форматирования. Кроме NumberFormat, мы можем настроить:
Font.Name, Font.Size, Font.Bold, Font.Italic, Font.Color – для шрифтов.Interior.Color, Interior.Pattern – для заливки ячеек.Borders.LineStyle, Borders.Weight, Borders.Color – для границ ячеек.HorizontalAlignment, VerticalAlignment – для выравнивания текста.WrapText – для переноса текста по словам.Range(Лист.Cells(НачальнаяСтрока, НачальнаяКолонка), Лист.Cells(КонечнаяСтрока, КонечнаяКолонка)).MergeCells = Истина; – для объединения ячеек.Используя эти свойства, мы можем создать отчет с любым желаемым оформлением.
При использовании COM-объектов важно убедиться, что у пользователя или учетной записи, под которой запускается 1С, есть необходимые права для запуска и взаимодействия с Microsoft Excel.
Таким образом, мы выяснили, что для точной и гибкой выгрузки данных из 1С в Excel с сохранением типов данных и применением расширенного форматирования, оптимальным решением является использование COM-объекта Microsoft Excel. Этот подход позволяет полностью контролировать процесс создания и оформления Excel-файла, что значительно расширяет возможности по формированию аналитических отчетов и выгрузок.