gifts2017

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

Опубликовал Andrey Matveev (matveev.andrey.v) в раздел Обмен - Загрузка и выгрузка в Excel

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

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

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

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

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

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

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

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

 

 

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Валерий К (klinval) 30.11.15 09:51
ИмяАрхива = ПолучитьИмяВременногоФайла("zip");
...    
    ФайлАрхива = Новый ДвоичныеДанные(ИмяАрхива);
        
    ХЗ = Новый ХранилищеЗначения(ФайлАрхива);
    
    УдалитьФайлы(ИмяАрхива);
...Показать Скрыть

Как то пробовал удалить временный файл, вылетает с ошибкой:
На сервере 1С:Предприятия произошла неисправимая ошибка. Приложение будет закрыто

У вас не так? Какая версия платформы?
2. Сергей К. (eskor) 30.11.15 15:02
Хорошая идея, осталось вставить строку запуска Excel на открытие файла и расстановку разделителей. Конечные пользователи не всегда любят возиться с форматированием.
Кстати, можешь попробовать выгружать в DBF, там, конечно, есть свои ограничения, но форматировать колонки не надо, плюс платформа напрямую с этим типом работает, Да и сам Excel открывает DBF без лишних вопросов. Кстати, можешь и оценить, что быстрее и удобнее.
3. Р С (Dach) 01.12.15 08:55
Есть сложный отчет на СКД, возвращающий на экран не плоскую, а с группировками, таблицу. Колонок много, строк тоже (несколько сотен тысяч). Ваш код как-то может помочь? То есть я его встраиваю в отчет, вешаю вызов на кнопку "Сохранить в csv"?
4. Сергей К. (eskor) 01.12.15 13:52
(3) Dach, с группировками вряд ли выгрузишь, из csv импортируется как "текст с разделителями", если группировки не нужны - то тогда хоть как выгружай, хотя я сторонник таблиц, а не текстов. Попробуй как в примере справки в DBF сливать игнорируя группировки.
xB = Новый xBase

xB.Поля.Добавить("CODE", "S", 5);
xB.Поля.Добавить("NAME", "S", 40);
xB.Поля.Добавить("COST", "N", 14, 2);

xB.Добавить();
xB.CODE = "00004";
xB.NAME = "Клавиатура";
xB.COST = 210.50;
xB.Записать();

xB.СоздатьФайл("c:\test.dbf");
...Показать Скрыть
5. q_i 01.12.15 15:03
Пара замечаний (надеюсь, конструктивных):
1 Вместо передачи двух массивов (например, МассивНазванийПараметров, МассивЗначенийПараметров) напрашивается передача Структуры.
2. Не понял зачем нужно получать МассивИменПолейЗапроса на клиенте (да ещё через Построитель). Можно передать МассивИменПолейЗапроса как есть на сервер, а там после выполнения запроса выполнить что-то вроде:
	Если МассивИменПолейЗапроса = Неопределено Тогда
		МассивИменПолейЗапроса = Новый Массив;
		Для Каждого Колонка Из РезультатЗапроса.Колонки Цикл
			МассивИменПолейЗапроса.Добавить(Колонка.Имя);
		КонецЦикла;
	КонецЕсли;
...Показать Скрыть

6. Яков Коган (Yashazz) 01.12.15 16:35
Маловато вы способов перепробовали. Есть ещё сохранение сразу в xml понятного экселю вида, и есть работа с COMSafeArray (там тоже теряется форматирование, но скорость замечательно высокая). Ваш способ - ну, так можно, но это не супер-пупер.
7. rasswet (rasswet) 02.12.15 08:33
про ADO это по моему в 6м комменте было
8. Serj (Serj1C) 02.12.15 15:51
А если отчет на столько большой, что компьютер его формирует 3 часа, то сколько человек его будет читать?
Пользователь не в состоянии поглотить столько информации. Может следует уменьшить детализацию?
Проблему нужно решать с "психологической" точки зрения, а не программной или аппаратной.
9. Andrey Matveev (matveev.andrey.v) 03.12.15 11:07
(2) eskor, спасибо за предложения, обязательно вставлю ЗапуститьПриложение(). Насчет форматирования не совсем вас понял, пользователи просто открывают Экселем, и работают а сохраняют уже в xlsx. А насчет выгрузки в DBF идея интересная, только возможно ли потоковая запись в этот формат. Каким бы вы способом предложили мне это сделать?
10. Andrey Matveev (matveev.andrey.v) 03.12.15 11:09
(3) Dach, к сожалению нет, эти процедуры выгружают только плоскую таблицу напрямую из запроса, к СКД так просто не прикрутишь
11. Andrey Matveev (matveev.andrey.v) 03.12.15 11:16
(5) q_i, спасибо, очень конструктивные замечания
12. Andrey Matveev (matveev.andrey.v) 03.12.15 15:42
(8) Serj1C, дело в том что пользователи используют эти огромные выборки в экселе для того что бы анализировать их средствами экселя в определенных разрезах, сворачивают их, делают сводные данный, используют фильтры. Или например для сравнения больших массивов данных из разных баз посредством функции ВПР(). Применений куча. Вы видимо мало работаете с 1с
13. Andrey Matveev (matveev.andrey.v) 03.12.15 15:45
(6) Yashazz, согласен, маловато, все что нашел в интернете. Предложенная вами идея выгрузки в xml файл мне очень понравилась, его действительно можно так же потоково сформировать и решить проблему форматирования. Жалко только что этот формат не поддерживает разворачиваемые группировки, которые создает 1с. Если бы поддерживал я бы обязательно на него переделал.
Про Com-safeArray немного не понял, не вижу возможности через него потоково формировать файл, или вы имеете ввиду сформировать весь эксель и потом разом записать?
14. Сергей К. (eskor) 03.12.15 17:53
(9) matveev.andrey.v, запись в dbf не сильно отличается от записи в текстовый файл, на скорость я никогда не жаловался. В далекие времена, когда перенос данных через xml был в зачаточном состоянии, только так данные и гонял. Главное удобство было в том, что данные как раз в Excel и проверялись на корректность. Вроде как заголовки колонок должны быть на латинице, хотя давно я с dbf не заморачивался.
15. Сергей Огородников (Serg O.) 31.12.15 09:21
самый простой и быстрый обмен - через Буфер обмена
16. Andrey Matveev (matveev.andrey.v) 12.01.16 10:16
(15) Serg O., Не совсем понял о чем вы. Здесь вроде как речь идет о выгрузке в файл
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа