Чтение данных из Excel. Шаблон кода

01.03.24

Интеграция - Загрузка и выгрузка в Excel

Коллеги, наверняка каждый писал или планирует какую-то загрузку данных из Excel. Задача до сих пор очень популярна. Мне лично надоело постоянно писать один и тот же код. Для этого сделал его максимально универсальным, чтобы заменить текст в 4 процедурах и данные из Excel у Вас в менеджере временных таблиц. Особенно полезна обработка начинающим разработчикам!

Скачать файлы

Наименование Файл Версия Размер
Обработка "Чтение данных из Excel". Шаблон кода.:
.epf 9,03Kb
1
.epf 1.0 9,03Kb 1 Скачать

Ранее уже делал подобную обработку для вывода на печать макета из файла Open Office. Вот ссылка на эту публикацию:

Пример работы с файлами odt в клиент-серверной модели работы

Тестирование проводилось на платформе 8.3.16. Но уверен, что будет работать на всех новых релизах и старых до 8.3.8.

 

Коллеги, прошу ознакомиться с целью данной публикации:

Данная публикация создана НЕ для того чтоб рекламировать устаревший способ чтения данных из Excel. Способ можно изменить за 2 минуты, он всем известен (в т.ч. и мне). 

Её цель - показать способ написания кода таким образом, чтоб его можно было за 20 минут адаптировать под любую новую задачу.

Опишу особенности реализации данной обработки:

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

2. Работает она через COM объект. Да способ не очень быстрый и как бы устаревший, но часто используемый и проверенный! 

3. Обработка позволяет считать одновременно неограниченное количество файлов. Их список задаётся в отдельной функции. Расширение задаёте там же, хоть xls, хоть xlsx. Главное условие - все файлы должны иметь одинаковые колонки. 

4. Предположу область применения: если есть много филиалов и они выгружают файлы одинаковой структуры, а Ваша задача консолидировать. 20 и более раз нажимать кнопку "Загрузить" это неудобно. Проще всё считать разом и обработать за один подход.

5. В отдельной функции задаётся соответствие номера колонки в файле и названия поля. Название далее используется для формирования колонки в таблице значений и поля в запросе.

6. В отдельной функции задаётся соответствие названия колонки и типа значения. Используются примитивные типы данных: Строка, Число, Булево, Дата. Обработана ситуация, если тип значения число, а значение в ячейке пустое. Ошибки не будет, в таблицу будет записан ноль.

7. В коде есть маркер "_НазваниеТаблицы". Его через поиск и замену значений нужно заменить на нормальное значение. Например, в моей задаче это ОстаткиОтпусков. Это необязательно, просто для читабельности кода.

8. В обработке выбирается именно каталог, в котором хранятся файлы. Имена файлов задаются в функции в виде массива.

9. Результат чтения всех файлов помещается во временную таблицу с именем "ВТ_Excel_" + "_НазваниеТаблицы".

10. Временные файлы, используемые для передачи данных с клиента на сервер после чтения данных удаляются.

 

Если решите скачать обработку и использовать, то Вам необходимо выполнить следующие действия:

1. Заменить имена файлов в функции "ПолучитьСписокФайлов":

&НаКлиенте
Функция ПолучитьСписокФайлов()

	СписокФайлов = Новый Массив;
	
	СписокФайлов.Добавить("Файл1.xlsx");
	СписокФайлов.Добавить("Файл2.xlsx");
	СписокФайлов.Добавить("Файл3.xlsx");
	СписокФайлов.Добавить("Файл4.xlsx");
	СписокФайлов.Добавить("Файл5.xlsx");
	СписокФайлов.Добавить("Файл6.xlsx");
	СписокФайлов.Добавить("Файл7.xlsx");
	СписокФайлов.Добавить("Файл8.xlsx");
	СписокФайлов.Добавить("Файл9.xlsx");
	СписокФайлов.Добавить("Файл10.xlsx");
	
	Возврат СписокФайлов;

КонецФункции // ПолучитьСписокФайлов()

2. Указать соответствие колонок файла Excel и названия полей в функции ПолучитьСоответствиеПолей_НазваниеТаблицы:

&НаКлиенте
Функция ПолучитьСоответствиеПолей_НазваниеТаблицы()

	СоответствиеПолей = Новый Соответствие;
	
	СоответствиеПолей.Вставить(1, "Поле1");
	СоответствиеПолей.Вставить(2, "Поле2");
	СоответствиеПолей.Вставить(3, "Поле3");
	СоответствиеПолей.Вставить(4, "Поле4");
	СоответствиеПолей.Вставить(5, "Поле5");
	СоответствиеПолей.Вставить(6, "Поле6");
	
	Возврат СоответствиеПолей;
	
КонецФункции // ПолучитьСоответствие_НазваниеТаблицы()

3. Указать для каждого поля тип значения в таблице, чтоб в запрос попали типизированные данные, а не неограниченные строки/числа. Соответствие задаётся в функции ПолучитьСоответствиеТиповПолей_НазваниеТаблицы:

&НаКлиенте
Функция ПолучитьСоответствиеТиповПолей_НазваниеТаблицы()

	СоответствиеПолей = Новый Соответствие;
	
	СоответствиеПолей.Вставить("Поле1", Новый ОписаниеТипов("Строка",,,, Новый КвалификаторыСтроки(150)));
	СоответствиеПолей.Вставить("Поле2", Новый ОписаниеТипов("Дата"));
	СоответствиеПолей.Вставить("Поле3", Новый ОписаниеТипов("Булево"));
	СоответствиеПолей.Вставить("Поле4", Новый ОписаниеТипов("Число",,, Новый КвалификаторыЧисла(6, 0)));
	СоответствиеПолей.Вставить("Поле5", Новый ОписаниеТипов("Число",,, Новый КвалификаторыЧисла(7, 2)));
	СоответствиеПолей.Вставить("Поле6", Новый ОписаниеТипов("Число",,, Новый КвалификаторыЧисла(7, 2)));
	
	Возврат СоответствиеПолей;
	
КонецФункции // ПолучитьСоответствиеПолей_НазваниеТаблицы()

4. Заменить строку "_НазваниеТаблицы" на название, описывающее Вашу решаемую задачу. 

 

Как видно, действия для адаптации этого коды простые и понятные. Время на адаптацию кода минут 20 максимум! 

Также этот код будет полезен новичкам/стажерам. Т.к. именно им часто приходится писать подобные загрузки в целях совершенствования навыков программирования.

На момент публикации в обработке более 300 строк структурированного кода. Обработка протестирована на реальных файлах и ошибок не содержит!

 

При наличии интереса к этой обработке сделаю такой же шаблон для чтения данных из файла Access. Разница в том, что чтение данных будет через ADO, и реализовано запросами. 

Напомню ранее написанные статьи с общим названием "Ни в ЗУП ногой?!  А мне нравится!":

1. Главные сложности решения, что отталкивает?

2. Плюсы решения, где они прячутся?

3. Как меня туда занесло?

4. Главное - правильный перенос данных!

Статьи под общим названием "Как читать чужой код?":

Часть 1. Общие вопросы. Доработка чужого кода. Code review.

Часть 2. Доработка типовой конфигурации. Обновление доработанной типовой конфигурации.

Часть 3. Разбор и доработка запросов

Часть 4. Программный интерфейс. Исправление чужих доработок.

Отдельная статья про роль и значимость архитектора на проекте:

1. Кто такой архитектор. Редакция 2!

Проверено на следующих конфигурациях и релизах:

  • 1С:ERP Управление предприятием 2, релизы 2.5.16.80

См. также

SALE! 10%

Загрузка номенклатуры из Excel в УТ11, КА 2, ERP 2, Розница 2. Дополнительные реквизиты и сведения, характеристики, картинки, цены, остатки

Загрузка и выгрузка в Excel Розничная торговля Логистика, склад и ТМЦ Ценообразование, анализ цен Прайсы Платформа 1С v8.3 1С:Комплексная автоматизация 1.х 1С:Розница 2 1С:ERP Управление предприятием 2 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х Управленческий учет Платные (руб)

Загрузка из файлов xls, xlsx, ods, csv, mxl в УТ11, КА 2, ERP 2, Розница 2. Задействованы все возможности конфигурации - заполнение реквизитов номенклатуры, дополнительных реквизитов и сведений, характеристики, доп.реквизиты и сведения характеристик. Дополнительные обработки для расширения возможностей.

10560 9504 руб.

29.10.2014    210208    620    524    

439

Загрузка номенклатуры c картинками (несколько потоков одновременно) и сопутствующими данными в базу и любые документы из yml, xls, xlsx, xlsm, ods, ots, csv для УТ 10.3, УТ 11 (все), БП 3, КА 2, ERP 2, УНФ 1.6/3.0, Розница 2

Загрузка и выгрузка в Excel Логистика, склад и ТМЦ Ценообразование, анализ цен Файловый обмен (TXT, XML, DBF), FTP Платформа 1С v8.3 1С:Бухгалтерия 2.0 1С:Управление торговлей 10 1С:Розница 2 1С:Управление нашей фирмой 1.6 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х 1С:Управление нашей фирмой 3.0 Платные (руб)

Эволюция не стоит на месте - новая удобная версия функциональной обработки для Вашего бизнеса! Что же Вы получаете? Удобный и интуитивно понятный интерфейс с 3-мя этапами работы. 2 режима - автоматический и ручной. Чтение XLSX, XLSM, CSV, XML/YML форматов без офиса, на любом сервере! Визуальное связывание колонок файла и реквизитов простым перетаскиванием колонок. Создание или обновление номенклатуры с иерархией, характеристик, доп. реквизитов, упаковок, загрузка практически неограниченного количества картинок на одну номенклатуру (с возможностью загрузки в несколько потоков одновременно), с хранением в томах или в базе. Загрузка номенклатуры поставщиков или поиск по их данным номенклатуры. Загрузка доп. реквизитов в характеристики. Загрузка штрихкодов с генерацией новых. Создание элементов справочников и ПВХ "на лету" для выбранных реквизитов. (Обновление от 11.12.2023, версия 9.5 - 9.9)

13200 руб.

20.11.2015    150739    365    375    

501

Загрузка данных отчета о реализации товаров из Excel файла СберМегаМаркет

Загрузка и выгрузка в Excel Маркетплейсы Платформа 1С v8.3 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Управление нашей фирмой 3.0 Бухгалтерский учет Управленческий учет Платные (руб)

Загрузка данных отчета о реализации товаров из сервиса СберМегаМаркет для конфигурации: Бухгалтерия предприятия, редакция 3.0; Управление торговлей, редакция 11 и Управление нашей фирмой, редакция 3.0 в документ "Отчет комиссионера (агента) о продажах".

4800 руб.

07.06.2022    15085    79    0    

60

SALE! 30%

Распознавание и загрузка сканов в 1С "одним нажатием": УПД, ТОРГ-12, накладные, счета, номенклатура, заказы и т.д.

Загрузка и выгрузка в Excel Документооборот и делопроизводство (СЭД) Учет документов Распознавание документов и образов Управляемые формы 1С:Комплексная автоматизация 1.х 1С:Бухгалтерия 2.0 1С:Управление торговлей 10 1С:Розница 2 1С:Управление производственным предприятием 1С:Управление нашей фирмой 1.6 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Управление холдингом 1С:Комплексная автоматизация 2.х 1С:Управление нашей фирмой 3.0 Бухгалтерский учет Управленческий учет Платные (руб)

Универсальная программа для распознавания ЛЮБЫХ СКАНОВ ИЛИ ФОТО ТОВАРНЫХ ДОКУМЕНТОВ в 1С. Не требует указания параметров и предварительной настройки. Просто выбираете файл (PDF, JPG, DOC, XLS, HTML и т.д.) выбираете документ 1С и нажимаете кнопку "Распознать и загрузить".

6000 5520 руб.

04.06.2019    101403    296    173    

312

SALE! 20%

Загрузка документов и номенклатуры из Excel в 1С "одним нажатием": УПД, ТОРГ-12, отчеты маркетплейсов, заказы, счета, прайсы

Загрузка и выгрузка в Excel Оптовая торговля Платформа 1С v8.3 Управляемые формы Платформа 1C v8.2 Конфигурации 1cv8 1С:Комплексная автоматизация 1.х 1С:Бухгалтерия 2.0 1С:Управление торговлей 10 1С:Розница 2 1С:Управление производственным предприятием 1С:Управление нашей фирмой 1.6 1С:ERP Управление предприятием 2 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х 1С:Управление нашей фирмой 3.0 Бухгалтерский учет Управленческий учет Платные (руб)

Универсальная обработка для загрузки документов из Excel в 1С. Не требует указания параметров (номера колонок, номер первой строки таблицы и т.д.) и предварительной настройки. Просто выбираете файл Excel, документ 1С и нажимаете кнопку "Загрузить". Обработка сама находит таблицу в файле Excel, необходимые для загрузки данные в ней (номенклатура, количество, НДС, цена, сумма) и загружает ее в 1С. Вместе с номенклатурой может найти контрагента, номер и дату документа, штрих-коды, серии ГТД, страну и т.д. Распознает документы ЛЮБОЙ ФОРМЫ (УПД, ТОРГ-12, заказ, отчет комиссионера и т.д.). Не требует MS Office. Для поиска таблиц используются методы эвристического поиска. Загружает только то, что нужно, т.е. пропускает повторы шапки таблицы, заголовки, промежуточные итоги, подписи и т.д. Содержит модуль работы с электронной почтой и api-загрузчик отчетов о продажах маркетплейсов.

5000 4000 руб.

09.11.2016    214951    923    886    

939

Универсальная загрузка остатков и цен поставщиков в 1С (УТ 11, КА 2, ERP 2.0) из файлов Excel, CSV, XML (YML и CML)

Прайсы Оптовая торговля Розничная торговля Ценообразование, анализ цен Файловый обмен (TXT, XML, DBF), FTP Загрузка и выгрузка в Excel Платформа 1С v8.3 1С:ERP Управление предприятием 2 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х Управленческий учет Платные (руб)

Обработка позволяет легко настроить загрузку прайс-листов и остатков поставщиков. Форматы файлов задаются для каждого поставщика отдельно. В настройках предусмотрена возможность гибкой обработки входящих данных (убрать лишние символы, пересчитать цены в другую валюту, сделать произвольную наценку и т.д.).

12000 руб.

23.01.2017    58866    59    48    

68
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. agentz 40 18.08.22 13:38 Сейчас в теме
а запустите этот код на машине под linux или на машине без экселя.. код устарел и морально и физически. COM соединение уже давно нужно забыть
читать из экселя можно одной функцией
ТабличныйДокумент (SpreadsheetDocument)
Прочитать (Read)
Вариант синтаксиса: Из файла
Синтаксис:
Прочитать(<ИмяФайла>, <СпособЧтенияЗначений>)
Параметры:
<ИмяФайла> (обязательный)
Тип: Строка. 
Имя файла табличного документа.
<СпособЧтенияЗначений> (необязательный)
Тип: СпособЧтенияЗначенийТабличногоДокумента. 
Определяет, каким образом нужно интерпретировать значения, считываемые из исходного документа XLS, XLSX или ODS.
При загрузке табличного документа из формата Excel 97 - 2010 и OpenOffice Calc, в случае если в ячейке исходного документа содержалось значение типа Дата или Число, то в ячейку результирующего табличного документа это значение попадает в зависимости от значения этого параметра.
Значение по умолчанию: Текст.
Показать
Revachol; sevenor; neyasytyf; Jimbo; +4 Ответить
2. biimmap 1835 18.08.22 13:45 Сейчас в теме
(1)
а запустите этот код на машине под linux или на машине без экселя


Слава богу у меня такой машины нет) Ни с линуксом, ни без экселя!


(1)
код устарел и морально и физически


Никакой новости в Вашем комментарии нет. Если Вам показалось, что статья рекламирует чтение из Excel через COM соединение - Вы невнимательно читали написанное.

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

Заменив всего лишь 2 функции в моей обработке можно прочитать Excel через ADODB или через Табличный документ.
Проблема в том, что каждый раз в разовых обработках код пишут почти нуля.

Предложенный подход позволяет за 20 минут написать чтение данных из Excel с результатом в Менеджере временных таблиц. Вот об этом публикация!
3. gybson 18.08.22 14:22 Сейчас в теме
(2)Я бы за пропаганду хардкода банил =)

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


Все, все данные в таблице значений. Где-то тут этот пример и был.
Nikola23; Jimbo; +2 Ответить
4. biimmap 1835 18.08.22 14:53 Сейчас в теме
(3) Уже ответил в чём назначение обработки.
5. gybson 18.08.22 15:05 Сейчас в теме
6. biimmap 1835 18.08.22 15:25 Сейчас в теме
(5) не возражаю. у него свои цели, у меня свои.
7. Jimbo 9 19.08.22 08:44 Сейчас в теме
(2)
Слава богу у меня такой машины нет) Ни с линуксом, ни без экселя!

Если нет, то скоро у всех будут. Импортозамещение - не, не слышал ? И не стоит так огрызаться на конструктивные замечания по существу. Критика по существа весьма приветствуется.
9. biimmap 1835 19.08.22 09:51 Сейчас в теме
(7) Кстати год назад столкнулся с тем что офис обычный не стоит. Стоял Open Office.
В начале статьи давал ссылку на публикацию о работе с шаблонами ODT.

На другом проекте с помощью табличного документа делал выгрузку в Excel. Удобный способ. Правда сохранение файла жутко долго идёт. И переход с сервера на клиент занимает много времени.
8. biimmap 1835 19.08.22 09:39 Сейчас в теме
(7)
Импортозамещение - не, не слышал ?


Работаю в коммерческом секторе. Так уж сложилось. Поэтому не слышал и вряд ли услышу. А если уж услышу 100% изменю способ чтения данных.


(7)
Критика по существа весьма приветствуется.


Полностью согласен с этим тезисом! НО! Теперь поясню почему она НЕ конструктивная:

В самом начале статьи я сам написал следующую строку:
2. Работает она через COM объект. Да способ не очень быстрый и как бы устаревший, но часто используемый и проверенный!


Т.е. я сам указал на то, что способ чтения данных не самый правильный. Зачем об этом писать так настойчиво?
Надо увидеть главную цель этой обработки. Выше выделил жирным. А способ чтения данных легко меняется.
10. Nikola23 696 25.08.22 14:23 Сейчас в теме
Чем вас не устраивает ТабДок.Прочитать(ИмяФайла) с последующим получением таблицы значений через построитель?

Лично меня вот, не устраивает статья, в которой предлагается решение, устаревшее и зависящее от наличия экселя и операционной системы, работающее долго.

Придет джун, получит задачу прочитать эксель - наткнется на вашу статью и получит устаревшие знания, вместо актуального и быстро-работающего кода.
11. biimmap 1835 25.08.22 14:26 Сейчас в теме
(10)
Чем вас не устраивает


Выше уже есть ответ на Ваш вопрос. Статья не о том, о чём Вы спрашиваете. Выделено жирным.
Для меня не важен способ чтения данных. Он меняется довольно легко.
12. biimmap 1835 25.08.22 14:28 Сейчас в теме
(10) и в самой статье в пункте 2 написано, что способ чтения не самый правильный. Смысла об этом писать в комментах нет никакого.

Посмотрите куски кода, которые скопированы в текст обработки. В них есть что-то про способ чтения?
13. Nikola23 696 25.08.22 14:30 Сейчас в теме
(12) полагаю, ценность материала от выкладывания "не самого правильного" метода снижается.
А еще, коллега, 10 строк кода и большие файлы начинают читаться "мгновенно". Это ли недостаточный повод, чтобы повысить как ценность статьи, так и экономию собственного времени?

Уверен, что и благодарностей будет больше, а претензий меньше.

К сожалению, сейчас - текст статьи написан непонятно зачем.
14. biimmap 1835 25.08.22 14:35 Сейчас в теме
(13) Наверно нужно уметь искать пользу в написанном!? И правильно улавливать суть. Т.к. "правильный" метод чтения известен всем и не является чем-то сложным, то в данном случае можно им пренебречь.

Следующая публикация на похожую тему будет про Access. Там чтение идёт запросом через ADO. Но главное в ней, как и в этой публикации будет не способ чтения, а универсальность кода и скорость его адаптации к новой задаче.
15. user1833560 26.08.22 10:51 Сейчас в теме
(14) biimmap странный у вас способ вести диалог.
Зачем вы добавили Nikola23 в черный список?
16. pvt071 31.08.22 14:10 Сейчас в теме
(10) полностью поддерживаю! поскольку именно таким джуном и являюсь.
Оставьте свое сообщение