Универсальная процедура выгрузки в эксель с помощью ADO

13.03.13

Интеграция - Загрузка и выгрузка в Excel

Универсальная процедура выгрузки данных в excel с помощью библиотеки ADO. Правда универсальность ограничена строкой подключения, связанной с версией установленного microsoft office. Однако на это можно закрыть глаза. Другое ограничение это отсутствие поддержки составного типа данных в колонке.

Скачать файлы

Наименование Файл Версия Размер
Обработка пример выгрузки упр. и обычная формы
.epf 16,51Kb
42
.epf 16,51Kb 42 Скачать
Схема работы процедуры
.grs 11,38Kb
9
.grs 11,38Kb 9 Скачать

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

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

 Схема работы процедуры

 

Копентарии редактор статьи режет, приведу отдельно:

записывает новый файл эксель версии не ниже 2007 на основании получаемых параметров  

Параметры:

  ИмяФайла  - Строка - Полный путь к вновь создаваемому файлу   

  ПерезаписыватьФайл  - Булево - отвечает за работу проверки существования  файла на диске прежде его создания  

  Данные  - Массив структур или табл. значений - содержит данные для выгрузки в эксель  Массив структур используется для работы на упр. приложении (первый элемент массива заголовки полей)  вместе с тем можно передать ТЗ при рабоче в обычном клиенте

 ИмяТаблицы  - Строка - Имя таблицы (листа) эксель

    

Далее сам код: 

 

&НаКлиенте
Процедура ЗаписатьВЭксельФайл(  Знач ИмяФайла,
                                                                Знач ПерезаписыватьФайл = Истина,
                                                                Знач Данные,
                                                                Знач ИмяТаблицы)
    //Проверим валидность имени файла
        Если ПустаяСтрока(ИмяФайла) Тогда 
                Возврат;
        Иначе
                //анализ нахождения файла на диске
                Если ПерезаписыватьФайл Тогда
                        Файл = Новый Файл(ИмяФайла);
                        Если Файл.Существует() Тогда
                                Файл = Неопределено;
                                УдалитьФайлы(ИмяФайла);
                        КонецЕсли;
                КонецЕсли;              
        КонецЕсли;
        
        //Проверка типа переданных данных
        //Подразумевается, что не массив означает ТЗ
        Если ТипЗнч(Данные) <> Тип("Массив") Тогда
                //строка подключения для эксель не ниже 2007 версии
                СтрокаПодключения = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""" + ИмяФайла + """;Extended Properties=""Excel 12.0 Xml;HDR=YES"""; 
                
                //объект отвечающий за связь с БД
                Коннектор = Новый COMОбъект("ADODB.Connection");
        Коннектор.ConnectionString = СтрокаПодключения;
                Коннектор.Open();
 
                //подключаемся
                Ком = Новый COMОбъект("ADODB.Command");
        Ком.ActiveConnection = Коннектор;
                //константа 1 означает "запрос", бывают еще представления и хранимые процедуры
        Ком.CommandType = 1;
                
                //анализируем тип данных в первой строке ТЗ
                СтрокаТЗ = Данные.Получить(0);
                СписокПолей = "";
                Для Каждого КолонкаТЗ Из Данные.Колонки Цикл
                        ТекЗначение = СтрокаТЗ[КолонкаТЗ.Имя];
                        //анализ типа данных
                        Если ТипЗнч(ТекЗначение) = Тип("Дата") Тогда
                                СписокПолей = СписокПолей + КолонкаТЗ.Имя + " date,";
                        ИначеЕсли ТипЗнч(ТекЗначение) = Тип("Число") Тогда
                                СписокПолей = СписокПолей + КолонкаТЗ.Имя + " float,";
                        ИначеЕсли ТипЗнч(ТекЗначение) = Тип("Булево") Тогда
                                СписокПолей = СписокПолей + КолонкаТЗ.Имя + " LOGICAL,";
                        Иначе
                                СписокПолей = СписокПолей + КолонкаТЗ.Имя + " char(255),";
                        КонецЕсли;              
                КонецЦикла;
                //обрезаем последнюю запятую
                СписокПолей = Лев(СписокПолей, СтрДлина(СписокПолей) - 1);
                Ком.CommandText = "CREATE TABLE " + ИмяТаблицы + " (" + СписокПолей + ")";
                Попытка
                        Ком.Execute();
                Исключение
                        Сообщить("При создании таблицы произошла ошибка!" + Символы.ПС 
                        + "Текст запроса: " + Ком.CommandText + Символы.ПС
                        + ОписаниеОшибки());
                        Возврат;
                КонецПопытки;
                
                //выгрузка данных
                //обходим строки данных
                Для Каждого СтрокаТЗ Из Данные Цикл
                        //обходим колонки данных
                        СписокПолей = "";
                        //цикл для формирования списка полей
                        Для Каждого КолонкаТЗ Из Данные.Колонки Цикл
                                ТекЗначение = СтрокаТЗ[КолонкаТЗ.Имя];
                                //анализ типа данных
                                Если ТипЗнч(ТекЗначение) = Тип("Число") Тогда
                                        СписокПолей = СписокПолей + Формат(ТекЗначение, "ЧРД=.; ЧН=0; ЧГ=") + ",";
                                ИначеЕсли ТипЗнч(ТекЗначение) = Тип("Дата") Тогда
                                        СписокПолей = СписокПолей + "'" + Формат(ТекЗначение, "ДФ=dd.MM.yyyy") + "',";
                                ИначеЕсли ТипЗнч(ТекЗначение) = Тип("Булево") Тогда
                                        СписокПолей = СписокПолей + "'" + Формат(ТекЗначение, "БЛ=0; БИ=1") + "',";
                                Иначе
                                        СписокПолей = СписокПолей + "'" + СокрЛП(ТекЗначение) + "',";
                                КонецЕсли;
                        КонецЦикла;
                        //обрезаем последнюю запятую
                        СписокПолей = Лев(СписокПолей, СтрДлина(СписокПолей) - 1);
                        //окончательное формирование теста запроса
                        ТекстЗапроса = "INSERT INTO " + ИмяТаблицы + " VALUES (" + СписокПолей + ")";
                        Ком.CommandText = ТекстЗапроса;
                        //запись данных в БД (выполнение запроса)
                        Попытка
                                Ком.Execute();
                        Исключение
                                Сообщить("При записи данных произошла ошибка!" + Символы.ПС 
                                + "Текст запроса: " + ТекстЗапроса + Символы.ПС
                                + ОписаниеОшибки());
                        КонецПопытки;
                КонецЦикла;
                Ком.ActiveConnection.Close();
        //************************************************      
        //Обработка массива структур
        //************************************************
        ИначеЕсли ТипЗнч(Данные) = Тип("Массив") Тогда
                //строка подключения для эксель не ниже 2007 версии
                СтрокаПодключения = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ИмяФайла + ";Extended Properties=""Excel 12.0 Xml;HDR=YES"""; 
                
                //объект отвечающий за связь с БД
                Коннектор = Новый COMОбъект("ADODB.Connection");
        Коннектор.ConnectionString = СтрокаПодключения;
                Коннектор.Open();
 
                //подключаемся
                Ком = Новый COMОбъект("ADODB.Command");
        Ком.ActiveConnection = Коннектор;
                //константа 1 означает "запрос", бывают еще представления и хранимые процедуры
        Ком.CommandType = 1;
                
                //анализируем тип данных в первом элементе массива
                ПервыйЭлемент = Данные.Получить(0);
                СписокПолей = "";
                Для Каждого ЭлементСтруктуры Из ПервыйЭлемент Цикл
                        ТекЗначение = ЭлементСтруктуры.Значение;
                        //анализ типа данных
                        Если ТипЗнч(ТекЗначение) = Тип("Дата") Тогда
                                СписокПолей = СписокПолей + ЭлементСтруктуры.Ключ + " date,";
                        ИначеЕсли ТипЗнч(ТекЗначение) = Тип("Число") Тогда
                                СписокПолей = СписокПолей + ЭлементСтруктуры.Ключ + " float,";
                        ИначеЕсли ТипЗнч(ТекЗначение) = Тип("Булево") Тогда
                                СписокПолей = СписокПолей + ЭлементСтруктуры.Ключ + " LOGICAL,";
                        Иначе
                                СписокПолей = СписокПолей + ЭлементСтруктуры.Ключ + " char(255),";
                        КонецЕсли;              
                КонецЦикла;
                //обрезаем последнюю запятую
                СписокПолей = Лев(СписокПолей, СтрДлина(СписокПолей) - 1);
                Ком.CommandText = "CREATE TABLE " + ИмяТаблицы + " (" + СписокПолей + ")";
                Попытка
                        Ком.Execute();
                Исключение
                        Сообщить("При создании таблицы произошла ошибка!" + Символы.ПС 
                        + "Текст запроса: " + Ком.CommandText + Символы.ПС
                        + ОписаниеОшибки());
                        Возврат;
                КонецПопытки;
                
                //выгрузка данных
                //обходим строки данных
                Для Каждого ЭлементМассива Из Данные Цикл
                        //обходим колонки данных
                        СписокПолей = "";
                        //цикл для формирования списка полей
                        Для Каждого ЭлементСтруктуры Из ЭлементМассива Цикл
                                ТекЗначение = ЭлементСтруктуры.Значение;
                                //анализ типа данных
                                Если ТипЗнч(ТекЗначение) = Тип("Число") Тогда
                                        СписокПолей = СписокПолей + Формат(ТекЗначение, "ЧРД=.; ЧН=0; ЧГ=") + ",";
                                ИначеЕсли ТипЗнч(ТекЗначение) = Тип("Дата") Тогда
                                        СписокПолей = СписокПолей + "'" + Формат(ТекЗначение, "ДФ=dd.MM.yyyy") + "',";
                                ИначеЕсли ТипЗнч(ТекЗначение) = Тип("Булево") Тогда
                                        СписокПолей = СписокПолей + "'" + Формат(ТекЗначение, "БЛ=0; БИ=1") + "',";
                                Иначе
                                        СписокПолей = СписокПолей + "'" + СокрЛП(ТекЗначение) + "',";
                                КонецЕсли;
                        КонецЦикла;
                        //обрезаем последнюю запятую
                        СписокПолей = Лев(СписокПолей, СтрДлина(СписокПолей) - 1);
                        //окончательное формирование теста запроса
                        ТекстЗапроса = "INSERT INTO " + ИмяТаблицы + " VALUES (" + СписокПолей + ")";
                        Ком.CommandText = ТекстЗапроса;
                        //запись данных в БД (выполнение запроса)
                        Попытка
                                Ком.Execute();
                        Исключение
                                Сообщить("При записи данных произошла ошибка!" + Символы.ПС 
                                + "Текст запроса: " + ТекстЗапроса + Символы.ПС
                                + ОписаниеОшибки());
                        КонецПопытки;
                КонецЦикла;
                Ком.ActiveConnection.Close();
        КонецЕсли;
КонецПроцедуры // ЗаписатьВЭксельФайл()

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

Если версия вашего "офиса" не 12 то можно обратится на http://www.connectionstrings.com/

Оптимизировать данную процедуру можно. Например в perl при работе с БД можно поместить команды (более одной) insert в определенный блок, а потом разом выполнить запрос в ADO я такого не нашел, может кто подскажет?

Обычная форма

 

Управляемая форма

См. также

SALE! 10%

Загрузка номенклатуры из Excel в УТ11, КА 2, ERP 2, Розница 2. Дополнительные реквизиты и сведения, характеристики, картинки, цены, остатки

Загрузка и выгрузка в Excel Розничная торговля Логистика, склад и ТМЦ Ценообразование, анализ цен Прайсы Платформа 1С v8.3 1С:Комплексная автоматизация 1.х 1С:Розница 2 1С:ERP Управление предприятием 2 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х Управленческий учет Платные (руб)

Загрузка из файлов xls, xlsx, ods, csv, mxl в УТ11, КА 2, ERP 2, Розница 2. Задействованы все возможности конфигурации - заполнение реквизитов номенклатуры, дополнительных реквизитов и сведений, характеристики, доп.реквизиты и сведения характеристик. Дополнительные обработки для расширения возможностей.

10560 9504 руб.

29.10.2014    210163    620    524    

439

Загрузка номенклатуры c картинками (несколько потоков одновременно) и сопутствующими данными в базу и любые документы из yml, xls, xlsx, xlsm, ods, ots, csv для УТ 10.3, УТ 11 (все), БП 3, КА 2, ERP 2, УНФ 1.6/3.0, Розница 2

Загрузка и выгрузка в Excel Логистика, склад и ТМЦ Ценообразование, анализ цен Файловый обмен (TXT, XML, DBF), FTP Платформа 1С v8.3 1С:Бухгалтерия 2.0 1С:Управление торговлей 10 1С:Розница 2 1С:Управление нашей фирмой 1.6 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х 1С:Управление нашей фирмой 3.0 Платные (руб)

Эволюция не стоит на месте - новая удобная версия функциональной обработки для Вашего бизнеса! Что же Вы получаете? Удобный и интуитивно понятный интерфейс с 3-мя этапами работы. 2 режима - автоматический и ручной. Чтение XLSX, XLSM, CSV, XML/YML форматов без офиса, на любом сервере! Визуальное связывание колонок файла и реквизитов простым перетаскиванием колонок. Создание или обновление номенклатуры с иерархией, характеристик, доп. реквизитов, упаковок, загрузка практически неограниченного количества картинок на одну номенклатуру (с возможностью загрузки в несколько потоков одновременно), с хранением в томах или в базе. Загрузка номенклатуры поставщиков или поиск по их данным номенклатуры. Загрузка доп. реквизитов в характеристики. Загрузка штрихкодов с генерацией новых. Создание элементов справочников и ПВХ "на лету" для выбранных реквизитов. (Обновление от 11.12.2023, версия 9.5 - 9.9)

13200 руб.

20.11.2015    150714    367    375    

501

Маркетплейсный загрузчик для 12-ти маркетплейсов в "БП 3", "УТ 11", "КА 2", ERP, УНФ

Загрузка и выгрузка в Excel Маркетплейсы Платформа 1С v8.3 Бухгалтерский учет 1С:Розница 2 1С:Управление нашей фирмой 1.6 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х Россия Бухгалтерский учет Управленческий учет Платные (руб)

Реальный помощник, с помощью которого Вы сделаете необходимые документы для Wildberries, OZON, ЯндексМаркет, Мегамаркет, Aliexpress, "Детский мир", Казань-Экспресс, "Леруа Мерлен", ЭНФАНТА (Акушерство), ЛаМода, Летуаль, "Твой дом" в документы "Отчет комиссионера (агента) о продажах" и другие, может работать в "Бухгалтерия 3", "Бухгалтерия 3 КОРП", УТ 11, УНФ, КА 2, ERP. Возможность подключить любые маркетплейсы. Анализ продаж ОЗОН. 30 дней БЕСПЛАТНОГО пользования!

1800 руб.

12.08.2021    31893    226    63    

117

SALE! 20%

Загрузка документов и номенклатуры из Excel в 1С "одним нажатием": УПД, ТОРГ-12, отчеты маркетплейсов, заказы, счета, прайсы

Загрузка и выгрузка в Excel Оптовая торговля Платформа 1С v8.3 Управляемые формы Платформа 1C v8.2 Конфигурации 1cv8 1С:Комплексная автоматизация 1.х 1С:Бухгалтерия 2.0 1С:Управление торговлей 10 1С:Розница 2 1С:Управление производственным предприятием 1С:Управление нашей фирмой 1.6 1С:ERP Управление предприятием 2 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х 1С:Управление нашей фирмой 3.0 Бухгалтерский учет Управленческий учет Платные (руб)

Универсальная обработка для загрузки документов из Excel в 1С. Не требует указания параметров (номера колонок, номер первой строки таблицы и т.д.) и предварительной настройки. Просто выбираете файл Excel, документ 1С и нажимаете кнопку "Загрузить". Обработка сама находит таблицу в файле Excel, необходимые для загрузки данные в ней (номенклатура, количество, НДС, цена, сумма) и загружает ее в 1С. Вместе с номенклатурой может найти контрагента, номер и дату документа, штрих-коды, серии ГТД, страну и т.д. Распознает документы ЛЮБОЙ ФОРМЫ (УПД, ТОРГ-12, заказ, отчет комиссионера и т.д.). Не требует MS Office. Для поиска таблиц используются методы эвристического поиска. Загружает только то, что нужно, т.е. пропускает повторы шапки таблицы, заголовки, промежуточные итоги, подписи и т.д. Содержит модуль работы с электронной почтой и api-загрузчик отчетов о продажах маркетплейсов.

5000 4000 руб.

09.11.2016    214923    925    886    

939

Загрузка данных отчета о реализации товаров из Excel файла СберМегаМаркет

Загрузка и выгрузка в Excel Маркетплейсы Платформа 1С v8.3 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Управление нашей фирмой 3.0 Бухгалтерский учет Управленческий учет Платные (руб)

Загрузка данных отчета о реализации товаров из сервиса СберМегаМаркет для конфигурации: Бухгалтерия предприятия, редакция 3.0; Управление торговлей, редакция 11 и Управление нашей фирмой, редакция 3.0 в документ "Отчет комиссионера (агента) о продажах".

4800 руб.

07.06.2022    15077    79    0    

60

SALE! 30%

Распознавание и загрузка сканов в 1С "одним нажатием": УПД, ТОРГ-12, накладные, счета, номенклатура, заказы и т.д.

Загрузка и выгрузка в Excel Документооборот и делопроизводство (СЭД) Учет документов Распознавание документов и образов Управляемые формы 1С:Комплексная автоматизация 1.х 1С:Бухгалтерия 2.0 1С:Управление торговлей 10 1С:Розница 2 1С:Управление производственным предприятием 1С:Управление нашей фирмой 1.6 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Управление холдингом 1С:Комплексная автоматизация 2.х 1С:Управление нашей фирмой 3.0 Бухгалтерский учет Управленческий учет Платные (руб)

Универсальная программа для распознавания ЛЮБЫХ СКАНОВ ИЛИ ФОТО ТОВАРНЫХ ДОКУМЕНТОВ в 1С. Не требует указания параметров и предварительной настройки. Просто выбираете файл (PDF, JPG, DOC, XLS, HTML и т.д.) выбираете документ 1С и нажимаете кнопку "Распознать и загрузить".

6000 5520 руб.

04.06.2019    101373    296    173    

312
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. rasswet 82 13.02.13 17:08 Сейчас в теме
видел где-то код, можно определить версию Эксель. тогда будет универсально работать под любым. может доделаете?
2. makc2k 107 13.02.13 22:06 Сейчас в теме
Вполне возможно, нужно найти время на это. Сейчас в планах сделать статью по выгрузке в access и HTML.
3. Alchimic 19.02.13 19:35 Сейчас в теме
4. platon_ 10 11.03.13 17:45 Сейчас в теме
А с апострафами как то боролись? которые получаются при выгрузке текста?
5. makc2k 107 11.03.13 19:34 Сейчас в теме
Их можно заменять отдельной функцией, как например отсекает проводник недопустимые символы в строке пути. Другой вариант покопать документацию на предмет экранирующих символов в тексте запроса, что наверное более правильно.
6. yuraos 991 13.03.13 19:58 Сейчас в теме
Кому интересно,
тоже самое можно сделать из консоли запросов
правда не в простой, а в вот такой
---
правда пока только в только в толстом клиенте (обычное приложение).
:)
7. makc2k 107 13.03.13 20:10 Сейчас в теме
Я поправил обход апострофоф. Оказывается в SQL есть замечательная функция CHR(кодсимвола). Статью менять не стал ибо интерфейс редактирования статьи страшный, а вот обработочку новенькую закинул.
8. lsp71 19.04.13 17:16 Сейчас в теме
Задачу выгрузить ТаблицуЗначений в Excel-файл решаю в 2 простых шага:
1. Выгрузка ТаблицыЗначений в ТабличныйДокумент (один раз написал функцию, все время пользуюсь).
2. ТаблДок.Записать(КаталогВыгрузки + ИмяФайлаБезРасширения + ".xls", ТипФайлаТабличногоДокумента.XLS);
Все просто, как молоток. Не требуется даже наличия самого Excel на компьютере. Работает быстро.
AltF1; yuraskas; +2 Ответить
Оставьте свое сообщение