Универсальная загрузка данных формата Excel

14.04.21

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

Универсальная обработка по чтению/импорту данных формата Excel в базы 1С.

 

В данной публикации приведено описание механизма загрузки данных в 1С из "двумерного формата" Excel, а также ряд примеров использования механизма.

Если лень читать длинное описание и смотреть унылые картинки: код можно взять здесь: https://github.com/artkor83/-ImportExcelOnec. И в дальнейшем разбираться самостоятельно: код полностью открыт.

 

Оглавление

Введение

Получение  данных

Получение данных: общее описание, алгоритмы

Получение данных: Порядок работы

Получение данных из листа excel

Получение данных из макета (буфера)

Режим чтения макета из внешнего файла

Режим чтения макета из буфера ячеек

Дополнительные команды

Дополнительные команды: Номера колонок: чтение наименований из файла

Дополнительные команды: Номера колонок: выбор типов

Дополнительные команды: Номера колонок: определение типов

 

Загрузка данных

Загрузка данных: Внешние файлы: обработки, макеты

Загрузка данных: Алгоритмы "Перед загрузкой", "Загрузка данных строки"

Загрузка данных: буфер вспомогательного кода

 

Примеры загрузки данных

Построчная загрузка данных: пример

Построчная загрузка данных: редактирование алгоритмов

Построчная загрузка данных: Загрузка данных в базу

Общая (непострочная) загрузка данных: пример

Общая (непострочная) загрузка данных:редактирование алгоритмов

Общая (непострочная) загрузка данных:загрузка данных в базу

 

Отладка алгоритмов

Отладка построчной загрузки

Отладка общей (непострочной) загрузки.

 

Сохранение / загрузка настроек

Интерфейс сохранения, краткое описание

Примеры файлов настроек, рекомендации

 

 

Введение.

В процессе работы очень часто встают задачи по импорту информации в базы 1С (далее БД1С) из различных excel-файлов. Наиболее распространенные варианты:

  • импорт начальных остатков,

  • прайс-листы различных видов и модификаций

  • сегменты номенклатуры, клиентов

  • прочее: справочники, документы, регистры сведений различного назначения

Если начать с последнего пункта: часто бывает нужно загрузить набор справочников со специально заполняемыми реквизитами, которые простой связью "1-к-1" никак не удается заполнить: появляются "красные поля" в карточке справочников типа "ФизическиеЛица", которые в дальнейшем "мешают жить" в процессе эксплуатации информационных систем.

 

 

На практике все неумолимо сводится к написанию программного кода, где на входе поступает некий объект "ТаблицаЗначений", полученный из Excel, на выходе "все что угодно":

  • Набор элементов ссылочного типа с фиксированными полями. Пример: элементы типа "Справочник.Контрагенты" с видом(физическое лицо/ юридическое лицо), зависящим от длины ИНН из файла excel

  • Набор элементов ссылочного типа нескольких метаданных. Пример: элементы типа Справочник.Сотрудники и "приклеенные" к ним физические лица: Справочник.ФизическиеЛица. При этом не забываем, что наименование физ.лица вовсе не единственное "обязательное поле": необходимо еще указать ФИО, разбивку по реквизитам "Фамилия Имя Отчество".

  • Документ по вводу начальных остатков: тут комментарии излишни. Вариантов "видов операций по вводу остатков" множество в базе формата УТ11, не говоря про ERP ;) И все их нужно как-то обыграть.

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

 

 

При записи объекта система зачастую ругается на поля которые и на форме-то не видны (являются скрытыми), пользователи в ярости, разработчик "на измене".

При импорте данных через формат excel процесс требует значительных программных доработок. Целесообразно под рукой иметь удобный механизм для чтения/обработки/загрузки данных...

Загрузка обработками "на коленке"

Это - плохой путь: не делайте так.

Путем практического опыта с различными загрузками из excel вначале все сводилось к следующему алгоритму:

  1. Есть заготовленный шаблон внешней обработки, который через com читает excel-файл и в нужной закладке собирает данные. Пример кода ниже, образцов можно нагуглить великое множество:

    Excel         = Новый COMОбъект("Excel.Application");
    Excel.Visible = 0;
    Excel.DisplayAlerts = 0;
    Excel.WorkBooks.Open(ИмяФайлаЗагрузки);
  2. Каждая прочитанная строка преобразуется в массив строковых ячеек, которые имеют свое назначение и в дальнейшем используются для загрузки данных

  3. Набор данных выводится на форму в таблицу значений. Как правило, случаются пользовательские ошибки в файле excel. Например:

    • вместо числового поля ввели какую-то непонятную "чудо-строку", которая в момент преобразования значения в число дала ошибку и тем самым файл даже не дочитался

    • ключевое значение типа "артикул номенклатуры" введен в файле неверно - тем самым имеем множество пустых строк товаров, по которым заполнены показатели, а "всего-то нолик вначале забыли добавить до длины=10 символов"

    • файл excel модифицировался: добавили 2 колонки данных в листе excel между "номенклатура" и "цена", а программисту сказать забыли ;)

  4. Загрузка данных. Полученная на форму таблица загружается в базу. При этом данные могут быть неполными из-за ошибок, также есть потребность отредактировать данные на форме. Сама загрузка зачастую идет по сложному алгоритму. одной конструкцией вида

    Процедура ЗагрузитьДанные(ТаблицаЗначенийИзExcel)

    Для каждого ДанныеСтроки Из ТаблицаДанныхExcel Цикл R03; // алгоритм обработки загрузки

    КонецЦикла;

    КонецПроцедуры

    никак не удаестся обойтись.

    Требуется множество процедур и функций на каждый конкретный случай.

  5. Итог: хаос из непонятных обработок чтения-загрузки, файлов-шаблонов excel различного вида "Штатнаярасстановка.xls, Штатнаярасстановка-02.xls, Штатнаярасстановка-03.xls, Штатнаярасстановка-03безОшибокФИО.xls, Штатнаярасстановка-03СНумерацией.xls, Штатная_расстановка-03Сбантиком.xls" и на каждый своя обработка загрузки... Решено было сделать хорошие вещи (ну хотя-бы попытаться).

Получение данных.

 

Общий интерфейс.

Пришли к выводу, что для чтения данных нужно: сам файл excel, расположенный на диске, закладка для чтения данных, интервал чтения строк данных, и кнопка "Получить данные" для вывода данных в ТЗ на форму:

 

 

Кроме этого требуется набор колонок с настройкой типов:

  • Нужна таблица настроек колонок вида "Имя колонки, номер колонки, Тип значения"

  • На форме обработки их нужно как-то сохранять, иначе будет "грустно" при каждом открытии формы заново их вводить.

  • Желательно сохранять их в какой-нибудь внешний файл и впоследствии читать из файла в другой копии БД1С.

В итоге решено было сделать такие настройки в виде таблицы на форме:

 

 

Пока нужно знать следующее: есть таблица имен колонок, соотствествие номеру колонки из excel "№ кол" и выбранный "Тип значения" колонки. Назначение команд формы "Прочитать колонки по номеру строки", "Поиск типов" будет описано ниже.

 

По команде "Получить данные" производится чтение данных из указанного диапазона строк excel-файла (это очевидно). Следует выделить 3 режима получения:

  • 1 лист excel

  • несколько листов excel

  • режим "по макету": вставка данных из буфера в табличное поле на форме

Получение данных: общее описание, алгоритмы

По команде "Получить данные" производится чтение данных из указанного диапазона строк excel-файла (это очевидно), после этого полученные данные требуется преобразовать в типы, введенные на закладке "Номера колонок".

Если тип примитивный (Число,Строка,Дата,Булево) - обработка автоматически преобразует строковое значение ячейки в полученный тип. Для прочих типов:

  • Справочник, план видов характеристик - "НайтиПоНаименованию"

  • План счетов, план видов расчета: "НайтиПоКоду"

  • Документ: "НайтиПоНомеру"

  • Перечисление: поиск по синониму или по имени реквизита. Для примера "ПеречислениеСсылка.ВидыЗанятости" по строке "Основное место работы" или "ОсновноеМестоРаботы" вернет элемент перечисления с данным синонимом.

Но зачастую обычного простого преобразования не хватает: нужна программная обработка. Для этого предусмотрен специальный текстовый код на языке 1С, который срабатывает после чтения каждой строки.

Получение данных: Порядок работы

  1. Переходим на закладку "Алгоритмы", редактируем поле "Поиск данных строки":

  1. В примере текст кода уже заполнен, предположим что "его нет". нажимаем кнопку "Поиск данных строки". В левой части подсказка кода (не редактируется), в правой - сам код: при желании можно изменять:

 

Наиболее "интересные" переменные:

  • ТекКолонкаЗначение - Значение реквизита (строкой). Это та строка, которая получена из ячейки excel - то есть значение колонки. Его можно преобразовывать к нужному типу программно.

  • ТекКолонкаЗначениеЗапись - !!! Значение реквизита ДЛЯ ЗАПИСИ. Это как раз то, что будет подставлено в колонку данных на форме 1С, то есть значение "ТекКолонкаЗначение", преобразованное в нужный тип.

    ПРИМЕЧАНИЕ: если преобразование по умолчанию удалось (например найден справочник по наименованию, или перечисление по синониму), то в данной переменной уже будет преобразованное значение и его можно программно использовать.

  • ТекОтказ: признак отказа от вывода строки. Если какие-то числовое значение в строке не заполнено, или строковое не обнаружено - то возможно, что эта строка вообще к загрузке не нужна. К примеру ФИО сотрудника пустое, оклад не указан итд..

  • Прочие функции: набор вспомогательных функций для преобразования данных из строки (бывает полезно).

  • Фиксированные значения. Можно задать фиксированные параметры данных на специальной закладке. Дабы не искать определенные статические параметры через "НайтиПоКоду", "НайтиПоНаименованию" (это нехорошо) предусмотрена спец. закладка:

 

Для примера подстановка организации в каждую колонку данных выглядит так:

 

  1. Печать кода для преобразования. Вверху расположена кнопка "Добавить обработку колонок" - при нажатии на нее идет печать шаблона кода, который затем можно модифицировать. Это сделано для "удобства" - чтобы не переключаться в родительское окно и не искать имена колонок,которые сами же задали для поиска данных.

    Пример:

    После нажатия кнопки видно, что добавился код обработки колонок в дополнение к существующему. ненужные участки обработки можно смело удалять, дописав только "специфическое" преобразование значений.

    Для примера допишем код, чтобы строки файла, в которых ФИО сотрудника не заполнено, просто не выводились:

  • Для сохранения кода нажимаем "Принять"

  • Также есть 2 вспомогательные кнопки "Сохранить, Восстановить" для импорта/экспорта кода во внешний текстовый файл, но про них позже.

  1. Чтение на форму. Для примера можно указать небольшой интервал строк, дабы сразу не читать весь листа на форму: возможно будут ошибки чтения данных, недочеты по преобразованию колонок:

    Результат чтения выведен на форме. В колонках таблицы видим преобразованные данные, при желании можно их отредактировать "на лету" перед непосредственной загрузкой.

     

  2. Чтение большого объема данных. В случае, если файл excel имеет большой объем - будет показан "прогресс бар" с процессом вывода на форму. При желании можно приостановить процесс, нажав Ctrl-break:

Если приостановить чтение - то будет выведена только часть данных, которые "удалось прочитать":

 

Получение данных из листа excel

Самый простой режим: требует указания:

  • Файл excel: можно выбрать через "диалог выбора файла"

  • Лист excel: выбирается из списка, который заполнится после выбора файла

  • интервал строк чтения данных.

Получение данных из нескольких листов excel

Бывают случаи, когда данные введены на нескольких листах одинакового формата. На этот случай предусмотрен вариант чтения нескольких листов. Указывается:

  • Файл excel: можно выбрать через "диалог выбора файла"

  • Список листов excel: выбирается из списка, который заполнится после выбора файла: откроется "список с галочками"

  • интервал строк чтения данных: как правило каждый лист содержит заголовок, а сами данные начинаются с единого номера строк .

ПРИМЕЧАНИЕ: в алгоритме "поиск данных строки" бывает необходимо прописать параметр вида "ИмяЛиста", в частности на примере чтения штатного расписания каждый лист - это организация.

Выбираем набор листов в списке, нажимаем "Получить данные".

Получение данных из макета (буфера)

В данном случае ячейки задаются на форме обработки. Этот режим бывает полезен, когда есть проблемы с COM-соединением, с программным открытием файла и прочие...

Режим чтения макета из внешнего файла

Указываем:

  • Имя файла загрузки: можно выбрать из диалога выбора файла с диска (формат mxl), можно

  • интервал строк чтения данных

После выбора файла имеем:

Далее по интервалу строк производим чтение данных в закладку "Вывод данных"

Режим чтения макета из буфера ячеек

Указываем:

  • Текст ячеек на закладке "макет данных" методом "копи-пасты": можно для тестирования взять из файла excel

  • Имя файла загрузки: можно выбрать из диалога выбора файла с диска (формат mxl), можно в табличном поле сделать "ctrl-C ctrl-V"

  • интервал строк чтения данных

Далее по интервалу строк производим чтение данных в закладку "Вывод данных" (аналогично подбору файла с диска). По итогу получится нечто вроде:

 

Дополнительные команды

Дополнительные команды: Номера колонок: чтение наименований из файла

Часто бывает, что в листе excel заголовки колонок стоят вверху листа: на 1-й строке (либо чуть ниже). Дабы облегчить жизнь разработчику решено предусмотреть режим чтения колонок по номеру строки: чтобы не вводить их в таблице.

В режиме excel: нажимаем кнопку на закладке "Номера колонок" и вводим число (номер строки шапки):

В таблицу будут считаны колонки по номеру строки:

Остается только задать нужные типы колонок:

Аналогично в режиме "По макету": в панели инструментов есть аналогичная команда:

 

Дополнительные команды: Номера колонок: выбор типов

По сути самое "противное" во всей работе - поиск нужных типов колонок в огромном "безликом" списке всех типов. Сделаны следующие полезные вещи:

  • Придуман специальный удобный диалог типов:

  • В диалоге есть поиск типа по подстроке:

  • Значение типа: редактирование. Иногда чтобы не "копаться" в диалоге подбора типов бывает удобно просто ввести тип строкой: в случае когда мы знаем точное представление типа, либо хотим "скопипастить" из типа другой колонки.

    Например, задан тип "Число (15,2)" в одной из колонок: просто берем его в буфер ОС и копируем в другую колонку с таким же типом:

  • Автоподбор типа по тексту ввода. При желании можно не вызывать диалог типов, а просто писать название в ячейке таблицы: будет отобрано 1-е 10 вхождений строки:

На примере видно, что есть справочник "Должности": и он-то нам как раз и нужен, нет необходимости вызывать диалог по кнопке "..." справа в поле.

Дополнительные команды: Номера колонок: определение типов

Также придумана специальная команда "Поиск типов". В случае с конфигурациями типа "ЗУП, ERP" имеет место множество типов данных: например перечисления с одинаковым назначением, похожие справочники, которые меняются от версии к версии на "такие же, но более актуальные".

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

ПРИМЕР:

Мы хотим разыскать типы для всех колонок, которые остались неопределенными. Нажимаем команду "Поиск типов":

В списке остаются только те колонки, по которым тип еще не выбран, но при желании можно отметить и другие:

Далее появится еще 1 диалог, в котором будут определены всевозможные типы. Предположим, мы хотим поискать все типы конфигурации, в которых реквизит имеет представление (синоним) под названием "Вид занятости", и при этом знаем, что реквизит "примерно где-то есть в регистрах сведений", только название регистров не помним. Выставляем режим поиска

При нажатии "Начать поиск" выведется окно выбора метаданных. Отмечаем "все регистры сведений" и начинаем поиск совпадений по синониму:

Спустя 5 секунд система найдет, что реквизит "вид занятости" есть в одном из регистров базы данных, измерений, ресурсов с наименованием "График" в регистрах сведений нет:

Принимаем результат поиска: хотя бы 1 тип нашли, скорее всего он нам нужен для последующего чтения, загрузки.

 

Чтение данных: окончание

В целом по чтению данных все описано, других особенностей нет. Остается добавить, что прочитанные данные сохраняются в настройки "ХранилищеСистемныхНастроек" при закрытии формы обработки. Также предусмотрено сохранение в специальный каталог, но это будет отдельно описано в разделе "Сохранение / загрузка настроек".

 

Загрузка данных

После того, как данные прочитаны в таблицу формы можно произвести загрузку данных. Для загрузки подразумевается использование специфических обработок, которые соlержат алгоритмы, предназначенные для преобразования данных, полученных из таблицы-excel. Но в целом, если алгоритм загрузки простой и уместится в 1 процедуру - можно обходиться и без внешних обработок, а просто писать/ сохранять код в режиме предприятия.

Загрузка данных: Внешние файлы: обработки, макеты

Предусмотрены специальные внешние файлы для загрузки данных. Различаем 2 вида:

а) "Внешняя обработка" - это файл EPF обработчик загрузки таблицы данных,

б) "Макет mxl": файлы печатных макетов. Основное название: печать различных отчетов по загрузке, а также можно использовать как внешний табличный документ, с которого можно брать фиксированные данные для загрузки (бывает весьма удобно, дабы не создавать большие массивы, структуры).

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

Для добавления нового файла обработки используем кнопку подбора файла:

  • Табличные макеты. Придуманы как правило для печати отчета по загруженным данным, но в целом можно использовать и для других алгоритмических целей. Располагается также на закладке "Внешние файлы", Установка файлов в закладку осуществляется по кнопке выбора: открывается диалог подбора файла - далее "вопрос техники":

  • Фиксированные значения: уже ранее были описаны: могут применяться как при получении, так и для загрузки данных:

     

    Загрузка данных: Алгоритмы "Перед загрузкой", "Загрузка данных строки"

Принцип формирования алгоритмов загрузки заключается в печати программного кода алгоритмов для удобной подстановки во внешние обработки/текст с последующим удалением лишнего кода/поправкой существующего.

Загрузка данных: буфер вспомогательного кода

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

Как видно из описания вверху текста: код предназначен для копирования во внешнюю обработку загрузки: копируем, раскомментируем...

Рассмотрим составляющие процедуры по порядку:

  • Макеты из закладки "Внешние файлы"

Содержит список данных с закладки "Внешние файлы" вида "макет mxl":

Имеет тип "ТабличныйДокумент"

  • Значения параметров.

    Здесь также все прозрачно - см закладку "Параметры".

    • Отчет по загрузке, шаблон для формирования печати

      Из текста вверху понятно, что код будет полезен для печати отчета по загрузке. Во внешней обработке можно взять запрос и использовать его "с обработкой результата"

    ВАЖНО: Есть следующий код:

    ПараметрыЗагрузкиДанных.ОтчетыПоЗагрузке.Вставить("Отчет по загрузке данных", ТабДок);

    После загрузки данный макет будет выведен пользователю на печать. При желании можно подготовить несколько таких "макетов" и расположить их в соответствие "ОтчетыПоЗагрузке", только с другим представлением.

    • Прочее. Добавлена свертка колонок: в 1 очередь чтобы имена колонок не вспоминать в процессе написания обработки загрузки; признак отказа от загрузки

 

Загрузка данных: дополнительные параметры

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

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

Зададим шаг загрузки: видеть "прогресс-бар" в процессе построчной загрузки данных, чтобы видеть "как идет процесс".

На примере шаг=10, это значит, что через каждые 10 загруженных строк будет взята паза и на клиентской форме изменен шаг прогресс-бара загрузки.

 

 

Примеры загрузки данных

 

Все примеры можно взять в репозитории в папочке "\ImportExcelOnec\tests". И использовать в тестовой базе данных ЗУП 3.1

 

Построчная загрузка данных: пример

Рассмотрим пример создания/использования кода загрузки. Для примера есть таблица с данными по сотрудникам: ФИО, должности, ставки итд... :

Задача состоит в том, чтобы создать набор данных: сотрудников, физ.лица к ним, штатное расписание, документы приема на работу...

 

Перед загрузкой данных: редактирование кода.

В данном алгоритме следует создать обработку загрузки и привязать ее к алгоритму. В данном случае она однозначно понадобится, так как алгоритм явно не умещается в 1 процедуру: сразу представляем, что штатное расписание нужно упаковать в отдельную процедуру, заполнение приема на работу в другие итд...

Переходим на закладку "алгоритмы", нажимаем кнопку открытия алгоритма:

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

Как понятно из написанного: вниманию представляются все внешние обработки, введенные на закладке "Внешние файлы". Нужно выбрать "правильную обработку". Обычно редко бывает нужно "несколько штук на 1 алгоритм": выбираем одну; лишний код убираем:

 

"Добавить буфер кода". Данная полезная кнопка выдает набор строк кода, который осуществляет загрузку. Нажимаем кнопку, получаем код. на данном этапе нас интересует только печатный отчет о загрузке, все остальное пока не интересно, так как загрузка производится внешней обработкой:

По итогу после редактирования кода получаем следующее - подключение обработки и создание отчета по загрузке:

Все, обработка подключена: можно делать построчную загрузку

Построчная загрузка данных: редактирование алгоритмов

В нашем случае весь алгоритм реализован в доп.обработке, просто делаем вызов:

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

Для процедуры "ЗагрузкаДанныхПриемНаРаботу" можно скопировать список колонок, дабы не "лазать" за ними в интерфейс формы, а иметь готовый список, разбитый по числовым и групповым. В целом алгоритм практически полностью самостоятельный.

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

image-20210409115248509

 

Макет печати: это файл mxl с закладки "Внешние файлы". По сути, простенький отчет по загрузке: можно было его использовать в самой обработке в разделе "Макеты", но для демонстрации сделан внешним:

 

В конечном итоге после загрузки печатаем строку данных:

 

Замечательно, обработка готова, после этого обновляем ее в закладке "Внешние файлы":

И проверяем написанный коды вызова обработки, все ли ОК:

 

 

Построчная загрузка данных: Загрузка в базу

 

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

В результате получаем следующий отчет:

Понимаем, что проблем в загрузке нет, можно грузить в боевом режиме.

Шаг загрузки. Бывает полезно видеть "прогресс-бар" в процессе построчной загрузки данных, чтобы видеть "как идет процесс". Для этого предусмотрен реквизит "Шаг загрузки":

По окончанию будет распечатан отчет, для просмотра можно нажать на "лупу в строке" и просмотреть загруженные данные:

Как видно, документ в случайно выбранной строке корректный, без лишних "косяков".

 

Общая (непострочная) загрузка данных: пример.

Бывают случаи, когда построчная загрузка не подходит. Помимо построчного импорта данных базу нужно сгруппировать строки таблицы и впоследствии разбить по "подчастям".

Примером служит момент с загрузкой начальных остатков по ЗУП:

В данном примере после поиска сотрудников нужно сгруппировать их по "Организация+месяц", затем расположить в документы по вводу начальных остатков для расчета среднего по больничным листам. Построчная загрузка здесь не подходит.

 

Общая (непострочная) загрузка данных: редактирование алгоритмов.

Как и в случае с построчной загрузкой, подключаем обработку из внешнего хранилища.

ВАЖНО: вначале программной обработки указано "СтандартнаяОбработка = Ложь;" - это означает, производится загрузка единым алгоритмом, построчная загрузка не используется.

 

Производим печать буфера кода - оно нам очень пригодится.

Берем полученный код, вставляем во внешнюю обработку, раскомментируем.

  • Алгоритм перебора строк

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

Сгруппировали исходную таблицу по "Организация+месяц", затем производим программную загрузку

  • Алгоритм печати отчета. Это также полезный код для печати отчета по загрузке:

Путем несложных преобразований получим отчет: берем запрос и "кидаем" его в "Конструктор запроса с обработкой результата", как написано в комментарии:

По итогу получим готовый макет отчета и набор секций, лишний код убираем:

После этого пишем код загрузки данных, в нужные места подставляем участки по формированию отчета по загрузке:

 

 

Замечательно, обработка готова, после этого обновляем ее в закладке "Внешние файлы":

И проверяем написанный код вызова обработки, все ли ОК:

 

Загрузка данных в базу.

Загружаем аналогично построчной загрузке: можно произвести тестовый импорт данных, можно грузить сразу (если база тестовая или уверены что все верно):

 

Исполняем код, по итогу получим вот такую красивую таблицу, сформированную "Конструктором запроса с обработкой результата" и программными вставками:

В целом вполне наглядный пример использования загрузки данных: все будет выложено в репозитории.

 

Отладка алгоритмов

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

  • Построчная загрузка данных

  • Общая (непострочная) загрузка данных

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

Отладка построчной загрузки.

Процедура обработки имеет 2 входных параметра:

ДанныеСтроки, ПараметрыЗагрузкиДанных.

Для примера: "Процедура ЗагрузкаДанныхПриемНаРаботу(ДанныеСтроки, ПараметрыЗагрузкиДанных) Экспорт"

Чтобы получить значение "ДанныеСтроки" типа "Структура" предусмотрена специальная команда формы. выбираем 1 строку в таблице полученных данных и нажимаем "Отладка (строка таблицы)"

Печатается некий текст: это строковое представление строки в виде структуры:

Порядок отладки:

  • Берем нужную внешнюю обработку - в примере Алгоритм_ПриемНаРаботу.epf

  • Создаем форму для внешней обработки,

  • На форме создаем реквизит "ОтладкаТекст" и команду для вызова нашего метода

     

  • В форме следует ввести программный код. Шаблон можно сформировать по кнопке "Добавить код отладки" в редакторе кода алгоритмов (только потом очистить за собой):

     

    И немного заменим участи кода:

  • &НаСервере Процедура ЗагрузкаДанныхПриемНаРаботуНаСервере()

      ...

      &НаКлиенте

      Процедура ЗагрузкаДанныхПриемНаРаботу(Команда) ЗагрузкаДанныхПриемНаРаботуНаСервере();

      КонецПроцедуры

 

  • В режиме предприятия в форме обработки вводим строковое представление строки в виде структуры, устанавливаем точку останова, нажимаем кнопку команды.

  • В реквизите "ДанныеСтроки" видим строку из таблицы значений.

Можем отлаживать алгоритм.

Отладка общей (непострочной) загрузки.

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

Процедура обработки имеет 3 входных параметра:

ТаблицаДанныхExcel, ПараметрыЗагрузкиДанных, СтандартнаяОбработка

Для примера "Процедура ЗагрузитьСуммыФСС(ТаблицаДанныхExcel, ПараметрыЗагрузкиДанных, СтандартнаяОбработка) Экспорт"

ВАЖНО: для отладки правильнее будет прочитать небольшое количество строк в форму обработки, нет нужды в отладку выкладывать всю огромную таблицу.

  • Берем нужную внешнюю обработку - в примере Алгоритм_ДляРасчетаСреднего.epf

  • Создаем форму для внешней обработки,

  • На форме создаем реквизит "ОтладкаТекст" и команду для вызова нашего метода

  • В форме вводим программный код:

    &НаСервере 
    Процедура ЗагрузкаДанныхПриемНаРаботуНаСервере() 
    ПараметрыЗагрузкиДанных = Новый Структура; 
    ПараметрыЗагрузкиДанных.Вставить("ОтчетыПоЗагрузке", Новый Соответствие); 
    ТабДок = Новый ТабличныйДокумент; 
    ТабДок.ТолькоПросмотр = истина; 
    ПараметрыЗагрузкиДанных.ОтчетыПоЗагрузке.Вставить("Отчет по загрузке данных", ТабДок); 
    РеквизитФормыВЗначение("Объект").ЗагрузитьСуммыФСС(ДеСериализоватьОбъект(ОтладкаТекст), ПараметрыЗагрузкиДанных, Ложь) 
    КонецПроцедуры
    
    &НаКлиенте 
    Процедура ЗагрузкаДанныхПриемНаРаботу(Команда) 
    ЗагрузкаДанныхПриемНаРаботуНаСервере(); 
    КонецПроцедуры
    
    &НаСервереБезКонтекста 
    Функция ДеСериализоватьОбъект(СтрокаXML) Экспорт
    
    //Создаем объект чтения из XML
    ЧтениеXML = Новый ЧтениеXML;
    ЧтениеXML.УстановитьСтроку(СтрокаXML);
    //Читаем таблицу значений из XML    
    ОбъектТЗ = СериализаторXDTO.ПрочитатьXML(ЧтениеXML);
        
    Возврат ОбъектТЗ
    КонецФункции

     

  • В режиме предприятия "распечатываем таблицу значений" для отладки:

  • В режиме предприятия в форме обработки вводим строковое представление строки в виде структуры, устанавливаем точку останова, нажимаем кнопку команды

  • В отладке видим таблицу значений, которая введена в форме:

 

Сохранение / загрузка настроек

Если кто-либо осилил прочитать до этого места: заметил, что есть множество различных настроек: это и номера+типы колонок и тексты алгоритмов, параметры...

Ключи настроек

Целесообразно предусмотреть механизм загрузки-восстановления настроек. Для набора настроек предусмотрен уникальный ключ сохранения:

  • Если загрузка идет из нескольких листов excel - ключом является имя файла excel

  • Если загрузка по макету / из буфера -то "идентификатор файлазагрузки"

  • Если загрузка из листа excel - ключом сохранения является "Имя файла+Лист"

 

Интерфейс сохранения, краткое описание

Производится сохранение:

  • "ХранилищеОбщихНастроек": при открытии/ закрытии формы обработки идет восстановление-сохранение. Тем самым при открытии формы все редактируемые значения будут подставлены в обработку.

  • Предусмотрено несколько наборов кнопок сохранения-загрузки в файлы/каталоги:

    Сохраняют колонки в файл xml: формат файла "ТаблицаНомеровКолонок_" +Ключ настроек.

Сохраняют колонки в файл txt: формат файлов алгоритмов: "Алгоритм_ПоискДанныхСтроки_" +Ключ настроек, "Алгоритм_ПередЗагрузкойДанных_" +Ключ настроек.

Это просто список файлов: как в таблице, так и на диске.

Сохраняют колонки в файл xml: формат файла "ТаблицаПараметры_" +Ключ настроек.

  • Также есть общие команды сохранения-восстановления настроек:

     

ВАЖНО: данная команда сохраняет все настройки кроме внешних обработок: иначе можно "натворить плохих дел" в процессе написания обработок. Это пожалуйста делайте на закладке "Внешние файлы".

Примеры файлов настроек, рекомендации

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

    1. Желательно производить версионирование папки настроек в git, применять полезные плагины для репозитория типа precommit4onec.

    Хотя бы чтобы

    • иметь историю разработок и ничего не потерять в процессе разработки,

    • иметь возможность откатить настройки к стабильному варианту.

Excel com ТабличныйДокумент Загрузка чтение данных

См. также

SALE! 20%

Загрузка и выгрузка в 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    222347    956    891    

964

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

Реальный помощник, с помощью которого Вы сделаете необходимые документы для Wildberries, OZON, ЯндексМаркет, Мегамаркет, Aliexpress, "Детский мир", МагнитЭкспресс (бывш.Казань-Экспресс), "Леруа Мерлен", ЭНФАНТА (Акушерство), ЛаМода, Летуаль, "Твой дом" в документы "Отчет комиссионера (агента) о продажах" и другие, может работать в "Бухгалтерия 3", "Бухгалтерия 3 КОРП", УТ 11, УНФ, КА 2, ERP. Возможность подключить любые маркетплейсы. Анализ продаж ОЗОН. 30 дней БЕСПЛАТНОГО пользования!

1800 руб.

12.08.2021    33670    286    65    

141

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

Загрузка данных отчета о реализации товаров из сервиса Яндекс.Маркета «Беру» в 1С из Отчетов Excel для конфигурации: Бухгалтерия предприятия, редакция 3.0; Управление торговлей, редакция 11; Управление торговлей, редакция 10.3 и Управление нашей фирмой, редакция 3.0 в документ «Отчет комиссионера о продажах».

4800 руб.

09.12.2020    23882    230    1    

95

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

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

10560 руб.

29.10.2014    214527    636    526    

451

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

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

4800 руб.

07.06.2022    16230    97    0    

76

Загрузка и выгрузка в 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 форматов без офиса, на любом сервере! Визуальное связывание колонок файла и реквизитов простым перетаскиванием колонок. Создание или обновление номенклатуры с иерархией, характеристик, доп. реквизитов, упаковок, загрузка практически неограниченного количества картинок на одну номенклатуру (с возможностью загрузки в несколько потоков одновременно), с хранением в томах или в базе. Загрузка номенклатуры поставщиков или поиск по их данным номенклатуры. Загрузка доп. реквизитов в характеристики. Загрузка штрихкодов с генерацией новых. Создание элементов справочников и ПВХ "на лету" для выбранных реквизитов. (Обновление от 10.07.2024, версия 9.8 - 9.11)

14400 руб.

20.11.2015    153414    373    376    

507
Комментарии
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
1. tvv56 15.04.21 09:18 Сейчас в теме
IMHO. Для оттачивания собственных навыков рабоота проделана интересная, но в практическом применении по времени не окупаемая. Под все разнообразие задач загрузок заранее код не написать, а на модификации алгоритмов всё равно придется тратить время и это удобнее делать в конфигураторе. 90% задач по загрузке одноразовые и для них хранение настройки бесполезно.Пользователям такое решение не отдать, в лучшем случае даже разбираться не станут, в худшем всё напутают и запорят базу а виноват будет конечно разратчик. Значит загрузку придется делать сопровожденцам, а то и самому разработчику. Проще поправить шаблончик с 1 кнопкой "загрузить", отдать и забыть.
user1882149; gigapevt; Gulloper; itmind; IgorS; papche; UncleLu; Aluvika4; kupala; hiduk; serg-666; qwinter; cleaner_it; konashenkov; JohnyDeath; Yashazz; papami; Pawlick; GATTUSO; etmarket; user618912_redgad; +21 1 Ответить
3. artkor 284 15.04.21 11:36 Сейчас в теме
(1) В планах как раз "впилить" историю настроек с возможностью выбора из спсика (чтобы похожие файлы загружать) - а то пока заставляю файлы переименовывать и в единый каталог размещать. Благо юзеры вменяемые. Ну и какую-нибудь "пародию" на систему прав сделать: в частности чтобы алгоритмы не трогали, а только номера колонок итд...
2. Cmapnep 18 15.04.21 10:50 Сейчас в теме
Проделанная работа внушает уважение!
Не планируете подключить редактор с подсказками и раскраской кода на основе монако?
4. artkor 284 15.04.21 11:37 Сейчас в теме
(2) Подскажите это о чем? а то про монако ничего не слышал кроме страны в Европе )
6. Cmapnep 18 15.04.21 13:55 Сейчас в теме
(4) посмотрите вот этот проект https://github.com/salexdv/bsl_console
Вот тут еще коллега интегрировал в КД3 https://github.com/ViktorErmakov/Conversion-3-Plus
Alia777; artkor; vulli; +3 Ответить
7. artkor 284 15.04.21 15:59 Сейчас в теме
(6) Спасибо, надо ознакомиться, может что-то полезного почерпну, добавлю
28. ivanovkpd 05.09.23 14:37 Сейчас в теме
(7) Не внедряли Монако? :)
Было б здорово..
5. gorakh 26 15.04.21 13:18 Сейчас в теме
А БСП шная подсистема чем не устроила?
8. artkor 284 15.04.21 16:02 Сейчас в теме
(5) Когда просто каталог номенклатуры или заказ клиента надо залить - вполне годится, для всего остального этот "велосипед" и придумал
9. user925427 126 15.04.21 21:32 Сейчас в теме
Мне статья понравилась. Автор поделился инструментом, разработанным для решения нетривиальных задач по загрузкам информации из Excel. Полагаю, что времени на его освоение и привыкание к нему уйдёт меньше, чем на решение какой-либо одной нестандартной задачи имеющимися средствами. Спасибо за потраченное время на ясную постановку задачи, что важно для статьи в целом, и подробное описание разработки, что встречается реже, чем хотелось бы.
natremiz; cleaner_it; artkor; +3 1 Ответить
10. Yashazz 4761 16.04.21 09:22 Сейчас в теме
Ну, баян, конечно, лютый, и нового совершенно ничего, примитивный com-обмен... Но работа проделана немалая, причём именно по автоматизации промежуточных действий. Правда, соглашусь с (1), это работа почти всегда бесполезная - чем круче и универсальнее универсал, тем труднее им рулить и тем более грамотный спец нужен, делать маленькие загрузчики "по месту" быстрее и дешевле. Я подобную супер-обработку (причём умевшую делать упрощённые грузилки) ещё на 7.7 наваял и тогда же все плюсы и минусы понял.
Оформлено хорошо. Продумано толково. Но, имхо, зря)
IgorS; rpgshnik; Aluvika4; kupala; cleaner_it; qwinter; +6 Ответить
16. artkor 284 16.04.21 17:59 Сейчас в теме
(10) Частично согласен с Вами. Целью было собрать "для себя" все накопленные наработки воедино, потому как надоело 1 и тотже код "копипастить" из различных мест. Заодно может кому-то еще в работе пригодится: для этого на гитхаб и выложил, чтобы не обидно было никому после скачивания )
11. user1145156 16.04.21 09:33 Сейчас в теме
Работу с почтой бы добавить и регламентные задания
12. cleaner_it 221 16.04.21 09:48 Сейчас в теме
Имеет смысл добавить группировку, вот как тут: https://infostart.ru/1c/articles/1020565/
15. tormozit 7190 16.04.21 13:00 Сейчас в теме
(12) Свернутые группировки блокируют поиск. А раскрыть их все сразу - насколько я знаю, способа нет.
13. qwinter 682 16.04.21 10:48 Сейчас в теме
Поздравляю, теперь вы опытный разработчик загрузок из эксель! Но зачем???
rpgshnik; +1 Ответить
17. artkor 284 16.04.21 18:00 Сейчас в теме
(13) Могу с ходу набор причин назвать - но боюсь ни одна Вас не убедит)
14. ITSun 16.04.21 11:39 Сейчас в теме
Плюс за проделанную работу!
(Пусть и "велосипед")
natremiz; +1 Ответить
18. Olenevod 33 18.04.21 22:51 Сейчас в теме
Уж не знаю на счет велосипеда, но на мой взгляд, вещь очень полезная. Допустим есть интеграционные механизмы с использованием Excel и гораздо удобней настроить что-то в пользовательском режиме, чем выпускать очередной релиз. Может есть, конечно, что-то подобное, но что-то не встречал.
Автору спасибо.
19. artkor 284 18.04.21 23:12 Сейчас в теме
(18) Пожалуйста, рад что полезно ;)
20. rpgshnik 3743 19.04.21 04:27 Сейчас в теме
Молодец конечно, прокачался.

Но имхо, мне вот эта нравится включенная в Универсальные инструменты - https://infostart.ru/public/1178280/ (первоисточник https://infostart.ru/public/269425/).
21. TimurD 6 05.07.21 14:08 Сейчас в теме
Здравствуйте. Скачал обработку (epf). У Вас в форме в строке 1595 (Функция ПараметрыЗагрузкиДанных) не в верном порядке заполняются тексты алгоритмов. Вместо ЗагрузкаДанныхСтроки подставляется ПослеЧтенияДанных, и на оборот.
22. artkor 284 06.07.21 23:31 Сейчас в теме
(21) Спасибо что заметили, поправил)
23. mvl_dimir 30.08.21 14:21 Сейчас в теме
Удалили? Не вижу файла для скачивания.
25. mvl_dimir 01.09.21 15:28 Сейчас в теме
(24)Не могли бы вы также подсказать. Пытаюсь загрузить .mxl файл, выходит ошибка:


Исправлено: убрал безопасный режим, все ок.
Прикрепленные файлы:
26. artkor 284 01.09.21 21:51 Сейчас в теме
(25) Наверное проблема в безопасном режиме. В модуле обработки
ПараметрыРегистрации.Вставить("БезопасныйРежим", Истина);

попробуйте прописать "Ложь". Пока не тестировал этот момент - в дальнейшем исправлю.
27. mvl_dimir 02.09.21 10:27 Сейчас в теме
(26)Да, я исправил и все ок стало. Выше в сообщение написал "Исправлено: убрал безопасный режим, все ок".
29. ivanovkpd 05.09.23 14:57 Сейчас в теме
Простите, вроде читал инструкцию внимательно...
Вот у нас есть процедура, которая вызывается перед построчной загрузкой, то есть 1 раз на лист, если правильно понимаю. И есть процедура построчной загрузки, которая вызывается для каждой строки на каждом листе, так?

Допустим мне нужно загрузить 1 лист, создать на его основе 1 док-т и с этого листа заполнить ТЧ.
Это нужно делать одной процедурой, которая "Перед загрузкой.."? Иначе я не вижу как передать объект документа в "построчную"...
30. artkor 284 06.09.23 09:45 Сейчас в теме
(29) Сейчас загружен, давно уже к этой обработке не возвращался. как руки дойдут - в личку отвечу, чтобы тут не писать. Лучше всего ишузы делайте на гитхабе, если какой-то баг нашли: понимаю что эта "дурацкая обработка" не идеальная - мог и накосячить гдето:)
31. ivanovkpd 06.09.23 10:44 Сейчас в теме
(30) да тут скорее не баг, а просто я ее готовить не умею, вот и спросил. И еще... у вас нет непреодолимого желания добавить туда Монако, и сделать продукт коммерческим? Если я сумею вашу обработку приручить, то на 95% моя контора ее готова будет купить, у нас много импорта планируется. Выложите ее тут за денюжку.. :)
32. artkor 284 06.09.23 13:56 Сейчас в теме
(31) рано или поздно сделаю монако - ишуз напишите. Я там еще конструктор шаблонов ваять начинал, пока забросил - надо возвращаться. Денюшек на надо, на то оно и опенсорс чтобы делать открытый код )
33. ivanovkpd 06.09.23 15:25 Сейчас в теме
Оставьте свое сообщение