Быстрая выгрузка больших плоских отчетов в Excel

Что делает процедура выгрузки:

1. Клиентская процедура получает в качестве входных параметров текст запроса и структуру параметров, и имена колонок(необязательный) 

2. Серверная процедура выполняет запрос и получая построчно результат запроса записывает его потоково в текстовый файл через объект типа ЗаписьТекста, не держа его в оперативной памяти

3. На сервере у сформированного текстового файла изменяется расширение на csv и он запаковывается в архив

4. Клиентская процедура получает файл распаковывает его и открывает файл через Excel. 

Описание

Наверное, всех раздражает долгая выгрузка сформированных отчетов в Excel через «Сохранить как» для последующего анализа. А так же кучу недовольства вызывает когда оставляешь такую выгрузку на ночь и утром видишь сообщение «Недостаточно памяти на клиенте» или «Недостаточно памяти на сервере». Как по мне так самое узкое место во всей 1С. Я перепробовал много вариантов:

1. Сохранение в MXL и конвертация в Excel средствами программы «1С Работа с файлами»

2. Сохранение в HTML4 и открытие этого файла через Excel

3. Сохранение через COM-объект Excel напрямую из запроса в файл на клиенте

Предлагаемый мною способ по скорости первышает самый быстрый из этих трех (HTML4) примерно в полтора — два раза, а стандартное сохранение из 1С в Excel в 8 раз(таблица 65 колонок 240 000 строк, заполнение 90%, за 1 час 11 минут, раньше она выгружалась около 6 часов). Сразу оговорюсь что оценки времени могут сильно отличатся, поскольку это зависит от производительности и загруженности сервер и клиента, а так же сетевого соединения.   

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

Но общеизвестно что сформированный 1с 8 табличный документ занимает очень много оперативной памяти. Например таблица с 65 колонками и 240 000 строк, у меня съела 900 Mб памяти. В таком виде конечно никакой сервер не справится. Поэтому возникла идея, записывать файл построчно, без накопления в оперативной памяти. И для этого как нельзя лучше подходит формат файла с разделителями CSV, который можно открыть через Excel и поработав с ним , сохранить в xlsx. CSV можно формировать потоково, так как это обычный текстовый файл. Есть ряд граблей при работе с этим форматом, такие как удаление символов переноса строки и символа «;», а так же то что Excel удаляет в строках левые нули, превращая в число. Все эти проблемы я в приведенных ниже процедурах решил. 

Решение выкладываю в виде шести процедур в двух общих модулях

Первый модуль, компилируемый только на сервере

Функция ВыгрузитьВФайлЧерезСервер(ЗапросТекст, МассивНазванийПараметров, МассивЗначенийПараметров, МассивИменПолейЗапроса, МассивИменПолейОтчета = Неопределено) Экспорт

Запрос = Новый Запрос;
Запрос.Текст = ЗапросТекст;
ПорядковыйНомерПараметра = 0;
Пока ПорядковыйНомерПараметра <= МассивНазванийПараметров.Количество() - 1 Цикл
НазваниеПараметра = МассивНазванийПараметров[ПорядковыйНомерПараметра];
ЗначениеПараметра = МассивЗначенийПараметров[ПорядковыйНомерПараметра];
Запрос.УстановитьПараметр(НазваниеПараметра, ЗначениеПараметра);
ПорядковыйНомерПараметра = ПорядковыйНомерПараметра + 1;
КонецЦикла;

ВремяПередЗапросом = ТекущаяДата();

Результат = Запрос.Выполнить();
ВремяПослеЗапроса = ТекущаяДата();

Выборка = Результат.Выбрать();

КаталогВР = КаталогВременныхФайлов();
ИмяФайла = ПолучитьИмяВременногоФайла("csv");
ЗТ = Новый ЗаписьТекста(ИмяФайла);

//Потоковое чтение


Сообщить(Строка(ТекущаяДата()) + " - Окончание выполнения запроса к серверу(Запрос выполнен за " +Строка(ВремяПослеЗапроса - ВремяПередЗапросом) + " секунд)");




СтрокаФайла = "№;";
Если МассивИменПолейОтчета <> Неопределено Тогда
ПерваяСтрокаФайла = МассивИменПолейОтчета;
Иначе
ПерваяСтрокаФайла = МассивИменПолейЗапроса;
КонецЕсли;
Для Каждого ИмяПоля Из ПерваяСтрокаФайла Цикл

СтрокаФайла = СтрокаФайла + ОбработатьТекст(ИмяПоля) + ";";
КонецЦикла;

ЗТ.ЗаписатьСтроку(СтрокаФайла);


Индекс = 0;
Пока Выборка.Следующий() Цикл

СтрокаФайла = Строка(Индекс) + ";";

Для Каждого ИмяПоля Из МассивИменПолейЗапроса Цикл

ТестЯчейки = ОбработатьТекст(Выборка[ИмяПоля]);

СтрокаФайла = СтрокаФайла + ТестЯчейки + ";";
КонецЦикла;

ЗТ.ЗаписатьСтроку(СтрокаФайла);
Если Индекс/10000 = Цел(Индекс/10000) и Индекс <> 0 Тогда
Сообщить("Выгружено " + Индекс + " строк");
КонецЕсли;
Индекс = Индекс + 1;

КонецЦикла;
ЗТ.Закрыть();
Сообщить("Выгружено всего " + Индекс + " строк");

ИмяАрхива = ПолучитьИмяВременногоФайла("zip");
ЗаписьZIP = Новый ЗаписьZipФайла(ИмяАрхива);

// Добавим необходимые файлы в архив
ЗаписьZIP.Добавить(ИмяФайла);

// Запишем архив на диск
ЗаписьZIP.Записать();

УдалитьФайлы(ИмяФайла);

ФайлАрхива = Новый ДвоичныеДанные(ИмяАрхива);

ХЗ = Новый ХранилищеЗначения(ФайлАрхива);
//Сообщить(Строка(ТекущаяДата()) + " - Окончание формирования файла на сервере");

УдалитьФайлы(ИмяАрхива);


Возврат ХЗ;


КонецФункции

Функция ОбработатьТекст (Знач ТестЯчейки)

ТестЯчейки = СтрЗаменить(ТестЯчейки, ";", " ");
ТестЯчейки = СтрЗаменить(ТестЯчейки, Символы.ПС, " ");
ТестЯчейки = СтрЗаменить(ТестЯчейки, """", " ");
ТестЯчейки = СтрЗаменить(ТестЯчейки, "'", " ");
ТестЯчейки = СтрЗаменить(ТестЯчейки, Символ(160), " ");

Возврат ТестЯчейки;
КонецФункции

Второй модуль, компилируемый исключительно на клиенте

Процедура ВыгрузитьИзЗапросаВCSVНаКлиенте(ЗапросТекст, ЗапросПараметры, МассивИменПолейЗапроса = Неопределено, МассивИменПолейОтчета = Неопределено) Экспорт

ДиалогФыбораФайла = Новый ДиалогВыбораФайла(РежимДиалогаВыбораФайла.Сохранение);

ДиалогФыбораФайла.Фильтр                  = "CSV документ(*.csv)|*.csv";
ДиалогФыбораФайла.Заголовок                 = "Выберите файл для выгрузки данных";
ДиалогФыбораФайла.ПредварительныйПросмотр   = Ложь;
ДиалогФыбораФайла.ПолноеИмяФайла = Лев(ТекущаяДата(), 10);


Если МассивИменПолейЗапроса = Неопределено Тогда
МассивИменПолейЗапроса = ПолучитьМассивИменПолейПоТекстуЗапроса(ЗапросТекст);
КонецЕсли;

Путь = "";
Если ДиалогФыбораФайла.Выбрать() Тогда


//Выгружаем на сервере в файл
//Так как сейчас передаем данные с клиента на сервер, есть ограничения, запрос нельзя, список значения нельзя, массив(а так же массив массивов) и простые типы можно
СтруктураПараметров = ПреобразоватьПараметрыЗапросаВСтруктуруДвухМассивов(ЗапросПараметры);
ХЗДвДанные = БольшиеОтчетыСервер.ВыгрузитьВФайлЧерезСервер(ЗапросТекст, СтруктураПараметров.НазваниеПараметров, СтруктураПараметров.ЗначениеПараметров, МассивИменПолейЗапроса, МассивИменПолейОтчета);

Путь = ДиалогФыбораФайла.Каталог;
ПутьФайлаАрхива = Путь + "ЖРДС_Выгрузка.zip";
ХЗДвДанные.Получить().Записать(ПутьФайлаАрхива);

ЧтениеZipФайла = Новый ЧтениеZipФайла(ПутьФайлаАрхива);
ЧтениеZipФайла.ИзвлечьВсе(Путь);
ПереместитьФайл(Путь + ЧтениеZipФайла.Элементы[0].Имя,ДиалогФыбораФайла.ПолноеИмяФайла);

УдалитьФайлы(ПутьФайлаАрхива);

КонецЕсли;

КонецПроцедуры

Функция ПреобразоватьПараметрыЗапросаВСтруктуруДвухМассивов(ПараметрыЗапроса)

СтруктураМассивов = Новый Структура;
НазваниеПараметров = Новый Массив;
ЗначениеПараметров = Новый Массив;

Для Каждого ПараметрЗапроса Из ПараметрыЗапроса Цикл
НазваниеПараметров.Добавить(ПараметрЗапроса.Ключ);
Если ТипЗнч(ПараметрЗапроса.Значение) = Тип("СписокЗначений") Тогда
ЗначениеПараметров.Добавить(ПреобразоватьСЗВМассив(ПараметрЗапроса.Значение));
Иначе
ЗначениеПараметров.Добавить(ПараметрЗапроса.Значение);
КонецЕсли;
КонецЦикла;

СтруктураМассивов.Вставить("НазваниеПараметров", НазваниеПараметров);
СтруктураМассивов.Вставить("ЗначениеПараметров", ЗначениеПараметров);

Возврат СтруктураМассивов;

КонецФункции

Функция ПреобразоватьСЗВМассив(СписокЗначений)

Массив = Новый Массив;

Для Каждого ЭлементСЗ Из СписокЗначений Цикл
Массив.Добавить(ЭлементСЗ.Значение);
КонецЦикла;
Возврат Массив;

КонецФункции

Функция ПолучитьМассивИменПолейПоТекстуЗапроса(ТекстЗапроса) Экспорт

П_О = Новый ПостроительОтчета;
П_О.Текст = ТекстЗапроса;

П_О.ЗаполнитьНастройки();
МассивИменПолейЗапроса = Новый Массив;


Для Каждого ИмяПоля Из П_О.ВыбранныеПоля Цикл
МассивИменПолейЗапроса.Добавить(ИмяПоля.Имя);
КонецЦикла;

Возврат МассивИменПолейЗапроса;
КонецФункции