При работе с большими объемами данных в 1С программисты и системные администраторы часто сталкиваются с аппаратными и программными ограничениями. Одной из классических задач является выгрузка реестров, остатков или оборотов, количество строк в которых превышает 1 048 576 — именно таков жесткий лимит строк на одном листе в современных версиях Microsoft Excel (формат .xlsx). Если же использовать старый формат .xls, ограничение еще жестче — всего 65 536 строк.
В этой статье мы подробно разберем, как эффективно выгрузить миллионы строк, не вызвав при этом "зависания" сервера или переполнения оперативной памяти (в этом поможет быстрая выгрузка больших пакетов данных из запросов 1С). Мы проанализируем как классические методы разделения данных внутри 1С, так и современные подходы с использованием внешних инструментов аналитики.
Рассмотрим стандартный механизм, который часто используют разработчики: создание объекта ТабличныйДокумент, заполнение его данными из запроса и метод Записать(). При достижении миллиона строк возникают две критические проблемы:
ТабличныйДокумент хранит данные в памяти вместе с форматированием. Выгрузка 2-3 миллионов строк может легко поглотить 16-32 Гб оперативной памяти, что приведет к аварийному завершению сеанса (ошибка Out of Memory).Выясним, какие существуют пути решения этой задачи.
Самый надежный способ — это разбить один огромный результат запроса на несколько частей (порций) по 1 000 000 строк и каждую порцию сохранять в отдельный файл или на отдельный лист — для этой задачи есть обработка автоматического разделения выгрузки из 1С на части. Для этого нам необходимо пронумеровать строки непосредственно в тексте запроса.
В современных версиях платформы (начиная с 8.3.20) появилась штатная функция АвтоНомерЗаписи(). Проанализируем пример кода, который позволяет пронумеровать строки и выбрать нужный диапазон:
// Пример запроса с использованием автонумерации для платформы 8.3.20+
ТекстЗапроса =
"ВЫБРАТЬ
| АвтоНомерЗаписи() КАК НомерСтроки,
| ХозрасчетныйОстатки.Счет,
| ХозрасчетныйОстатки.Субконто1,
| ХозрасчетныйОстатки.СуммаОстаток
|ПОМЕСТИТЬ ВТ_Данные
|ИЗ
| РегистрБухгалтерии.Хозрасчетный.Остатки(&Дата) КАК ХозрасчетныйОстатки
|;
|
|ВЫБРАТЬ * ИЗ ВТ_Данные ГДЕ НомерСтроки МЕЖДУ &Начало И &Конец";
Если ваша версия платформы ниже 8.3.20, нам придется использовать временные таблицы для создания нумерации. Рассмотрим этот алгоритм. Мы помещаем данные во временную таблицу, а затем соединяем таблицу саму с собой по условию "больше", чтобы подсчитать количество записей. Однако на миллионах строк это будет работать крайне медленно. В таком случае лучше использовать перебор ВыборкаИзРезультатаЗапроса в цикле с простым счетчиком.
Разберем по шагам, как выгрузить данные в несколько файлов без создания гигантских объектов в памяти:
Запрос = Новый Запрос(ТекстВыбораДанных);
РезультатЗапроса = Запрос.Выполнить();
Выборка = РезультатЗапроса.Выбрать();
ФайлНомер = 1;
СтрокВФайле = 0;
МаксСтрок = 1000000;
Пока Выборка.Следующий() Цикл
Если СтрокВФайле = 0 Тогда
// Создаем новый документ для очередной порции
ТабДок = Новый ТабличныйДокумент;
КонецЕсли;
// Заполнение строк (лучше использовать области для скорости)
// ... логика заполнения данных ...
СтрокВФайле = СтрокВФайле + 1;
Если СтрокВФайле >= МаксСтрок Тогда
ТабДок.Записать("Выгрузка_" + ФайлНомер + ".xlsx", ТипФайлаТабличногоДокумента.XLSX);
ФайлНомер = ФайлНомер + 1;
СтрокВФайле = 0;
// Очищаем память
ТабДок = Неопределено;
КонецЕсли;
КонецЦикла;
// Записываем остаток, если он есть
Если СтрокВФайле > 0 Тогда
ТабДок.Записать("Выгрузка_" + ФайлНомер + ".xlsx", ТипФайлаТабличногоДокумента.XLSX);
КонецЕсли;
Если главная цель — передать данные для дальнейшей обработки (например, аудиторам или в другую базу), то формат Excel является избыточным. CSV (Comma-Separated Values) — это текстовый формат, который практически не имеет ограничений по объему и открывается любым табличным процессором.
Рассмотрим, почему это быстрее. При записи в CSV мы используем объект ЗаписьТекста, который записывает данные порциями сразу на диск (потоковая запись), не накапливая их в оперативной памяти.
Важный нюанс: Чтобы Excel корректно понимал кодировку (кириллицу) и разделял текст по столбцам, используйте "BOM" и правильный разделитель. Проанализируем пример кода:
ПутьКФайлу = "C:\Export\Data.csv";
// Используем кодировку UTF-8 с BOM для корректного открытия в Excel
ЗаписьТекста = Новый ЗаписьТекста(ПутьКФайлу, КодировкаТекста.UTF8, Символы.ПС, Ложь);
// Записываем заголовок (разделитель - точка с запятой для русской локализации)
ЗаписьТекста.ЗаписатьСтроку("Счет;Субконто;Сумма");
Выборка = РезультатЗапроса.Выбрать();
Пока Выборка.Следующий() Цикл
СтрокаДанных = Строка(Выборка.Счет) + ";" +
Строка(Выборка.Субконто1) + ";" +
Формат(Выборка.СуммаОстаток, "ЧГ=0");
ЗаписьТекста.ЗаписатьСтроку(СтрокаДанных);
КонецЦикла;
ЗаписьТекста.Закрыть();
Такой метод позволяет выгружать 5-10 миллионов строк за считанные минуты, при этом потребление памяти процессом 1С будет минимальным.
Если данные нужны именно в Excel, мы можем не "выталкивать" их из 1С, а "забирать" их со стороны Excel. Современный Excel включает инструмент Power Query, который умеет работать с источниками данных объемом в десятки миллионов строк через так называемую Модель данных — для выгрузки таких массивов в аналитические СУБД разработан инструмент переноса данных 1С во внешние базы данных.
Разберем этот алгоритм:
Результат: Excel загрузит миллионы строк во внутреннее хранилище (сжатое). На самом листе данные могут и не отображаться, но вы сможете построить по ним Сводную таблицу, которая будет мгновенно обрабатывать все эти миллионы записей. Это идеальный вариант для аудиторов.
Для продвинутых разработчиков существует способ формирования структуры файла Excel вручную через XML. Объект ТабличныйДокумент тратит много времени на расчет высоты строк, ширины колонок и стилей. Если формировать XML-структуру самостоятельно через ЗаписьXML, скорость возрастает в разы.
Файл формата .xml, сформированный по схеме SpreadsheetML, открывается в Excel как полноценная таблица. В этом случае также стоит придерживаться стратегии разделения на несколько файлов, если общее количество строк превышает лимит листа, так как Excel при открытии тяжелого XML все равно попытается "запихнуть" данные на листы.
Проанализировав ситуацию, мы видим, что выбор метода зависит от конечной цели:
CSV с потоковой записью — для этого разработан универсальный экспорт из 1С в CSV и Excel.Power Query и подключение к 1С через OData — с этой задачей справится модуль автоматического экспорта 1С во внешние СУБД.Помните, что работа с большими данными требует осторожности: всегда ограничивайте выборку в запросе только необходимыми полями и избегайте использования ПостроительОтчета для задач свыше 500 000 строк, так как он менее гибок в управлении памятью, чем прямой обход выборки.