0. Eret1k 607 18.04.19 14:00 Сейчас в теме

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

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

Перейти к публикации

Лучшие комментарии
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 189 18.04.19 17:57 Сейчас в теме
(2)через поток читает только ods и mxl, к сожалению.
3. ЛеваРоскошный 35 18.04.19 17:49 Сейчас в теме
ни фига себе , я тут сидел изучал Адо , как преобразовать xlsx в xml ... все тлен ..
Автор гений Спасибо.
graphbuh; leshiy26; Eret1k; +3 Ответить
5. nikita0832 189 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 189 18.04.19 18:08 Сейчас в теме
(5)поясню: листы перечислены в областях документа, сортировать их можно по признаку "Верх" , т.е. где он начинается. Есть так же многоэтажные шапки, которые часто делают Экселеводы, для них сделал отдельную функцию. Ещё метод прочитать может читать как ячейки как строку, а может с теми типами, которые указаны в экселе(но это рисково, надо контролировать, особенно если надо использовать эту таблицу в запросе).
7. Lapitskiy 902 18.04.19 20:48 Сейчас в теме
Это работает только для xlsx
К сожалению, xls не прочитает.
Вот здесь человек проделал огромный труд, читайте: https://infostart.ru/public/120961/
10. logarifm 1048 18.04.19 21:31 Сейчас в теме
(7) С какого это перепуга не сработает. Все прекрасно там работает!
11. ksnik 358 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 38 19.04.19 10:31 Сейчас в теме
(11) Если количество листов в документе больше 20, то так же виснет намертво
sulfur17; +1 Ответить
25. Rustig 1195 20.04.19 10:35 Сейчас в теме
(11) спасибо за информацию! значит используем предложенный способ для небольших списков товаров - к примеру, при загрузке приходных накладных - к примеру в небольшом магазине игрушек...
8. logarifm 1048 18.04.19 21:28 Сейчас в теме
(0) О Боже автор наконец-то освоил уже устаревший метод Прочитать!
chemezov; Yashazz; MSK_Step; qwinter; shard; YPermitin; temsan; Lapitskiy; +8 3 Ответить
15. qwinter 605 19.04.19 09:42 Сейчас в теме
(8) Больше поражает сколько плюсов статья получила))
Yashazz; YPermitin; +2 Ответить
27. Rustig 1195 20.04.19 10:39 Сейчас в теме
(15) загрузка из Эксель одна из самых популярных задач - не зря же 1С развивает эту тему....поэтому любые нововведения встречают на "ура"
33. Yashazz 2855 06.08.19 13:37 Сейчас в теме
(15) Ну дык примитив всегда поражает воображение: "всего N строчек и вау-вау!". А про нормальную работу через ADO итд автор не знает, да и специфику внутренних преобразований эксельных таблиц внутри платформы никто не знает точно. Зато вау.
9. logarifm 1048 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 1195 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 2855 06.08.19 13:40 Сейчас в теме
(24) Соглашусь. Построитель иногда очень эффективен. И я в своё время его много изучал, и сейчас использую. Любой инструмент должен быть адекватен своему контексту применения.
34. Yashazz 2855 06.08.19 13:39 Сейчас в теме
(9) Это ещё автор не в курсе, какие у построителя ограничения по производительности, да как он обойдётся с ячейками сложных форматов, да что будет делать с объединёнными областями... Гы, много автору суждено открытий чудных. И тем, кто повёлся на этот способ, тоже)
AllexSoft; John_Davidson; +2 Ответить
39. AllexSoft 09.08.19 17:38 Сейчас в теме
(9) Построитель запроса (не путать с построителем отчета!!) очень даже хороший инструмент, незаслуженно обделенный вниманием. Позволяет по сути уйти от всеми нами "любимых" СтрЗаменить или склеек условий. Построитель запроса это младший брат СКД, никто не ругается же на СКД в 1С.
12. Crazy_Max 55 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 55 19.04.19 12:27 Сейчас в теме
(14) А как мне еще "размочить" критику? Я указал на конкретную, грубую ошибку, заключающуюся в том, что наименование основной функции описывает один ТИП возвращаемого значения, а по факту возвращает ДРУГОЙ ТИП значения. Исполняться такой код будет, но ведь исполнение кода не является единственным критерием оценки его качества.
Как в том афоризме: "он - начинающий программист, и пока еще кодит под себя"...
John_Davidson; Krio2; fvadim; practik1c; Vortigaunt; Rustig; +6 Ответить
26. Rustig 1195 20.04.19 10:37 Сейчас в теме
(14) критика обидна, поскольку в такой критикующей форме идет общение, но раз другой нет - то приходится учиться принимать ее в таком виде - в результате, если автор услышит суть, уберет эмоции критикующего, то ему будет полезно.
1Cappldev; Eret1k; +2 Ответить
13. Lapitskiy 902 19.04.19 07:33 Сейчас в теме
17. ksnik 358 19.04.19 11:19 Сейчас в теме
(13) она универсальная, а не заточенная под конкретную задачу, если и удастся самому приложив усилия импортировать конкретный документ и все сопутствующие справочники и регистры сведений, то никак не получится заставить ей пользоваться рядовых пользователей в ежедневной рутинной работе.
21. Serg O. 172 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 1195 20.04.19 10:22 Сейчас в теме
(21)
Есть минус... Excel. Или хотя бы OpenOffice должен быть установлен.…

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

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

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

Вакансии

Бизнес-архитектор 1С, ведущий консультант
Санкт-Петербург
Полный день

Руководитель проектов 1С
Санкт-Петербург
Полный день

Программист 1С
Краснодар
зарплата от 80 000 руб. до 160 000 руб.
Полный день

Консультант 1 С
Краснодар
зарплата от 50 000 руб. до 150 000 руб.
Полный день

Консультант-методолог 1С
Краснодар
зарплата от 110 000 руб.
Полный день