Как перенести номенклатуру из excel в 1с. Бухучет инфо. В отрывшемся окне находим файл обработки
В этой статье я расскажу как с помощью универсальной обработки “Загрузка данных из табличного документа” с диска ИТС загрузить данные в в конфигурацию Управление торговлей 10.3. Загрузка производится из файлов с расширением xls (формат excel 97-2003) в несколько этапов.
Если ваша конфигурация основана на управляемых формах 1С 8.3 или 8.2, рекомендуем к просмотру данное видео:
Мы для примера загрузим небольшой прайс в котором содержится следующая информация: наименование, артикул, единица измерения, два типа цен, штрих-код.
- Для начала перепишем обработку ЗагрузкаДанныхИзТабличногоДокумента.epf с диска ИТС. Если диска под рукой нет — скачайте её по ссылке . Для этого на диске заходим Технологическая поддержка =>1С Предприятие 8:
Нажимаем Копировать:
Выбираем путь для копирования кнопкой искать(в моем случае я копирую на рабочий стол) и нажимаем Копировать:
Ждем окончания копирования и нажимаем ОК:
Закрываем ИТС,больше нам диск не понадобится.
Подробнее о том как добавить внешнюю обработку, отчет или печатную форму написано здесь.
2.Запустим конфигурацию 1С Управление торговлей 10.3 в режиме Предприятие. Для начала добавим нашу обработку в состав внешних обработок базы для удобства использования и чтобы в следующий раз не искать ее на диске ИТС. Заходим Сервис => Внешние печатные формы и обработки => Внешние обработки:
Открывается список внешних обработок вашей базы. Нажимаем Добавить:
Выбираем кнопкой “Открыть” скопированный ранее с диска ИТС файл ЗагрузкаДанныхИзТабличногоДокумента.epf:
Нажимаем ОК для сохранения обработки в базе 1С Управление торговлей:
Теперь двойным щелчком мыши можем запустить обработку из списка доступных внешних обработок:
- Мой прайс-лист выглядит следующим образом:
Убедитесь что ваш прайс-лист сохранен в формате xls, если нет, то сохраните именно в этом формате. Чтобы элементы номенклатуры попали в нужные группы (столбец А) нам необходимо предварительно создать их в справочнике Номенклатура вручную.
Следите за тем чтобы после названия группы не было лишних пробелов, так как название группы в 1С в результате не будет соответствовать названию группы в прайс-листе Excel. Создаем группы:
Запускаем внешнюю обработку как показано выше, либо через меню Файл=>Открыть и выбрав файл ЗагрузкаДанныхИзТабличногоДокумента.epf. Выбираем Режим загрузки — Загрузка в справочник, Вид справочника — Номенклатура. Нажимаем значок Открыть:
Выбираем файл вашего прайс-листа, предварительно изменив Тип файлов на Лист Excel (*.xls) и нажимаем Открыть:
Видим загруженный в обработку прайс лист:
Если у вас очень большой прайс-лист, его открытие займет продолжительное время, следует подождать. Я рекомендую такой прайс-лист сохранить из самой обработки в формате mxl после открытия, такой файл открывается очень быстро. Для этого нажимаем значок Сохранить, пишем название файла и нажимаем Сохранить:
Переходим во вкладку Настройка. Ставим номер первой строки данных табличного документа, в моем случае первая строка это шапка с названиями реквизитов а сами данные начинаются со второй. Далее выбираем ручную нумерацию колонок, это нужно чтобы указать в какой колонке табличного документа находится реквизит из списка:
Настраиваем реквизиты для загрузки:
Наименование:
Режим загрузки — Искать
Родитель (имеется в виду группа номенклатуры):
Режим загрузки — Искать
№ Колонки табличного документа — 1 (ставите свое значение)
Артикул:
Ставим галочку поле поиска. Это означает что с уже имеющейся в вашей базе номенклатурой обработка будет сравнить по артикулу, и если найдется элемент с таким же артикулом,то обработка заменит его реквизиты на реквизиты из данной настройки.
Режим загрузки — Искать
Базовая единица измерения:
Режим загрузки — Вычислять.
В поле Выражение пишем формулу:
Результат = Справочники.КлассификаторЕдиницИзмерения.НайтиПоНаименованию(ТекстыЯчеек);
Это выражение означает,что обработка будет сравнить наименование из 6-ой колонки прайс-листа с наименованием в справочнике Классификатор единиц измерения и при совпадение названий присваивать соответствующую единицу из справочника.
Вам необходимо убедиться что все наименования единиц измерения из прайса есть в соотвествующем справочнике 1С. Если их нет,то следует предварительно создать.
Весовой:
Значение по умолчанию — Нет
Полное наименование:
Режим загрузки — Искать
№ Колонки табличного документа — 5 (ставите свое значение)
В моем случае полное и сокращенное наименование совпадают. Напомню что полное наименование используется в программе в печатных формах документов, а сокращенное для удобства поиска в самой программе.
Ставка НДС:
Режим загрузки — Устанавливать
Значение по умолчанию — 18%
Вид номенклатуры:
Режим загрузки — Устанавливать
Значение по умолчанию — Товар
После заполнения всех настроек необходимо произвести контроль заполнения. И если ошибок нет, нажимаем Загрузить.
Ждем окончания загрузки и смотрим как загрузились элементы. Я открыл один из них:
Здесь видим что не хватает обязательных для заполнения полей: единицы хранения остатков и единицы для отчетов. Мы загрузим их следующим этапом.
Если все загрузилось в соответствии с настройками то мы можем сохранить настройки в файл, чтобы в следующий раз не производить настройку заново.Нажимаем значок сохранить во вкладке Настройка:
Пишем название файла и сохраняем. Формат файла будет *.mxlz:
Примечание:
Если в какой либо строке настройки стоит Режим загрузки “вычислять” или “устанавливать” то обработка не будет учитывать значение в колонке настроек “№ Колонки табличного документа” этой же строки настройки.
Если вы загрузили настройку из ранее сохраненной то вам придется заново вводить значения в колонку настроек “№ Колонки табличного документа”.
- Приступим к загрузке единиц хранения остатков и единиц для отчетов. Убираем все галочки с реквизитов кроме Артикул, Единица хранения остатков и Единица для отчетов. Ставим галочку не создавать новых элементов.
Артикул:
Ставим галочку поле поиска.
Режим загрузки — Искать
№ Колонки табличного документа — 4 (ставите свое значение)
Единица хранения остатков:
Режим загрузки — Вычислять
В поле выражение вставляем код на встроенном языке программирования 1С:
СсылкаНоменклатуры = Справочники.Номенклатура.НайтиПоРеквизиту("Артикул" , ТекущиеДанные["Артикул" ]); БазоваяЕдиницаИзмерения = Справочники.КлассификаторЕдиницИзмерения.НайтиПоНаименованию(ТекстыЯчеек); ТекСпр=Справочники.ЕдиницыИзмерения.НайтиПоРеквизиту("ЕдиницаПоКлассификатору" "Не удалось записать"
После заполнения настроек, нажимаем контроль заполнения и если нет ошибок нажимаем Загрузить:
Открываем любой из загруженных элементов номенклатуры и проверяем загрузились ли единицы измерений:
Сохраним настройки в файл и приступим к следующему этапу.
Чтобы загрузить эту настройку нажмите сюда
- Загрузим 2-типа цен: Цена Розница 1 и Цена Розница 2. Заходим Справочники => Номенклатура => Типы цен номенклатуры:
Добавляем новый тип цен:
Заполняем необходимые реквизиты и сохраняем:
Аналогично добавляем еще один тип цен.
После сохранения типов цен, создадим два пустых документа , так как загрузка производится с помощью заполнения обработкой табличной части документа “Установка цен номенклатуры”.
Заходим Документы => Ценообразование => Установка цен номенклатуры:
Добавляем новый документ:
В документе выбираем один из двух созданных типов цен и сохраняем его пустым:
Аналогично создаем второй документ.
В итоге должно получиться следующим образом:
Заходим в нашу обработку. В обработке выбираем Режим загрузки — Загрузка в табличную часть, Ссылка выбираем тип данных документ Установка цен номенклатуры,заново Ссылка выбираем созданный документ Установка цен номенклатуры для первой цены,Табличная часть выбираем Товары.
Переходим во вкладку табличный документ и загружаем ваш прайс. Снова перейдем во вкладку настройки для ввода настроек:
Вводим настройки:
Нумерация колонок — Ручная нумерация колонок.
Номенклатура:
Режим загрузки — Искать
№ Колонки табличного документа — 5 (ставите свое значение).
Цена:
Режим загрузки — Искать
№ Колонки табличного документа — 8 (ставите свое значение для первого типа цен).
Валюта:
Режим загрузки — Устанавливать
Значение по умолчанию — руб. (ставите свое значение)
Единица измерения:
Режим загрузки — Вычислять
Тип цен:
Режим загрузки — Устанавливать
Значение по умолчанию — Цена Розница 1 (выбираете свое значение)
После того как ввели все настройки, нажимаем контроль заполнения во вкладке табличный документ. Если все правильно нажимаем Загрузить:
После окончания загрузки заходим в заранее созданный документ Установка цен номенклатуры, проверяем все ли правильно загрузилось и нажимаем ОК:
Аналогичным образом открываем второй документ Установка цен номенклатуры и нажимаем ОК для перепроведения.
Результат загрузки можно увидеть если зайти в элемент номенклатуры и во вкладку Цены номенклатуры.Там видим два созданных типа цен и по нажатию кнопки Перечитать текущие цены мы увидим загруженные нами цены:
Примечание: в один документ Установка цен номенклатуры с помощью данной обработки можно загрузить только один тип цен, т.е. для каждого типа цен создается отдельный документ.
Сохраняем наши настройки в файл и переходим к следующему этапу.
Чтобы загрузить эту настройку нажмите сюда
- Загрузим штрих-коды для нашей номенклатуры. Штрих-коды хранятся в регистре сведений, поэтому выбираем Режим загрузки — В регистр сведений, Вид регистра — Штрих-коды.Загружаем наш прайс во вкладке табличный документ.
Для реквизита Владелец заходим в описание типов и оставляем галочку только напротив номенклатура:
Для реквизита Владелец в поле Искать по выбираем Артикул:
Вводим настройки:
Штрих-код:
Режим загрузки — Искать
№ Колонки табличного документа — 7 (ставите свое значение).
Владелец:
Режим загрузки — Искать
№ Колонки табличного документа — 4 (ставите свое значение для колонки с артикулом).
Тип штрих-кода:
Режим загрузки — Устанавливать
Значение по умолчанию — EAN13 (ставите свое значение)
Единица измерения:
Режим загрузки — Вычислять
В поле выражение вводим код на встроенном языке программирования 1С:
СсылкаНоменклатуры = Справочники.Номенклатура.НайтиПоРеквизиту("Артикул" , ТекстыЯчеек); БазоваяЕдиницаИзмерения = Справочники.КлассификаторЕдиницИзмерения.НайтиПоНаименованию(ТекстыЯчеек); ТекСпр=Справочники.ЕдиницыИзмерения.НайтиПоРеквизиту("ЕдиницаПоКлассификатору" ,БазоваяЕдиницаИзмерения,СсылкаНоменклатуры); Если ТекСпр.Пустая() Тогда НайденнаяЕдиницаОбъект = Справочники.ЕдиницыИзмерения.СоздатьЭлемент(); НайденнаяЕдиницаОбъект.Наименование = СокрЛП (БазоваяЕдиницаИзмерения); НайденнаяЕдиницаОбъект.ЕдиницаПоКлассификатору = БазоваяЕдиницаИзмерения; НайденнаяЕдиницаОбъект.Коэффициент = 1 ; НайденнаяЕдиницаОбъект.Владелец = СсылкаНоменклатуры; Попытка НайденнаяЕдиницаОбъект.Записать(); Результат = НайденнаяЕдиницаОбъект.Ссылка; Исключение Сообщить ("Не удалось записать" ); КонецПопытки ; Иначе Результат =ТекСпр.Ссылка; КонецЕсли ;
Качество:
Режим загрузки — Устанавливать
Значение по умолчанию — Новый
Остальные реквизиты:
Режим загрузки — Устанавливать
Значение по умолчанию — оставляем пустым
После заполнения выполняем контроль заполнения во вкладке табличный документ и нажимаем Загрузить.
После успешной загрузки сохраняем настройки в файл.
Чтобы загрузить эту настройку нажмите сюда.
Конфигурация: 1С:Бухгалтерия
Версия конфигурации: 3.0.44.177
Дата публикации: 20.01.2017
В программе 1С:Бухгалтерия появилась возможность штатной выгрузки и загрузки данных из табличных документов Excel, без применения сторонних обработок и изменения конфигурации. Загрузка и выгрузка на столько проста, что опытные пользователи очень быстро полюбят этот удобюный механизм, а новые пользователи быстро его освоят.
Загрузка из Excel 1С:Бухгалтерия 8.3 ред. 3.0
К примеру мы имеем вот такой табличный документ Excel для загрузки списка номенклатуры и цены(розничной и закупочной).
Заходи в .
В верхней части нажимаем Номенклатура и Цены - Загрузить . Именно в этом спрятана загрузка и выгрузка из табличных документов.
Загрузка поддерживает форматы xls(старый формат Excel 97-2003) и xlsx(новый формат), а так же mxl и ods форматы. Теперь выбираем наш файл с номенклатурой и ждем загрузки.
Загрузился табличный документ в том виде как мы его видели в Excel, теперь назначим колонки и удалим лишнее.
Нажимаем на Укажите реквизит и выбираем нужный пункт из списка.
Выделяем строку, в которой есть ненужные записи, а именно шапка таблицы и нажимаем Удалить строку .
Теперь удаляем столбец с закупочной ценой, обратите внимание, что обработка умеет за 1н раз заполнять только 1н вид цены!!! Потом вы можете проделать эту операцию снова и загрузить 2й вид цены, за 1ю загрузку Розничную за 2ю закупочную.
Заполняем не заполненные данные, Тип цены, Установить цены на. Кнопки Все, Найденные, Новые, Дубли существенно облегчат поиск и помогут сопоставить номенклатуру. Заполняем и нажимаем Загрузить после чего данные будут загружены в 1С:Бухгалтерия.
В списке номенклатуры появились новые позиции, заполненные и с назначенными ценами. Если требуется подгрузить другой вид цены, проделайте снова все пункты, но выбирайте столбец с другой ценой, 1С сама найдет уже созданные позиции и не будет пытаться их дублировать, а просто допишет новый вид цен.
Как видите, загрузка создала 2 документа установка цен номенклатуры, для Закупочной цены и для Розничной. Отличие этой обработки по Загрузке из Excel в 1С:Бухгалтерия 8.3, в том что это штатная обработка и она работает без сбоев и учитывая все особенности заполнения документа.
Выгрузка в Excel из 1С:Бухгалтерия 8.3 ред. 3.0
Очень полезной функцией в редакции 3.0.44.177 и выше является выгрузка в Excel из 1С:Бухгалтерия 8.3, эта обработка поможет вам сформировать прайс-лист компании или передать поставщикам или покупателям свой список номенклатуры сразу с ценами.
Все там же в справочнике Номенклатура - Номенклатура и Цены - Выгрузить .
В пункте Колонки , выбираем те колонки которые надо построить в документе Excel. Отбор служит для отбора номенклатуры по Группам, свойствам, названиям... итд.
Для выгрузки в excel мы будем добавлять новую колонку, а именно ставку НДС, вроде не очень надо, но мы практикуемся.
Нередко предприятия параллельно с 1С ведут учет и в программе Microsoft Excel. Такое возможно, например, когда переход на 1С с других систем был совсем недавно, или если у бухгалтера в системе 1С не настроены должным образом отчеты и ему удобнее анализировать данные в Excel, а может быть поставщики присылают накладные или прайс листы в файлах Excel (обмен информацией в электронном виде через файлы Excel очень распространен во взаимоотношениях между организациями). В этих случаях остро встает вопрос о необходимости автоматизации процесса и избавления пользователей от монотонного вбивания информации в 1С. Для написания соответствующей обработки приходится обращаться к программистам 1С, а это - дополнительные затраты. Не все знают, что компания 1С уже давно разработала подобную бесплатную универсальную обработку, главное - научиться ею правильно пользоваться. А так как главный принцип нашей фирмы - "Клиент платит только за фактически выполненную работу", мы совершенно бесплатно в данной статье обучим Вас, как с помощью данной обработки загрузить данные из файла Excel в любую программу 1С на платформах 1С:Предприятие 8.2 и 1С:Предприятие 8.1.
Где взять обработку по загрузке данных в 1С из excel
Универсальная обработка от фирмы 1С по загрузке данных из excel называется ЗагрузкаДанныхИзТабличногоДокумента.epf и находиться на диске ИТС.
Для 8.1 E:1CIts\EXE\EXTREPS\UNIREPS81\UploadFromTableDocument
или скачать
Для 8.2 E:1CIts\EXE\EXTREPS\UNIREPS82\UploadFromTableDocument или скачать
Как запустить обработку по загрузке данных в 1С из excel
Для запуска обработки ЗагрузкаДанныхИзТабличногоДокумента.epf необходимо:- Запустить 1С в режиме 1С:Предтиятие, то есть открыть базу, в которую Вы собираетесь загрузить данные;
- Далее нажать Файл - Открыть;
В появившемся окне выбрать файл ЗагрузкаДанныхИзТабличногоДокумента.epf, по тому пути, где данная обработка у Вас находится;
Вот так выглядит сама обработка по загрузке данных из excel:
Загрузка справочника номенклатура из excel
Ну и, наконец, мы дошли до самого главного - как работать с данной обработкой. Данная обработка позволяет загрузить данные как в справочники,так и в документы. В данной статье мы опишие пример заполнения справочника "Номенклатура", по данному примеру Вы научитесь самостоятельно загружать любые справочники в 1С. Пример заполнения документов, например, загрузку прайс листов в 1С смотрите .
Для того,чтобы загрузить номенклатуру, необходимо:
- в поле "Вид справочника" выбрать "Номенклатура";
- на закладке "Табличный документ" нажать кнопку "Открыть файл", как показано на рисунке;
- в открывшемся окне выбираем тот файл, который Вы собираетесь загрузить, не забудьте установить правильный тип файлов, в нашем случае "Лист Excel(*.xls)";
Обработка заполнит табличную часть закладки "Табличный документ";
Переходим на закладку "Настройка" и заполняем следующими данными, как показано на рисунке;
Поле "Первая строка данных табличного документа" - здесь необходимо указать номер строки, с которой необходимо загружать данные, в нашем случае это 2
;
Галочка "не создавать новых элементов" служит для того, чтобы не загружать новые данные, ее можно использовать при заполнении документов, чтобы новые элементы справочников, например, номенклатура не создавались, а подставлялись только из базы 1С. Так как в нашем случае мы как раз хотим загрузить новые номенклатуры, галочку не ставим
.
Далее нам необходимо нажать кнопку "Нумерация колонок" - "Ручная нумерация колонок", это нам позволит вручную проставить номера колонок из которых обработка будет считывать данные и автоматически подставлять в нужные поля.
А теперь проставим номера колонок:
- в нашем случае поля "Наименование" и "Полное наименование" будут одинаковы и должны запонняться из 3-ей колонки, ставим 3
в колонке "№ колонки табличного документа", "Режим загрузки" - "Искать";
- строку "Код" отключим, пусть 1С сама сформирует коды номенклатурам;
- "Родитель" - скажем обработке, пусть она всю загруженную номенклатуру загружает в "Новую папку", во избежании бардака, а мы в дальнейшем, если потребуется перенесем номенклатуры по своим папкам вручную. Если же вся загружаемая номенклатура относится к определенной папке, например "Оборудование", то ее можно сразу здесь указать. "Режим загрузки" - "Устанавливать";
- "Базовая единица измерения", тоже установим из существующих, у нас это шт. "Режим загрузки" - "Устанавливать";
- "Ставка НДС" - у нас будет 18%, установим и "Режим загрузки" - "Устанавливать";
- "Комментарий" - можно не устанавливать, но мы заполним значением "Загрузка из Excel 20.09.2012", чтобы сохранилась своего рода история, что и когда загрузили, "Режим загрузки" - "Устанавливать";
- "Услуга" - "Нет", так как мы загружаем товар,"Режим загрузки" - "Устанавливать";
- "Артикул" - должны запонняться из 1-ой колонки, ставим 1
в колонке "№ колонки табличного документа", "Режим загрузки" - "Искать". И самое главное устанавливаем галочку "Поле поиска"
, этим самым мы сообщаем обработке, чтобы она искала номенклатуру по артиклу, и если не нашла, то пусть создает новый. Если Вы не пользуетесь Артикулом, то устанавливайте галочку "Поле поиска" напротив "Наименование", но помните, если у Вас в базе будет такая же номенклатура как и при загрузке,только отличаться хотя бы на 1 символ, например, точку,запятую или букву, то обработка будет считать что это две разные номенклатуры и создаст новую. Обработка осуществляет поиск по Наименованию только строго по точному соответствию.
Далее жмем кнопку "Загрузить" и проверяем результаты загрузки справочника номенклатура из excel.
Данный способ простой. Его суть заключается в том, что объект ТабличныйДокумент имеет методы:
- Записать(< ИмяФайла>, < ТипФайлаТаблицы >) для выгрузки данных в файл;
- Прочитать(< ИмяФайла>, < СпособЧтенияЗначений >) для загрузки данных из файла.
Внимание!
Метод Записать
()
доступен как на клиенте, так и на сервере. Метод Прочитать
()
доступен только на стороне сервера. Необходимо помнить об этом
при планировании клиент-серверного взаимодействия.
Рассмотрим пример сохранения табличного документа в файл. Необходимо любым способом создать и заполнить объект ТабличныйДокумент, а выгрузка в файл осуществляется всего лишь одной строкой:
ТабДок. Записать(ПутьКФайлу, ТипФайлаТабличногоДокумента. XLSX );
Здесь ТабДок — сформированный табличный документ, ПутьКФайлу — имя файла для выгрузки, ТипФайлаТабличногоДокумента.XLSX — формат создаваемого файла. Поддерживаются следующие форматы Excel:
- XLS95 — формат Excel 95;
- XLS97 — формат Excel 97;
- XLSX — формат Excel 2007.
ТабДок = Новый ТабличныйДокумент;
ТабДок. Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Значение );
Здесь ПутьКФайлу — путь к загружаемому файлу Excel. СпособЧтенияЗначенийТабличногоДокумента.Значение определяет, каким образом нужно интерпретировать данные, считываемые из исходного документа. Доступны варианты:
- Значение;
- Текст.
Обмен через OLE
Обмен через технологию OLE automation, пожалуй, самый распространенный вариант программной работы с файлами Excel. Он позволяет использовать весь функционал, предоставляемый Excel, но отличается медленной скоростью работы по сравнению с другими способами. Для обмена через OLE требуется установка MS Excel:
- На компьютере конечного пользователя, если обмен происходит на стороне клиента;
- На компьютере сервера 1С:Предприятие, если обмен происходит на стороне сервера.
Пример выгрузки :
// Создание COM-объекта
Эксель = Новый COMОбъект(«Excel.Application»);
// Отключение вывода предупреждений и вопросов
Эксель. DisplayAlerts = Ложь;
// Создание новой книги
Книга = Эксель. WorkBooks. Add();
// Позиционирование на первом листе
Лист = Книга. Worksheets(1);// Запись значения в ячейку
Лист. Cells(НомерСтроки, НомерКолонки). Value = ЗначениеЯчейки;// Сохранение файла
Книга. SaveAs(ИмяФайла);
Эксель. Quit();
Эксель = 0;
Примеры чтения :
// —— ВАРИАНТ 1 ——
// Создание COM-объекта
Эксель = Новый COMОбъект(«Excel.Application» );
// Открытие книги
Книга = Эксель. Workbooks. Open(ПутьКФайлу );
Лист = Книга. Worksheets(1 );// Закрытие книги
Книга. Close(0 );// Закрытие Эксель и освобождение памяти
Эксель. Quit();
Эксель = 0 ;// —— ВАРИАНТ 2 ——
// Открытие книги
Книга = ПолучитьCOMОбъект(ПутьКФайлу );
// Позиционирование на нужном листе
Лист = Книга. Worksheets(1 );// Чтение значения ячейки, обычно здесь располагается цикл обхода ячеек
ЗначениеЯчейки = Лист. Cells(НомерСтроки, НомерКолонки). Value;// Закрытие книги
Книга. Application. Qui t();
Для обхода всех заполненных строк листа Excel можно использовать следующие приемы:
// —— ВАРИАНТ 1 ——
КоличествоСтрок = Лист. Cells(1 , 1 ). SpecialCells(11 ). Row;
Для НомерСтроки = 1 По КоличествоСтрок Цикл
ЗначениеЯчейки = Лист. Cells(НомерСтроки, НомерКолонки). Value ;
КонецЦикла;// —— ВАРИАНТ 2 ——
НомерСтроки = 0 ;
Пока Истина Цикл
НомерСтроки = НомерСтроки + 1 ;
ЗначениеЯчейки = Лист. Cells(НомерСтроки, НомерКолонки). Value;
Если НЕ ЗначениеЗаполнено(ЗначениеЯчейки ) Тогда
Прервать;
КонецЕсли;
КонецЦикла;
Вместо последовательного обхода всех строк листа можно выгрузить все данные в массив и работать с ним. Такой подход будет быстрее при чтении большого объема данных:
ВсегоКолонок = Лист. Cells(1 , 1 ). SpecialCells(11 ). Column;
ВсегоСтрок = Лист. Cells(1 , 1 ). SpecialCells(11 ). Row;Область = Лист. Range(Лист. Cells(1 , 1 ), Лист. Cells(ВсегоСтрок, ВсегоКолонок));
Данные = Область. Value. Выгрузить ();
В таблице ниже приведены наиболее востребованные свойства и методы для работы с Excel через OLE:
Действие | Код | Комментарий |
Работа с приложением | ||
Установка видимости окна приложения | Эксель. Visible = Ложь; | |
Установка режима вывода предупреждений (выводить/не выводить) | Эксель. DisplayAlerts = Ложь; | |
Закрытие приложения | Эксель. Quit(); | |
Работа с книгой | ||
Создание новой книги | Книга = Эксель. WorkBooks. Add (); | |
Открытие существующей книги | Книга = Эксель. WorkBooks. Open(ИмяФайла ); | |
Сохранение книги | Книга. SaveAs(ИмяФайла ); | |
Закрытие книги | Книга. Close(0 ); | |
Работа с листом | ||
Установка текущего листа | Лист = Книга. WorkSheets(НомерЛиста ); | |
Установка имени | Лист. Name = Имя ; | |
Установка защиты | Лист. Protect (); | |
Снятие защиты | Лист. UnProtect (); | |
Установка ориентации страницы | Лист. PageSetup. Orientation = 2 ; | 1 — книжная, 2 — альбомная |
Установка левой границы | Лист. PageSetup. LeftMargin = Эксель. CentimetersToPoints(Сантиметры ); | |
Установка верхней границы | Лист. PageSetup. TopMargin = Эксель. CentimetersToPoints(Сантиметры ); | |
Установка правой границы | Лист. PageSetup. RightMargin = Эксель. CentimetersToPoints(Сантиметры ); | |
Установка нижней границы | Лист. PageSetup. BottomMargin = Эксель. CentimetersToPoints(Сантиметры ); | |
Работа со строками, колонками, ячейками | ||
Установка ширины колонки | Лист. Columns(НомерКолонки). ColumnWidth = Ширина ; | |
Удаление строки | Лист. Rows(НомерСтроки). Delete (); | |
Удаление колонки | Лист. Columns(НомерКолонки). Delete(); | |
Удаление ячейки | Лист. Cells(НомерСтроки, НомерКолонки). Delete (); | |
Установка значения | Лист. Cells(НомерСтроки, НомерКолонки). Value = Значение ; | |
Объединение ячеек | Лист. Range(Лист. Cells(НомерСтроки, НомерКолонки), Лист. Cells(НомерСтроки1, НомерКолонки1)). Merge (); | |
Установка шрифта | Лист. Cells(НомерСтроки, НомерКолонки). Font. Name = ИмяШрифта ; | |
Установка размера шрифта | Лист. Cells(НомерСтроки, НомерКолонки). Font. Size = РазмерШрифта ; | |
Установка жирного шрифта | Лист. Cells(НомерСтроки, НомерКолонки). Font. Bold = 1 ; | 1 — жирный шрифт, 0 — нормальный |
Установка курсива | Лист. Cells(НомерСтроки, НомерКолонки). Font. Italic = 1 ; | 1 — курсив, 0 — нормальный |
Установка подчеркнутого шрифта | Лист. Cells(НомерСтроки, НомерКолонки). Font. Underline = 2 ; | 2 — подчеркнутый, 1 — нет |
Для того, чтобы узнать какое свойство нужно менять или какой метод вызвать можно воспользоваться макросами Excel. Если записать макрос с требуемыми действиями, то после можно посмотреть программный код на VBA записанного макроса.
Использование COMSafeArray
При выгрузке больших объемов данных из 1С в Excel для ускорения можно использовать объект COMSafeArray . Согласно определению из синтакс-помощника, COMSafeArray — объектная оболочка над многомерным массивом SafeArray из COM. Позволяет создавать и использовать SafeArray для обмена данными между COM-объектами. Проще говоря, это массив значений, который можно использовать для обмена между приложениями по технологии OLE.
// Создание COMSafeArray
МассивКом = Новый COMSafeArray(«VT_Variant» , ВсегоКолонок, ВсегоСтрок );
// Заполнение COMSafeArray
Для Стр = 0 По ВсегоСтрок — 1 Цикл
Для Кол = 0 По ВсегоКолонок — 1 Цикл
МассивКом. SetValue(Кол, Стр, Значение );
КонецЦикла;
КонецЦикла;
// Присвоение области листа Excel значений из COMSafeArray
Лист. Range(Лист. Cells(1 , 1 ), Лист. Cells(ВсегоСтрок, ВсегоКолонок)). Value = МассивКом ;
Обмен через ADO
Файл Excel при обмене через ADO представляет собой базу данных, к которой можно обращаться при помощи SQL-запросов. Установка MS Excel не требуется, но обязательно наличие драйвера ODBC, при помощи которого будет осуществляться доступ. Используемый драйвер ODBC определяется при указании строки соединения к файлу. Обычно требуемый драйвер уже установлен на компьютере.
Обмен через ADO заметно быстрее обмена через OLE, но при выгрузке нет возможности использовать функционал Excel для оформления ячеек, разметки страниц, задания формул и т.д.
Пример выгрузки :
Соединение = Новый COMОбъект(«ADODB.Connection»);
Соединение. ConnectionString = «
|Data Source=»+ ИмяФайла + «;
;
Соединение. Open(); // Открытие соединения// Создание COM-объекта для команды
Команда = Новый COMОбъект(«ADODB.Command» );
Команда// Присвоение текста команды для создания таблицы
Команда. CommandText = «CREATE TABLE [Лист1] (Колонка1 char(255), Колонка2 date, Колонка3 int, Колонка4 float)» ;
Команда. Execute(); // Выполнение команды// Присвоение текста команды для добавления строки таблицы
Команда. CommandText = «INSERT INTO [Лист1] (Колонка1, Колонка2, Колонка3, Колонка4) values (‘абвгдеё’, ‘8/11/2017’, ‘12345’, ‘12345,6789’)» ;
Команда.Execute(); // Выполнение команды// Удаление команды и закрытие соединения
Команда = Неопределено;
Соединение. Close ();
Соединение = Неопределено ;
Для создания нового листа и формирования его структуры можно воспользоваться объектами ADOX.Catalog и ADOX.Table . В этом случае код примет вид:
// Создание COM-объекта для работы с книгой
Книга = Новый COMОбъект(«ADOX.Catalog» );
Книга. ActiveConnection = Соединение;// Создание COM-объекта для работы со структурой данных на листе
Таблица = Новый COMОбъект(«ADOX.Table» );
Таблица. Name = «Лист1» ;
Таблица. Columns. Append(«Колонка1» , 202 );
Таблица. Columns. Append(«Колонка2» , 7 );
Таблица. Columns. Append(«Колонка3» , 5 );
Таблица. Columns. Append(«Колонка4» , 5 );// Создание в книге листа с описанной структурой
Книга. Tables. Append(Таблица);
Таблица = Неопределено;
Книга = Неопределено;
В приведенном примере в методе
Таблица. Columns. Append («Колонка1» , 202 );
во втором параметре указывается тип колонки. Параметр необязательный, вот некоторые значения типа колонки:
- 5 — adDouble;
- 6 — adCurrency;
- 7 — adDate;
- 11 — adBoolean;
- 202 — adVarWChar;
- 203 — adLongVarWChar.
Пример чтения :
// Создание COM-объекта для соединения
Соединение = Новый COMОбъект(«ADODB.Connection» );// Установка строки соединения
Соединение. ConnectionString = «
|Provider=Microsoft.ACE.OLEDB.12.0;
|Data Source=»+ ИмяФайла + «;
|Extended Properties=»»Excel 12.0 XML;HDR=YES»»;» ;
Соединение. Open(); // Открытие соединения// Создание COM-объекта для получения выборки
Выборка = Новый COMОбъект(«ADODB.Recordset» );
ТекстЗапроса = «SELECT * FROM [Лист1$]» ;// Выполнение запроса
Выборка. Open(ТекстЗапроса, Соединение);// Обход результата выборки
Пока НЕ Выборка. EOF() Цикл
ЗначениеКолонки1 = Выборка. Fields. Item(«Колонка1» ). Value; // Обращение по имени колонки
ЗначениеКолонки2 = Выборка. Fields. Item(0 ). Value; // Обращение по индексу колонки
Выборка. MoveNext();
КонецЦикла;Выборка. Close();
Выборка = Неопределено;
Соединение. Close();
Соединение = Неопределено;
В строке соединения параметр HDR определяет как будет восприниматься первая строка на листе. Возможны варианты:
- YES — первая строка воспринимается как названия колонок. К значениям можно обращаться по имени и по индексу колонки.
- NO — первая строка воспринимается как данные. К значениям можно обращаться только по индексу колонки.
В приведенных примерах рассмотрено лишь несколько объектов ADO. Объектная модель ADO состоит из следующих объектов:
- Connection;
- Command;
- Recordset;
- Record;
- Fields;
- Stream;
- Errors;
- Parameters;
- Properties.
Выгрузка без программирования
Для сохранения данных из 1С в Excel не всегда целесообразно прибегать к программированию. Если в режиме Предприятия пользователь может отобразить требуемые для выгрузки данные, то, их возможно сохранить в Excel без программирования.
Для сохранения табличного документа (например, результата отчета) можно вызвать команду Сохранить или Сохранить как… главного меню.
В открывшемся окне требуется выбрать каталог, имя и формат сохраняемого файла.
Для сохранения данных динамических списков (например, списка номенклатуры) необходимо:
- Вывести данные в табличный документ при помощи команды Еще ⇒ Вывести список… ;
- Сохранить табличный документ в требуемый формат.
Видеоматериал в тему:
Многие, кто работают в программе 1С, сталкиваются с необходимостью загрузки данных из электронной таблицы Excel в 1С. Когда у пользователя может возникнуть необходимость такой загрузки:
- сформирован отчет, сохранен в Excel
, обработан. Обработанные данные необходимо загрузить в 1С в виде документа (перемещение, возврат поставщику)
- есть список номенклатуры, который нужно загрузить в справочник. Список номенклатуры находится в Excel
- любые другие данные, которые нужно загрузить в табличную часть документа, справочник, регистр сведений
Ниже приведена инструкция загрузки данных из Excel в табличную часть документа "Возврат товаров от покупателя"
.
Действие 1
. Подготовка документа, в который будут загружаться данные. Необходимо создать пустой документ "Возврат товаров от покупателя" и записать его.
Действие 2 . Открыть внешнюю обработку "Загрузка данных из табличного документа".
Действие 3 . В обработке выбрать место, в которое мы будем загружать данные.
Действие 4 . Настройка параметров загрузки. Сделайте все как на рисунке, чтобы потренироваться.
Действие 5 . Копирование данных из Excel в обработку в закладку "Табличный документ" (как на рисунке). Красным выделены две кнопки: «Контроль» заполнения (показывает ошибки, например, если неправильно указана единица измерения загружаемой номенклатуры) и «Загрузить» (выполняет загрузку).
Чем меньше параметров для загрузки Вы укажите, тем меньше вероятность ошибок при загрузке, но тем больше данных придется корректировать вручную в загруженном документе.
Cобственно, сама обработка загрузки данных из Excel в 1С