Выгрузка данных в эксель

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

Выгрузка информации из 1C в Excel

Если загрузка данных из Excel в 1С – это довольно сложная процедура, автоматизировать которую можно только с помощью сторонних решений, то обратный процесс, а именно выгрузка из 1С в Эксель – это сравнительно простой набор действий. Его можно легко выполнить с помощью встроенных инструментов вышеуказанных программ, причем сделать это можно несколькими способами, в зависимости от того, что именно пользователю нужно перенести. Рассмотрим, как это выполнить на конкретных примерах в 1С версии 8.3.

Способ 1: копирование содержимого ячейки

Одна единица данных содержится в ячейке 1С. Её можно перенести в Эксель обычным методом копирования.

1. Выделяем ячейку в 1С, содержимое которой нужно скопировать. Кликаем по ней правой кнопкой мыши. В контекстном меню выбираем пункт «Копировать». Можно воспользоваться также универсальным способом, который действует в большинстве программ, работающих на ОС Windows: просто выделяем содержимое ячейки и набираем комбинацию клавиш на клавиатуре Ctrl+C.

Открываем чистый лист Excel или документ, куда нужно вставить содержимое. Кликаем правой кнопкой мыши и в появившемся контекстном меню в параметрах вставки выбираем пункт «Сохранить только текст», который изображен в форме пиктограммы в виде большой буквы «А».

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

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

Содержимое ячейки 1С будет вставлено в Excel.

Способ 2: вставка списка в существующую книгу Excel

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

1. Открываем любой список, журнал или справочник в 1С. Кликаем на кнопку «Все действия», которая должна располагаться вверху от обрабатываемого массива данных. Запускается меню. Выбираем в нем пункт «Вывести список».

Открывается небольшое окошко вывода списка. Тут можно произвести некоторые настройки.

Поле «Выводить в» имеет два значения:

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

В блоке «Выводить колонки» можно указать, какие колонки из списка вы хотите перевести в Excel. Если вы собираетесь производить перенос всех данных, то эту настройку тоже не трогаем. Если же вы хотите произвести конвертацию без какого-то столбца или нескольких столбцов, то снимаем галочку с соответствующих элементов.

После того, как настройки завершены, жмем на кнопку «OK».

Затем на экран выводится список в табличном виде. Если вы хотите перенести его в готовый файл Excel, то просто выделите в нем все данные курсором с зажатой левой кнопкой мыши, потом кликните по выделению правой кнопкой мышки и в открывшемся меню выберите пункт «Копировать». Можно так же, как и в предыдущем способе использовать сочетание горячих клавиш Ctrl+С.

Открываем лист Microsoft Excel и выделяем верхнюю левую ячейку диапазона, в который будут вставляться данные. Затем жмем на кнопку «Вставить» на ленте во вкладке «Главная» или набираем сочетание клавиш Ctrl+V.

Список вставлен в документ.

Способ 3: создание новой книги Excel со списком

Также список из программы 1С можно сразу вывести в новый файл Эксель.

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

Ещё проще сделать переход, нажав на кнопку «Сохранить», которая имеет вид дискеты и расположена в блоке инструментов 1С в самом верху окна. Но такая возможность доступна только пользователям, которые применяют программу версии 8.3. В ранних версиях можно использовать только предыдущий вариант.

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

Запускается окно сохранение файла. Переходим в ту директорию, в которой планируем сохранить книгу, если место расположения по умолчанию не устраивает. В поле «Тип файла» по умолчанию указано значение «Табличный документ (*.mxl)». Нам это не подходит, поэтому из выпадающего списка выбираем пункт «Лист Excel (*.xls)» или «Лист Excel 2007 — … (*.xlsx)». Также при желании можно выбрать совсем старые форматы – «Лист Excel 95» или «Лист Excel 97». После того, как настройки сохранения произведены, жмем на кнопку «Сохранить».

Весь список будет сохранен отдельной книгой.

Способ 4: копирование диапазона из списка 1С в Excel

Бывают случаи, когда нужно перенести не весь список, а только отдельные строки или диапазон данных. Этот вариант тоже вполне воплотим с помощью встроенных инструментов.

Выделяем строки или диапазон данных в списке. Для этого зажимаем кнопку Shift и кликаем левой кнопкой мыши по тем строкам, которые нужно перенести. Жмем на кнопку «Все действия». В появившемся меню выбираем пункт «Вывести список…».

Запускается окошко вывода списка. Настройки в нем производим так же, как и в предыдущих двух способах. Единственный нюанс состоит в том, что нужно установить галочку около параметра «Только выделенные». После этого жмем на кнопку «OK».

Как видим, список, состоящий исключительно из выделенных строк, выведен. Дальше нам нужно будет выполнить точно такие же действия как в Способе 2 или в Способе 3, в зависимости от того, собираемся мы добавлять список к уже имеющейся книге Excel или создавать новый документ.

Способ 5: Сохранение документов в формате Excel

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

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

Открывается форма для печати. Но нам, как мы помним, нужно не распечатать документ, а конвертировать его в Эксель. Легче всего в версии 1С 8.3 это сделать, нажав на кнопку «Сохранить» в виде дискеты.

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

Открывается окно сохранение документа. Как и в предыдущих способах, в нем нужно указать место расположения сохраняемого файла. В поле «Тип файла» следует указать один из форматов Эксель. Не забываем дать название документу в поле «Имя файла». После выполнения всех настроек жмем на кнопку «Сохранить».

Документ будет сохранен в формате Эксель. Этот файл теперь можно открывать в данной программе, и проводить дальнейшую его обработку уже в ней.

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

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

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

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

1С загрузка из Excel и выгрузка из 1С в Excel глазами рядового пользователя 1С

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

Почему нет универсальной обработки по загрузке данных из Excel в 1С ?

Давайте ответим на этот вопрос и рассмотрим его основные моменты:

1. У каждого пользователя есть различные файлы Excel, данные из которых он хочет перенести в 1С, конечно же эти файлы имеют разные количество строк и столбцов, а также несут разную смысловую нагрузку.
2. Каждый пользователь работает с отдельной конфигурацией, т.е. конфигурации могут быть различными, то что работает в одной конфигурации, в другой конфигурации без модернизации работать не будет.
3. Данные из Excel должны переноситься в различные объекты ,соответственно нужен разный код, ведь у каждого пользователя свои требования.

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

Как происходит загрузка и выгрузка данных из Excel в 1С на примере реальной разработки

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

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

Выгрузка данных из 1С в Excel происходит примерно также, только тут сложность написания обработки зависит от того какие именно данные должны выгружаться из объекта. Например у нас есть справочник «Номенклатура» и мы хотим выгрузить наименование самой номенклатуры и единицу измерения данной номенклатуры, хотя у самой номенклатуры может быть намного больше реквизитов, такие как, вид номенклатуры, тип номенклатуры, наименование полное и т.д.

Мне нужна обработка по загрузке и/или выгрузке данных из Excel, что мне делать?

Все что Вам нужно сделать это поднять трубку и позвонить нам. После этого наши специалисты выполнят все необходимые действия чтобы вы получили именно то, что Вам требуется. Если Вам нужно выполнить срочное обновление 1с то звоните нам, мы рады помочь каждому нашему клиенту!

Загрузка и выгрузка данных из Excel в 1С глазами рядового программиста 1С

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

Начнем мы с того как подключить Excel к 1С, делается это посредством COM-объекта. Для того чтобы этот код работал нормально Excel должен быть установлен на компьютере, в противном случае ничего работать не будет:

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

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

Мы можем узнать количество листов книги эксель с помощью поля "Count":

Чтобы получить имя листа под определенным номером, нужно написать подобную строчку:

Если нам необходимо прочитать определенную ячейку нужно вызывать метод Cells(), после чего обратится к полю Value. Можно конечно прочитать сразу весь файл экселя, но об этом чуть позже:

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

Пример функции которая загружает отдельные ячейки из Excel в 1С

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

Мы думаем, что никаких дополнительных разъяснений по данному коду приводить не нужно, так как все эти функции детально рассматривались выше.

Пример функции которая загружает все данные из Excel в табличную часть 1С

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

Пример функции которая выгружает данные из 1С в Excel

Выше мы с вами разобрали много всего интересного, но мы не касались такой темы как выгрузка данных из 1С в Excel. На самом деле принцип работы тут в абсолютности почти такой же, за исключением того, что мы программно создаем новый файл эксель, и программно его записываем. Ну а для того чтобы записать значение в ячейку вы как уже наверное догадались нужно поставить выражение Лист.Cells(x, y) в левую часть. Пример выгрузки данных из 1С в Excel представлен ниже:

Есть в IT-отрасли задачи, которые на фоне успехов в big data, machine learning, blockchain и прочих модных течений выглядят совершенно непривлекательно, но на протяжении десятков лет не перестают быть актуальными для целой армии разработчиков. Речь пойдёт о старой как мир задаче формирования и выгрузки Excel-документов, с которой сталкивался каждый, кто когда-либо писал приложения для бизнеса.

Какие возможности построения файлов Excel существуют в принципе?

1. VBA-макросы. В наше время по соображениям безопасности идея использовать макросы чаще всего не подходит.
2. Автоматизация Excel внешней программой через API. Требует наличия Excel на одной машине с программой, генерирующей Excel-отчёты. Во времена, когда клиенты были толстыми и писались в виде десктопных приложений Windows, такой способ годился (хотя не отличался скоростью и надёжностью), в нынешних реалиях это с трудом достижимый случай.
3. Генерация XML-Excel-файла напрямую. Как известно, Excel поддерживает XML-формат сохранения документа, который потенциально можно сгенерировать/модифицировать с помощью любого средства работы с XML. Этот файл можно сохранить с расширением .xls, и хотя он, строго говоря, при этом не является xls-файлом, Excel его хорошо открывает. Такой подход довольно популярен, но к недостаткам следует отнести то, что всякое решение, основанное на прямом редактировании XML-Excel-формата, является одноразовым «хаком», лишенным общности.
4. Наконец, возможна генерация Excel-файлов с использованием open source библиотек, из которых особо известна Apache POI. Разработчики Apache POI проделали титанический труд по reverse engineering бинарных форматов документов MS Office, и продолжают на протяжении многих лет поддерживать и развивать эту библиотеку. Результат этого reverse engineering-а, например, используется в Open Office для реализации сохранения документов в форматах, совместимых с MS Office.

На мой взгляд, именно последний из способов является сейчас предпочтительным для генерации MS Office-совместимых документов. С одной стороны, он не требует установки никакого проприетарного ПО на сервер, а с другой стороны, предоставляет богатый API, позволяющий использовать все функциональные возможности MS Office.

Но у прямого использования Apache POI есть и недостатки. Во-первых, это Java-библиотека, и если ваше приложение написано не на одном из JVM-языков, вы ей вряд ли сможете воспользоваться. Во-вторых, это низкоуровневая библиотека, работающая с такими понятиями, как «ячейка», «колонка», «шрифт». Поэтому «в лоб» написанная процедура генерации документа быстро превращается в обильную «лапшу» трудночитаемого кода, где отсутствует разделение на модель данных и представление, трудно вносить изменения и вообще — боль и стыд. И прекрасный повод делегировать задачу самому неопытному программисту – пусть ковыряется.

Но всё может быть совершенно иначе. Проект Xylophone под лицензией LGPL, построенный на базе Apache POI, основан на идее, которая имеет примерно 15-летнюю историю. В проектах, где я участвовал, он использовался в комбинации с самыми разными платформами и языками – а счёт разновидностей форм, сделанных с его помощью в самых разнообразных проектах, идёт, наверное, уже на тысячи. Это Java-проект, который может работать как в качестве утилиты командной строки, так и в качестве библиотеки (если у вас код на JVM-языке — вы можете подключить её как Maven-зависимость).

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

Шаблон документа (xls/xlsx template) выглядит примерно следующим образом:

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

Когда «оформительская» работа выполнена, разработчику остаётся

1. Создать процедуру выгрузки необходимых данных в формате XML.
2. Создать дескриптор, описывающий порядок обхода элементов XML-файла и копирования фрагментов шаблона в результирующий отчёт
3. Обеспечить привязку ячеек шаблона к элементам XML-файла с помощью XPath-выражений.

С выгрузкой в XML всё более-менее понятно: достаточно выбрать адекватное XML-представление данных, необходимых для заполнения формы. Что такое дескриптор?

Если бы в форме, которую мы создаём, не было повторяющихся элементов с разным количеством (таких, как строки накладной, которых разное количество у разных накладных), то дескриптор выглядел бы следующим образом:

Здесь root – название корневого элемента нашего XML-файла с данными, а диапазон A1:Z100 – это прямоугольный диапазон ячеек из шаблона, который будет скопирован в результат. При этом, как можно видеть из предыдущей иллюстрации, подстановочные поля, значения которых заменяются на данные из XML-файла, имеют формат

(тильда, фигурная скобка, XPath-выражение относительно текущего элемента XML, закрывающая фигурная скобка).

Что делать, если в отчёте нам нужны повторяющиеся элементы? Естественным образом их можно представить в виде элементов XML-файла с данными, а помочь проитерировать по ним нужным образом помогает дескриптор. Повторение элементов в отчёте может иметь как вертикальное направление (когда мы вставляем строки накладной, например), так и горизонтальное (когда мы вставляем столбцы аналитического отчёта). При этом мы можем пользоваться вложенностью элементов XML, чтобы отразить сколь угодно глубокую вложенность повторяющихся элементов отчёта, как показано на диаграмме:

Красными квадратиками отмечены ячейки, которые будут являться левым верхним углом очередного прямоугольного фрагмента, который пристыковывает генератор отчёта.

Есть и ещё один возможный вариант повторяющихся элементов: листы в книге Excel. Возможность организовать такую итерацию тоже имеется.

Рассмотрим чуть более сложный пример. Допустим, нам надо получить сводный отчёт наподобие следующего:

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

Мы вольны выбирать названия тэгов по своему вкусу, структура также может быть произвольной, но с оглядкой на простоту конвертации в отчёт. Например, выводимые на лист значения я обычно записываю в атрибуты, потому что это упрощает XPath-выражения (удобно, когда они имеют вид @имяатрибута ).

Шаблон такого отчёта будет выглядеть так (сравните XPath-выражения с именами атрибутов соответствующих тэгов):

Теперь наступает самая интересная часть: создание дескриптора. Т. к. это практически полностью динамически собираемый отчёт, дескриптор довольно сложен, на практике (когда у нас есть только «шапка» документа, его строки и «подвал») всё обычно гораздо проще. Вот какой в данном случае необходим дескриптор:

Полностью элементы дескриптора описаны в документации. Вкратце, основные элементы дескриптора означают следующее:

• element — переход в режим чтения элемента XML-файла. Может или являться корневым элементом дескриптора, или находиться внутри iteration . С помощью атрибута name могут быть заданы разнообразные фильтры для элементов, например

• name="foo" — элементы с именем тэга foo
• name="*" — все элементы
• name="tagname[@attribute=’value’]" — элементы с определённым именем и значением атрибута
• name="(before)" , name="(after)" — «виртуальные» элементы, предшествующие итерации и закрывающие итерацию.

iteration — переход в режим итерации. Может находиться только внутри element . Могут быть выставлены различные параметры, например

mode="horizontal" — режим вывода по горизонтали (по умолчанию — vertical)

index=0 — ограничить итерацию только самым первым встреченным элементом

output — переход в режим вывода. Основные атрибуты следующие:

sourcesheet —лист книги шаблона, с которого берётся диапазон вывода. Если не указывать, то применяется текущий (последний использованный) лист.

range – диапазон шаблона, копируемый в результирующий документ, например “A1:M10”, или “5:6”, или “C:C”. (Применение диапазонов строк типа “5:6” в режиме вывода horizontal и диапазонов столбцов типа “C:C” в режиме вывода vertical приведёт к ошибке).

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

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

Ну что же, настало время скачать Xylophone и запустить формирование отчёта.
Возьмите архив с bintray или Maven Central (NB: на момент прочтения этой статьи возможно наличие более свежих версий). В папке /bin находится shell-скрипт, при запуске которого без параметров вы увидите подсказку о параметрах командной строки. Для получения результата нам надо «скормить» ксилофону все приготовленные ранее ингредиенты:

Открываем файл report.xlsx и убеждаемся, что получилось именно то, что нам нужно:

Так как библиотека ru.curs:xylophone доступна на Maven Central под лицензией LGPL, её можно без проблем использовать в программах на любом JVM-языке. Пожалуй, самый компактный полностью рабочий пример получается на языке Groovy, код в комментариях не нуждается:

У класса XML2Spreadsheet есть несколько перегруженных вариантов статического метода process , но все они сводятся к передаче всё тех же «ингредиентов», необходимых для подготовки отчёта.

Важная опция, о которой я до сих пор не упомянул — это возможность выбора между DOM и SAX парсерами на этапе разбора файла с XML-данными. Как известно, DOM-парсер загружает весь файл в память целиком, строит его объектное представление и даёт возможность обходить его содержимое произвольным образом (в том числе повторно возвращаясь в один и тот же элемент). SAX-парсер никогда не помещает файл с данными целиком в память, вместо этого обрабатывает его как «поток» элементов, не давая возможности вернуться к элементу повторно.

Использование SAX-режима в Xylophone (через параметр командной строки -sax или установкой в true параметра useSax метода XML2Spreadsheet.process ) бывает критически полезно в случаях, когда необходимо генерировать очень большие файлы. За счёт скорости и экономичности к ресурсам SAX-парсера скорость генерации файлов возрастает многократно. Это даётся ценой некоторых небольших ограничений на дескриптор (описано в документации), но в большинстве случаев отчёты удовлетворяют этим ограничениям, поэтому я бы рекомендовал использование SAX-режима везде, где это возможно.

Надеюсь, что способ выгрузки в Excel через Xylophone вам понравился и сэкономит много времени и нервов — как сэкономил нам.