Преобразование EXCEL в таблицу значений без COM и других извращений

Публикация № 1050198

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

Обработка Загрузка EXCEL 1C Программирование ТаблицаЗначений таблица значений Запрос

204
Получение таблицы значений из excel в 1С v8, без COM, внешних источников данных и т.д. EXCELВТаблицуЗначений() - За 10 строчек кода! Реализация протестирована на 1С 8.3.12.1714 (x64).

WARNING

Данная статья не претендует на оригинальность и не является конечным решением.

Подходы решения задач и примеры программного кода несут исключительно обучающий характер.

 

 

 

 

Существует масса подходов загрузки данных из EXCEL в 1С. Большинство решений основаны на COM или еще более сложных возможностях платформы.

Для себя разработал наиболее оптимальный вариант:
EXCEL -> Табличный документ -> Построитель запроса -> Таблица значений

Итак, приступим:

  1. Прочитать Excel файл на клиенте, для передачи на сервер: 
    &НаКлиенте
    Процедура ЗагрузитьEXCEL(Команда)
    	
    	Файл = Новый Файл(ПутьКEXCEL);
    	Если НЕ Файл.Существует() Тогда
    		Возврат;
    	КонецЕсли;
    	ДвоичныеДанные = Новый ДвоичныеДанные(ПутьКEXCEL);
    	ДокументРезультат = EXCELВТаблицуЗначений(ДвоичныеДанные, Файл.Расширение);
    	ДокументРезультат.Показать();
    	
    КонецПроцедуры

     

  1. Выполнить чтение Excel в таблицу значений: 
    &НаСервере
    Функция EXCELВТаблицуЗначений(ДвоичныеДанные, Расширение)
    	
    	ФайлEXCELНаСервере = ПолучитьИмяВременногоФайла(Расширение);
    	ДвоичныеДанные.Записать(ФайлEXCELНаСервере);
    	
    	ТабличныйДокумент = Новый ТабличныйДокумент;
    	ТабличныйДокумент.Прочитать(ФайлEXCELНаСервере);
    	УдалитьФайлы(ФайлEXCELНаСервере);
    	ОбластьТаблицы = ТабличныйДокумент.Область(8, 1, ТабличныйДокумент.ВысотаТаблицы, ТабличныйДокумент.ШиринаТаблицы);
    	
    	ПостроительЗапроса = Новый ПостроительЗапроса;
    	ПостроительЗапроса.ИсточникДанных = Новый ОписаниеИсточникаДанных(ОбластьТаблицы);  
    	ПостроительЗапроса.Выполнить();
    	ТаблицаДанных = ПостроительЗапроса.Результат.Выгрузить();
    	
    	// теперь поссмторим что в Таблице
    	Возврат ТаблицаЗначенийВТабличныйДокумент(ТаблицаДанных);
    	
    КонецФункции 

     

  2. Визуализировать таблицу значений: 

    &НаСервереБезКонтекста
    Функция ТаблицаЗначенийВТабличныйДокумент(ТаблицаДанных)
    	
    	ПостроительОтчета = Новый ПостроительОтчета;
    	ПостроительОтчета.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТаблицаДанных);
    	ДокументРезультат = Новый ТабличныйДокумент;
    	Построительотчета.Вывести(ДокументРезультат);
    	Возврат ДокументРезультат;
    	
    КонецФункции

     

Описание преобразования данных:

  1. Получаем исходный файл на клиенте;
  2. На основании файла создаем двоичные данные и получаем расширение файла;
  3. Передаем двоичные данные на сервер;
  4. Создаем дубль файла excel на сервере;
  5. Читаем файл в новый табличный документ;
  6. Получаем область табличного документа, где находятся полезные данные (ТабличныйДокумент.Область(8, 1, ТабличныйДокумент.ВысотаТаблицы, ТабличныйДокумент.ШиринаТаблицы));
  7. Создаем новый построитель запроса и установим в качестве источника данных - область полезных данных;
  8. Выполним построитель и получим результат запроса;
  9. Результат запроса выгрузим в таблицу значений.

Плюсы реализации:

  • Скорость работы по сравнению с COM EXCEL
  • Возможно выполнять загрузку где EXCEL нет вообще (MAC | LINUX | etc...)
  • Простота реализации
204

См. также

Специальные предложения

Лучшие комментарии
1. VmvLer 18.04.19 14:30 Сейчас в теме
ок. просто, понятно, рационально, полезно.
seperblunt2; Lem0n; D_e_X_T_e_R; EliasShy; Gang031; davydoff; jaroslav.h; ram3; YPermitin; korzhishe; igo1; bulpi; Eret1k; +13 Ответить
Остальные комментарии
Избранное Подписка Сортировка: Древо
1. VmvLer 18.04.19 14:30 Сейчас в теме
ок. просто, понятно, рационально, полезно.
seperblunt2; Lem0n; D_e_X_T_e_R; EliasShy; Gang031; davydoff; jaroslav.h; ram3; YPermitin; korzhishe; igo1; bulpi; Eret1k; +13 Ответить
2. A_Max 17 18.04.19 16:22 Сейчас в теме
Не нужно плодить временные сущностифайлы!

ФайлEXCELНаСервере = ПолучитьИмяВременногоФайла(Расширение);
ДвоичныеДанные.Записать(ФайлEXCELНаСервере);
ТабличныйДокумент.Прочитать(ФайлEXCELНаСервере);
УдалитьФайлы(ФайлEXCELНаСервере);

Заменить на одну строку:
ТабличныйДокумент.Прочитать(ДвоичныеДанные.ОткрытьПотокДляЧтения())
rudnitskij; androgin; +2 Ответить
4. nikita0832 183 18.04.19 17:57 Сейчас в теме
(2)через поток читает только ods и mxl, к сожалению.
3. ЛеваРоскошный 35 18.04.19 17:49 Сейчас в теме
ни фига себе , я тут сидел изучал Адо , как преобразовать xlsx в xml ... все тлен ..
Автор гений Спасибо.
graphbuh; leshiy26; Eret1k; +3 Ответить
5. nikita0832 183 18.04.19 18:01 Сейчас в теме
от себя добавлю как это реализовано у меня:

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

КонецФункции // ФайлExcelВТаблицуЗначений()

// Для парсинга табличных документов: сворачивает шапку для построителя запросов до 1 уровня
//
// Параметры:
//  Область  - ТабличныйДокумент,ОбластьТабличногоДокумента - <описание параметра>
//  КолУровней  - Число - Исходное количество уровней начиная с 2. Передавать меньше 2 не имеет смысла.
//        
// Возвращаемое значение:
//   ОбластьТабличногоДокумента   - Область с обрезанной до 1 строки шапкой.
Функция СвернутьСложнуюШапку(ТабДок,КолУровней,Область=Неопределено) Экспорт
	Если Область=Неопределено Тогда
		Область = ТабДок.Область();
	КонецЕсли;
	МассивЗаголовков = Новый Массив;	
	МаксКолонок = ТабДок.ПолучитьРазмерОбластиДанныхПоГоризонтали();
	ПерваяСтрока = Область.Верх;
	Для Сч = 1 По КолУровней Цикл
		ПредЗначение = "";
		Для Кол = 1 По МаксКолонок Цикл
			Текст = ТабДок.Область("R"+Строка(ПерваяСтрока+Сч-1)+"C"+Кол).Текст;
			
			Если Сч = 1 Тогда
				МассивЗаголовков.Добавить(Новый Массив);
				Если ЗначениеЗаполнено(Текст) Тогда
					МассивЗаголовков[Кол-1].Добавить(Текст);
					ПредЗначение = Текст;
				Иначе
					МассивЗаголовков[Кол-1].Добавить(ПредЗначение);
				КонецЕсли;
			Иначе
				ИменаЗаголовка = МассивЗаголовков[Кол-1];
				РодительОтличается = Ложь;
				Если Кол >1 Тогда
					Для Инд = 0 по Сч-2 Цикл
						Если ИменаЗаголовка[Инд] <> МассивЗаголовков[Кол-2][Инд] Тогда
							РодительОтличается = Истина;
							Прервать;
						КонецЕсли;
					КонецЦикла;
				КонецЕсли;
				Если ЗначениеЗаполнено(Текст) Тогда
					ИменаЗаголовка.Добавить(Текст);
				Иначе
					Если ЗначениеЗаполнено(ПредЗначение) И НЕ РодительОтличается Тогда 
						ИменаЗаголовка.Добавить(ПредЗначение);
					КонецЕсли;
				КонецЕсли;
				ПредЗначение = Текст;
			КонецЕсли;
			
		КонецЦикла;
	КонецЦикла;
	Для Кол = 1 По МаксКолонок Цикл
		ТабДок.Область("R"+Строка(ПерваяСтрока+КолУровней-1)+"C"+Кол).Текст = СтрСоединить(МассивЗаголовков[Кол-1],"_");
		
	КонецЦикла;
	НоваяОбласть = ТабДок.Область("R"+Строка(ПерваяСтрока+КолУровней-1)+":R"+Область.Низ);
	Возврат НоваяОбласть;
КонецФункции // СвернутьСложнуюШапку()
Показать
babys; glek; acanta; +3 Ответить
6. nikita0832 183 18.04.19 18:08 Сейчас в теме
(5)поясню: листы перечислены в областях документа, сортировать их можно по признаку "Верх" , т.е. где он начинается. Есть так же многоэтажные шапки, которые часто делают Экселеводы, для них сделал отдельную функцию. Ещё метод прочитать может читать как ячейки как строку, а может с теми типами, которые указаны в экселе(но это рисково, надо контролировать, особенно если надо использовать эту таблицу в запросе).
7. Lapitskiy 901 18.04.19 20:48 Сейчас в теме
Это работает только для xlsx
К сожалению, xls не прочитает.
Вот здесь человек проделал огромный труд, читайте: https://infostart.ru/public/120961/
10. logarifm 1045 18.04.19 21:31 Сейчас в теме
(7) С какого это перепуга не сработает. Все прекрасно там работает!
11. ksnik 320 19.04.19 00:18 Сейчас в теме
Метод1С "Прочитать эксель в табличный документ" намертво на несколько часов виснет если на одном из листов >30000 строк, в этом случае весь документ не доступен и работать не возможно, поэтому "прочитать" можно использовать только для маленьких файлов. А для больших - можно делать на основе быстрого парсера https://infostart.ru/public/139556/
(7) при условии если не хотим убить сутки на разбирательство с https://infostart.ru/public/120961/ которую рядовые пользователи не смогут осилить
sulfur17; Dach; Lapitskiy; Eret1k; +4 Ответить
16. batsy66 37 19.04.19 10:31 Сейчас в теме
(11) Если количество листов в документе больше 20, то так же виснет намертво
sulfur17; +1 Ответить
25. Rustig 1172 20.04.19 10:35 Сейчас в теме
(11) спасибо за информацию! значит используем предложенный способ для небольших списков товаров - к примеру, при загрузке приходных накладных - к примеру в небольшом магазине игрушек...
8. logarifm 1045 18.04.19 21:28 Сейчас в теме
(0) О Боже автор наконец-то освоил уже устаревший метод Прочитать!
chemezov; Yashazz; MSK_Step; qwinter; shard; YPermitin; temsan; Lapitskiy; +8 3 Ответить
15. qwinter 596 19.04.19 09:42 Сейчас в теме
(8) Больше поражает сколько плюсов статья получила))
Yashazz; YPermitin; +2 Ответить
27. Rustig 1172 20.04.19 10:39 Сейчас в теме
(15) загрузка из Эксель одна из самых популярных задач - не зря же 1С развивает эту тему....поэтому любые нововведения встречают на "ура"
33. Yashazz 2502 06.08.19 13:37 Сейчас в теме
(15) Ну дык примитив всегда поражает воображение: "всего N строчек и вау-вау!". А про нормальную работу через ADO итд автор не знает, да и специфику внутренних преобразований эксельных таблиц внутри платформы никто не знает точно. Зато вау.
9. logarifm 1045 18.04.19 21:30 Сейчас в теме
И резко упал вниз в цепочке развития где-то в район колчатых червей:

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

В 8.3 использовать построитель это уже не комильфо...
MSK_Step; YPermitin; ЛеваРоскошный; +3 4 Ответить
19. veri123 19.04.19 14:48 Сейчас в теме
(9) А что плохого в построителе?
AllexSoft; Eret1k; +2 Ответить
20. kembrik 3 19.04.19 16:06 Сейчас в теме
(9) Отнюдь, удобней способа выгрузить табличный документ в ТЗ и наоборот ещё поискать
chemezov; Eret1k; +2 Ответить
24. Rustig 1172 20.04.19 10:33 Сейчас в теме
(9) 1. в защиту ПостроителяЗапроса - вот моя разработка https://infostart.ru/public/933060/ - которая использует ПостроительЗапроса - реализовал полгода назад - считаю объект ПостроительЗапроса - очень интересным и малоизученным программистами
2.
В 8.3 использовать построитель это уже

8.3 - это платформа - у меня все программы на обычных формах работают на 8.3 - ут 10.3, бп 2.0.
в ут 10.3 до сих пор многие механизмы на ПостроителеЗапроса - к примеру, отчеты или отборы в документе Инвентаризация товаров.
AllexSoft; chemezov; Eret1k; +3 Ответить
35. Yashazz 2502 06.08.19 13:40 Сейчас в теме
(24) Соглашусь. Построитель иногда очень эффективен. И я в своё время его много изучал, и сейчас использую. Любой инструмент должен быть адекватен своему контексту применения.
34. Yashazz 2502 06.08.19 13:39 Сейчас в теме
(9) Это ещё автор не в курсе, какие у построителя ограничения по производительности, да как он обойдётся с ячейками сложных форматов, да что будет делать с объединёнными областями... Гы, много автору суждено открытий чудных. И тем, кто повёлся на этот способ, тоже)
AllexSoft; John_Davidson; +2 Ответить
39. AllexSoft 09.08.19 17:38 Сейчас в теме
(9) Построитель запроса (не путать с построителем отчета!!) очень даже хороший инструмент, незаслуженно обделенный вниманием. Позволяет по сути уйти от всеми нами "любимых" СтрЗаменить или склеек условий. Построитель запроса это младший брат СКД, никто не ругается же на СКД в 1С.
12. Crazy_Max 52 19.04.19 07:00 Сейчас в теме
Ну что за грязь! Назвать функцию "EXCELВТаблицуЗначений", а возвращать из неё "ТабличныйДокумент"...
Тем более, что статья писана для начинающих, и это обязательно будет вводить их в ступор и/или приучать к бардаку в коде.
Ну раз уж выложили свою статью на всеобщий суд, так хоть немного причешите код, что ли...
14. 1Cappldev 19.04.19 08:10 Сейчас в теме
(12) Добрый день! Сухая критика автора ни к чему не приведёт. Вот что действительно поможет комьюнити, напишете как сделали бы Вы, или поделитесь полезными ссылочками дабы предотвратить бардак в коде.
Ignatov_mu; dadel; RickyTickyTok; more; user1098673; batsy66; korzhishe; Eret1k; +8 Ответить
18. Crazy_Max 52 19.04.19 12:27 Сейчас в теме
(14) А как мне еще "размочить" критику? Я указал на конкретную, грубую ошибку, заключающуюся в том, что наименование основной функции описывает один ТИП возвращаемого значения, а по факту возвращает ДРУГОЙ ТИП значения. Исполняться такой код будет, но ведь исполнение кода не является единственным критерием оценки его качества.
Как в том афоризме: "он - начинающий программист, и пока еще кодит под себя"...
John_Davidson; Krio2; fvadim; practik1c; Vortigaunt; Rustig; +6 Ответить
26. Rustig 1172 20.04.19 10:37 Сейчас в теме
(14) критика обидна, поскольку в такой критикующей форме идет общение, но раз другой нет - то приходится учиться принимать ее в таком виде - в результате, если автор услышит суть, уберет эмоции критикующего, то ему будет полезно.
1Cappldev; Eret1k; +2 Ответить
13. Lapitskiy 901 19.04.19 07:33 Сейчас в теме
17. ksnik 320 19.04.19 11:19 Сейчас в теме
(13) она универсальная, а не заточенная под конкретную задачу, если и удастся самому приложив усилия импортировать конкретный документ и все сопутствующие справочники и регистры сведений, то никак не получится заставить ей пользоваться рядовых пользователей в ежедневной рутинной работе.
21. Serg O. 170 19.04.19 20:43 Сейчас в теме
А как насчет метода копипаста? Почему то никто тут про него не вспоминает.... Самый быстрый способ, под winwows так точно. через Буфер обмена можно под wsShell отправкой ctrl+C потом в своей таблице ctrl + V или "стандартными" методами Excel - select - copy. Потом в 1с из буфера вставить... Так независимо от версии 1с... Хоть с 8.1 и то работает...
Есть минус... Excel. Или хотя бы OpenOffice должен быть установлен.… 2) должно быть открыто, моргает при открытии 3) при многократной (более 20) вставке... Exсel умирает... ошибка завершения, а процесс остаётся висеть
23. Rustig 1172 20.04.19 10:22 Сейчас в теме
(21)
Есть минус... Excel. Или хотя бы OpenOffice должен быть установлен.…

я на платформе 8.3.13 из 1с просто открыл файл эксель (через меню Файл-Открыть) - открылся табличный документ mxl.
при этом эксель не был установлен на компе.
а так как я не использую чтение эксель, а использую для обработки информации mxl, то мне этого достаточно (можете мои загрузки из эксель поизучать)
22. DanDy 3 20.04.19 10:16 Сейчас в теме
Почему нельзя вынести работу с файлом на клиент? А уже полученный Табличный документ передать на сервер? Тогда временный файл вроде как не нужен
29. pbabincev 112 21.04.19 15:54 Сейчас в теме
(22)
Это уже детали.
Автор хотел донести общую суть самого простого способа загрузки из Excel.
31. DanDy 3 22.04.19 17:01 Сейчас в теме
(29)
Когда однажды столкнешься с проблемой доступа к файлу на сервере, по пути выбранном на клиенте, тогда поймешь что "детали" и работоспособность на файловом и клиент-серверном варианте это разные вещи
28. pbabincev 112 21.04.19 15:53 Сейчас в теме
ОбластьТаблицы = ТабличныйДокумент.Область(8, 1, ТабличныйДокумент.ВысотаТаблицы, ТабличныйДокумент.ШиринаТаблицы);
...
ПостроительЗапроса.ИсточникДанных = Новый ОписаниеИсточникаДанных(ОбластьТаблицы);

А что, так можно было?)
Не знал, спасибо, автор)
30. d.zhukov 451 22.04.19 06:16 Сейчас в теме
- Старые форматы excel не прочитает.
- Открытый файл не прочитает.
- Ну и на а 8.2, разумеется, не заработает.
32. ksnik 320 06.08.19 09:15 Сейчас в теме
Данное решение не пригодно для больших таблиц потому что платформа 1С8 пока не умеет работать с табличными документами так же быстро как это делает Эксель. Данное решение пригодно исключительно для мелких файлов.
Yashazz; Поручик; +2 Ответить
36. Yashazz 2502 06.08.19 13:42 Сейчас в теме
37. skv_79 156 06.08.19 16:52 Сейчас в теме
Отличная реализация частой задачи!
38. kuzyara 775 07.08.19 04:45 Сейчас в теме
Можно читать и через ТабДок, но есть ньюансы.
Yashazz; AllexSoft; +2 Ответить
40. Yashazz 2502 09.08.19 17:44 Сейчас в теме
(38) а про нюансы люди сами узнают, когда попытаются это применить))) плюсов, смотрю, вообще уже запредельное количество. Как и желающих с размаху встать на грабли)
41. AllexSoft 09.08.19 18:10 Сейчас в теме
Плюсанул только за функцию ТаблицаЗначенийВТабличныйДокумент, как то запамятовал что построитель отчета может по таблице значений выводить табличный документ. Иногда нужно вывести какую нибудь простенькую таблицу ошибок обработки например или какую нибудь отладочную инфу пользователю, а городить макет и вывод в табдок не хочется.. вот тут то эта функция будет самое оно. Кстати функция прокатит и для РезультатаЗапроса, а не только для ТаблицыЗначений.
А по поводу загрузки из экселя функция увы совсем не универсальная будет из за построителя запроса, он совсем не все подряд кушает, но если есть простенькая четко структурированная табличка, то вполне прокатит.
42. Yashazz 2502 11.08.19 10:40 Сейчас в теме
(41) Алекс, а если люди ещё почитают про ОписаниеИсточникаДанных, а потом сравнят, что в СП написано про "ИсточникДанных" и что про конструктор описания, будет ещё много открытий)

Ну раз подобное теперь "выбор экспертов", то у меня уже просто нет слов...
43. AllexSoft 12.08.19 10:32 Сейчас в теме
(42) это да, хотя с другой стороны каждую неделю какую нибудь годноту типа перфоленты никто выкладывать не сможет, а выбор экспертов надо чем-то наполнять...
Оставьте свое сообщение