Что делает процедура выгрузки:
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].Имя,ДиалогФыбораФайла.ПолноеИмяФайла);
УдалитьФайлы(ПутьФайлаАрхива);
КонецЕсли;
КонецПроцедуры
Функция ПреобразоватьПараметрыЗапросаВСтруктуруДвухМассивов(ПараметрыЗапроса)
СтруктураМассивов = Новый Структура;
НазваниеПараметров = Новый Массив;
ЗначениеПараметров = Новый Массив;
Для Каждого ПараметрЗапроса Из ПараметрыЗапроса Цикл
НазваниеПараметров.Добавить(ПараметрЗапроса.Ключ);
Если ТипЗнч(ПараметрЗапроса.Значение) = Тип("СписокЗначений") Тогда
ЗначениеПараметров.Добавить(ПреобразоватьСЗВМассив(ПараметрЗапроса.Значение));
Иначе
ЗначениеПараметров.Добавить(ПараметрЗапроса.Значение);
КонецЕсли;
КонецЦикла;
СтруктураМассивов.Вставить("НазваниеПараметров", НазваниеПараметров);
СтруктураМассивов.Вставить("ЗначениеПараметров", ЗначениеПараметров);
Возврат СтруктураМассивов;
КонецФункции
Функция ПреобразоватьСЗВМассив(СписокЗначений)
Массив = Новый Массив;
Для Каждого ЭлементСЗ Из СписокЗначений Цикл
Массив.Добавить(ЭлементСЗ.Значение);
КонецЦикла;
Возврат Массив;
КонецФункции
Функция ПолучитьМассивИменПолейПоТекстуЗапроса(ТекстЗапроса) Экспорт
П_О = Новый ПостроительОтчета;
П_О.Текст = ТекстЗапроса;
П_О.ЗаполнитьНастройки();
МассивИменПолейЗапроса = Новый Массив;
Для Каждого ИмяПоля Из П_О.ВыбранныеПоля Цикл
МассивИменПолейЗапроса.Добавить(ИмяПоля.Имя);
КонецЦикла;
Возврат МассивИменПолейЗапроса;
КонецФункции
