При работе с системой 1С часто возникает необходимость выгрузки данных в сторонние форматы, например, в Microsoft Excel или растровые и векторные изображения. Стандартные механизмы, такие как УниверсальныйОбменДаннымиXML или СистемаКомпоновкиДанных (СКД), предоставляют широкие возможности, но иногда их гибкости не хватает для формирования сложных отчетов с нестандартными шапками, подвалами или специфическим форматированием ячеек, объединением и границами. В таких случаях мы сталкиваемся с потребностью в более тонком контроле над формированием итогового Excel-файла, хотя в простых случаях достаточно использовать готовый экспорт справочников в Excel. В этой статье мы подробно рассмотрим, как решить эту задачу, используя прямой программный доступ к Microsoft Excel через COM-объект.
Мы разберем по шагам процесс создания, заполнения и форматирования Excel-документа прямо из кода 1С, обеспечивая максимальную гибкость и точность в представлении данных. Это позволяет нам создавать отчеты любой сложности, полностью контролируя их внешний вид.
Первый и ключевой шаг в работе с Excel из 1С – это создание COM-объекта Excel.Application (для которого может быть полезен анализ структуры метаданных). Этот объект представляет собой запущенное приложение Microsoft Excel. Мы должны убедиться, что Excel установлен на той машине, где будет выполняться код (клиент или сервер). Мы всегда используем блок Попытка...Исключение для корректной обработки возможных ошибок, если Excel не найден или не может быть запущен.
Перем Excel; // Объявим переменную для объекта Excel
Попытка
// Создаем COM-объект Excel.Application
Excel = Новый COMОбъект("Excel.Application");
Исключение
Сообщить("Не удалось запустить Microsoft Excel. Проверьте, что он установлен на компьютере. Описание ошибки: " + ОписаниеОшибки(), СтатусСообщения.Важное);
Возврат;
КонецПопытки;
После успешной инициализации объекта Excel мы можем управлять видимостью приложения для пользователя. Если нам не требуется интерактивное взаимодействие, и файл просто сохраняется, мы можем установить свойство Excel.Visible = Ложь. Если же мы хотим, чтобы пользователь видел процесс или мог сразу просмотреть отчет, устанавливаем Excel.Visible = Истина.
Excel.Visible = Истина; // Или Ложь, если не нужно показывать пользователю
Далее мы создаем новую рабочую книгу (workbook) и получаем доступ к первому листу (sheet) этой книги. Мы сразу же дадим листу осмысленное имя, чтобы отчет был более понятным.
// Добавляем новую рабочую книгу
Книга = Excel.Workbooks.Add();
// Получаем ссылку на первый лист
Лист = Книга.Sheets(1);
// Присваиваем листу имя
Лист.Name = "ОтчетПоПродажам";
Шапка отчета обычно содержит общую, суммирующую информацию, такую как название отчета, период, наименование контрагента и т.д. Мы можем заполнять ячейки, используя свойство Cells(Строка, Колонка).Value. Для придания отчету профессионального вида мы сразу же применим базовое форматирование.
Важно: При работе с COMОбъект Excel, индексация строк и столбцов начинается с 1.
// Заполняем основные данные шапки отчета
Лист.Cells(1, 1).Value = "Отчет по продажам товаров";
Лист.Cells(2, 1).Value = "Период с " + Формат(ДатаНачала, "ДФ=dd.MM.yyyy") + " по " + Формат(ДатаОкончания, "ДФ=dd.MM.yyyy");
Лист.Cells(3, 1).Value = "Контрагент: " + КонтрагентПредставление;
// Применяем форматирование для заголовка
Лист.Cells(1, 1).Font.Bold = Истина; // Делаем текст жирным
Лист.Cells(1, 1).Font.Size = 14; // Устанавливаем размер шрифта
// Объединяем ячейки для заголовка, чтобы он выглядел централизованно
// xlCenter (-4108) - это константа для выравнивания по центру
Лист.Range("A1:D1").Merge();
Лист.Cells(1, 1).HorizontalAlignment = -4108; // Выравнивание по центру
Мы видим, как просто можно задавать значения ячеек и применять к ним форматирование, используя свойства Font.Bold, Font.Size. Для объединения ячеек используется метод Range("НачальнаяЯчейка:КонечнаяЯчейка").Merge(), а выравнивание текста задается через HorizontalAlignment.
После шапки перейдем к заполнению основной, детализированной части отчета, которая обычно представлена в виде таблицы. Мы заранее определим, с какой строки начинается наша таблица, чтобы данные не наслаивались на шапку.
// Определяем начальную строку для табличной части
СтрокаНачалоТаблицы = 5;
// Заполняем заголовки столбцов табличной части
Лист.Cells(СтрокаНачалоТаблицы, 1).Value = "Номенклатура";
Лист.Cells(СтрокаНачалоТаблицы, 2).Value = "Количество";
Лист.Cells(СтрокаНачалоТаблицы, 3).Value = "Цена";
Лист.Cells(СтрокаНачалоТаблицы, 4).Value = "Сумма";
// Применим форматирование для заголовков табличной части
Для Колонка = 1 По 4 Цикл
Лист.Cells(СтрокаНачалоТаблицы, Колонка).Font.Bold = Истина;
Лист.Cells(СтрокаНачалоТаблицы, Колонка).Interior.Color = 12632256; // Светло-серый фон
Лист.Cells(СтрокаНачалоТаблицы, Колонка).HorizontalAlignment = -4108; // Выравнивание по центру
КонецЦикла;
Теперь нам нужно заполнить саму таблицу данными. Предположим, у нас есть некая ТаблицаЗначений (сформированная, например, через универсальную консоль запросов), содержащая нужные данные. Мы будем перебирать ее строки и записывать значения в соответствующие ячейки Excel.
// Пример получения данных (в реальном приложении это будет запрос к БД)
ДанныеОтчета = Новый ТаблицаЗначений;
ДанныеОтчета.Колонки.Добавить("Номенклатура");
ДанныеОтчета.Колонки.Добавить("Количество", Новый ОписаниеТипов("Число", , , Новый КвалификаторыЧисла(15, 3)));
ДанныеОтчета.Колонки.Добавить("Цена", Новый ОписаниеТипов("Число", , , Новый КвалификаторыЧисла(15, 2)));
ДанныеОтчета.Колонки.Добавить("Сумма", Новый ОписаниеТипов("Число", , , Новый КвалификаторыЧисла(15, 2)));
// Добавляем тестовые данные
Стр = ДанныеОтчета.Добавить();
Стр.Номенклатура = "Мышь беспроводная Logitech";
Стр.Количество = 10;
Стр.Цена = 1250.00;
Стр.Сумма = Стр.Количество * Стр.Цена;
Стр = ДанныеОтчета.Добавить();
Стр.Номенклатура = "Клавиатура механическая HyperX";
Стр.Количество = 5;
Стр.Цена = 7500.00;
Стр.Сумма = Стр.Количество * Стр.Цена;
Стр = ДанныеОтчета.Добавить();
Стр.Номенклатура = "Монитор Dell 27 дюймов";
Стр.Количество = 2;
Стр.Цена = 22000.00;
Стр.Сумма = Стр.Количество * Стр.Цена;
// Заполняем данными из ТаблицыЗначений
ТекущаяСтрокаExcel = СтрокаНачалоТаблицы + 1; // Начинаем со следующей строки после заголовков
Для Каждого СтрокаДанных Из ДанныеОтчета Цикла
Лист.Cells(ТекущаяСтрокаExcel, 1).Value = СтрокаДанных.Номенклатура;
Лист.Cells(ТекущаяСтрокаExcel, 2).Value = СтрокаДанных.Количество;
Лист.Cells(ТекущаяСтрокаExcel, 3).Value = СтрокаДанных.Цена;
Лист.Cells(ТекущаяСтрокаExcel, 4).Value = СтрокаДанных.Сумма;
// Форматирование числовых значений
Лист.Cells(ТекущаяСтрокаExcel, 2).NumberFormat = "0"; // Целые числа
Лист.Cells(ТекущаяСтрокаExcel, 3).NumberFormat = "0.00"; // Два знака после запятой
Лист.Cells(ТекущаяСтрокаExcel, 4).NumberFormat = "#,##0.00 ₽"; // Денежный формат с валютой
ТекущаяСтрокаExcel = ТекущаяСтрокаExcel + 1;
КонецЦикла;
// Добавим строку итогов
Лист.Cells(ТекущаяСтрокаExcel, 1).Value = "Итого:";
Лист.Cells(ТекущаяСтрокаExcel, 1).Font.Bold = Истина;
Лист.Cells(ТекущаяСтрокаExcel, 4).Formula = "=SUM(D" + (СтрокаНачалоТаблицы + 1) + ":D" + (ТекущаяСтрокаExcel - 1) + ")"; // Формула суммы
Лист.Cells(ТекущаяСтрокаExcel, 4).NumberFormat = "#,##0.00 ₽";
Лист.Cells(ТекущаяСтрокаExcel, 4).Font.Bold = Истина;
Обратите внимание, что мы можем применять форматирование для чисел, используя свойство NumberFormat. Это позволяет нам задавать требуемое количество знаков после запятой, использовать разделители тысяч и добавлять символы валют.
Для придания таблице законченного вида часто требуется добавить границы. Мы можем управлять границами для отдельных ячеек или для целых диапазонов, используя свойство Borders. Также мы можем автоматически подбирать ширину столбцов, чтобы текст полностью помещался.
// Определим диапазон для нашей табличной части, включая заголовки и итоги
ДиапазонТаблицы = Лист.Range(Лист.Cells(СтрокаНачалоТаблицы, 1), Лист.Cells(ТекущаяСтрокаExcel, 4));
// Устанавливаем все внутренние и внешние границы для диапазона
// LineStyle = 1 (xlContinuous) - сплошная линия
// Weight = 2 (xlThin) - тонкая линия
ДиапазонТаблицы.Borders.LineStyle = 1;
ДиапазонТаблицы.Borders.Weight = 2;
// Если нужны только внешние границы, можно использовать:
// ДиапазонТаблицы.BorderAround(1, 2); // xlContinuous, xlThin
// Автоподбор ширины столбцов для лучшей читаемости
Лист.Columns.AutoFit();
// Дополнительно можно установить масштаб отображения для комфортного просмотра
Excel.ActiveWindow.Zoom = 100;
Свойство Borders предоставляет доступ к коллекциям границ (верхняя, нижняя, левая, правая, внутренние горизонтальные, внутренние вертикальные), для каждой из которых можно настроить стиль (LineStyle) и толщину (Weight). Значения констант, таких как xlContinuous или xlThin, можно найти в документации по Excel VBA или методом подбора, но обычно 1 и 2 являются частыми значениями для сплошных тонких линий.
После того как все данные заполнены и отформатированы, нам необходимо сохранить созданный Excel-файл и корректно завершить работу с COM-объектом, чтобы не оставлять висящие процессы Excel в памяти.
// Определяем путь и имя файла для сохранения
ИмяФайла = "C:\Temp\ОтчетПоПродажам_" + Формат(ТекущаяДата(), "ДФ=yyyyMMdd_HHmmss") + ".xlsx";
Попытка
// Сохраняем рабочую книгу
Книга.SaveAs(ИмяФайла);
Сообщить("Отчет успешно сохранен в файл: " + ИмяФайла, СтатусСообщения.Информация);
Исключение
Сообщить("Ошибка при сохранении файла Excel: " + ОписаниеОшибки(), СтатусСообщения.Важное);
КонецПопытки;
// Закрываем рабочую книгу (параметр SaveChanges = Ложь, чтобы не предлагал сохранить еще раз)
Книга.Close(Ложь);
// Выходим из приложения Excel
Excel.Quit();
// Освобождаем COM-объекты, чтобы они не оставались в памяти
Excel = Неопределено;
Книга = Неопределено;
Лист = Неопределено;
Обязательно используйте метод Quit() для объекта Excel.Application, чтобы закрыть процесс Excel. Также критически важно присвоить переменным, хранящим COM-объекты, значение Неопределено после завершения работы. Это помогает системе корректно освободить ресурсы и предотвратить утечки памяти или "зависшие" процессы Excel.
Value сразу целый массив значений или ТаблицуЗначений.
// Пример записи массива данных в диапазон
МассивДанныхДляЗаписи = Новый Массив();
МассивДанныхДляЗаписи.Добавить(Новый Массив("Значение1", "Значение2"));
МассивДанныхДляЗаписи.Добавить(Новый Массив("Значение3", "Значение4"));
// Диапазон A1:B2
Лист.Range("A1").Resize(МассивДанныхДляЗаписи.ВВерхнейГранице() + 1, МассивДанныхДляЗаписи[0].ВВерхнейГранице() + 1).Value = МассивДанныхДляЗаписи;
xlCenter или xlContinuous, в 1С приходится использовать по их числовым значениям. Эти значения можно найти в документации по VBA для Excel или экспериментально, записав макрос в Excel и посмотрев на сгенерированный код.Использование COM-объекта Excel в 1С предоставляет мощный и гибкий инструмент для создания сложных и полностью настраиваемых отчетов в формате Excel. Мы рассмотрели основные этапы работы: от инициализации объекта и заполнения данных до применения форматирования и корректного завершения работы. Следуя этим рекомендациям, мы можем создавать профессионально оформленные отчеты, которые точно соответствуют требованиям бизнеса и пользователей, выходя за рамки возможностей стандартных средств 1С. Не забывайте о важности обработки ошибок и освобождения ресурсов для стабильной работы вашего решения.