Как сохранить отчет 1С в несколько файлов Excel, разделенных по количеству строк, на неуправляемых формах?

← На главную

При работе с большими отчетами в 1С часто возникает необходимость не просто выгрузить данные в Excel, но и разделить их на несколько отдельных файлов или листов по определенному критерию, например, по количеству строк. Это может быть актуально, когда общий объем данных слишком велик для одного файла или если требуется отправка частей отчета разным получателям. В данном материале мы рассмотрим несколько подходов к решению этой задачи, начиная с прямого программного разделения табличного документа и заканчивая более сложными методами для оптимизации работы с очень большими объемами данных.

Разделение табличного документа на несколько файлов Excel (решение из форума)

Основной вопрос, который мы решаем, заключается в том, как программно взять сформированный табличный документ и разбить его на части, сохраняя каждую часть в отдельный файл Excel. Это особенно актуально для неуправляемых форм, где возможности пользователя по интерактивной обработке больших отчетов могут быть ограничены.

Для реализации этой задачи мы будем использовать методы объекта ТабличныйДокумент, который является основным представлением отчетов и печатных форм в 1С. Наша стратегия будет заключаться в пошаговом извлечении определенных областей из исходного табличного документа и сохранении каждой из этих областей в новый, временный табличный документ, который затем будет записан в отдельный файл Excel.

Определение исходного табличного документа: Предположим, у нас уже есть сформированный объект ТабличныйДокумент, содержащий весь отчет. Пусть это будет переменная Таблица.
Задание количества строк на файл: Мы должны определить, сколько строк должно быть в каждом выходном файле Excel. Это будет переменная КоличествоСтрокНа.
Итерация по частям: Мы будем проходить по исходной таблице циклами, определяя начальную и конечную строку для каждой порции.
Получение области: Для каждой порции мы будем использовать метод Таблица.ПолучитьОбласть(), который позволяет извлечь прямоугольную область из существующего табличного документа.
Создание нового табличного документа и вывод области: Каждую полученную область мы будем выводить в новый объект ТабличныйДокумент.
Сохранение в файл: Полученный временный табличный документ сохраняем в файл Excel с уникальным именем.

Рассмотрим пример кода, который демонстрирует этот подход:


// Предполагаем, что "Таблица" - это наш исходный ТабличныйДокумент, содержащий весь отчет.
// Например, Таблица = Отчет.Сформировать();

// Задаем количество строк в каждом выходном файле
КоличествоСтрокНа = 10000; // Пример: по 10 тысяч строк в каждом файле

// Определяем общее количество строк в исходной таблице
ВысотаИсходнойТаблицы = Таблица.ВысотаТаблицы;

// Определяем количество порций (файлов)
КоличествоПорций = Цел(ВысотаИсходнойТаблицы / КоличествоСтрокНа);
Если ВысотаИсходнойТаблицы % КоличествоСтрокНа > 0 Тогда
    КоличествоПорций = КоличествоПорций + 1;
КонецЕсли;

Для НомерПорции = 1 По КоличествоПорций Цикл
    // Определяем начальную строку для текущей порции
    НачальнаяСтрока = (НомерПорции - 1) * КоличествоСтрокНа + 1;
    // Определяем конечную строку для текущей порции
    КонечнаяСтрока  = Мин(НомерПорции * КоличествоСтрокНа, ВысотаИсходнойТаблицы);

    // Создаем новый табличный документ для текущей порции
    ТабПорция = Новый ТабличныйДокумент;

    // Получаем область из исходного табличного документа
    ОбластьДляВывода = Таблица.ПолучитьОбласть(НачальнаяСтрока, 1, КонечнаяСтрока, Таблица.ШиринаТаблицы);

    // Выводим полученную область в новый табличный документ порции
    ТабПорция.Вывести(ОбластьДляВывода);

    // Формируем имя файла для текущей порции
    ИмяОчередногоФайла = "Отчет_Часть_" + Формат(НомерПорции, "ЧГ=0") + ".xlsx";

    // Сохраняем табличный документ порции в файл Excel
    ТабПорция.Записать(ИмяОчередногоФайла, ТипТабличногоДокумента.XLSX);

    Сообщить("Файл " + ИмяОчередногоФайла + " успешно сохранен.");
КонецЦикла;

Сообщить("Выгрузка отчета в несколько файлов завершена.");

Данный код позволяет итеративно обрабатывать большой табличный документ, извлекая из него заданные порции данных и сохраняя их в отдельные файлы Excel. Мы используем функцию Мин() для корректной обработки последней порции, которая может содержать меньше строк, чем заданное КоличествоСтрокНа. Тип файла XLSX является современным форматом Excel.

Дополнительные подходы и рекомендации для выгрузки больших отчетов

Хотя вышеописанный метод является прямым ответом на исходный вопрос, при работе с очень большими отчетами, превышающими десятки тысяч или сотни тысяч строк, могут возникать проблемы с производительностью и потреблением памяти. Рассмотрим дополнительные, более оптимизированные подходы, которые расширяют возможности выгрузки.

Выгрузка нескольких табличных документов на разные листы Excel

Начиная с версии 1С 8.3.12, нам стала доступна возможность программно сохранять несколько табличных документов в один файл Excel, при этом каждый табличный документ будет расположен на отдельном листе. Этот подход идеально подходит, если мы хотим не разбить отчет на отдельные файлы, а собрать несколько логически связанных частей или версий одного отчета в единый многостраничный файл Excel.

Для реализации этого метода мы будем использовать объекты ПакетОтображаемыхДокументов и ЭлементПакетаОтображаемыхДокументов. Каждый ЭлементПакетаОтображаемыхДокументов представляет собой отдельный лист в итоговом файле Excel, и мы можем задать ему уникальное имя с помощью свойства Наименование.

Создание пакета документов: Инициализируем объект ПакетОтображаемыхДокументов.
Добавление элементов: Для каждого табличного документа, который мы хотим сохранить как отдельный лист, создаем ЭлементПакетаОтображаемыхДокументов.
Назначение табличного документа и имени листа: Присваиваем свойству Документ элемента наш сформированный ТабличныйДокумент, а свойству Наименование — желаемое имя листа в Excel.
Сохранение пакета: Используем метод Записать() объекта ПакетОтображаемыхДокументов для сохранения всех документов в один файл Excel.

Этот способ является более структурированным и удобным решением для создания многостраничных отчетов в Excel, чем ручное копирование областей, если целью является объединение, а не разделение на отдельные файлы.


// Пример использования для сохранения нескольких табличных документов в один файл Excel на разных листах
ПакетДокументов = Новый ПакетОтображаемыхДокументов;

// Таблица1 - первый отчет или порция
ЭлементПакета1 = ПакетДокументов.Элементы.Добавить();
ЭлементПакета1.Документ = Таблица1;
ЭлементПакета1.Наименование = "Общая сводка";

// Таблица2 - второй отчет или порция
ЭлементПакета2 = ПакетДокументов.Элементы.Добавить();
ЭлементПакета2.Документ = Таблица2;
ЭлементПакета2.Наименование = "Детализация продаж";

// Таблица3 - третий отчет или порция
ЭлементПакета3 = ПакетДокументов.Элементы.Добавить();
ЭлементПакета3.Документ = Таблица3;
ЭлементПакета3.Наименование = "Список товаров";

// Сохраняем пакет в один файл Excel
ПакетДокументов.Записать("ПолныйОтчет_Многолистовой.xlsx", ТипТабличногоДокумента.XLSX);

Сообщить("Многолистовой файл Excel успешно создан.");

Оптимизация производительности при выгрузке очень больших отчетов

Когда мы сталкиваемся с отчетами, объем которых измеряется миллионами строк или десятками мегабайт, стандартные методы выгрузки могут приводить к проблемам "Недостаточно памяти на клиенте" или "Недостаточно памяти на сервере", а также к крайне низкой производительности. В таких случаях нам необходимо применять более продвинутые и ресурсоэффективные подходы.

Рассмотрим два ключевых метода:

Потоковая запись в CSV: Вместо формирования всего табличного документа в памяти, что является затратной операцией, мы можем построчно получать результаты запроса и записывать их напрямую в текстовый файл на сервере. Формат CSV (Comma Separated Values) идеально подходит для этой цели, так как он является простым текстовым представлением табличных данных.
- Принцип работы: Мы выполняем запрос к базе данных, но вместо того чтобы собирать все результаты в одну таблицу значений или табличный документ, мы обрабатываем каждую строку результата запроса по отдельности. Для каждой строки мы формируем текстовую строку, разделяя значения полей запятыми или другими разделителями (например, точкой с запятой для корректного открытия в Excel на русской локали).
- Объекты 1С: Для записи в файл используем объект ЗаписьТекста. Этот объект позволяет нам открыть файл на сервере и построчно записывать в него данные, что минимизирует потребление памяти.
- Преимущества: Значительно снижается потребление оперативной памяти, так как в памяти одновременно находится только одна строка данных. Процесс выгрузки становится гораздо быстрее.
- Недостатки: Отчет будет неформатированным. Все форматирование (ширина колонок, границы, шрифты, выравнивание) придется настраивать уже в Excel вручную или с помощью макросов.
Пример концептуального кода:
```
// Предполагаем, что у нас есть Запрос, который возвращает данные.
// Запрос = Новый Запрос("ВЫБРАТЬ ...");
// РезультатЗапроса = Запрос.Выполнить();
// Выборка = РезультатЗапроса.Выбрать();

ИмяФайлаCSV = КаталогВременныхФайлов() + "Отчет_Потоковый.csv";
Запись = Новый ЗаписьТекста(ИмяФайлаCSV, КодировкаТекста.UTF8, , Истина); // Истина для добавления BOM

// Записываем заголовки
СписокПолей = "Поле1;Поле2;Поле3"; // Замените на реальные имена полей
Запись.ЗаписатьСтроку(СписокПолей);

Пока Выборка.Следующий() Цикл
    СтрокаCSV = Строка(Выборка.Поле1) + ";" + Строка(Выборка.Поле2) + ";" + Строка(Выборка.Поле3);
    Запись.ЗаписатьСтроку(СтрокаCSV);
КонецЦикла;

Запись.Закрыть();
Сообщить("CSV файл успешно создан: " + ИмяФайлаCSV);
```
Архивация и передача: Сформированный CSV файл, даже если он очень большой, можно заархивировать, используя встроенные средства 1С (объект ЗаписьZipФайла). Это позволяет уменьшить размер файла, что критично при его передаче по сети. После архивации файл передается на клиентскую сторону, где пользователь может его распаковать и открыть в Excel.
- Преимущества: Экономия трафика, ускорение передачи, обход ограничений на размер вложений при отправке по почте.
- Объекты 1С: Используем ЗаписьZipФайла для архивации и Файл или другие механизмы для передачи.

Использование программных объектов Excel (COM-объекты)

Еще один способ выгрузки данных, который предоставляет нам максимальный контроль над форматированием и структурой Excel-файла, – это программная работа с Excel через COM-объекты. Мы можем создать экземпляр приложения Excel (Excel.Application) и напрямую управлять его объектами (рабочими книгами, листами, ячейками, диапазонами).

Создание объекта Excel: Инициализируем COM-объект: Excel = Новый COMОбъект("Excel.Application");
Работа с книгами и листами: Открываем новую книгу или существующую, добавляем листы, задаем им имена.
Запись данных: Построчно записываем данные в ячейки, используя методы диапазонов Excel (например, Excel.Cells(Строка, Колонка).Value = Значение).
Форматирование: Применяем форматирование: ширину колонок, шрифты, цвета, границы, объединение ячеек и т.д.
Сохранение и закрытие: Сохраняем книгу и закрываем приложение Excel.

Однако для очень больших объемов данных частые обращения к COM-объектам могут значительно замедлять процесс. Каждое такое обращение — это межпроцессное взаимодействие, которое требует времени. Рекомендуется минимизировать количество таких вызовов, например, записывать данные блоками (диапазонами), а не по одной ячейке. Пример реализации блочной работы можно изучить в обработке для чтения и записи области листа Excel через COM.

Важный момент: Этот метод требует установленного Microsoft Excel на машине, где выполняется код (чаще всего на клиентской). На сервере этот метод может быть нежелателен или вовсе неработоспособен из-за отсутствия GUI и специфики серверных операционных систем.

Подготовка данных в 1С перед выгрузкой

Независимо от выбранного метода выгрузки, ключевым фактором производительности является сама подготовка данных в 1С. Если данные формируются медленно, то и выгрузка будет медленной. Рассмотрим основные рекомендации:

Оптимизация запросов и структуры базы:
- Индексы: Убедитесь, что все поля, используемые в условиях отбора (ГДЕ) и связи таблиц (СОЕДИНЕНИЕ), имеют соответствующие индексы в базе данных. Это критично для скорости выполнения запросов.
- Минимизация данных: Выбирайте только те поля, которые действительно необходимы. Избегайте выборки излишних колонок.
- Агрегация: Используйте агрегатные функции (СУММА, КОЛИЧЕСТВО, МАКСИМУМ, МИНИМУМ) и группировки (СГРУППИРОВАТЬ ПО) непосредственно в запросе 1С. Это позволит базе данных выполнить вычисления более эффективно, чем если бы мы делали это программно в цикле Пока Выборка.Следующий().
- Временные таблицы: Для сложных запросов с несколькими этапами обработки данных используйте временные таблицы. Это может значительно улучшить читаемость запроса и его производительность, позволяя оптимизатору 1С более эффективно планировать выполнение.
Упрощение логики отчетов:
- Расчеты: Сложные отчеты с большим количеством программных расчетов после получения данных запросом могут значительно снижать производительность. Постарайтесь максимально перенести расчеты в запрос или выполнить их на более ранних этапах формирования данных.
- Объем данных: Минимизируйте объем данных, необходимых для формирования отчета. Возможно, нет необходимости выгружать все исторические данные за 10 лет, если требуются только данные за последний год.
Консоль запросов:

Стандартная обработка "Консоль запросов" является незаменимым инструментом для разработчика 1С. Она позволяет нам быстро получать данные в виде плоских таблиц по заданным запросам и сохранять их в Excel. Это может быть очень удобно для быстрой выгрузки данных для дальнейшего анализа, когда нет необходимости в сложной структуре отчета 1С.

Постобработка в Excel с помощью Power Query

Если наша основная цель выгрузки — это дальнейший анализ данных в Excel, и мы сталкиваемся с тем, что отчеты из 1С приходят в неудобном для анализа формате (например, с объединенными ячейками, сложной шапкой или многоуровневой структурой), то после стандартной выгрузки (даже в формате CSV или простого Excel-файла) мы можем использовать встроенный инструмент Excel — Power Query.

Power Query позволяет нам:

Импортировать данные: Он может импортировать данные из различных источников, включая текстовые файлы, Excel-файлы, базы данных и веб-источники.
Преобразовать данные: Мы можем выполнить широкий спектр преобразований: разделить столбцы, удалить лишние строки/столбцы, изменить типы данных, объединить таблицы, развернуть сводные таблицы и многое другое. Это позволяет быстро преобразовать данные из отчета 1С в плоский формат, идеально подходящий для анализа и построения сводных таблиц.
Обновить данные: После настройки всех шагов преобразования, мы можем просто обновить запрос Power Query, и он повторно загрузит и преобразует новые данные из исходного файла 1С, что экономит массу времени при регулярной работе с отчетом.

Использование Power Query переносит часть логики обработки данных из 1С в Excel, что может быть удобно для бизнес-пользователей, которые хорошо владеют Excel, но не имеют доступа к конфигурированию 1С. Это позволяет им самостоятельно адаптировать данные под свои аналитические нужды, не прибегая к помощи программистов 1С. Для быстрого устранения типичных недостатков форматирования в выгруженных отчетах также подойдет надстройка для Excel с инструментами коррекции данных 1С.

Таким образом, мы видим, что задача выгрузки отчетов в Excel с разбивкой на порции может быть решена несколькими способами, от простого программного разделения табличного документа до комплексных подходов с оптимизацией производительности и постобработкой данных. Выбор конкретного метода зависит от объема данных, требований к форматированию, наличия ресурсов и целевого назначения выгружаемых файлов.