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

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

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

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

144
Получение таблицы значений из 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...)
  • Простота реализации
144

См. также

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

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

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

Заменить на одну строку:
ТабличныйДокумент.Прочитать(ДвоичныеДанные.ОткрытьПотокДляЧтения())
rudnitskij; androgin; +2 Ответить
4. nikita0832 173 18.04.19 17:57 Сейчас в теме
(2)через поток читает только ods и mxl, к сожалению.
3. ЛеваРоскошный 34 18.04.19 17:49 Сейчас в теме
ни фига себе , я тут сидел изучал Адо , как преобразовать xlsx в xml ... все тлен ..
Автор гений Спасибо.
5. nikita0832 173 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"+Область.Низ);
	Возврат НоваяОбласть;
КонецФункции // СвернутьСложнуюШапку()
Показать
6. nikita0832 173 18.04.19 18:08 Сейчас в теме
(5)поясню: листы перечислены в областях документа, сортировать их можно по признаку "Верх" , т.е. где он начинается. Есть так же многоэтажные шапки, которые часто делают Экселеводы, для них сделал отдельную функцию. Ещё метод прочитать может читать как ячейки как строку, а может с теми типами, которые указаны в экселе(но это рисково, надо контролировать, особенно если надо использовать эту таблицу в запросе).
7. Lapitskiy 898 18.04.19 20:48 Сейчас в теме
Это работает только для xlsx
К сожалению, xls не прочитает.
Вот здесь человек проделал огромный труд, читайте: https://infostart.ru/public/120961/
10. logarifm 1038 18.04.19 21:31 Сейчас в теме
(7) С какого это перепуга не сработает. Все прекрасно там работает!
11. ksnik 310 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 32 19.04.19 10:31 Сейчас в теме
(11) Если количество листов в документе больше 20, то так же виснет намертво
sulfur17; +1 Ответить
25. Rustig 1152 20.04.19 10:35 Сейчас в теме
(11) спасибо за информацию! значит используем предложенный способ для небольших списков товаров - к примеру, при загрузке приходных накладных - к примеру в небольшом магазине игрушек...
8. logarifm 1038 18.04.19 21:28 Сейчас в теме
(0) О Боже автор наконец-то освоил уже устаревший метод Прочитать!
qwinter; shard; YPermitin; temsan; Lapitskiy; +5 2 Ответить
15. qwinter 580 19.04.19 09:42 Сейчас в теме
(8) Больше поражает сколько плюсов статья получила))
YPermitin; +1 Ответить
27. Rustig 1152 20.04.19 10:39 Сейчас в теме
(15) загрузка из Эксель одна из самых популярных задач - не зря же 1С развивает эту тему....поэтому любые нововведения встречают на "ура"
9. logarifm 1038 18.04.19 21:30 Сейчас в теме
И резко упал вниз в цепочке развития где-то в район колчатых червей:

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

В 8.3 использовать построитель это уже не комильфо...
YPermitin; ЛеваРоскошный; +2 3 Ответить
19. veri123 19.04.19 14:48 Сейчас в теме
(9) А что плохого в построителе?
20. kembrik 2 19.04.19 16:06 Сейчас в теме
(9) Отнюдь, удобней способа выгрузить табличный документ в ТЗ и наоборот ещё поискать
24. Rustig 1152 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 до сих пор многие механизмы на ПостроителеЗапроса - к примеру, отчеты или отборы в документе Инвентаризация товаров.
12. Crazy_Max 50 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 50 19.04.19 12:27 Сейчас в теме
(14) А как мне еще "размочить" критику? Я указал на конкретную, грубую ошибку, заключающуюся в том, что наименование основной функции описывает один ТИП возвращаемого значения, а по факту возвращает ДРУГОЙ ТИП значения. Исполняться такой код будет, но ведь исполнение кода не является единственным критерием оценки его качества.
Как в том афоризме: "он - начинающий программист, и пока еще кодит под себя"...
practik1c; Vortigaunt; Rustig; +3 Ответить
26. Rustig 1152 20.04.19 10:37 Сейчас в теме
(14) критика обидна, поскольку в такой критикующей форме идет общение, но раз другой нет - то приходится учиться принимать ее в таком виде - в результате, если автор услышит суть, уберет эмоции критикующего, то ему будет полезно.
1Cappldev; Eret1k; +2 Ответить
13. Lapitskiy 898 19.04.19 07:33 Сейчас в теме
17. ksnik 310 19.04.19 11:19 Сейчас в теме
(13) она универсальная, а не заточенная под конкретную задачу, если и удастся самому приложив усилия импортировать конкретный документ и все сопутствующие справочники и регистры сведений, то никак не получится заставить ей пользоваться рядовых пользователей в ежедневной рутинной работе.
21. Serg O. 171 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 1152 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 394 22.04.19 06:16 Сейчас в теме
- Старые форматы excel не прочитает.
- Открытый файл не прочитает.
- Ну и на а 8.2, разумеется, не заработает.
Оставьте свое сообщение