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

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

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

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

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