Приемы эффективной загрузки данных из 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
17
.epf 1.0 7,15Kb 17 Скачать

См. также

Лучшие комментарии
22. Алексей Лапицкий (Lapitskiy) 843 14.12.17 13:21 Сейчас в теме
(15) полностью перешел на этот метод, скорость сравнима с ADO
А совместимость выше.
alexey.kutya; +1 Ответить
Остальные комментарии
1. BigB (BigB) 168 13.12.17 14:21 Сейчас в теме
Как воспримет построитель вот такие найменования колонок например:
Наименование товара
Кол-во
Дата документа
2. Алексей Кутья (alexey.kutya) 91 13.12.17 14:57 Сейчас в теме
(1) воспримет нормально, но переименует по-своему ))
вот так
НаименованиеТовара
Кол_во
ДатаДокумента
Прикрепленные файлы:
3. Максим Сухов (MaxS) 492 13.12.17 16:21 Сейчас в теме
Допустим требуется заполнить реквизит Артикул справочника номенклатура. Исходный файл содержит соответствующую колонку с текстом "000123", "000124" и т.п. все символы цифровые. Какое значение попадёт в строковый реквизит номенклатуры?
Если "123", "124" и т.п., то приемы эффективной загрузки данных из Excel в 1С разбиваются о реальность ))
5. Алексей Кутья (alexey.kutya) 91 13.12.17 16:49 Сейчас в теме
(3) Попадет значение "000123".
Прикрепленные файлы:
4. Алексей Кутья (alexey.kutya) 91 13.12.17 16:47 Сейчас в теме
Попадет значение "000123".
Прикрепленные файлы:
7. Максим Сухов (MaxS) 492 13.12.17 16:57 Сейчас в теме
(4) Спасибо за эксперимент, пересмотрю своё мнение об этом методе. Мои опыты приводили к потере лидирующих нулей.
8. Алексей Кутья (alexey.kutya) 91 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) 91 13.12.17 17:43 Сейчас в теме
(6) обидно )) но что поделаешь
11. max pik (maxopik2) 48 14.12.17 07:20 Сейчас в теме
(9) Не обидно, а вопрос: Это ошибка платформы (т.к. не может из файла 2 Mb появиться файл mxl объемом 11Gb) или я что-то не так делаю?
12. Алексей Кутья (alexey.kutya) 91 14.12.17 08:45 Сейчас в теме
(11) да интересно ) если хотите, отправьте мне этот файл. Я попробую его загрузить. Если конечно там нет конфиденциальной информации.
19. max pik (maxopik2) 48 14.12.17 12:20 Сейчас в теме
25. Алексей Кутья (alexey.kutya) 91 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) 492 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) 843 14.12.17 09:31 Сейчас в теме
а зачем построитель?
Можно по циклу обойти Табличный документ.
16. Алексей Кутья (alexey.kutya) 91 14.12.17 10:02 Сейчас в теме
(14) можно и циклом. Это альтернативный способ. Мне больше нравится через построитель.
23. Алексей Лапицкий (Lapitskiy) 843 14.12.17 13:22 Сейчас в теме
(16) я думаю, из-за построителя и расход памяти поболее будет.
alexey.kutya; +1 Ответить
24. Алексей Кутья (alexey.kutya) 91 14.12.17 13:46 Сейчас в теме
(23) сам не замерял, но вполне возможно )
15. Валерий М (VmvLer) 14.12.17 09:50 Сейчас в теме
табличные документы 1С много "весят" - это особенно ощутимо для больших данных.

покрутите предлагаемый метод для эксель-файлов с сотнями тысяч строк, интересно за какое время вы прочтете
17. Алексей Кутья (alexey.kutya) 91 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) 843 14.12.17 13:21 Сейчас в теме
(15) полностью перешел на этот метод, скорость сравнима с ADO
А совместимость выше.
alexey.kutya; +1 Ответить
37. Антон Рощин (wolfsoft) 2417 20.12.17 08:58 Сейчас в теме
(22) Ага, ещё бы при чтении некоторых эксель-файлов не вылетало с ошибкой и страницы бы читала.
18. Владимир (vladismi) 158 14.12.17 10:59 Сейчас в теме
Забавно. Попробуем.
alexey.kutya; +1 Ответить
21. Павел Алексеенко (qwinter) 194 14.12.17 13:04 Сейчас в теме
Кто то узнал о существовании построителя. Круто.
26. Павел Колобков (PavelKolobkov) 14.12.17 15:34 Сейчас в теме
Здесь обсуждалось подобное сабжу
27. Алексей Кутья (alexey.kutya) 91 14.12.17 16:07 Сейчас в теме
(26) там немного про другое, но в комментариях да, упоминается про метод Прочитать.
28. Александр Хомяк (logarifm) 1003 15.12.17 01:06 Сейчас в теме
А где приемы? Я насчитал только один! Отчитали и накрыли построителем, а остальные ?
DmitrySinichnikov; +1 1 Ответить
29. Алексей Кутья (alexey.kutya) 91 15.12.17 08:29 Сейчас в теме
(28) ну вот и получается загрузка в 2 приема ))
Рассмотреть все возможные варианты работы с Excel не являлось целью публикации. Я просто хотел показать как можно с наменьшими затратами, используя код в несколько строк выполнить задачу.
30. Андрей Конев (Infector) 99 15.12.17 09:32 Сейчас в теме
Был небольшой опыт:
Когда попытался обработать через "прочитать" сохраненную контрагентом печатную форму с графическими объектами, все это дело просто повесилось. Пока что ADO и COM-объекты в таких случаях надежнее.
alexey.kutya; +1 Ответить
32. Алексей Кутья (alexey.kutya) 91 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) 91 15.12.17 19:28 Сейчас в теме
(31) Я не проверял, но думаю должен работать.
33. Алексей Кутья (alexey.kutya) 91 15.12.17 13:12 Сейчас в теме
Я не проверял, но думаю должен работать.
38. Евгений Кредько (kredko) 17 20.12.17 09:16 Сейчас в теме
Хороший метод, только вот не работает с файлами эксель с расширением xlm, который с макросами. И Данные с нескольких страниц помещает в один табличный документ.
alexey.kutya; +1 Ответить
Оставьте свое сообщение