Выгрузка из 1с в excel sql

Обновлено: 22.01.2025

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

Обмен через табличный документ

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

Записать (< ИмяФайла >, < ТипФайлаТаблицы >) для выгрузки данных в файл;
Прочитать (< ИмяФайла >, < СпособЧтенияЗначений >) для загрузки данных из файла.

Внимание!

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

Рассмотрим пример сохранения табличного документа в файл. Необходимо любым способом создать и заполнить объект ТабличныйДокумент, а выгрузка в файл осуществляется всего лишь одной строкой:

ТабДок . Записать ( ПутьКФайлу , ТипФайлаТабличногоДокумента . XLSX );

Загрузка из файла осуществляется также достаточно просто:

ТабДок = Новый ТабличныйДокумент ;
ТабДок . Прочитать ( ПутьКФайлу , СпособЧтенияЗначенийТабличногоДокумента.Значение );

Обмен через OLE

Обмен через технологию OLE automation, пожалуй, самый распространенный вариант программной работы с файлами Excel. Он позволяет использовать весь функционал, предоставляемый Excel, но отличается медленной скоростью работы по сравнению с другими способами. Для обмена через OLE требуется установка MS Excel:

На компьютере конечного пользователя, если обмен происходит на стороне клиента;
На компьютере сервера 1С:Предприятие, если обмен происходит на стороне сервера.

Пример выгрузки:

Примеры чтения:

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

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

ВсегоКолонок = Лист . Cells ( 1 , 1 ). SpecialCells ( 11 ). Column ;
ВсегоСтрок = Лист . Cells ( 1 , 1 ). SpecialCells ( 11 ). Row ;

Область = Лист . Range ( Лист . Cells ( 1 , 1 ), Лист . Cells ( ВсегоСтрок , ВсегоКолонок ));
Данные = Область . Value . Выгрузить ();

В таблице ниже приведены наиболее востребованные свойства и методы для работы с Excel через OLE:

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

Использование COMSafeArray

Обмен через ADO

Файл Excel при обмене через ADO представляет собой базу данных, к которой можно обращаться при помощи SQL-запросов. Установка MS Excel не требуется, но обязательно наличие драйвера ODBC, при помощи которого будет осуществляться доступ. Используемый драйвер ODBC определяется при указании строки соединения к файлу. Обычно требуемый драйвер уже установлен на компьютере.

Обмен через ADO заметно быстрее обмена через OLE, но при выгрузке нет возможности использовать функционал Excel для оформления ячеек, разметки страниц, задания формул и т.д.

Пример выгрузки:

Для создания нового листа и формирования его структуры можно воспользоваться объектами ADOX.Catalog и ADOX.Table. В этом случае код примет вид:

В приведенном примере в методе

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

Пример чтения:

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

В приведенных примерах рассмотрено лишь несколько объектов ADO. Объектная модель ADO состоит из следующих объектов:

Connection;
Command;
Recordset;
Record;
Fields;
Stream;
Errors;
Parameters;
Properties.

Выгрузка без программирования

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

В открывшемся окне требуется выбрать каталог, имя и формат сохраняемого файла.

Для сохранения данных динамических списков (например, списка номенклатуры) необходимо:

Остались вопросы?
Спросите в комментариях к статье.

Какие способы подключения Power BI к 1С существуют?

Проблема. Нет единственного и правильного коннектора или способа подключения! Нельзя открыть Power BI, выбрать источник данных 1С, вбить логин, пароль и подключиться. Из-за этого у руководителей, начинающих аналитиков появляется много вопросов.

Мой опыт программиста позволяет использовать 4 способа подключения Power BI к 1С.
Разберу по порядку:
-выгрузка данных 1С в Excel файлы
-публикация базы на веб-сервере, подключение к ней через OData
-подключение к SQL базе 1С напрямую
-построение собственного аналитического хранилища.

Выгрузка данных 1С в Excel файлы

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

Аналитик изучает файлы Excel и использует в работе, пока разрабатываются продвинутые способы подключения Power BI к 1С.

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

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

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

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

Кто может сделать? Штатный бухгалтер или программист. Подробную техническую инструкцию найдете в статье Настройка выгрузок в Excel из 1С и Автоматизация выгрузок в Excel из 1С с помощью Рассылки отчетов.

Подключение к веб-базе 1С через OData

Веб-база — это такая база, к которой подключаются через браузер, не устанавливая программу 1С на локальный компьютер.

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

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

Во вторых, подключение через браузер это тоже обмен файлами. Power BI делает запрос к 1С, получает ответ в виде набора данных - файла формата JSON. Если данных много, файл большой, скорость интернета низкая — произойдет обрыв соединения. Поэтому этот способ техничнее чем Excel выгрузки, но по производительности такой же. Для небольших компаний и небольших объемов. Ритейлу, банкам, крупному бизнесу он не подойдет

По стоимости — оплатить 1 день работы программиста 1С и 1 день работы аналитика — забрать, обработать данные.

Подключение к базе SQL 1C напрямую

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

Тут 2 проблемы. Информация в SQL базе хранится в зашифрованном виде в виде метаданных. Регистр бухгалтерии это не одна таблица с русскими названиями, а десяток таблиц с ключами, кодами, непонятными значениями.

Вторая проблема — лицензионная политика 1С запрещает подключение напрямую к этой базе.

Решаем эти проблемы следующим образом.
Делаем копию SQL базы, программисты настраивают синхронизацию боевой базы 1С и этой копии (по расписанию), подключаемся к этой копии и разбираем эти метаданные. Существуют специальные обработки, которые позволяют из этого хаоса найти нужные таблицы и вместо метаданных получить таблицу как в Excel. Но нужно время, трудозатраты уже составляют неделю работы специалиста. Поэтому рекомендую 100 000 рублей заложить в бюджет проекта.

Такой способ хорош, когда данных много, их нельзя получить через Excel или веб-файлы. Через SQL десятки миллионов строчек прогрузятся в Power BI за пару минут. Скорость обновления отчетов Power BI будет быстрой.

Готовых статей — инструкций по этому способу у меня нет. Мой опыт программиста 1С позволяет в этом разобраться, а вот клиенты уже не могут. Используем этот способ в больших проектах.

Построение собственной аналитической базы

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

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

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

Аналогичная задача ставится программистам других сервисов. Информация загружается на первый уровень, это сырые данные. Система проверяет, что данные загружены, запускает механизм обновления — на лету чистит, обрезает, модифицирует, обновляет главные таблицы. Обеспечивается поэтапное добавление и контроль целостности данных.

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

Стоимость. Сильно зависит от трудозатрат. Это не 1 месяц работы программиста-аналитика. Сразу нельзя предусмотреть все варианты, начнете с базовых таблиц 1С, потом постепенно добавите другие сервисы. Потом поймете, какие изменения нужно сделать в этой базе, потому что Power BI не справляется с текущей архитектурой. Это сотни часов работы аналитиков и программистов. В крупном холдинге или компании это занимает не один год.

Тоже скажу — знаю, умею, но не берусь. Мы делаем проекты по аналитике от 100 000 руб. до 300 000 руб., иногда дороже, если растянуты по времени. А тут смета на хранилище начнется только от 300 000 руб. за 1-2 месяца работы, а этого может не хватить для большой компании.

Но если все по-взрослому, серьезно, надолго — это лучший вариант. Мерседес, если сравнивать с машинами. Но не всем нужно.

Выгрузка в 1С из v7.7 стандартными средствами

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

Далее следует задать имя файла (если не подходит предложенное системой), путь сохранения и поменять тип на Таблица Excel:

Всё! Данные в 1С выгружены:

Выгрузка данных из 1С v. 8.3 (8.2, 8.1)

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

Данная команда позволяет вывести видимые колонки.

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

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

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

Обратите внимание: восьмерка более современная, для сохранения доступен более широкий список форматов.

Также следует заметить:

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

Сохранение в Excel программным путем

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

ТабличныйДокумент . Записать ( ТипФайлаТабличногоДокумента . XLS, ) ;

Вариант 2: использование объекта Excel.Aplication

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

В холдинге имеются файловые базы 1С на каждую отдельную компанию. Сейчас сбор отчетности производится следующим образом:
1) специалист фин отдела заходит в каждую базу и собирает необходимую информацию о движении денежных средств, карточку 20,25,26 счетов
2) копирует информацию в Excel
3) обрабатывает вручную в едином шаблоне в Excel, чтобы видеть консолидированную информацию по группе компаний.

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

Вопрос задан более трёх лет назад
8583 просмотра

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

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

АртемЪ: а как быть с тем, что базы могут быть закрыты? Точнее они итак закрыты. Хочется открыть файл Excel, нажать кнопку "Получить данные" и обработка сама вытащит из баз все данные. Как я понимаю, базы все равно нужно открывать.

Из батника вызываете 1cv8.exe указав в качестве параметров режим Enterprise и имя обработки.
Она откроется в фоновом режиме, выполнит обработку и закроется.

Читайте также: