gifts2017

Скорость закачки с EXCEL при работе с большими файлами

Опубликовал Юрий Батяев (ybatiaev) в раздел Администрирование - Оптимизация БД (HighLoad)

Поставлена задача уменьшить время загрузки данных с EXCEL. Пока ждал ответа от разработчиков ядра, сам начал копать всё, что угодно.
ЗАДАЧА решена, с чем спешу поделиться.

1. Файл должен быть закачан в МАССИВ весь сразу!!!!!! У меня 27000 строк за 0,1-0,5 сек. СОМ-объект можно сразу отключить(закрыть). Замечание: метод VALUE отрабатывает в 5 раз быстрее, чем TEXT, ввиду того, что не преобразует значение в текст;

 

ЗначениеСтр = ExcelЛист.Range(ExcelЛист.Cells(1,1), ExcelЛист.Cells(СтрокаПо,КолонкаПО));
Данные = ЗначениеСтр.Value.Выгрузить();
 

2. Если его необходимо загрузить в ТабличныйДокумент, то загрузку можно сделать или по строкам, или по колонкам. По времени загрузки строки или колонки грузятся почти одинаково, но строк больше же. Так вот надо грузить по тому, что меньше. У меня колонок 20, а строк 27000. В итоге, приблизительно в 100 раз быстрее загрузился в ТабличныйДокумент при использовании метода "по колонкам" (строка первая). А можно и не грузить в ТД, не тратить время, а начать обрабатывать сразу МАССИВ.

Выше написанное значит, что распространенный в инете пример работы с EXCEL с перебором каждой ячейки работает намного медленнее, чем вышеописанный. Постараюсь выложить на Инфостарт решение

Для Column = КолонкаС По КолонкаПО Цикл   
   Для Row = СтрокаС По СтрокаПО Цикл    
        ТД.Область("R" + Формат(Row, "ЧГ=")+"C" + Формат(Column, "ЧГ=")).Текст = Данные[Column-1][Row-1];   
   КонецЦикла;  
КонецЦикла;
 
 

Если такой метод не совсем корректный - напишите, плиз. Может, есть и еще более быстрый, к примеру, не понятный пока для меня метод, используемый в ядре 1С без использования СОМ-объектов вообще: ФАЙЛ/ОТКРЫТЬ/*.XLS*

См. также

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

Комментарии

1. Вадим Титов (flyDrag) 09.06.15 14:17
Было бы хорошо увидеть этот пример в готовой обработке
2. Сергей Самарин (Samarin) 09.06.15 14:38
1. Есть новая фишка в платформе 8.3.6. Попробуйте ее посмотреть.

ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать("C:\My Documents\Таблица1.mxl");

Описание:
Считывает табличный документ из файла.
Позволяет считывать табличный документ из файла табличного документа Microsoft Excel 97 - 2010 ( *.xls и *.xlsx) или электронной таблицы OpenOffice Calc ( *.ods).

2. Посмотреть и попробовать применить многопоточную обработку:
http://infostart.ru/public/306865/

Если изыскания будут успешны - с меня плюс, лайк или что тут раздают))
3. hobo alone (alonehobo) 09.06.15 14:42
ТД.Область("R" + Формат(Row, "ЧГ=")+"C" + Формат(Column, "ЧГ=")).Текст = Данные[Column-1][Row-1];

Можно просто использовать числа для указания адреса ячейки, без лишнего форматирования:
ТД.Область(Row, Column).Текст = Данные[Column-1][Row-1];
4. Роман Ложкин (webester) 09.06.15 16:00
Неудобно прерывать вашу радость от найденного решения, но... <Зануда MODE> поиском пользоваться не пробовали?</Зануда MODE>
http://infostart.ru/public/20090/ с достаточно бурными обсуждением, замерами и сравнениями в комментариях.
artfa; fvadim; ram3; qwinter; +4 Ответить 2
5. Юрий Батяев (ybatiaev) 09.06.15 16:02
(2) Samarin, Этот метод (ТД.Прочитать()) и ранее был и в старых версиях, Но он работает только в толстом клиенте. А у всех сейчас уже тонкие
6. Юрий Батяев (ybatiaev) 09.06.15 16:09
(3) alonehobo, это был тестовый пример... спешил поделиться
А функция ФОРМАТ() у меня использовалась для считывания значения ячеек, а некоторые ячейки имели число с разделителями триад пробелом.
Раньше использовал метод не VALUE (это работает так, как надо) , а TEXT, а он преобразовывал число 1 333 в строку "1 333". Ну далее Вы понимаете...

ну вот на старых обработках и экспериментировал... забыл убрать это убожество :-)))
7. Юрий Батяев (ybatiaev) 09.06.15 16:16
(4) webester,
спасибо за замечание, по ссылкам я "ходил" ... но вот не нашел того, что записывать в ТД быстрее оказалось по колонкам (первый цикл), а потом по строкам (цикл внутри). Если поменять это, т.е. сначала взять строку и разобрать её по колонкам, потом другую... работает на файлах с большим количеством записей намного дольше. ЭТОГО НЕ НАШЕЛ. С чем и поделился. Ну может всего прочитать невозможно или промелькнуло мимо за ненадобностью тогда.
8. Юрий Батяев (ybatiaev) 09.06.15 16:20
(2) Samarin, Про потоки ИНТЕРЕСНО. Мне это еще со старых времен работы с С++ известно было... почитаю!!! Спасибо!
9. Сергей Самарин (Samarin) 09.06.15 16:38
(5) ybatiaev, Во-первых, в изменениях к 8.3.6 по данному поводу написано ясно, что в предыдущих версиях подобной возможности не было. Во-вторых, правильная работа в клиент-серверном режиме и через веб-клиент подразумевает правильный подход к работе с файлами: получение файлов осуществляется на клиентской части, далее данные передаются на сервер, обработка данных производится на серверной стороне.
ybatiaev; +1 Ответить
10. Павел Алексеенко (qwinter) 09.06.15 16:48
(4) webester, ну так чукча не читатель, чукча писатель © )))))
ybatiaev; +1 Ответить
11. Павел Алексеенко (qwinter) 09.06.15 16:53
(7) ybatiaev,
но вот не нашел того, что записывать в ТД быстрее оказалось по колонкам (первый цикл), а потом по строкам (цикл внутри).
а построителем или СКД еще быстрее))
12. Яков Коган (Yashazz) 09.06.15 17:17
Батенька, ну вы уж поиском-то и правда, воспользуйтесь... А ещё про обмен через COMSafeArray и ADO почитайте)
13. Михаил Гусев (Идальго) 09.06.15 17:49
27000 строк это вроде совсем немного (скорее это очень мало). Странно, что у вас оно слишком долго читает.
14. Вадик Лавин (LavinVadik) 10.06.15 03:51
вот готовое решение на v7, прикуруть к 8-ке не стоставит большого труда
http://infostart.ru/public/329372/
15. Павел Алексеенко (qwinter) 10.06.15 08:23
(14) LavinVadik, на восьмерке давно есть в разы более универсальные.
16. BAZIL BAZIL (wbazil) 10.06.15 08:26
довольно давно пользуюсь функцией

// Функция читает лист их xls файла и загружает содержимое в таблицу значений
Функция ПолучитьТаблЗначИзЛистаXLS(ТаблЗнач = Неопределено, прИмяКолонки = "Колонка", ИмяФайла, прНомерЛистаExcel = Неопределено, ЛистЭксель = Неопределено, НомерПервойСтроки = 1, НомерПервойКолонки = 1, ВсегоСтрок = 0, ВсегоКолонок = 0) Экспорт

	Если ТаблЗнач = Неопределено Тогда
		ТаблЗнач =  Новый ТаблицаЗначений;
	КонецЕсли;

	
	Если ЛистЭксель = Неопределено Тогда
		Попытка
			Excel = Новый COMОбъект("Excel.Application");
		Исключение
			Сообщить("Не удалось подключиться к MS Excel");
			Возврат ТаблЗнач;
		КонецПопытки;
		Попытка
			Состояние("Обработка файла Microsoft Excel...");
			Excel.DisplayAlerts = 0;
			Excel.WorkBooks.Open(ИмяФайла);
		Исключение
			Сообщить("Не удалось прочитать файл (" + ОписаниеОшибки() + ")");
			Возврат ТаблЗнач;
		КонецПопытки;
		лпСписокЛистов = Новый СписокЗначений;
		Попытка
			Если прНомерЛистаExcel = Неопределено Тогда
				лпКоличествоЛистов = Excel.Sheets.Count;
				Если лпКоличествоЛистов = 0 Тогда
					Сообщить("Пусто");
					Возврат ТаблЗнач;
				КонецЕсли;
				Если лпКоличествоЛистов > 1 Тогда
					лпСписокВыбор = Новый СписокЗначений;
					лпСписокВыбор.Добавить("all", "Все листы");
					Для лпНомерЛиста = 1 По лпКоличествоЛистов Цикл
						лпЛист = Excel.Sheets(лпНомерЛиста);	
						лпСписокВыбор.Добавить(лпНомерЛиста, СокрЛП(лпЛист.Name));
					КонецЦикла;
					лпВыбор = лпСписокВыбор.ВыбратьЭлемент("Выбор листа");
					Если лпВыбор = Неопределено Тогда
						Сообщить("Не выбран лист!");
						Возврат ТаблЗнач;
					КонецЕсли;
					лпВыбран = лпВыбор.Значение;
					Если лпВыбран = "all" Тогда
						лпСписокЛистов = лпСписокВыбор.Скопировать();
					Иначе	
						лпСписокЛистов.Добавить(лпВыбран);
					КонецЕсли;
					//НомерЛистаExcel = ;	
				Иначе	
					//НомерЛистаExcel = лпКоличествоЛистов;
					лпСписокЛистов.Добавить(лпКоличествоЛистов);
				КонецЕсли;
			Иначе
				//НомерЛистаExcel = прНомерЛистаExcel;
				лпСписокЛистов.Добавить(прНомерЛистаExcel);
			КонецЕсли;
			
		Исключение
			Сообщить("Не удалось прочитать листы (" + ОписаниеОшибки() + ")");
			Возврат ТаблЗнач;
		КонецПопытки;
	КонецЕсли;
	
	лпСписокВозв = Новый СписокЗначений;
	
	Для каждого лпЭлемент Из лпСписокЛистов Цикл
		НомерЛистаExcel = лпЭлемент.Значение;
		Если НомерЛистаExcel = "all" Тогда
        	Продолжить;
		КонецЕсли;
		ТаблЗнач =  Новый ТаблицаЗначений;
		Попытка
			Состояние("Установка текущего листа...");
			ЛистЭксель = Excel.Sheets(НомерЛистаExcel);
		Исключение
			Сообщить("Не удалось установить текущий лист (" + ОписаниеОшибки() + ")");
			Возврат ТаблЗнач;
		КонецПопытки;
		
		//Range	= ЛистЭксель.UsedRange;
		Если ВсегоСтрок = 0 Тогда
			ВсегоСтрок = ЛистЭксель.Cells.SpecialCells(11).Row;
			//ВсегоСтрок = Range.Rows.Count;
		КонецЕсли;
		
		Если ВсегоКолонок = 0 Тогда
			ВсегоКолонок = ЛистЭксель.Cells.SpecialCells(11).Column;
			//ВсегоКолонок = Range.Columns.Count;  ЛистЭксель.UsedRange.Columns.Count
		КонецЕсли;
		
		Для Счетчик = 1 По ВсегоКолонок Цикл
			ТаблЗнач.Колонки.Добавить(прИмяКолонки+Счетчик, Новый ОписаниеТипов("Строка"));
		КонецЦикла;
		
		Для Счетчик = НомерПервойСтроки По ВсегоСтрок Цикл
			НоваяСтрока = ТаблЗнач.Добавить();
			Состояние("Запись пустых строк:" + Счетчик);
		КонецЦикла;
		
		Область = ЛистЭксель.Range(ЛистЭксель.Cells(НомерПервойСтроки,НомерПервойКолонки), ЛистЭксель.Cells(ВсегоСтрок,ВсегоКолонок));
		Данные = Область.Value.Выгрузить();
		
		Для Счетчик = 0 По ВсегоКолонок - 1 Цикл
			ТаблЗнач.ЗагрузитьКолонку(Данные[Счетчик], Счетчик);
			Состояние("Загружена колонка :" + Счетчик);
		КонецЦикла;	
		
		ЛистЭксель = Неопределено;
	
		лпСписокВозв.Добавить(ТаблЗнач, НомерЛистаExcel);	
		//Сообщить("Лист: " + СокрЛП(лпЭлемент.Представление) + " строк: " + СокрЛП(ТаблЗнач.Количество()));
		ВсегоСтрок = 0;
		ВсегоКолонок = 0;
	КонецЦикла;
	
    Excel.WorkBooks.Close();
    Excel = 0;

	Возврат лпСписокВозв;

КонецФункции
...Показать Скрыть
17. Юрий Батяев (ybatiaev) 10.06.15 12:40
(15) qwinter, Дайте ссылку плиз. Читает да, теперь быстро. 27000 строк по 30 колонок - 0,1 сек
тут рассовывается по документам и справочникам долго... сейчас с этим разберусь тоже
18. Павел Алексеенко (qwinter) 10.06.15 12:55
19. Ruslan (rus128) 10.06.15 14:37
Все понимаю.
Не понимаю только, почему "закачка", а не "загрузка" или (еще лучше) "импорт".
20. Ийон Тихий (cool.vlad4) 12.06.15 16:04
(2) Samarin, да, только этой штукой пользоваться пока нельзя. выбора листа нет, при чтении больших файлов (свыше 50000 строк) все падает и ничего не читает
21. Ийон Тихий (cool.vlad4) 12.06.15 16:10
Могу еще и изменение существующего файла быстрое подсказать как делать, то что можно через ADO это и так понятно, но зачастую была проблема считать значения из файла с формулами и потом туда что-нибудь записать и т.п. можно сформировать ТЗ (или любой другой набор данных), выгрузить в другой excel файл средствами платформы, потом считать этот второй файл через ADO в Recordset и выгрузить в нужную область в нужный файл Excel через CopyFromRecordset. Самое смешное , что все эти записи в файлы, считывание на порядок быстрее чем запись значений через Excel.Application
22. Сергей Огородников (Serg O.) 16.06.15 09:26
здорово, что нашел такую функцию....

но ускорение только на 1/2 так делается
скорость "разбора" строк - тоже иногда очень долго идет...

самый быстрый способ чтения из Excel это программный COPY-PAST
c использованием WScript.Shell
"посылкой" команды Ctrl+C (на выделенный диапазон ячеек Excel)
и Ctrl+V в таблицу 1С (лучше в ТабличныйДокумент)
WSHShell.SendKeys("^c") WSHShell.SendKeys("^v")
см. http://forum.infostart.ru/forum26/topic75936/

дополнительные команды управления книгой Excel из 1C см. http://www.1c-h.ru/?p=238
23. Юрий Батяев (ybatiaev) 21.08.15 15:12
(13) Идальго, Добрый день!
Если не тяжело - напишите ГДЕ почитать про скорость закачки большого числа строк для документов.
Вот, к примеру? в файле присутствует 5500 документов (в ~53000 строках в EXCEL)
Сделал следующее:
1. Чтение из файла в массив ДАННЫХ с определением границ и промежуточных данных, потом отключаю СОМ с EXCEL;
---------- 8 секунд ------------------------------------- ОК!
2. Работаю уже с массивом ДАННЫХ;
3. Проверяю/создаю номенклатуру, причем записываю в массивы данные по ней (мНоменклТХТ - текстовое представление номенклатуры, мНоменклСсылка - ссылку на номенклатуру). При обработке новой строки ищу в массиве подобную номенклатуру (нашел индекс элемента) и с другого массива вытаскиваю по индексу значение(ссылка) и её уже подставляю в дополнительную ячейку в массив ДАННЫЕ (т.е. не дергаю саму базу, если ссылка на номенклатуру была "запомнена" ранее, подобие "КЭШа");
4. Параллельно создаю записи в ТЧ обработки со списком документов(несколько строк файла - это один документ, другие - другой и т.д.). Тут же проверяю/создаю КОНТРАГЕНТА и ДОГОВОР. Механизм как в 3 пункте ("КЭШ");
5. Также определяю СтавкуНДС;
6. В итоге получаю массив ДАННЫХ со списком документов с УЖЕ созданными и приготовленными КОНТРАГЕНТАМИ, ДОГОВОРАМИ, НОМЕНКЛАТУРОЙ, СТАВКОЙ НДС (ссылки для подстановки в документ);
---------- 1590 сек (27 минут) ----------------- нуууу... приемлемо... ОК!
7. Теперь я просто пытаюсь создать сами документы - вот тут то и большая проблема, точнее большие тормоза. Т.е. есть всё, что надо, все найдено или создано. И я могу все проверить и подправить, если что. Но документы создавались 17(!) часов. Не могу пока понять ПОЧЕМУ.
---------- 17 часов ---------- КОНКРЕТНО НЕ ОК :-( -------------------------
ну да, по сравнению с предыдущей обработкой, которая создает ЭТО за 2,5 суток(60 часов) - это решение, но все же. В среднем на создание одного документа в связке с одной Сч-ф потрачено 12(!) секунд. И это не на рабочей базе, там будет в 2 раза дольше.

Если есть что подсказать - киньте ссылку на то, где и что почитать. Поругайте меня, если что не знаю, но помогите!
24. Юрий Батяев (ybatiaev) 24.08.15 15:44
(11) qwinter, Добрый день!
сделал чтение файла 1-8 сек
первичную обработку более имения приемлемую - 53000 строк за 8 минут, в ней поиск/создание контрагентов. договоров, номенклатуры и т.п.

Не подскажите ли чукче-писателю как убыстрить создание документов. По замерам производительности именно тут ТОРМОЗА. Фоновые задания не подходят, т.к. конфигурацию вскрывать нельзя. Асинхронные вызовы копаю сейчас.
Хоть в каком направлении копать? Количество документов в структуре файла до 24000, количество обрабатываемых строк файла до 60000. Основные тормоза в месте НовыйДокумент.Записать()

Буду очень признателен
25. Михаил Гусев (Идальго) 24.08.15 20:40
(23) ybatiaev, я не знаю где вам почитать, но, думаю, что на инфостарте много информации. Как я понял, собственно чтение достаточно быстро идёт, по крайней мере вас устраивает... Замечу лишь, что при создании и записи новых объектов, я бы использовал транзакции, а также ссылку нового. Ну и проверок всяких поменьше делал (т.е. работать должны только нужные), если они есть (при записи и/или проведении).

По поводу документов - наверное нужно выяснить, что конкретно занимает больше всего времени и оптимизировать процедуры. Т.е. смотреть и замерять в отладчике (в первую очередь), а так же использовать технологический журнал (тоже смотреть счетчики всякие) и SQL Profiler.

Вообще у вас проблема наверное какая нибудь банальная, м.б. файловая подсистема барахлит, а может в коде много чего лишнего или неоптимального делается (посмотрите, что происходит в модуле документа, м.б. подписки какие дикие потом еще выполняются). Главное копать, не бросать это дело. Тогда по-любому разберётесь и почините свою загрузку.


Как идею, можете рассмотреть вариант загрузки в несколько потоков. Т.е., можно файл разбить на части и в нескольких сеансах его обрабатывать, если конечно это возможно.
26. Юрий Батяев (ybatiaev) 25.08.15 13:14
(25) Идальго, добрый день!
именно потоками и начал вчера заниматься. Вообще обнаружил, что именно НовыйДокумент.Записать() и тратит время самое большее, в 20-100 раз большее, чем всё остальное. Про подписки - поразбираюсь, но не думаю. Подписки на создание документов по моему не работают.
Про многопоточность - в МАНах написано, что текст обработки должен быть в общем модуле, а конфигурация закрытая. Сейчас сделал убыстрение ОБЩЕЕ в 5 раз. Доделаю и покопаюсь с многопоточностью. Я бы разбил на 2 потока минимум - создание Документа(Реализация, Поступление, ПКО или вписка) в одном, а счет-фактуры в другом (после создания самого документа)... но не получилось пока, а так еще бы время уменьшилась бы в 2 раза максимум.
Сегодня гляну в планы обмена.... может там что-то по новым объектам пишется дополнительно

а по поводу проверок:
так я и разбил на 2 этапа: 1 - это закачка, все проверки и поиск/создание всех объектов. В итоге у меня получается таблица со всеми УЖЕ НУЖНЫМИ ссылками, перечислениями и данными.
и 2 этап - это создание НовыхДокументов на основании этой таблицы (пока в этом траблзы)
1-й этап - общее время на ~25000 записей от 3,5 до 27 минут, что принципе нормально
2-й этап - более 17 часов (со старой обработкой было около 60 часов)

еще заметил, что в замерах производительности когда в накопленных вызовах менее 24000, то обрабатываются быстро(5-7 вызовов в секунду), а дальше 2 в секунду... Что-то накапливается и переполняется в КЭШе что-ли.... копаюсь еще
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа