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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

75 Comments

BigB 13.12.2017 at 14:21

Как воспримет построитель вот такие найменования колонок например:

Наименование товара

Кол-во

Дата документа

Reply ↓
alexey.kutya 13.12.2017 at 14:57

(1) воспримет нормально, но переименует по-своему ))

вот так

НаименованиеТовара

Кол_во

ДатаДокумента

Reply ↓
MaxS 13.12.2017 at 16:21

Допустим требуется заполнить реквизит Артикул справочника номенклатура. Исходный файл содержит соответствующую колонку с текстом «000123», «000124» и т.п. все символы цифровые. Какое значение попадёт в строковый реквизит номенклатуры?

Если «123», «124» и т.п., то приемы эффективной загрузки данных из Excel в 1С разбиваются о реальность ))

Reply ↓
alexey.kutya 13.12.2017 at 16:47

Попадет значение «000123».

Reply ↓
alexey.kutya 13.12.2017 at 16:49

(3) Попадет значение «000123».

Reply ↓
maxopik2 13.12.2017 at 16:54

У меня проблема в методе «Прочитать()» , ядро 8.3.10.2580 : при загрузке больших файлов (2-3 Mb) расход памяти сервера может достигать 12 Gb

пришлось отказаться от этого метода и пользоваться ADODB

Reply ↓
MaxS 13.12.2017 at 16:57

(4) Спасибо за эксперимент, пересмотрю своё мнение об этом методе. Мои опыты приводили к потере лидирующих нулей.

Reply ↓
alexey.kutya 13.12.2017 at 17:42

(7) возможно что-то уже изменили в платформе

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

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

Тип: СпособЧтенияЗначенийТабличногоДокумента.

Определяет, каким образом нужно интерпретировать значения, считываемые из исходного документа XLS, XLSX или ODS.

При загрузке табличного документа из формата Excel 97 — 2010 и OpenOffice Calc, в случае если в ячейке исходного документа содержалось значение типа Дата или Число, то в ячейку результирующего табличного документа это значение попадает в зависимости от значения этого параметра.

Значение по умолчанию: Текст.

Reply ↓
alexey.kutya 13.12.2017 at 17:43

(6) обидно )) но что поделаешь

Reply ↓
Alligator84 14.12.2017 at 05:23

Спасибо, познавательно!

Reply ↓
maxopik2 14.12.2017 at 07:20

(9) Не обидно, а вопрос: Это ошибка платформы (т.к. не может из файла 2 Mb появиться файл mxl объемом 11Gb) или я что-то не так делаю?

Reply ↓
alexey.kutya 14.12.2017 at 08:45

(11) да интересно ) если хотите, отправьте мне этот файл. Я попробую его загрузить. Если конечно там нет конфиденциальной информации.

Reply ↓
MaxS 14.12.2017 at 08:49

(11) А если на этом же сервере запустить тонкий клиент и через «Файл — Открыть» открыть этот файл? Расход памяти такой же будет?

Reply ↓
Lapitskiy 14.12.2017 at 09:31

а зачем построитель?

Можно по циклу обойти Табличный документ.

Reply ↓
VmvLer 14.12.2017 at 09:50

табличные документы 1С много «весят» — это особенно ощутимо для больших данных.

покрутите предлагаемый метод для эксель-файлов с сотнями тысяч строк, интересно за какое время вы прочтете

Reply ↓
alexey.kutya 14.12.2017 at 10:02

(14) можно и циклом. Это альтернативный способ. Мне больше нравится через построитель.

Reply ↓
alexey.kutya 14.12.2017 at 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

Reply ↓
vladismi 14.12.2017 at 10:59

Забавно. Попробуем.

Reply ↓
maxopik2 14.12.2017 at 12:20

(12) Вот файл

Reply ↓
maxopik2 14.12.2017 at 12:21

(13) Попробовал через тонкий клиент. Расход памяти такой же.

Reply ↓
qwinter 14.12.2017 at 13:04

Кто то узнал о существовании построителя. Круто.

Reply ↓
Lapitskiy 14.12.2017 at 13:22

(16) я думаю, из-за построителя и расход памяти поболее будет.

Reply ↓
alexey.kutya 14.12.2017 at 13:46

(23) сам не замерял, но вполне возможно )

Reply ↓
alexey.kutya 14.12.2017 at 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

Reply ↓
PavelKolobkov 14.12.2017 at 15:34

Здесь обсуждалось подобное сабжу

Reply ↓
alexey.kutya 14.12.2017 at 16:07

(26) там немного про другое, но в комментариях да, упоминается про метод Прочитать.

Reply ↓
logarifm 15.12.2017 at 01:06

А где приемы? Я насчитал только один! Отчитали и накрыли построителем, а остальные ?

Reply ↓
alexey.kutya 15.12.2017 at 08:29

(28) ну вот и получается загрузка в 2 приема ))

Рассмотреть все возможные варианты работы с Excel не являлось целью публикации. Я просто хотел показать как можно с наменьшими затратами, используя код в несколько строк выполнить задачу.

Reply ↓
Infector 15.12.2017 at 09:32

Был небольшой опыт:

Когда попытался обработать через «прочитать» сохраненную контрагентом печатную форму с графическими объектами, все это дело просто повесилось. Пока что ADO и COM-объекты в таких случаях надежнее.

Reply ↓
AzagTot 15.12.2017 at 12:16

Спасибо! Очень интересный способ.

Насколько я понял, он не требует наличия на компьютере Microsoft Excel или OpenOffice Calc для чтения соответствующих таблиц?

Метод Табличного Документа «Записать» точно не требует, проверено)

Reply ↓
alexey.kutya 15.12.2017 at 13:11

(30) да, в таком случае лучше ADO.

Reply ↓
alexey.kutya 15.12.2017 at 13:12

Я не проверял, но думаю должен работать.

Reply ↓
alexey.kutya 15.12.2017 at 19:28

(31) Я не проверял, но думаю должен работать.

Reply ↓
wolfsoft 20.12.2017 at 08:58

(22) Ага, ещё бы при чтении некоторых эксель-файлов не вылетало с ошибкой и страницы бы читала.

Reply ↓
kredko 20.12.2017 at 09:16

Хороший метод, только вот не работает с файлами эксель с расширением xlm, который с макросами. И Данные с нескольких страниц помещает в один табличный документ.

Reply ↓
Stradivari 13.04.2018 at 08:46

Спасибо. Статья помогла!

Reply ↓
echo77 16.04.2018 at 13:09

Просто и полезно. Укажите в публикации, пожалуйста, что это работает начиная с платформы версии 8.3.6, а с версии 8.3.10 можно грузить данные с определенного листа. Можно так же подкрепить ссылка на ИТС

Reply ↓
d4rkmesa 16.04.2018 at 22:10

Гениально. Не знал про такие фокусы с построителем и табличным документом.

Reply ↓
triviumfan 16.04.2018 at 22:39

(41) Тоже не знал) До сих пор ADO/EXCEL юзаю =)

Reply ↓
YuriIn 16.04.2018 at 22:53

(31) на сколько мне известно в 1С есть встроенный механизм работы с табличными документами, и в данном случае работает как раз встроенный механизм и получается приложение необязательное.

Reply ↓
alexey.kutya 17.04.2018 at 10:08

(44) Спасибо за дополнение 🙂 У вас отлично реализовано. Думаю будет очень полезно.

Reply ↓
ediks 17.04.2018 at 10:33

(44) А что за объект КэшФункции в функции ТекстОбластиЛиста?

И в строке Ф = НовыйФайл(ФайлExcel); пробел не пропущен НовыйФайл?

Reply ↓

ImHunter 17.04.2018 at 10:55

(46) Да, точно

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

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

ТипЗнчФайл = ТипЗнч(Файл);
Результат = Неопределено;
Если ТипЗнчФайл=КэшФункции.ТипФайл() Тогда
Результат = Файл;
ИначеЕсли ТипЗнчФайл=КэшФункции.ТипСтрока() Тогда
Результат = Новый Файл(Файл);
Иначе
ВызватьИсключение(«Непредусмотренный тип параметра Файл » + ТипЗнчФайл);
КонецЕсли;

Если ПроверятьСуществование=Истина И Не Результат.Существует() Тогда
ВызватьИсключение(«Файл не существует»);
КонецЕсли;

Возврат Результат;

КонецФункции

Показать

А КэшФункции — это модуль, закешированный на сеанс. Там, в том числе, закешированы типы данных:

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

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

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

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

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

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

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

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

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

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

Показать

Reply ↓

ImHunter 17.04.2018 at 16:32

На основании публикации доработал и свой модуль. Теперь выгружаю и в ТЗ.

// Функция — Получить таблицу значений excel
//
// Параметры:
//  Лист — ЭлементСпискаЗначений, ТабличныйДокумент  — Где искать текст.
//  ПоляТолькоПоДокументу  — Булево — Если Истина, то создаются поля по верхней строке ТабДока. Если Ложь, то создаются авто-поля Поле[N] по всей ширине ТабДока.
//
// Возвращаемое значение:
//  ТаблицаЗначений — ТЗ с данными ТабДока
//
Функция ПолучитьТаблицуЗначенийExcel(Лист, ПоляТолькоПоДокументу = Ложь) Экспорт

Если Истина Тогда
ТабДок = ПолучитьТабДок(Лист);
Иначе
ТабДок = Новый ТабличныйДокумент;
КонецЕсли;

Если ТабДок.ВысотаТаблицы=0 Тогда
Возврат Неопределено;
КонецЕсли;

// вставим заголовки
Если ПоляТолькоПоДокументу=Ложь Тогда
ВерхняяСтрока = ТабДок.Область(«R1»);
ТабДок.ВставитьОбласть(ВерхняяСтрока, ВерхняяСтрока, ТипСмещенияТабличногоДокумента.ПоВертикали, Ложь);
ВерхняяСтрока = ТабДок.Область(«R1»);
ВерхняяСтрока.Очистить();
Для Кол=1 По ТабДок.ШиринаТаблицы Цикл
ТабДок.Область(1, Кол).Текст = «Поле» + Формат(Кол, «ЧГ=»);
КонецЦикла;
КонецЕсли;

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

// вставим заголовки
Если ПоляТолькоПоДокументу=Ложь Тогда
ВерхняяСтрока = ТабДок.Область(«R1»);
ТабДок.УдалитьОбласть(ВерхняяСтрока, ТипСмещенияТабличногоДокумента.ПоВертикали);
КонецЕсли;

Возврат ТаблицаЗначений;

КонецФункции

Функция ПолучитьТабДок(Лист)

ТипЗнчЛист = ТипЗнч(Лист);
Если ТипЗнчЛист=КэшФункции.ТипТабличныйДокумент() Тогда
ТабДок = Лист;
ИначеЕсли ТипЗнчЛист=КэшФункции.ТипЭлементСпискаЗначений() Тогда
ТабДок = Лист.Значение;
Иначе
ВызватьИсключение(«Неподдерживаемый тип параметра Лист » + ТипЗнчЛист);
КонецЕсли;

Возврат ТабДок;

КонецФункции

Показать

Reply ↓

vpaoli 18.04.2018 at 09:42

(45) Я недавно сравнивал скорость загрузки через ADO и с помощью метода

Область = ЛистЭксель.Range(…);

Данные = Область.Value.Выгрузить();

Получилось, что второй метод быстрее ADO где то на 25%

Вы не сравнивали метод Range с вашим методом загрузки ? Что быстрее и насколько. Может сравните для полноты ощущений …?

Reply ↓
vpaoli 20.04.2018 at 12:22
Раз уж Вы пишете «Приемы эффективной загрузки …» , наверно необходимо сравнение с другими существующими . Иначе это может быть и «не эффективной загрузки».

Есть метод:
```
Область = ЛистЭксель.Range(…);
Данные = Область.Value.Выгрузить(); 
```
Вы не сравнивали метод Range с вашим методом загрузки ? Что быстрее и насколько ?

Reply ↓
alexey.kutya 23.04.2018 at 12:58

(59)

Область = ЛистЭксель.Range(…);

Данные = Область.Value.Выгрузить();

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

Время выполнения чтения файла вашим методом 36 сек. Время выполнения методом описанным в моей публикации 193 сек. Разница в 5 раз 🙂 Но ваш метод возвращает массивы значений, надо попробовать их разобрать.

Reply ↓
ImHunter 23.04.2018 at 13:24

(65) Так ожидаемо, что ТабДоком медленнее. И значительно тяжелее, наверное, по расходу памяти.

Но профит в том, что не нужен Excel, ADO-провайдеры. Пожалуй, только из-за этого на табдоки перебрался.

Reply ↓
alexey.kutya 23.04.2018 at 14:11

(66)

ADO-провайдер

Конечно, если время выполнения приемлемое, то почему бы и нет.

Reply ↓
vpaoli 24.04.2018 at 10:52

(65)

Но ваш метод возвращает массивы значений, надо попробовать их разобрать.

Все уже разобрано…. Есть публикации вместе с кодом на инфостарте.

Reply ↓
ImHunter 27.04.2018 at 13:42

Хех, недолго радовался:( Придется отказаться от такого чтения. Тесты в боевых условиях валятся по нехватке памяти. Похоже, чтение ТабДоком сильно не любит формулы.

Reply ↓
alexey.kutya 27.04.2018 at 15:11

(69) Да, жаль )

Reply ↓
ImHunter 28.04.2018 at 06:47

(70) Для справки. Тесты гонял на 8.3.10.2580, 32-бит.

Так что, народ, аккуратнее с загрузкой ТабДоком…

Reply ↓
vis_tmp 05.05.2018 at 15:06

Не могу понять, вашим методом загружается ли гиперссылка из ячейки Excel-а?

Гиперссылка представляет из себя URL на картинку на сайте (прилагаю для примера кусочек такого файла).

Кому-нибудь удалось получить URL гиперссылки из подобного файла?

Reply ↓
alexey.kutya 25.12.2018 at 15:08

(72) может уже не актуально, но нет, гиперссылка не загружается

Reply ↓
user755058 05.02.2019 at 16:06

(40) везде только и говорят про

а с версии 8.3.10 можно грузить данные с определенного листа

но нигде не нашёл ни ссылку на документацию ни пример реализации . Может кто знает — поделитесь пожалуйста.

Reply ↓
vis_tmp 10.03.2019 at 23:04

(73)Актуально!

Как бы научиться загружать гиперссылки?

Reply ↓
user700035_6550355 11.04.2019 at 08:17

(4) При загрузке Артикула = «80665» загружается как «80 665» вне зависимости от СпособЧтенияЗначенийТабличногоДокумента (Текст или Значение)

Reply ↓
pushkarev 10.06.2019 at 16:49

добрый день. На 64 битную подойдет?

Reply ↓
alexey.kutya 10.06.2019 at 17:37

(78) не проверял, но думаю должно работать

Reply ↓
pushkarev 10.06.2019 at 18:22

Перехожу на 1с розница 2.2. Хочу из EXEL импортировать. Не могу тут ничего скачать. на почту не перешлете.?

Reply ↓
pushkarev 10.06.2019 at 18:46

pushkarev.artem@mail.ru

Reply ↓
alexey.kutya 11.06.2019 at 09:38

Отправлю, только доберусь до компа

Reply ↓
alexey.kutya 11.06.2019 at 09:40

(80) отправлю, только доберусь до компа

Reply ↓
user811769 16.07.2019 at 11:09

Добрый день! Алексей, а эффективно ли будет работать построитель с такой шапкой:

Reply ↓
alexey.kutya 16.07.2019 at 12:06

(84) добрый день!

Я думаю, что построитель некорректно обработает такую шапку. Лучше чтобы шапка была в одну строку.

Reply ↓
user811769 16.07.2019 at 12:16

(85) спасибо за ответ и за статью!

Построитель положу в копилочку решений с простыми шапками, для этой попробую COM или ТабличныйДокумент =)

Reply ↓
alexey.kutya 16.07.2019 at 13:31

(86) рад что вам пригодилось )

Reply ↓
user703966_martynyuknatalia 17.07.2019 at 04:20

Функция ПрочитатьЛистExcel(ТЗ = Неопределено, ЛистЭксель = Неопределено, НомерПервойСтроки = 1, НомерПервойКолонки = 1, ВсегоСтрок = 0, ВсегоКолонок = 0) Экспорт

Если ЛистЭксель = Неопределено Тогда

ЛистЭксель = ПолучитьCOMОбъект(,»Excel.Application»);

КонецЕсли;

Если ВсегоСтрок = 0 Тогда

ВсегоСтрок = ЛистЭксель.Cells.SpecialCells(11).Row;

КонецЕсли;

Если ВсегоКолонок = 0 Тогда

ВсегоКолонок = ЛистЭксель.Cells.SpecialCells(11).Column;

КонецЕсли;

Если ТЗ = Неопределено Тогда

ТЗ = Новый ТаблицаЗначений;

Для Счетчик = 1 По ВсегоКолонок Цикл

ТЗ.Колонки.Добавить(«Колонка»+Счетчик, Новый ОписаниеТипов(«Строка»));

КонецЦикла;

КонецЕсли;

Для Счетчик = НомерПервойСтроки По ВсегоСтрок Цикл

НоваяСтрока = ТЗ.Добавить();

КонецЦикла;

Область = ЛистЭксель.Range(ЛистЭксель.Cells(НомерПервойСтроки,НомерПервойКолонки), ЛистЭксель.Cells(ВсегоСтрок,ВсегоКолонок));

Данные = Область.Value.Выгрузить();

Для Счетчик = 0 По ВсегоКолонок-1 Цикл

ТЗ.ЗагрузитьКолонку(Данные[Счетчик], Счетчик);

КонецЦикла;

ЛистЭксель = Неопределено;

Возврат ТЗ;

КонецФункции

Reply ↓
medangel 13.09.2019 at 11:04

Используем этот механизм, спасибо. Но есть проблемы при загрузке файла с группировками (уровень группировки не определить) + в шапке не должно быть объединенных ячеек.

Reply ↓
alexey.kutya 16.09.2019 at 10:14

(89) да, некоторые ограничения есть

Reply ↓
user892257 31.10.2019 at 08:22

Вот пример файла, который данный способ не охватывает.

Reply ↓
alexey.kutya 31.10.2019 at 09:23

(91) да, этот файл метод 1с не сможет обработать корректно, первая строка должна содержать заголовки

Reply ↓
user1315758 24.11.2019 at 19:59

Можно ли этим способом вытащить из файла только нужные колонки и сразу их сгруппировать? Далее нужно эти данные поместить во временную таблицу и соединять с регистрами. Для этого ТЗ должна быть типизированной. Как это сделать на уровне построителя?

Reply ↓
alexey.kutya 25.11.2019 at 14:22

(93) я думаю построитель автоматом не типизирует колонки, для запроса не получится подставить такую таблицу без спец. обработки

Reply ↓

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

Related Posts

75 Comments

Leave a Comment Отменить ответ