gifts2017

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

Опубликовал Makc *** (makc2k) в раздел Обработки - Универсальные обработки

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

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

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

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

 

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

записывает новый файл эксель версии не ниже 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 я такого не нашел, может кто подскажет?

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

 

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

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

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

См. также

Подписаться Добавить вознаграждение

Комментарии

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