Выгрузка из 1с в excel sql
Выгружаем из 1С в EXCEL с помощью ADO. Часть I
Наверное уже многие знают что с помощью технологии ADO можно быстро читать большие объемы информации из книг excel. Но ведь с помощью данной технологии можно так же легко создавать и заполнять файлы excel прямо из 1С. Неверите ? Смотрите …
Итак, постановка задачи: Создать с помощью механизма ADO из 1С файл excel и выгрузить в него из справочника номенклатура код и наименование.
Все, готово !
Плюсы данного способа формирования файла excel:
- Большая скорость работы.
- Не требуется запуск самого процесса excel.exe, как это происходит в случае использования OLE-сервера excel.application, а следовательно на него не тратятся ресурсы. Кроме того нет необходимости отслеживать и перехватывать ошибки, связанные с процессом excel.exe, те кто использовал OLE-cоединение — тот поймет о чем я говорю, мало радости когда в случае программной или пользовательской ошибки процесс excel.exe остается висеть в памяти.
- Не требуется самого приложения EXCEL, достаточно что бы на компьютере был установлен OLEDB провайдер Microsoft.Jet.OLEDB.4.0 (он входит в состав практически всех ОС семейства Windows) Это особенно актуально, когда данный код выполняется на сервере 1С:Предприятия, например в качестве регламентного задания.
Минусы тоже имеются:
- Нельзя красиво оформить ячейки — шрифт, рамки и т.д.
Возможен «комбинированный» способ: с помощью ADO формируется файл, а затем с помощью OLE «наводится марафет». Читайте продолжение «Выгружаем из 1С в EXCEL с помощью ADO. Часть II»
Если публикация помогла Вам справится с проблемой, решить задачу или расширить свои знания - поддержите автора.
Комментарии:
Пишет ошибку: Ошибка при вызове метода контекста (Execute): Произошла исключительная ситуация (Microsoft JET Database Engine): Ошибка синтаксиса (пропущен оператор) в выражении запроса ‘с выключателем’. Command.Execute();
Опубликованный выше пример полностью рабочий, ищите ошибку у себя в коде. Позже выложу готовую обработку, доступную для скачивания.
при открытии в версии 8.2 (файл/открыть) пишет «Ошибка загрузки документа. Внешняя обработка не может быть прочитана текущей версией программы»…
Откройте сначала в Конфигураторе 8.2 обработку, переконвертируйте (совместимость с 8.1 пропадет!), и если без управляемых форм (в Толстом клиенте) — открывайте переконвертированную обработку уже в Предприятии 8.2.
Код рабочий. Одна проблема — если в наименовании присутствует апостроф — система падает. Как его можно сохранить? (Вариант — вырезать возможен, но некрасиво). Спасибо
Насколько я помню в SQL апостроф экранируется обратным слэшом, таким образом можно попробовать заменить все ‘ в наименовании на \’
Сам не пробовал, но на мой взгляд решение данной проблемы следует искать в этом направлении.
Добрый день, большое спасибо за статью, очень помогла.
Есть ньюанс — в файле Экселя, в начале каждого значения присутствует апостроф ‘ — можно ли от него избавиться ?
1) при повторном сохранении данного примера выводится ошибка.
2) или можно ли узнать более много команд как «Command.Execute()» .
или спрошу по другому, если надо будет перезаписать данный файл то как поступить.
В силу сиквельного синтаксиса заполнения эксельного листа возникают проблемы, если в названии номенклатуры были всевозможные кавычки.
Да, есть такая проблема.
Экранируйте подобные символы.
: Ошибка при установке значения атрибута контекста (ActiveConnection)
Catalog.ActiveConnection = СтрокаПодключения;
по причине:
Произошла исключительная ситуация (Microsoft JET Database Engine): Файл ‘D:\1С\Для аудиторов\’ не может быть открыт ядром базы данных Microsoft Jet. Файл уже открыт другим пользователем для монопольного доступа или требуется разрешение на просмотр данных.
вот такую штуку выдает, хоть при попытке создать новый, хоть при попытке записи в старый файл :-(
Возможно ли в данном случае добавить все необходимые записи одним запросом?
Т.е. в одном INSERT’е добавить одновременно несколько строк.
Спасибо.
Добрый день. Вопрос немного на другую тему. Загружаем из Excel в 1С. В экселевском файле в ячейке вместо значения указана ссылка на ячейку на другом листе книги. Как считать конечное значение?
Приведен пример реализации механизма прямого доступа к базе данных 1С на SQL-сервере с целью извлечения необходимых данных для получения аналитических отчетов.
Основными достоинствами данного метода являются:
1. Очень высокая скорость выборки данных.
2. Использование всех возможностей сводных таблиц.
Наиболее серьезным недостатком является сложность организации анализа данных в процессе выполнения запроса.
Пример реализован с помощью "MS Excel 2007"
Общий порядок действий:
1. Написать на понятном SQL-серверу языке запрос, выбирающий все необходимые данные из БД 1С.
Это конечно можно сделать и ручками, а можно использовать для построения запроса "Конструктор SQL-запросов 1C++" (Freeware 2005, 2006 by Ivan T Berezdetsky). Конструктор полностью копирует внешний вид и функциональность конструктора из восьмерки, поэтому трудностей с его использованием быть не должно. Полученный через конструктор текст запроса нельзя использовать в таком виде, т.к. он еще требует "перевода" имен таблиц, полей и т.д. Для этого можно воспользоваться "Просмотр регистров 1С++ без ВТ.ert" (к сожалению автора не помню), если установить галку "Фиксировать" и "Debug", а также вставить имеющийся текст запроса, то обработка выдаст нам уже готовый к использованию текст SQL-запроса. Для большей надежности рекомендую проверить его работоспособность прямо в консоли запросов SQL-сервера.
При формировании текста запроса необходим учесть то, что SQL сервер не умеет типизировать объекты 1С, а значит, например, нельзя просто выбрать измерения регистра продажи, обязательно необходимо делать связи между измерением "Номенклатура" и таблицей "номенклатура", а выбирать наименование из справочника. Если этого не учесть, то вместо наименований объектов в Excel будут непонятные надписи типа " 9N2Х", которые является идентификаторами объектов в БД.
Пример правильного текста запроса:
SELECT
Номенклатура.DESCR AS [1. Номенклатура],
Продажи.SP2375 AS [2. Количество],
Продажи.SP2350 AS [3 Продажная стоимость],
Продажи.SP2349 AS [4. Себестоимость]
FROM
dbo.RA2351 AS Продажи INNER JOIN
dbo.SC84 AS Номенклатура ON Продажи.SP2343 = Номенклатура.ID INNER JOIN
2. Необходимо создать файл подключения к БД.
Для этого можно в моих документах найти папку "Мои источники данных", в ней на "+Новое подключение к SQL-серверу.odc" щелкнуть правой, выбрать "Изменить в Excel". Далее необходимо ввести имя SQL-сервера, логин и пароль (рекомендуется создать служебного пользователя, имеющего права только на чтение в SQL-базе), далее выбрать необходимую БД и снять галку "Подключение к отдельной таблице", далее необходимо сохранить настройки подключения в файл. Затем при запросе таблицы, к которой мы хотим подключиться, щелкаем на любой. В появившемся окне меняем тип команды на SQL, а в поле "Текст команды" вставляем текст SQL-запроса, полученный ранее. Еще раз сохраняем файл подключения, и на этом Excel должен закрыться.
3. Непосредственный импорт данных.
Открываем Excel, нажимаем "Данные" - "Получить внешние данные" - "Существующие подключения", по-идее созданный нами файл подключения должен быть в списке, но если его нет - можно воспользоваться кнопкой "найти другие".
Дважды на нем щелкаем, появляется окно с запросом способа представления данных. Выбираем сводная таблица, нажимаем "ОК" и все! В зависимости от объема данных, рано или поздно сводная таблица будет сформирована и представлена пользователю.
Для примера, база 4 Гб, количество реализаций за день около 1000, среднее количество позиций в документе 10 шт. За 3 года запросом выбирается около 1 млн. записей. Общее время формирования сводной таблицы, учитывая не только время выборки но и обновление структуры сводной таблицы порядка 90 секунд.
В дальнейшем можно сохранить Excel'евский файл, создать в нем несколько различных сводных таблиц с одним источником данных. При обновлении данных обновляются сразу все сводные таблицы.
Наверное, уже многие знают, что с помощью технологии ADO можно быстро читать большие объемы информации из книг excel. Но ведь с помощью данной технологии можно также легко создавать и заполнять файлы excel.
Не верите? Смотрите .
Итак, постановка задачи: Создать с помощью механизма ADO файл excel и выгрузить в него из справочника "Номенклатура" код и наименование.
ИмяФайлаЭксель = "D:\Export.xls" ; // Имя создаваемого файла
// Собираем строку подключения
СтрокаПодключения = "
|Provider=Microsoft.Jet.OLEDB.4.0;
|Data Source color: red;">+ ИмяФайлаЭксель + ";
|Extended Properties=""Excel 8.0;HDR=No;"";" ;
// Создаем объект ADOX.Catalog
Catalog = Новый COMОбъект ( "ADOX.Catalog" );
Catalog . ActiveConnection = СтрокаПодключения ;
// Создаем таблицу и добавляем в неё два столбца
Table = Новый COMОбъект ( "ADOX.Table" );
Table . Name = "Table" ;
Table . Columns . Append ( "Code" );
Table . Columns . Append ( "Description" );
// Присоединяем таблицу
Catalog . Tables . Append ( Table );
Table = Неопределено;
Catalog = Неопределено;
// Создаем соединение
Connection = Новый COMОбъект ( "ADODB.Connection" );
Connection . Open ( СтрокаПодключения );
Command = Новый COMОбъект ( "ADODB.Command" );
Command . ActiveConnection = Connection ;
Command . CommandType = 1 ;
// Получаем номенклатуру для выгрузки
Запрос = Новый Запрос ( "
|ВЫБРАТЬ
| Номенклатура.Код,
| Номенклатура.Наименование
|ИЗ
| Справочник.Номенклатура КАК Номенклатура" );
Результат = Запрос . Выполнить ();
Выборка = Результат . Выбрать ();
// Заполняем таблицу данными выборки
Пока Выборка . Следующий () Цикл
Command . CommandText = "
|INSERT INTO [Table] VALUES ('" + Выборка . Код + "','" + Выборка . Наименование + "')" ;
Command . Execute ();
КонецЦикла;
// Закрываем соединение
Command = Неопределено;
Connection . Close ();
Connection = Неопределено;
Плюсы данного способа формирования файла excel:
- Большая скорость работы.
- Не требуется запуск самого процесса excel.exe, как это происходит в случае использования OLE-сервера excel.application, а следовательно на него не тратятся ресурсы. Кроме того нет необходимости отслеживать и перехватывать ошибки, связанные с процессом excel.exe, те кто использовал OLE-cоединение - тот поймет о чем я говорю, мало радости когда в случае программной или пользовательской ошибки процесс excel.exe остается висеть в памяти.
- Не требуется самого приложения EXCEL, достаточно что бы на компьютере был установлен OLEDB провайдер Microsoft.Jet.OLEDB.4.0 (он входит в состав практически всех ОС семейства Windows) Это особенно актуально, когда данный код выполняется на сервере 1С:Предприятия, например в качестве регламентного задания.
Минусы тоже имеются:
- Нельзя красиво оформить ячейки - шрифт, рамки и т.д.
P.S. Возможен "комбинированный" способ: с помощью ADO формируется файл, а затем с помощью OLE "наводится марафет".
Не секрет, что среди офисных работников, в частности тех, которые заняты в расчетной и финансовой сфере, особенно популярны программы Excel и 1С. Поэтому довольно часто приходится производить обмен данными между этими приложениями. Но, к сожалению, далеко не все пользователи знают, как быстро это сделать. Давайте выясним, как выгрузить данные из 1С в документ Excel.
Выгрузка информации из 1C в Excel
Если загрузка данных из Excel в 1С – это довольно сложная процедура, автоматизировать которую можно только с помощью сторонних решений, то обратный процесс, а именно выгрузка из 1С в Эксель – это сравнительно простой набор действий. Его можно легко выполнить с помощью встроенных инструментов вышеуказанных программ, причем сделать это можно несколькими способами, в зависимости от того, что именно пользователю нужно перенести. Рассмотрим, как это выполнить на конкретных примерах в 1С версии 8.3.
Способ 1: копирование содержимого ячейки
Одна единица данных содержится в ячейке 1С. Её можно перенести в Эксель обычным методом копирования.
-
Выделяем ячейку в 1С, содержимое которой нужно скопировать. Кликаем по ней правой кнопкой мыши. В контекстном меню выбираем пункт «Копировать». Можно воспользоваться также универсальным способом, который действует в большинстве программ, работающих на ОС Windows: просто выделяем содержимое ячейки и набираем комбинацию клавиш на клавиатуре Ctrl+C.
Вместо этого действия можно после выделения ячейки, находясь во вкладке «Главная», щелкнуть по значку «Вставить», который расположен на ленте в блоке «Буфер обмена».
Содержимое ячейки 1С будет вставлено в Excel.
Способ 2: вставка списка в существующую книгу Excel
Но вышеуказанный способ подойдет лишь в том случае, если нужно перенести данные из одной ячейки. Когда же нужно совершить перенос целого списка, то следует воспользоваться другим способом, ведь копирование по одному элементу отберет очень много времени.
-
Открываем любой список, журнал или справочник в 1С. Кликаем на кнопку «Все действия», которая должна располагаться вверху от обрабатываемого массива данных. Запускается меню. Выбираем в нем пункт «Вывести список».
Поле «Выводить в» имеет два значения:
По умолчанию установлен первый вариант. Для переноса данных в Эксель он как раз и подходит, так что тут мы ничего не меняем.
В блоке «Выводить колонки» можно указать, какие колонки из списка вы хотите перевести в Excel. Если вы собираетесь производить перенос всех данных, то эту настройку тоже не трогаем. Если же вы хотите произвести конвертацию без какого-то столбца или нескольких столбцов, то снимаем галочку с соответствующих элементов.
Список вставлен в документ.
Способ 3: создание новой книги Excel со списком
Также список из программы 1С можно сразу вывести в новый файл Эксель.
- Выполняем все те шаги, которые были указаны в предыдущем способе до формирования списка в 1С в табличном варианте включительно. После этого жмем на кнопку вызова меню, которая расположена в верхней части окна в виде треугольника, вписанного в оранжевый круг. В запустившемся меню последовательно переходим по пунктам «Файл» и «Сохранить как…».
Ещё проще сделать переход, нажав на кнопку «Сохранить», которая имеет вид дискеты и расположена в блоке инструментов 1С в самом верху окна. Но такая возможность доступна только пользователям, которые применяют программу версии 8.3. В ранних версиях можно использовать только предыдущий вариант.
Весь список будет сохранен отдельной книгой.
Способ 4: копирование диапазона из списка 1С в Excel
Бывают случаи, когда нужно перенести не весь список, а только отдельные строки или диапазон данных. Этот вариант тоже вполне воплотим с помощью встроенных инструментов.
-
Выделяем строки или диапазон данных в списке. Для этого зажимаем кнопку Shift и кликаем левой кнопкой мыши по тем строкам, которые нужно перенести. Жмем на кнопку «Все действия». В появившемся меню выбираем пункт «Вывести список…».
Способ 5: Сохранение документов в формате Excel
В Excel иногда нужно сохранять не только списки, но и созданные в 1С документы (счета, накладные платежные поручения и т.д.). Это связано с тем, что для многих пользователей редактировать документ проще в Экселе. К тому же в Excel можно удалить заполненные данные и, распечатав документ, использовать его при необходимости как бланк для ручного заполнения.
-
В 1С в форме создания любого документа имеется кнопка печати. На ней расположена пиктограмма в виде изображения принтера. После того, как в документ введены необходимые данные и он сохранен, жмем на этот значок.
Документ будет сохранен в формате Эксель. Этот файл теперь можно открывать в данной программе, и проводить дальнейшую его обработку уже в ней.
Как видим, выгрузка информации из 1С в формат Excel не составляет никаких сложностей. Нужно знать только алгоритм действий, так как, к сожалению, не для всех пользователей он интуитивно понятен. С помощью встроенных инструментов 1С и Эксель можно копировать содержимое ячеек, списков и диапазонов из первого приложения во второе, а также сохранять списки и документы в отдельные книги. Вариантов сохранения достаточно много и для того, чтобы пользователь мог найти подходящий именно для его ситуации, совсем не нужно прибегать к использованию стороннего программного обеспечения или применять сложные комбинации действий.
Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
Еще статьи по данной теме:
В 1С формируются квитанции ЖКХ на 200 квартир,
затем выгружается в Excel, приэтом бухгалтер утверждает, что она не может сформировать список жильцов по возрастанию номеров квартир. Например кв. № 1, 2, 3 и т.д.
Присылает бухгалтер: кв. № 46, кв. 001, кв. 010, кв. 100, кв 111 и.д.
Работать с таким порядком в ТСЖ для подготовки выписок квитанций за три года для суда, крайне сложно. Ответ бухгалтера- так работает программа. (т.е. программа создана не для людей).
В Excel — есть программа поиска по фамилии, по квартире, но она не работает с выгрузкой из 1С.
как выйти из этого дурацкого положения?
Красные флаги, броневик и вся власть советам
можно ли сделать выгрузу с 1с в EXcel книги учета материальных ценностей
Задайте вопрос или оставьте свое мнение Отменить комментарий
В холдинге имеются файловые базы 1С на каждую отдельную компанию. Сейчас сбор отчетности производится следующим образом:
1) специалист фин отдела заходит в каждую базу и собирает необходимую информацию о движении денежных средств, карточку 20,25,26 счетов
2) копирует информацию в Excel
3) обрабатывает вручную в едином шаблоне в Excel, чтобы видеть консолидированную информацию по группе компаний.
Как хочется делать: нажимать кнопку в Excel и получать данные из 1С автоматически.
Базы 1С не на SQL, а с обычным файловым доступом.
1С: Консолидацию не пробовали, но она требует очень долгой настройки, потому что виды бизнеса разные и разные учетные политики у организаций.
- Вопрос задан более трёх лет назад
- 9624 просмотра
По кнопке "Выгрузить в Excel" создаете ТабличныйДокумент и заполняете нужными данными. Потом вызываете у него метод Записать и указываете в параметрах путь для сохранения и формат в котором нужно сохранить.
За адекватную цену сделаю для вас эту задачу.
Поскольку баз много, лучше без кнопок.
Банальная обработка которая будет запускаться из батника поочередно в каждой базе выгружая информацию в нужные поля листа Excel.
АртемЪ: а как быть с тем, что базы могут быть закрыты? Точнее они итак закрыты. Хочется открыть файл Excel, нажать кнопку "Получить данные" и обработка сама вытащит из баз все данные. Как я понимаю, базы все равно нужно открывать.
Из батника вызываете 1cv8.exe указав в качестве параметров режим Enterprise и имя обработки.
Она откроется в фоновом режиме, выполнит обработку и закроется.
Константин Нагибович: доступ есть к ним, они на удаленном сервере лежат. Я имею в виду, что в них никто не работает в этом время, т.е. программа не запущена.
АртемЪ: получается в батнике нужно будет имя пользователя и пароль прописать? И схема дальше получается: батник запустил обработку, обработка собрала нужные данные в файл и куда то положила, сводный Excel файл забрал этот файл. Так?
Читайте также: