Куперс

Бухучет и анализ

Excel в 1С

Загрузка данных из Excel в 1С, как правило, нужна в следующих случаях:

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

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

Универсальный механизм

На дисках ИТС, а также на портале 1С, зайдя в меню «Технологическая поддержка» -> «Универсальные отчеты и обработки», в папке «Загрузка данных из табличного документа» находится соответствующая обработка.

При ее запуске открывается форма (Рис.1):

Рис.1

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

  1. Справочник;
  2. Табличную часть документа или справочника;
  3. Регистр сведений.

В зависимости от того, в какое положение установлен переключатель, меняется поле выбора объекта.

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

Типовая обработка поддерживает выгрузки из:

  • Разработанного специалистами фирмы 1С, файлов формата mxl;
  • Листа xls, сохраненного в формате Excel 97-2003;
  • Текстового файла txt;
  • Таблицы dbf.

Excel файлы большого объема могут загружаться достаточно долго, так что, если предполагается, что загрузка данных может происходить в несколько этапов или Вы точно знаете, что Вам придется несколько раз открывать существующий файл, для экономии нервов и времени, информацию из него лучше сохранить в mxl формат. Сделать это можно напрямую из формы обработки.

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

Теперь перейдем к закладке «Настройка» (Рис.2):

Рис.2

Достаточно часто экселевские таблицы содержат шапку со всевозможными реквизитами и данными (наименование печатной формы, реквизиты контрагента, дату и номер входящего документа, имена колонок и так далее), чтобы исключить их обработку программой на форме, в реквизите «Первая строка табличного документа» надо указать первую строку с переносимой информацией

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

Колонки «Настройки»

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

Представление реквизита – здесь прописывается синоним (наименование) реквизита метаданных, как оно задано в конфигураторе.

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

Описание типов – отображает тип данных, который имеет тот или иной реквизит метаданных.

Режим загрузки – предлагает три варианта на выбор (Рис.3):

Рис.3

  • Искать – будет осуществляться поиск соответствующего элемента, в случае его отсутствия может быть создан новый;
  • Устанавливать – подчиненным образом устанавливается определенное значение;
  • Вычислять – в этом случае в поле создаваемого элемента будет установлен результат вычисления выражения, указанного в колонке «Условие связи/Выражение для значения».

В последнем случае, активация поля Выражение вызовет открытие формы (Рис.4).

Рис.4

Номер колонки – поле, используемое для указания, из какой колонки экселевской таблицы необходимо брать данные для заполнения.

Значение по умолчанию – достаточно часто возникает ситуация, когда в загружаемом файле содержатся не все необходимые для записи элемента данные, в этом случае для заполнения будет проставлена информация, содержащаяся в данном поле.

Условие связи/Выражение для значения – частично мы уже коснулись этого поля, когда рассматривали вычисляемые поля, дополнительно в нем можно указать условие, в соответствии с которым будут синхронизированы исходные данные.

Вот, в принципе и вся информация, которая доступна на закладке «Настройка».

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

Проверить корректность переносимых данных можно нажатием на кнопку «Контроль заполнения» (Рис.1). После этого можно запускать процесс загрузки. Об успешном выполнении процедуры или нестандартных ситуациях Вы будете оповещены отдельно.

Загрузка данных через буфер обмена

Для загрузки данных из Экселя в информационных базах «Управление и торговля» существует еще один механизм. Он менее универсален, чем приведенный выше способ, однако не требует подписки ИТС и содержится в типовой поставке.

Эту обработку можно найти на закладке «Закупки» , в меню «Сервис», она носит наименование «Загрузка цен поставщика из файлов»(Рис.5)

Рис.5

Форма обработки содержит:

  1. Поле выбора даты, которая будет указывать, на какое время актуальна данная цена;
  2. Поле выбора контрагента, который прислал свой прайс-лист;
  3. Кнопку, позволяющую выбрать тип устанавливаемых цен;
  4. Табличную часть, которую можно заполнить загружаемыми данными.

Эту форму можно увидеть на Рис.6

Рис.6

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

После выбора контрагента (в зависимости от того, выступает он поставщиком, комиссионером или продавцом) в таблице станут доступны дополнительные колонки для каждого типа цен.

При работе через веб-интерфейс, в некоторых браузерах может потребоваться установка браузерного дополнения (Рис.7). Нам надо нажать кнопку «Начать установку» и перезапустить наше подключение.

Рис.7

После этого мы, используя буфер обмена, сможем переносить информацию из одной таблицы в другую. Когда необходимые нам колонки («Артикул», «Наименование», «Цена») будут заполнены, мы нажимаем кнопку «Далее» и переходим на вторую страницу (Рис.8)

Рис.8

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

Второе нажатие на кнопку «Далее» открывает перед нами форму, в которой мы должны определить, что делать с загружаемыми ценами:

  • Регистрировать все подряд;
  • Регистрировать только те, которые изменились по сравнению с уже имеющимися в базе.

В текстовом поле можно внести комментарий, который будет записан в документе (Рис.9):

Рис.9

После выполнения обработки:

  • В справочнике «Номенклатура поставщика» будет создан соответствующий элемент (если его не было);
  • Ему будет сопоставлен элемент справочника «Номенклатура»;
  • Будет создан и проведен документ «Установка цен номенклатуры» с указанием: поставщика, типа цен и даты фиксации данных.

Аналогичным образом работает обработка «Загрузка товаров из внешних файлов».

Варианты обработки переноса своими руками

Главная проблема в выдергивании данных из экселевского файла заключается в том, что 1С не имеет встроенного однозначного механизма его открытия. Существуют несколько вариантов подключения Excel к 1С:

  • Через Microsoft ADODB – достаточно быстрый способ, который, как правило, применим и для файлового и для клиент-серверного варианта хранения базы;
  • Через использование Microsoft Office – метод, который иногда сбоит при работе с SQL базами, как правило, работает несколько медленнее первого способа, к тому же требуется установленный Office;
  • Через Libre Office – в отличие от предыдущего метода бесплатен, помимо xls и xlsx форматов поддерживает еще и собственные таблицы, но требует установленного пакета LibreOffice и некоторой подготовки загружаемого файла (первая строка таблицы должна содержать имена колонок).

Рассмотрим подробнее различные способы и варианты.

Через ADODB.Connection

Вообще ADO расшифровывается как ActiveX Data Object и служит для программного доступа к различным базам данных. Самая большая проблема при создании любого подключения к стороннему файлу (в том числе и к экселевскому) – грамотно составить строку подключения.

Для файлов Excel тут возможны три варианта:

  1. Если на компьютере установлено программное обеспечение Microsoft Access Database Engine 2010 Redistributable 32/64 bit, можно использовать строку (Рис.10); Рис.10
  2. Стандартное Windows подключение через драйвер Jet.OLEDB.4.0 будет выглядеть следующим образом (Рис.11) правда, открывать через эту строку таблицы, созданные в Microsoft Office 2007 и более поздних версиях (расширение xslx) невозможно; Рис.11
  3. Кроме этого, можно вызвать драйвер для Эксель, используемый в системе по умолчанию для этих таблиц (Рис.12); Рис.12

Параметры строки подключения:

  • Provider – здесь определяется используемый драйвер;
  • Data Source – определяет имя файла, который мы будем открывать;
  • Extended Properties – здесь можно указать: нужна ли строка-заголовок для таблицы (HDR = YES говорит о том, что данные будут считываться с первой строки, HDR = NO – что со второй), открывается ли файл только для чтения (ReadOnly) и некоторые другие дополнительные параметры.

Создав строку подключения, мы можем соединиться с загружаемым файлом (Рис.13)

Рис.13

Теперь мы можем с помощью простого запроса (Рис.14) запустить выборку информации из загружаемого файла.

Рис.14

В данном случае параметр «Лист» определяет, с каким листом из экселевской книги нам предстоит работать.

Рис.15

Набор записей, хранящихся на листе можно прочитать с помощью объекта Recordset. При этом первую запись листа можно получить параметром BOF (начало файла), а последнюю EOF (конец файла).

Через Excel приложение

Главное отличие от предыдущего способа – помимо драйверов работы с базами данных, на компьютере, где выполняется соединение, должен быть установлен Excel. Только в этом случае мы можем инициализировать приложение для чтения данных из таблицы (Рис 16).

Рис.16

У этого COM объекта есть несколько дочерних параметров, но главным для нас, при текущих условиях задачи, является параметр WorkBooks (Рис.17).

Рис.17

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

Рис.18

После этого можно перебирать внутри циклов строки и колонки таблицы открываемого файла.

Несколько слов о возможных ошибках

Львиная доля ошибок при подключении к файлу возникает из-за того, что файл уже занят другим приложением. Хорошо, если в панели задач видно, что Excel запущен на компьютере, но если Вы или другой пользователь открыли его из внешней обработки, визуально это можно определить только через «Диспетчер задач», так что не забывайте перед окончанием процедуры переноса закрыть подключение:

В случае работы через ADO (Рис.19);

Рис.19

  • В случае работы с приложением (Рис. 20).

Рис. 20.

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

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

Рассмотрим на примере, как из 1С 8.3 выгрузить в Excel нужные документы, отчеты и информацию.

Как из 1С выгрузить в Excel печатные формы документов

Сохраним в Excel печатную форму какого-либо документа. Для примера возьмем Платежное получение. В любом документе есть печатная форма, и сохранение идет именно оттуда.

Шаг 1. Нажмите кнопку Платежное поручение. В другом документе это может быть кнопка Печать.

Шаг 2. В открывшейся печатной форме документа нажмите кнопку Сохранить.

Шаг 3. Укажите папку на компьютере, куда хотите сохранить документ и выберите формат (в нашем примере для сохранения в формате Excel выберите лист Microsoft Excel 2007 или Microsoft Excel 97-2003). Нажмите Сохранить — выгрузка из 1С в Excel произведена.

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

Выгрузка отчетов из 1С в Excel

Выгрузка отчетов из 1С в Excel осуществляется немного иначе.

Шаг 1. Сформируйте любой отчет. Например, Оборотно-сальдовую ведомость.

Обратите внимание на меню в виде трех точек в верхнем правом углу самого отчета. В новых версиях платформы (данный пример рассмотрен на платформе 1С: Предприятие 8.3.16.1063) в этом меню расположены команды Сохранить и Сохранить как.

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

Шаг 2. Для сохранения отчета выберите команду Сохранить.

Шаг 3. В открывшемся окне укажите:

  • место для сохранения сформированного отчета;
  • имя (название) сохраняемого отчета;
  • в поле Тип файла из списка предложенных — формат сохранения Лист Excel2007.

Вариант Лист Excel — более ранняя версия табличного документа.

Шаг 4. Нажмите кнопку Сохранить — отчет сохранен в указанную папку.

Выгрузка из 1C в Excel списков

У пользователя может возникнуть необходимость выгрузить в Excel какие-либо списки: перечень документов журнала, элементов справочников (номенклатуры, контрагентов) и т. п. Рассмотрим, как получить этот список из справочника или журнала и как из 1С 8.3 выгрузить в Excel полученный результат.

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

Шаг 1. Установите нужный период журнала (в нашем примере — январь) и выберите команду Еще – Вывести список.

Шаг 2. В окне настройки укажите вариант вывода информации Табличный документ. Отрегулируйте, необходимые для вывода колонки таблицы и нажмите ОK.

Программа позволяет сформировать информацию в двух вариантах:

  • табличный документ;
  • текстовый документ.

Шаг 3. Для сохранения полученного списка в Excel нажмите меню из трех точек в верхнем правом углу списка, далее нажмите Файл – Сохранить (или Сохранить как).

Шаг 4. Задайте название сохраняемого файла, его тип и папку, куда его поместить.

Шаг 5. Нажмите Сохранить — выгрузка из 1C в Excel завершена.

Таким образом сохраняются не только списки документов, но и справочники.

Выгрузка номенклатуры из 1С 8.3 в Excel

Рассмотрим на примере справочника Номенклатура.

Формирование списка и выгрузка номенклатуры из 1С 8.3 в Excel выполняются аналогично списку документов.

Шаг 1. Откройте список номенклатуры и выполните команду в меню Еще – Вывести список.

Шаг 2. Настройте выводимый список и нажмите ОK.

Шаг 3. Сохраните в таблицу Excel, нажав меню из трех точек в верхнем правом углу таблицы.

Если вы привыкли использовать горячие клавиши 1С, возьмите на вооружение комбинацию Ctrl+S, которая вызовет команду сохранения документа, и вам не придется искать ее в меню.

Мы разобрали, как из 1С выгрузить в Excel табличные формы документов, списки этих документов и справочников. Как видите, выгрузка из 1С в Excel совсем несложна и облегчает работу по вводу и обработке необходимых табличных данных.

Поставьте вашу оценку этой статье:

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

  • Пользователи системы 1С Предприятие
  • Программисты 1С

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

Теперь давайте разберем в общих чертах как происходит загрузка и выгрузка данных из Excel в 1С, и как вообще это все будет выглядеть в готовом решении?. Пример реальной индивидуальной разработки, а также исходный Excel файл представлены ниже.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Наверх