Каталог решений - Быстрая выгрузка больших плоских отчетов в 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].Имя,ДиалогФыбораФайла.ПолноеИмяФайла);
		
		УдалитьФайлы(ПутьФайлаАрхива);
		
	КонецЕсли;                                                     
	
КонецПроцедуры

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

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

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

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

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