Приемы эффективной загрузки данных из Excel в 1С

Обмен - Загрузка и выгрузка в Excel

Показаны приемы эффективной работы с Excel для загрузки данных в 1С.

В публикации показаны приемы по загрузке данных из файла Excel в таблицу значений..

Протестировано на версии платформы 1С:Предприятие 8.3 (8.3.9.1818).

В версии 8.3 для загрузки из Excel существует альтернатива COM объекту. У табличного документа есть метод Прочитать.

Особенно актуально для файлов больших объемов, т.к. табличный документ обрабатывается гораздо быстрее.

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

Во вложении обработка - пример загрузки и вывода в табличный документ и таблицу значений.

Рабочий код занимает несколько строк:

//Вывод в табличный документ

   ТабличныйДокумент = Новый ТабличныйДокумент;

   ТабличныйДокумент.Прочитать(Файл, СпособЧтенияЗначенийТабличногоДокумента.Значение);

//вывод в таблицу значений

   ПЗ = Новый ПостроительЗапроса;

   ПЗ.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТабличныйДокумент.Область());

   ПЗ.ДобавлениеПредставлений = ТипДобавленияПредставлений.НеДобавлять;

   ПЗ.ЗаполнитьНастройки();

   ПЗ.Выполнить();

   ТаблицаЗначений = ПЗ.Результат.Выгрузить();

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

Во внешней обработке демонстрация загрузки.

Скачать файлы

Наименование Файл Версия Размер
Приемы эффективной загрузки данных из Excel в 1С:
.epf 7,15Kb
12.12.17
24
.epf 1.0 7,15Kb 24 Скачать

См. также

Лучшие комментарии
44. Сергей Долинин (ImHunter) 19 17.04.18 09:25 Сейчас в теме
(0) (38) Разбивка по листам, похоже, не раскрыта?
Недавно тоже вот перешел на этот способ с ADO.
Разбивать по листам, в принципе, несложно. В загруженном ТабДоке присутствует несколько областей с названиями листов.

В итоге, получилось вот так:

///////////////////////////////////
// Работа с Excel через ТабДок

// Функция - Прочитать excel
//
// Параметры:
//  ФайлExcel - Файл, Строка	 - Объект Файл или полное имя файла.
//  ФункцияОбработкиСтрок	 - Строка, Неопределено	 - Шаблон выражения для обработки строковых значений ячеек. 
//	В шаблоне производится подстановка - вместо символа ? будет подставляться обрабатываемое значение строки.
//	Если передать Неопределено или пустую строку, то обработка производиться не будет.
//  МаксШирина				 - Число - Максимальное кол-во колонок, просматриваемых при усечении таблицы по ширине.
//  ОграничиватьСверху		 - Булево - Убирать пустые строки сверху
//  ОграничиватьСлева		 - Булево - Убирать пустые строки слева
//  ОграничиватьСнизу		 - Булево - Убирать пустые строки снизу
//  ОграничиватьСправа		 - Булево - Убирать пустые строки справа
// 
// Возвращаемое значение:
//  СписокЗначений - Список значений содержимого. Элементы списка в поле Значение содержат ячейки листа (объект ОбластьЯчеекТабличногоДокумента), в поле Представление - имя листа
//
Функция ПрочитатьExcel(ФайлExcel, ФункцияОбработкиСтрок = "СокрЛП(?)",
		ОграничиватьСверху = Истина, ОграничиватьСлева = Истина, ОграничиватьСнизу = Истина, ОграничиватьСправа = Истина) Экспорт 
	
	Ф = НовыйФайл(ФайлExcel);
	ВесьТабДок = Новый ТабличныйДокумент();
	ВесьТабДок.Прочитать(Ф.ПолноеИмя, СпособЧтенияЗначенийТабличногоДокумента.Значение);
	
	Результат = Новый СписокЗначений;
	Области = ВесьТабДок.Области;
	Если Области.Количество()=0 Тогда 
		ВызватьИсключение("В файле нет областей");
	КонецЕсли;
	
	Для Каждого Обл Из Области Цикл
		ТекТабДок = Новый ТабличныйДокумент;
		ОгрОбласть = ПолучитьОграниченнуюОбласть(ВесьТабДок, Обл);
		// вставим область в табдок
		ОгрОбласть.Имя = Обл.Имя;
		ТекТабДок.ВставитьОбласть(ОгрОбласть, 
			ТекТабДок.Область(1, ОгрОбласть.Лево, ОгрОбласть.Низ - ОгрОбласть.Верх + 1, ОгрОбласть.Право)
		);
		СократитьСтроки(ТекТабДок, ФункцияОбработкиСтрок);
		Результат.Добавить(ТекТабДок, Обл.Имя);
	КонецЦикла;
	// зачищаем исходный таб док. хз, может лишнее
	ВесьТабДок.Очистить();
	ВесьТабДок = Неопределено;
	
	Возврат Результат;
	
КонецФункции

Процедура СократитьСтроки(ТабДок, ФункцияОбработкиСтрок = "СокрЛП(?)")
	
	Если ПустаяСтрока(ФункцияОбработкиСтрок) Тогда 
		Возврат;
	КонецЕсли;
	
	Если Ложь Тогда ТабДок = Новый ТабличныйДокумент; КонецЕсли; // фейк
	
	Для Стр = 1 По ТабДок.ВысотаТаблицы Цикл 
		Для Кол = 1 По ТабДок.ШиринаСтраницы Цикл 
			ТекОбласть = ТабДок.Область(Стр, Кол);
			ТекстОбласти = ТекОбласть.Текст;
			Если Не ТекОбласть.СодержитЗначение И Не ПустаяСтрока(ТекОбласть.Текст)	Тогда 
				Выражение = СтрЗаменить(ФункцияОбработкиСтрок, "?", "ТекстОбласти");
				ТекОбласть.Текст = Вычислить(Выражение);
			КонецЕсли;
		КонецЦикла;
	КонецЦикла;
	
КонецПроцедуры

Функция ПолучитьОграниченнуюОбласть(ТабДок, ОбластьЯчеек,
	ОграничиватьСверху = Истина, ОграничиватьСлева = Истина, ОграничиватьСнизу = Истина, ОграничиватьСправа = Истина)
	
	Если Не ОграничиватьСверху И Не ОграничиватьСлева И Не ОграничиватьСправа И Не ОграничиватьСнизу Тогда 
		Возврат ОбластьЯчеек;
	КонецЕсли;
	
	Если Ложь Тогда // фейк
		ТабДок = Новый ТабличныйДокумент;
		ОбластьЯчеек = ТабДок.Области[0];
	КонецЕсли;
	
	Лево = Неопределено; Право = Неопределено; Верх = Неопределено; Низ = Неопределено;
	МаксШирина = ТабДок.ШиринаТаблицы;
	Для Стр = ОбластьЯчеек.Верх По ОбластьЯчеек.Низ Цикл
		Для Кол = 1 По МаксШирина Цикл 
			ТекОбласть = ТабДок.Область(Стр, Кол);
			Если ЭтоЗначащаяОбласть(ТекОбласть) Тогда 
				Лево = ?(Лево=Неопределено, Кол, Мин(Лево, Кол));
				Право = ?(Право=Неопределено, Кол, Макс(Право, Кол));
				Верх = ?(Верх=Неопределено, Стр, Верх);
				Низ = ?(Низ=Неопределено, Стр, Макс(Низ, Стр));
			КонецЕсли;
		КонецЦикла;
	КонецЦикла;
	
	Результат = ТабДок.Область(
		?(ОграничиватьСверху, ?(Верх=Неопределено, ОбластьЯчеек.Верх, Верх), ОбластьЯчеек.Верх),
		?(ОграничиватьСлева, ?(Лево=Неопределено, ОбластьЯчеек.Лево, Лево), ОбластьЯчеек.Лево),
		?(ОграничиватьСнизу, ?(Низ=Неопределено, ОбластьЯчеек.Низ, Низ), ОбластьЯчеек.Низ),
		?(ОграничиватьСправа, ?(Право=Неопределено, ОбластьЯчеек.Право, Право), ОбластьЯчеек.Право)
	);
	
	Возврат Результат;
	
КонецФункции

Функция ЭтоЗначащаяОбласть(ОбластьЯчеек)
	Возврат Не ПустаяСтрока(ОбластьЯчеек.Текст) 
		Или ОбластьЯчеек.Гиперссылка
		Или ОбластьЯчеек.СодержитЗначение
		//Или ОбластьЯчеек.ГраницаСверху.ТипЛинии<>ТипЛинииЯчейкиТабличногоДокумента.НетЛинии
		//Или ОбластьЯчеек.ГраницаСнизу.ТипЛинии<>ТипЛинииЯчейкиТабличногоДокумента.НетЛинии
		Или ОбластьЯчеек.ГраницаСлева.ТипЛинии<>ТипЛинииЯчейкиТабличногоДокумента.НетЛинии
		Или ОбластьЯчеек.ГраницаСправа.ТипЛинии<>ТипЛинииЯчейкиТабличногоДокумента.НетЛинии
	;
КонецФункции

// Функция - Текст области листа
// Предполагаем, что имеем дело с ТабДоком, обрезанным по нужным размерам.
// Параметры:
//  Лист - ЭлементСпискаЗначений, ТабличныйДокумент	 - Где искать текст.
//  Верх	 - Число, Неопределено	 - Верхняя граница области
//  Лево	 - Число, Неопределено	 - Левая граница области
//  Низ		 - Число, Неопределено	 - Нижняя граница области
//  Право	 - Число, Неопределено	 - Правая граница области
// 
// Возвращаемое значение:
//  Строка - Объединенный текст указанной области. Ячейки по горизонтали разделяются символом ТАБ, строки переносятся ВК
//
Функция ТекстОбластиЛиста(Лист, Знач Верх = Неопределено, Знач Лево = Неопределено, Знач Низ = Неопределено, Знач Право = Неопределено) Экспорт
	
	ТипЗнчЛист = ТипЗнч(Лист);
	Если ТипЗнчЛист=КэшФункции.ТипТабличныйДокумент() Тогда 
		ТабДок = Лист;
	ИначеЕсли ТипЗнчЛист=КэшФункции.ТипЭлементСпискаЗначений() Тогда 
		ТабДок = Лист.Значение;
	Иначе 
		ТабДок = Новый ТабличныйДокумент; // фейк
		ВызватьИсключение("Неподдерживаемый тип параметра Лист " + ТипЗнчЛист);
	КонецЕсли;
	
	Область = ТабДок.Область(Верх, Лево, Низ, Право);
	Результат = "";
	ГорРазд = Символы.Таб;
	ВертРазд = Символы.ВК;
	
	ОблЛево = ?(Область.Лево=0, 1, Область.Лево);
	ОблПраво = ?(Область.Право=0, ТабДок.ШиринаТаблицы, Мин(Область.Право, ТабДок.ШиринаТаблицы));
	ОблВерх = ?(Область.Верх=0, 1, Область.Верх);
	ОблНиз = ?(Область.Низ=0, ТабДок.ВысотаТаблицы, Мин(Область.Низ, ТабДок.ВысотаТаблицы));
	
	Для Стр=ОблВерх По ОблНиз Цикл 
		
		Для Кол=ОблЛево По ОблПраво Цикл
			
			ТекстЯчейки = ТабДок.Область(Стр, Кол).Текст;
			ЭтоПустаяЯчейка = ПустаяСтрока(ТекстЯчейки);
			
			Если ЭтоПустаяЯчейка Тогда 
				ТекстЯчейки = ГорРазд;
			КонецЕсли;
			Если Кол=ОблПраво И Стр=ОблНиз Тогда 
				Разделитель = "";
			ИначеЕсли Кол=ОблПраво Тогда
				Разделитель = ВертРазд;
			Иначе 
				Разделитель = ?(ЭтоПустаяЯчейка, "", ГорРазд);
			КонецЕсли;
			
			Результат = Результат + ТекстЯчейки + Разделитель;
				
		КонецЦикла;
		
	КонецЦикла;
	
	Возврат Результат;
	
КонецФункции

Показать
sansys; Somebody1; necropunk; DeD MustDie; LexSeIch; frkbvfnjh; alexey.kutya; +7 Ответить
22. Алексей Лапицкий (Lapitskiy) 869 14.12.17 13:21 Сейчас в теме
(15) полностью перешел на этот метод, скорость сравнима с ADO
А совместимость выше.
Дмитрий31178; alexey.kutya; +2 Ответить
Остальные комментарии
1. BigB (BigB) 170 13.12.17 14:21 Сейчас в теме
Как воспримет построитель вот такие найменования колонок например:
Наименование товара
Кол-во
Дата документа
2. Алексей Кутья (alexey.kutya) 139 13.12.17 14:57 Сейчас в теме
(1) воспримет нормально, но переименует по-своему ))
вот так
НаименованиеТовара
Кол_во
ДатаДокумента
Прикрепленные файлы:
frkbvfnjh; BigB; +2 Ответить
3. Максим Сухов (MaxS) 998 13.12.17 16:21 Сейчас в теме
Допустим требуется заполнить реквизит Артикул справочника номенклатура. Исходный файл содержит соответствующую колонку с текстом "000123", "000124" и т.п. все символы цифровые. Какое значение попадёт в строковый реквизит номенклатуры?
Если "123", "124" и т.п., то приемы эффективной загрузки данных из Excel в 1С разбиваются о реальность ))
5. Алексей Кутья (alexey.kutya) 139 13.12.17 16:49 Сейчас в теме
(3) Попадет значение "000123".
Прикрепленные файлы:
4. Алексей Кутья (alexey.kutya) 139 13.12.17 16:47 Сейчас в теме
Попадет значение "000123".
Прикрепленные файлы:
7. Максим Сухов (MaxS) 998 13.12.17 16:57 Сейчас в теме
(4) Спасибо за эксперимент, пересмотрю своё мнение об этом методе. Мои опыты приводили к потере лидирующих нулей.
8. Алексей Кутья (alexey.kutya) 139 13.12.17 17:42 Сейчас в теме
(7) возможно что-то уже изменили в платформе

и еще есть один нюанс. У метода Прочитать есть второй параметр СпособЧтенияЗначений.

<СпособЧтенияЗначений> (необязательный)

Тип: СпособЧтенияЗначенийТабличногоДокумента.
Определяет, каким образом нужно интерпретировать значения, считываемые из исходного документа XLS, XLSX или ODS.
При загрузке табличного документа из формата Excel 97 - 2010 и OpenOffice Calc, в случае если в ячейке исходного документа содержалось значение типа Дата или Число, то в ячейку результирующего табличного документа это значение попадает в зависимости от значения этого параметра.
Значение по умолчанию: Текст.
6. max pik (maxopik2) 48 13.12.17 16:54 Сейчас в теме
У меня проблема в методе "Прочитать()" , ядро 8.3.10.2580 : при загрузке больших файлов (2-3 Mb) расход памяти сервера может достигать 12 Gb
пришлось отказаться от этого метода и пользоваться ADODB
9. Алексей Кутья (alexey.kutya) 139 13.12.17 17:43 Сейчас в теме
(6) обидно )) но что поделаешь
11. max pik (maxopik2) 48 14.12.17 07:20 Сейчас в теме
(9) Не обидно, а вопрос: Это ошибка платформы (т.к. не может из файла 2 Mb появиться файл mxl объемом 11Gb) или я что-то не так делаю?
12. Алексей Кутья (alexey.kutya) 139 14.12.17 08:45 Сейчас в теме
(11) да интересно ) если хотите, отправьте мне этот файл. Я попробую его загрузить. Если конечно там нет конфиденциальной информации.
19. max pik (maxopik2) 48 14.12.17 12:20 Сейчас в теме
25. Алексей Кутья (alexey.kutya) 139 14.12.17 14:01 Сейчас в теме
(19) вот результат. Обработал за 2 сек )) размер mxl файла 3 Мб.

Время начала чтения табличного документа: 14.12.2017 13:52:46
Время окончания чтения табличного документа: 14.12.2017 13:52:48
Количество секунд чтения: 2

Время начала выгрузки табличного документа в ТЗ: 14.12.2017 13:52:48
Время окончания выгрузки табличного документа в ТЗ: 14.12.2017 13:52:49
Количество строк таблицы значений: 11 874
Количество секунд выгрузки: 1
13. Максим Сухов (MaxS) 998 14.12.17 08:49 Сейчас в теме
(11) А если на этом же сервере запустить тонкий клиент и через "Файл - Открыть" открыть этот файл? Расход памяти такой же будет?
20. max pik (maxopik2) 48 14.12.17 12:21 Сейчас в теме
(13) Попробовал через тонкий клиент. Расход памяти такой же.
10. Олег (Alligator84) 14.12.17 05:23 Сейчас в теме
Спасибо, познавательно!
alexey.kutya; +1 Ответить
14. Алексей Лапицкий (Lapitskiy) 869 14.12.17 09:31 Сейчас в теме
а зачем построитель?
Можно по циклу обойти Табличный документ.
16. Алексей Кутья (alexey.kutya) 139 14.12.17 10:02 Сейчас в теме
(14) можно и циклом. Это альтернативный способ. Мне больше нравится через построитель.
vovan_victory; +1 Ответить
23. Алексей Лапицкий (Lapitskiy) 869 14.12.17 13:22 Сейчас в теме
(16) я думаю, из-за построителя и расход памяти поболее будет.
alexey.kutya; +1 Ответить
24. Алексей Кутья (alexey.kutya) 139 14.12.17 13:46 Сейчас в теме
(23) сам не замерял, но вполне возможно )
15. Валерий М (VmvLer) 14.12.17 09:50 Сейчас в теме
табличные документы 1С много "весят" - это особенно ощутимо для больших данных.

покрутите предлагаемый метод для эксель-файлов с сотнями тысяч строк, интересно за какое время вы прочтете
17. Алексей Кутья (alexey.kutya) 139 14.12.17 10:27 Сейчас в теме
(15) вот результат замера. 1 млн. строк, файл excel 25 мб.

Время начала чтения табличного документа: 14.12.2017 10:24:40
Время окончания чтения табличного документа: 14.12.2017 10:26:31
Количество секунд чтения: 111

Время начала выгрузки табличного документа в ТЗ: 14.12.2017 10:26:37
Время окончания выгрузки табличного документа в ТЗ: 14.12.2017 10:26:49
Количество строк таблицы значений: 1 000 000
Количество секунд выгрузки: 12
22. Алексей Лапицкий (Lapitskiy) 869 14.12.17 13:21 Сейчас в теме
(15) полностью перешел на этот метод, скорость сравнима с ADO
А совместимость выше.
Дмитрий31178; alexey.kutya; +2 Ответить
37. Антон Рощин (wolfsoft) 2418 20.12.17 08:58 Сейчас в теме
(22) Ага, ещё бы при чтении некоторых эксель-файлов не вылетало с ошибкой и страницы бы читала.
18. Владимир (vladismi) 159 14.12.17 10:59 Сейчас в теме
Забавно. Попробуем.
alexey.kutya; +1 Ответить
21. Павел Алексеенко (qwinter) 199 14.12.17 13:04 Сейчас в теме
Кто то узнал о существовании построителя. Круто.
26. Павел Колобков (PavelKolobkov) 14.12.17 15:34 Сейчас в теме
Здесь обсуждалось подобное сабжу
27. Алексей Кутья (alexey.kutya) 139 14.12.17 16:07 Сейчас в теме
(26) там немного про другое, но в комментариях да, упоминается про метод Прочитать.
28. Александр Хомяк (logarifm) 1015 15.12.17 01:06 Сейчас в теме
А где приемы? Я насчитал только один! Отчитали и накрыли построителем, а остальные ?
DmitrySinichnikov; +1 1 Ответить
29. Алексей Кутья (alexey.kutya) 139 15.12.17 08:29 Сейчас в теме
(28) ну вот и получается загрузка в 2 приема ))
Рассмотреть все возможные варианты работы с Excel не являлось целью публикации. Я просто хотел показать как можно с наменьшими затратами, используя код в несколько строк выполнить задачу.
30. Андрей Конев (Infector) 99 15.12.17 09:32 Сейчас в теме
Был небольшой опыт:
Когда попытался обработать через "прочитать" сохраненную контрагентом печатную форму с графическими объектами, все это дело просто повесилось. Пока что ADO и COM-объекты в таких случаях надежнее.
alexey.kutya; +1 Ответить
32. Алексей Кутья (alexey.kutya) 139 15.12.17 13:11 Сейчас в теме
(30) да, в таком случае лучше ADO.
31. Андрей Сябренко (AzagTot) 37 15.12.17 12:16 Сейчас в теме
Спасибо! Очень интересный способ.
Насколько я понял, он не требует наличия на компьютере Microsoft Excel или OpenOffice Calc для чтения соответствующих таблиц?
Метод Табличного Документа "Записать" точно не требует, проверено)
alexey.kutya; +1 Ответить
36. Алексей Кутья (alexey.kutya) 139 15.12.17 19:28 Сейчас в теме
(31) Я не проверял, но думаю должен работать.
43. Юрий Иночкин (user816930) 16.04.18 22:53 Сейчас в теме
(31) на сколько мне известно в 1С есть встроенный механизм работы с табличными документами, и в данном случае работает как раз встроенный механизм и получается приложение необязательное.
33. Алексей Кутья (alexey.kutya) 139 15.12.17 13:12 Сейчас в теме
Я не проверял, но думаю должен работать.
38. Евгений Кредько (kredko) 17 20.12.17 09:16 Сейчас в теме
Хороший метод, только вот не работает с файлами эксель с расширением xlm, который с макросами. И Данные с нескольких страниц помещает в один табличный документ.
alexey.kutya; +1 Ответить
39. Роман Г (Stradivari) 139 13.04.18 08:46 Сейчас в теме
Спасибо. Статья помогла!
40. Александр Крынецкий (echo77) 824 16.04.18 13:09 Сейчас в теме
Просто и полезно. Укажите в публикации, пожалуйста, что это работает начиная с платформы версии 8.3.6, а с версии 8.3.10 можно грузить данные с определенного листа. Можно так же подкрепить ссылка на ИТС
frkbvfnjh; alexey.kutya; +2 Ответить
41. d4rkmesa (d4rkmesa) 16.04.18 22:10 Сейчас в теме
Гениально. Не знал про такие фокусы с построителем и табличным документом.
alexey.kutya; +1 Ответить
42. Евгений Артемьев (yelloo) 16.04.18 22:39 Сейчас в теме
(41) Тоже не знал) До сих пор ADO/EXCEL юзаю =)
alexey.kutya; +1 Ответить
44. Сергей Долинин (ImHunter) 19 17.04.18 09:25 Сейчас в теме
(0) (38) Разбивка по листам, похоже, не раскрыта?
Недавно тоже вот перешел на этот способ с ADO.
Разбивать по листам, в принципе, несложно. В загруженном ТабДоке присутствует несколько областей с названиями листов.

В итоге, получилось вот так:

///////////////////////////////////
// Работа с Excel через ТабДок

// Функция - Прочитать excel
//
// Параметры:
//  ФайлExcel - Файл, Строка	 - Объект Файл или полное имя файла.
//  ФункцияОбработкиСтрок	 - Строка, Неопределено	 - Шаблон выражения для обработки строковых значений ячеек. 
//	В шаблоне производится подстановка - вместо символа ? будет подставляться обрабатываемое значение строки.
//	Если передать Неопределено или пустую строку, то обработка производиться не будет.
//  МаксШирина				 - Число - Максимальное кол-во колонок, просматриваемых при усечении таблицы по ширине.
//  ОграничиватьСверху		 - Булево - Убирать пустые строки сверху
//  ОграничиватьСлева		 - Булево - Убирать пустые строки слева
//  ОграничиватьСнизу		 - Булево - Убирать пустые строки снизу
//  ОграничиватьСправа		 - Булево - Убирать пустые строки справа
// 
// Возвращаемое значение:
//  СписокЗначений - Список значений содержимого. Элементы списка в поле Значение содержат ячейки листа (объект ОбластьЯчеекТабличногоДокумента), в поле Представление - имя листа
//
Функция ПрочитатьExcel(ФайлExcel, ФункцияОбработкиСтрок = "СокрЛП(?)",
		ОграничиватьСверху = Истина, ОграничиватьСлева = Истина, ОграничиватьСнизу = Истина, ОграничиватьСправа = Истина) Экспорт 
	
	Ф = НовыйФайл(ФайлExcel);
	ВесьТабДок = Новый ТабличныйДокумент();
	ВесьТабДок.Прочитать(Ф.ПолноеИмя, СпособЧтенияЗначенийТабличногоДокумента.Значение);
	
	Результат = Новый СписокЗначений;
	Области = ВесьТабДок.Области;
	Если Области.Количество()=0 Тогда 
		ВызватьИсключение("В файле нет областей");
	КонецЕсли;
	
	Для Каждого Обл Из Области Цикл
		ТекТабДок = Новый ТабличныйДокумент;
		ОгрОбласть = ПолучитьОграниченнуюОбласть(ВесьТабДок, Обл);
		// вставим область в табдок
		ОгрОбласть.Имя = Обл.Имя;
		ТекТабДок.ВставитьОбласть(ОгрОбласть, 
			ТекТабДок.Область(1, ОгрОбласть.Лево, ОгрОбласть.Низ - ОгрОбласть.Верх + 1, ОгрОбласть.Право)
		);
		СократитьСтроки(ТекТабДок, ФункцияОбработкиСтрок);
		Результат.Добавить(ТекТабДок, Обл.Имя);
	КонецЦикла;
	// зачищаем исходный таб док. хз, может лишнее
	ВесьТабДок.Очистить();
	ВесьТабДок = Неопределено;
	
	Возврат Результат;
	
КонецФункции

Процедура СократитьСтроки(ТабДок, ФункцияОбработкиСтрок = "СокрЛП(?)")
	
	Если ПустаяСтрока(ФункцияОбработкиСтрок) Тогда 
		Возврат;
	КонецЕсли;
	
	Если Ложь Тогда ТабДок = Новый ТабличныйДокумент; КонецЕсли; // фейк
	
	Для Стр = 1 По ТабДок.ВысотаТаблицы Цикл 
		Для Кол = 1 По ТабДок.ШиринаСтраницы Цикл 
			ТекОбласть = ТабДок.Область(Стр, Кол);
			ТекстОбласти = ТекОбласть.Текст;
			Если Не ТекОбласть.СодержитЗначение И Не ПустаяСтрока(ТекОбласть.Текст)	Тогда 
				Выражение = СтрЗаменить(ФункцияОбработкиСтрок, "?", "ТекстОбласти");
				ТекОбласть.Текст = Вычислить(Выражение);
			КонецЕсли;
		КонецЦикла;
	КонецЦикла;
	
КонецПроцедуры

Функция ПолучитьОграниченнуюОбласть(ТабДок, ОбластьЯчеек,
	ОграничиватьСверху = Истина, ОграничиватьСлева = Истина, ОграничиватьСнизу = Истина, ОграничиватьСправа = Истина)
	
	Если Не ОграничиватьСверху И Не ОграничиватьСлева И Не ОграничиватьСправа И Не ОграничиватьСнизу Тогда 
		Возврат ОбластьЯчеек;
	КонецЕсли;
	
	Если Ложь Тогда // фейк
		ТабДок = Новый ТабличныйДокумент;
		ОбластьЯчеек = ТабДок.Области[0];
	КонецЕсли;
	
	Лево = Неопределено; Право = Неопределено; Верх = Неопределено; Низ = Неопределено;
	МаксШирина = ТабДок.ШиринаТаблицы;
	Для Стр = ОбластьЯчеек.Верх По ОбластьЯчеек.Низ Цикл
		Для Кол = 1 По МаксШирина Цикл 
			ТекОбласть = ТабДок.Область(Стр, Кол);
			Если ЭтоЗначащаяОбласть(ТекОбласть) Тогда 
				Лево = ?(Лево=Неопределено, Кол, Мин(Лево, Кол));
				Право = ?(Право=Неопределено, Кол, Макс(Право, Кол));
				Верх = ?(Верх=Неопределено, Стр, Верх);
				Низ = ?(Низ=Неопределено, Стр, Макс(Низ, Стр));
			КонецЕсли;
		КонецЦикла;
	КонецЦикла;
	
	Результат = ТабДок.Область(
		?(ОграничиватьСверху, ?(Верх=Неопределено, ОбластьЯчеек.Верх, Верх), ОбластьЯчеек.Верх),
		?(ОграничиватьСлева, ?(Лево=Неопределено, ОбластьЯчеек.Лево, Лево), ОбластьЯчеек.Лево),
		?(ОграничиватьСнизу, ?(Низ=Неопределено, ОбластьЯчеек.Низ, Низ), ОбластьЯчеек.Низ),
		?(ОграничиватьСправа, ?(Право=Неопределено, ОбластьЯчеек.Право, Право), ОбластьЯчеек.Право)
	);
	
	Возврат Результат;
	
КонецФункции

Функция ЭтоЗначащаяОбласть(ОбластьЯчеек)
	Возврат Не ПустаяСтрока(ОбластьЯчеек.Текст) 
		Или ОбластьЯчеек.Гиперссылка
		Или ОбластьЯчеек.СодержитЗначение
		//Или ОбластьЯчеек.ГраницаСверху.ТипЛинии<>ТипЛинииЯчейкиТабличногоДокумента.НетЛинии
		//Или ОбластьЯчеек.ГраницаСнизу.ТипЛинии<>ТипЛинииЯчейкиТабличногоДокумента.НетЛинии
		Или ОбластьЯчеек.ГраницаСлева.ТипЛинии<>ТипЛинииЯчейкиТабличногоДокумента.НетЛинии
		Или ОбластьЯчеек.ГраницаСправа.ТипЛинии<>ТипЛинииЯчейкиТабличногоДокумента.НетЛинии
	;
КонецФункции

// Функция - Текст области листа
// Предполагаем, что имеем дело с ТабДоком, обрезанным по нужным размерам.
// Параметры:
//  Лист - ЭлементСпискаЗначений, ТабличныйДокумент	 - Где искать текст.
//  Верх	 - Число, Неопределено	 - Верхняя граница области
//  Лево	 - Число, Неопределено	 - Левая граница области
//  Низ		 - Число, Неопределено	 - Нижняя граница области
//  Право	 - Число, Неопределено	 - Правая граница области
// 
// Возвращаемое значение:
//  Строка - Объединенный текст указанной области. Ячейки по горизонтали разделяются символом ТАБ, строки переносятся ВК
//
Функция ТекстОбластиЛиста(Лист, Знач Верх = Неопределено, Знач Лево = Неопределено, Знач Низ = Неопределено, Знач Право = Неопределено) Экспорт
	
	ТипЗнчЛист = ТипЗнч(Лист);
	Если ТипЗнчЛист=КэшФункции.ТипТабличныйДокумент() Тогда 
		ТабДок = Лист;
	ИначеЕсли ТипЗнчЛист=КэшФункции.ТипЭлементСпискаЗначений() Тогда 
		ТабДок = Лист.Значение;
	Иначе 
		ТабДок = Новый ТабличныйДокумент; // фейк
		ВызватьИсключение("Неподдерживаемый тип параметра Лист " + ТипЗнчЛист);
	КонецЕсли;
	
	Область = ТабДок.Область(Верх, Лево, Низ, Право);
	Результат = "";
	ГорРазд = Символы.Таб;
	ВертРазд = Символы.ВК;
	
	ОблЛево = ?(Область.Лево=0, 1, Область.Лево);
	ОблПраво = ?(Область.Право=0, ТабДок.ШиринаТаблицы, Мин(Область.Право, ТабДок.ШиринаТаблицы));
	ОблВерх = ?(Область.Верх=0, 1, Область.Верх);
	ОблНиз = ?(Область.Низ=0, ТабДок.ВысотаТаблицы, Мин(Область.Низ, ТабДок.ВысотаТаблицы));
	
	Для Стр=ОблВерх По ОблНиз Цикл 
		
		Для Кол=ОблЛево По ОблПраво Цикл
			
			ТекстЯчейки = ТабДок.Область(Стр, Кол).Текст;
			ЭтоПустаяЯчейка = ПустаяСтрока(ТекстЯчейки);
			
			Если ЭтоПустаяЯчейка Тогда 
				ТекстЯчейки = ГорРазд;
			КонецЕсли;
			Если Кол=ОблПраво И Стр=ОблНиз Тогда 
				Разделитель = "";
			ИначеЕсли Кол=ОблПраво Тогда
				Разделитель = ВертРазд;
			Иначе 
				Разделитель = ?(ЭтоПустаяЯчейка, "", ГорРазд);
			КонецЕсли;
			
			Результат = Результат + ТекстЯчейки + Разделитель;
				
		КонецЦикла;
		
	КонецЦикла;
	
	Возврат Результат;
	
КонецФункции

Показать
sansys; Somebody1; necropunk; DeD MustDie; LexSeIch; frkbvfnjh; alexey.kutya; +7 Ответить
45. Алексей Кутья (alexey.kutya) 139 17.04.18 10:08 Сейчас в теме
(44) Спасибо за дополнение :) У вас отлично реализовано. Думаю будет очень полезно.
50. vpaoli 22 18.04.18 09:42 Сейчас в теме
(45) Я недавно сравнивал скорость загрузки через ADO и с помощью метода
Область = ЛистЭксель.Range(...);
Данные = Область.Value.Выгрузить();
Получилось, что второй метод быстрее ADO где то на 25%
Вы не сравнивали метод Range с вашим методом загрузки ? Что быстрее и насколько. Может сравните для полноты ощущений ...?
46. ediks (ediks) 324 17.04.18 10:33 Сейчас в теме
(44) А что за объект КэшФункции в функции ТекстОбластиЛиста?
И в строке Ф = НовыйФайл(ФайлExcel); пробел не пропущен НовыйФайл?
47. Сергей Долинин (ImHunter) 19 17.04.18 10:55 Сейчас в теме
(46) Да, точно

Тут же в модуле

// Функция - Новый файл
// Создает или переиспользует объект Файл. Для сокращения кол-ва кода. Заодно можно делать проверку существования файла.
// Параметры:
//  Файл					 - Файл, Строка	 - Если передается объект Файл, то он же и возвращается. 
//	Если передается Строка (путь), то создается Файл по переданному пути
//  ПроверятьСуществование	 - Булево - Проверять ли существование файла. Если не существует, то идет вызов исключения.
// 
// Возвращаемое значение:
//  Файл - Созданный или переиспользованный файл
//
Функция НовыйФайл(Файл, ПроверятьСуществование = Истина)
	
	ТипЗнчФайл = ТипЗнч(Файл);
	Результат = Неопределено;
	Если ТипЗнчФайл=КэшФункции.ТипФайл() Тогда 
		Результат = Файл;
	ИначеЕсли ТипЗнчФайл=КэшФункции.ТипСтрока() Тогда 
		Результат = Новый Файл(Файл);
	Иначе 
		ВызватьИсключение("Непредусмотренный тип параметра Файл " + ТипЗнчФайл);
	КонецЕсли;
	
	Если ПроверятьСуществование=Истина И Не Результат.Существует() Тогда 
		ВызватьИсключение("Файл не существует");
	КонецЕсли;
	
	Возврат Результат;
	
КонецФункции
Показать


А КэшФункции - это модуль, закешированный на сеанс. Там, в том числе, закешированы типы данных:
Функция ТипСтрока() Экспорт
	Возврат Тип("Строка");
КонецФункции

Функция ТипДата() Экспорт
	Возврат Тип("Дата");
КонецФункции

Функция ТипФайл() Экспорт
	Возврат Тип("Файл");
КонецФункции

Функция ТипСтруктура() Экспорт
	Возврат Тип("Структура");
КонецФункции

Функция ТипНеопределено() Экспорт
	Возврат Тип("Неопределено");
КонецФункции

Функция ТипУникальныйИдентификатор() Экспорт
	Возврат Тип("УникальныйИдентификатор");
КонецФункции

Функция ТипЭлементСпискаЗначений() Экспорт 
	Возврат Тип("ЭлементСпискаЗначений");
КонецФункции

Функция ТипСписокЗначений() Экспорт 
	Возврат Тип("СписокЗначений");
КонецФункции

Функция ТипТабличныйДокумент() Экспорт 
	Возврат Тип("ТабличныйДокумент");
КонецФункции

Функция ТипМассив() Экспорт 
	Возврат Тип("Массив");
КонецФункции
Показать
48. Сергей Долинин (ImHunter) 19 17.04.18 16:32 Сейчас в теме
На основании публикации доработал и свой модуль. Теперь выгружаю и в ТЗ.
// Функция - Получить таблицу значений excel
//
// Параметры:
//  Лист - ЭлементСпискаЗначений, ТабличныйДокумент	 - Где искать текст.
//  ПоляТолькоПоДокументу	 - Булево - Если Истина, то создаются поля по верхней строке ТабДока. Если Ложь, то создаются авто-поля Поле[N] по всей ширине ТабДока.
// 
// Возвращаемое значение:
//  ТаблицаЗначений - ТЗ с данными ТабДока
//
Функция ПолучитьТаблицуЗначенийExcel(Лист, ПоляТолькоПоДокументу = Ложь) Экспорт
	
	Если Истина Тогда 
		ТабДок = ПолучитьТабДок(Лист);
	Иначе 
		ТабДок = Новый ТабличныйДокумент;
	КонецЕсли;
	
	Если ТабДок.ВысотаТаблицы=0 Тогда 
		Возврат Неопределено;
	КонецЕсли;
	
	// вставим заголовки
	Если ПоляТолькоПоДокументу=Ложь Тогда 
		ВерхняяСтрока = ТабДок.Область("R1");
		ТабДок.ВставитьОбласть(ВерхняяСтрока, ВерхняяСтрока, ТипСмещенияТабличногоДокумента.ПоВертикали, Ложь);
		ВерхняяСтрока = ТабДок.Область("R1");
		ВерхняяСтрока.Очистить();
		Для Кол=1 По ТабДок.ШиринаТаблицы Цикл
			ТабДок.Область(1, Кол).Текст = "Поле" + Формат(Кол, "ЧГ=");
		КонецЦикла;
	КонецЕсли;
	
	ПЗ = Новый ПостроительЗапроса;
	ПЗ.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТабДок.Область());
	ПЗ.ДобавлениеПредставлений = ТипДобавленияПредставлений.НеДобавлять;
	ПЗ.ЗаполнитьНастройки();
	ПЗ.Выполнить();
	ТаблицаЗначений = ПЗ.Результат.Выгрузить();	
	
	// вставим заголовки
	Если ПоляТолькоПоДокументу=Ложь Тогда
		ВерхняяСтрока = ТабДок.Область("R1");
		ТабДок.УдалитьОбласть(ВерхняяСтрока, ТипСмещенияТабличногоДокумента.ПоВертикали);
	КонецЕсли;
   
    Возврат ТаблицаЗначений;
   
КонецФункции

Функция ПолучитьТабДок(Лист)
	
	ТипЗнчЛист = ТипЗнч(Лист);
	Если ТипЗнчЛист=КэшФункции.ТипТабличныйДокумент() Тогда 
		ТабДок = Лист;
	ИначеЕсли ТипЗнчЛист=КэшФункции.ТипЭлементСпискаЗначений() Тогда 
		ТабДок = Лист.Значение;
	Иначе 
		ВызватьИсключение("Неподдерживаемый тип параметра Лист " + ТипЗнчЛист);
	КонецЕсли;
	
	Возврат ТабДок;
	
КонецФункции
Показать
alexey.kutya; +1 Ответить
59. vpaoli 22 20.04.18 12:22 Сейчас в теме
Раз уж Вы пишете "Приемы эффективной загрузки ..." , наверно необходимо сравнение с другими существующими . Иначе это может быть и "не эффективной загрузки".
Есть метод:
Область = ЛистЭксель.Range(...); 
Данные = Область.Value.Выгрузить(); 


Вы не сравнивали метод Range с вашим методом загрузки ? Что быстрее и насколько ?
65. Алексей Кутья (alexey.kutya) 139 23.04.18 12:58 Сейчас в теме
(59)
Область = ЛистЭксель.Range(...);
Данные = Область.Value.Выгрузить();


Попробовал на 1 млн. строк.

Время выполнения чтения файла вашим методом 36 сек. Время выполнения методом описанным в моей публикации 193 сек. Разница в 5 раз :) Но ваш метод возвращает массивы значений, надо попробовать их разобрать.
66. Сергей Долинин (ImHunter) 19 23.04.18 13:24 Сейчас в теме
(65) Так ожидаемо, что ТабДоком медленнее. И значительно тяжелее, наверное, по расходу памяти.
Но профит в том, что не нужен Excel, ADO-провайдеры. Пожалуй, только из-за этого на табдоки перебрался.
alexey.kutya; +1 Ответить
67. Алексей Кутья (alexey.kutya) 139 23.04.18 14:11 Сейчас в теме
(66)
ADO-провайдер


Конечно, если время выполнения приемлемое, то почему бы и нет.
68. vpaoli 22 24.04.18 10:52 Сейчас в теме
(65)
Но ваш метод возвращает массивы значений, надо попробовать их разобрать.
Все уже разобрано.... Есть публикации вместе с кодом на инфостарте.
alexey.kutya; +1 Ответить
Оставьте свое сообщение