gifts2017

Выгрузка Таблицы значений из 1С 8 в Excel с помощью MS ADO

Опубликовал Андрей Сябренко (AzagTot) в раздел Обмен - Загрузка и выгрузка в Excel

Функции для выгрузки Таблицы значений из 1С 8 в таблицу Excel с использованием объектов Microsoft ActiveX Data Objects (ADO) и Microsoft ActiveX Data Objects Extensions (ADOX).

Основная функция: ВыгрузитьТЗвExcel_ADO.

Все значения из ТЗ выгружаются в виде строк. Если есть необходимость после выгрузки ТЗ работать с числами в Excel, то можно преобразовать ячейки, содержащие числа в виде строк, в числовые ячейки с помощью формулы =ЗНАЧЕН(ПОДСТАВИТЬ(Текст; "."; ",")) где Текст - это ссылка на ячейку, содержащую число в виде текста.


// Получает структуру для индикации прогресса цикла.
//
// Параметры:
//  КоличествоПроходов – Число - максимальное значение счетчика;
//  ПредставлениеПроцесса – Строка, "Выполнено" – отображаемое название процесса;
//  ВнутреннийСчетчик - Булево, *Истина - использовать внутренний счетчик с начальным значением 1,
//          иначе нужно будет передавать значение счетчика при каждом вызове обновления индикатора;
//  КоличествоОбновлений - Число, *100 - всего количество обновлений индикатора;
//  ЛиВыводитьВремя - Булево, *Истина - выводить приблизительное время до окончания процесса;
//  РазрешитьПрерывание - Булево, *Истина - разрешает пользователю прерывать процесс.
//  МинимальныйПериодОбновления - Число, *1 - с, обновлять не чаще чем этот период,
//                                        0 - по количеству обновлений,
//                                        эта реализация не поддерживает дробные значения;
//
// Возвращаемое значение:
//  Структура - которую потом нужно будет передавать в метод ЛксОбработатьИндикатор.
//
Функция ЛксПолучитьИндикаторПроцесса(Знач КоличествоПроходов = 0,
   
ПредставлениеПроцесса = "Выполнение", ВнутреннийСчетчик = Истина,
    Знач
КоличествоОбновлений = 100, ЛиВыводитьВремя = Истина, РазрешитьПрерывание = Истина,
   
МинимальныйПериодОбновления = 1) Экспорт

   
Индикатор = Новый Структура;

    Если
КоличествоПроходов = 0 Тогда
       
Состояние(ПредставлениеПроцесса + "...");
       
КоличествоПроходов = 1;
    КонецЕсли;

   
Индикатор.Вставить("КоличествоПроходов", КоличествоПроходов);
   
Индикатор.Вставить("ПредставлениеПроцесса", ПредставлениеПроцесса);
   
Индикатор.Вставить("ЛиВыводитьВремя", ЛиВыводитьВремя);
   
Индикатор.Вставить("РазрешитьПрерывание", РазрешитьПрерывание);
   
Индикатор.Вставить("ДатаНачалаПроцесса", ТекущаяДата());
   
Индикатор.Вставить("МинимальныйПериодОбновления", МинимальныйПериодОбновления);
   
Индикатор.Вставить("ДатаСледующегоОбновления", Дата('00010101'));
   
Индикатор.Вставить("ВнутреннийСчетчик", ВнутреннийСчетчик);
   
Индикатор.Вставить("Шаг", ?(КоличествоОбновлений > 0, КоличествоПроходов / КоличествоОбновлений, 0));
   
Индикатор.Вставить("СледующийСчетчик", 0);
   
Индикатор.Вставить("Счетчик", 1);

    Возврат
Индикатор;
КонецФункции
// ЛксПолучитьИндикаторПроцесса()


// Проверяет и обновляет индикатор. Нужно вызывать на каждом проходе индицируемого цикла.
//
// Параметры:
//  Индикатор    – Структура – индикатора, полученная методом ЛксПолучитьИндикаторПроцесса;
//  Счетчик      – Число – внешний счетчик цикла, используется при ВнутреннийСчетчик = Ложь.
//
Процедура ЛксОбработатьИндикатор(Индикатор, Счетчик = 0) Экспорт

    Если
Индикатор.ВнутреннийСчетчик Тогда
       
Счетчик = Индикатор.Счетчик;
    КонецЕсли;

    Если
Индикатор.РазрешитьПрерывание Тогда
       
ОбработкаПрерыванияПользователя();
    КонецЕсли;

   
ОбновитьИндикатор = Истина;

   
ТекущаяДата = ТекущаяДата();
    Если
Индикатор.МинимальныйПериодОбновления > 0 Тогда
        Если
ТекущаяДата >= Индикатор.ДатаСледующегоОбновления Тогда
           
Индикатор.ДатаСледующегоОбновления = ТекущаяДата + Индикатор.МинимальныйПериодОбновления;
        Иначе
           
ОбновитьИндикатор = Ложь;
        КонецЕсли;
    КонецЕсли;

    Если
ОбновитьИндикатор Тогда
        Если
Индикатор.Шаг > 0 Тогда
            Если
Счетчик >= Индикатор.СледующийСчетчик Тогда
               
Индикатор.СледующийСчетчик = Цел(Счетчик + Индикатор.Шаг);
            Иначе
               
ОбновитьИндикатор = Ложь;
            КонецЕсли;
        КонецЕсли;
    КонецЕсли;

    Если
ОбновитьИндикатор Тогда

       
Индикатор.СледующийСчетчик = Цел(Счетчик + Индикатор.Шаг);

        Если
Индикатор.ЛиВыводитьВремя Тогда
           
ПрошлоВремени = ТекущаяДата - Индикатор.ДатаНачалаПроцесса;
           
Осталось = ПрошлоВремени * (Индикатор.КоличествоПроходов / Счетчик - 1);
           
Часов = Цел(Осталось / 3600);
           
Осталось = Осталось - (Часов * 3600);
           
Минут = Цел(Осталось / 60);
           
Секунд = Цел(Цел(Осталось - (Минут * 60)));
           
ОсталосьВремени = Формат(Часов, "ЧЦ=2; ЧН=00; ЧВН=") + ":"
               
+ Формат(Минут, "ЧЦ=2; ЧН=00; ЧВН=") + ":"
               
+ Формат(Секунд, "ЧЦ=2; ЧН=00; ЧВН=");
           
ТекстОсталось = "Осталось: ~" + ОсталосьВремени;
        Иначе
           
ТекстОсталось = "";
        КонецЕсли;

       
ТекстСостояния = Индикатор.ПредставлениеПроцесса + " "
           
+ Формат(Счетчик / Индикатор.КоличествоПроходов * 100, "ЧЦ=3; ЧДЦ=0") + "%  " + ТекстОсталось;

        Если
ТипЗнч(Индикатор) = Тип("СтрокаТаблицыЗначений") Тогда
           
ТаблицаИндикаторов = Индикатор.Владелец();
           
ИндексИндикатора = ТаблицаИндикаторов.Индекс(Индикатор);
            Если
ИндексИндикатора > 0 Тогда
               
ТекстСостояния = ТаблицаИндикаторов[ИндексИндикатора - 1].ТекстСостояния + " >> " + ТекстСостояния;
            КонецЕсли;
           
Индикатор.ТекстСостояния = ТекстСостояния;
        КонецЕсли;

       
Состояние(ТекстСостояния);

    КонецЕсли;

    Если
Индикатор.ВнутреннийСчетчик Тогда
       
Индикатор.Счетчик = Счетчик + 1;
    КонецЕсли;

    Если
Счетчик = Индикатор.КоличествоПроходов Тогда
       
Состояние("");
    КонецЕсли;

КонецПроцедуры
// ЛксОбработатьИндикатор()


// Преобразование числа к виду, необходимому для последующей загрузки:
// - удаление символов разделителей разрядов;
// - использование "." в качестве символа-разделителя целой и дробной части;
// - представление нулевых чисел в виде "0", а не "".
Функция ЧислоВСтроку(ЗначениеЧисла) Экспорт
    Если
ТипЗнч(ЗначениеЧисла) = Тип("Число") Тогда
        Возврат
Формат(ЗначениеЧисла, "ЧРД=.; ЧГ=; ЧН=");
    Иначе
        Возврат
ЗначениеЧисла;
    КонецЕсли;
КонецФункции
// ЧислоВСтроку(ЗначениеЧисла)


// Создает объект ADODB.Connection
// Для работы с EXCEL с помощью MS ADODB.Connection.
// Поддерживаемый тип файлов Excel: *.xls
//
// Для файлов *.xls (Excel 1997-2003): Jet.OLEDB.4.0
// Стандартное подключение, как правило, не требующее установки дополнительного ПО.
// Рекомендуется установить последний Service Pack Windows.
//
// Функция создает и открывает объект ADODB.Connection с подключением к файлу "ФайлExcel"
// При успешном создании и подключении возвращает объект ADODB.Connection,
// в случае возникновения ошибки возвращает "Неопределено"
Функция СоздатьADODBConnection(ФайлExcel) Экспорт

   
типФайла = Прав(СокрП(ФайлExcel), 4);
    Если
типФайла = ".xls" Тогда
       
СonnectionString = "
        |Provider=Microsoft.Jet.OLEDB.4.0;
        |Data Source=" 
+ ФайлExcel + ";
        |Extended Properties=""Excel 8.0;HDR=No;"""
//IMEX=1;
   
Иначе
       
Сообщить("Не распознано расширение файла " + ФайлExcel);
        Возврат Неопределено;
    КонецЕсли;

   
// Инициализация основного объекта ADODB.Connection. Открытие соединения.
   
Попытка
       
ADODBConnection = Новый COMОбъект("ADODB.Connection");
       
ADODBConnection.ConnectionString = СonnectionString;
       
ADODBConnection.Open();
    Исключение
       
Сообщить(ОписаниеОшибки(), СтатусСообщения.Внимание);
        Возврат Неопределено;
    КонецПопытки;

    Возврат
ADODBConnection;

КонецФункции
// СоздатьADODBConnection(ФайлExcel)


// Получает Таблицу значений. Выгружает ее в файл Excel с помощью объектной модели ADO и ADOX
//
// Параметры:
//  ТЗ – ТаблицаЗначений, выгружаемая Таблица значений;
//  ФайлExcel – Строка, полное имя файла Excel, в который выгружается ТЗ;
//  ИмяЛиста  - имя листа Excel, в который выгружается ТЗ.
//
// Перед выгрузкой ТЗ Функция проверяет наличие файла с полным именем ФайлExcel.
// Если такого файла нет, то он создается и в нем создается новый лист с именем ИмяЛиста.
// Если ФайлExcel уже существует, то Функция создает в нем новый лист с именем ИмяЛиста.
// Если же в ФайлExcel существует лист с именем ИмяЛиста, то Функция сообщает об ошибке и возвращает Ложь.
//
// В первую строку листа книги записываются имена колонок ТЗ, далее выгружаются непосредственно строки ТЗ.
// Все значения из ТЗ выгружаются в виде строк.
// Числовые значения с помощью Функции ЧислоВСтроку(ЗначениеЧисла) преобразуются к виду, необходимому для последующей загрузки в 1С.
//
// Возвращаемое значение:
//  Булево - результат выполнения выгрузки, Истина - выгрузка прошла успешно, Ложь - выгрузка завершилась с ошибкой.
//
Функция ВыгрузитьТЗвExcel_ADO(ТЗ, ФайлExcel, ИмяЛиста) Экспорт

   
ADODBConnection = СоздатьADODBConnection(ФайлExcel);
    Если
ADODBConnection <> Неопределено Тогда

       
// Создаем объект ADOX.Catalog (это книга Эксель)
       
Попытка
           
Catalog = Новый COMОбъект("ADOX.Catalog");
           
Catalog.ActiveConnection = ADODBConnection;
        Исключение
           
Сообщить(ОписаниеОшибки(), СтатусСообщения.Внимание);
           
Catalog = Неопределено;
           
ADODBConnection.Close();
           
ADODBConnection = Неопределено;
            Возврат Ложь;
        КонецПопытки;

       
//{Параметры метода Append объекта Columns: Name, Type (не обязательный), DefinedSize (не обязательный)
        // Type - Целое число (long), тип данных параметра (строка, число, булево и т.д.).
        // - adDouble,   5
        // - adDate,     7
        // - adCurrency, 6
        // - adBoolean,  11
        // - adVarWChar, 202
        // - adLongVarWChar («memo»), введите 203
        //}

        // Создаем таблицу и добавляем в неё столбцы (это лист Эксель)
       
Попытка
           
Table = Новый COMОбъект("ADOX.Table");
           
Table.Name = ИмяЛиста;

           
// Создаем колонки листа
           
Для каждого тКол Из ТЗ.Колонки Цикл
               
Table.Columns.Append(тКол.Имя);
            КонецЦикла;

           
// Присоединяем лист к книге
           
Catalog.Tables.Append(Table);
        Исключение
           
Сообщить(ОписаниеОшибки(), СтатусСообщения.Внимание);
           
Table = Неопределено;
           
Catalog = Неопределено;
           
ADODBConnection.Close();
           
ADODBConnection = Неопределено;
            Возврат Ложь;
        КонецПопытки;

       
// Заполняем лист данными выборки
       
Command = Новый COMОбъект("ADODB.Command");
       
Command.ActiveConnection = ADODBConnection;
       
Command.CommandType = 1//adCmdText

       
Индикатор = ЛксПолучитьИндикаторПроцесса(ТЗ.Количество(), "Выгрузка таблицы " + ИмяЛиста + " ->", , , Ложь, , 0);
        Для каждого
СтрокаТЗ Из ТЗ Цикл

           
СтрЗнач = "";

            Для каждого
ячТЗ Из СтрокаТЗ Цикл
               
СтрЗнач = СтрЗнач + ",'" + ЧислоВСтроку(ячТЗ) + "'";
            КонецЦикла;
           
СтрЗнач = "(" + Сред(СтрЗнач, 2) + ")";

           
Command.CommandText = "INSERT INTO [" + ИмяЛиста + "] VALUES " + СтрЗнач;
            Попытка
               
Command.Execute();
            Исключение
               
Сообщить("Не удалось записать строку с данными " + СтрЗнач + "; Номер строки ТЗ: " + Индикатор.Счетчик);
            КонецПопытки;

           
ЛксОбработатьИндикатор(Индикатор);
        КонецЦикла;

       
Command = Неопределено;
       
Table   = Неопределено;
       
Catalog = Неопределено;
       
ADODBConnection.Close();
       
ADODBConnection = Неопределено;

    Иначе
        Возврат Ложь;
    КонецЕсли;

    Возврат Истина;

КонецФункции
// ВыгрузитьТЗвExcel_ADO(ТЗ, ФайлExcel, ИмяЛиста)

 

При написании данной статьи использовались материалы из следующих публикаций:

- Загрузка из Excel в 1С тремя методами. Часть 1. MS ADODB.Connection  автор  StepByStep

- Выгружаем в EXCEL с помощью ADO  автор  ll13

- Правильная индикация прогресса цикла  автор  tormozit

Большое спасибо выше перечисленным авторам за интересные публикации!

Также использовалась информация из MSDN.

Плюсы и минусы работы с Excel через ADO описаны в работах ll13 и StepByStep, не буду повторяться.

См. также

Подписаться Добавить вознаграждение
Комментарии
1. B2B (B2B) 11.06.13 13:28
Никогда не понимал, зачем вот так писать: Дата('00010101')?

Можно ведь проще: '00010101'!
2. Андрей Сябренко (AzagTot) 11.06.13 14:48
(1) Буду рад, если это окажется самым серьезным замечанием к данной публикации))
3. Алексей Роза (DoctorRoza) 11.06.13 15:18
Ну а что собственно Вы хотите услышать? StepByStep уже все разжевал аж в 3-х вариантах. Ничего нового, кроме как индикатор, не раскрыто, да и то на тонком клиенте этот индикатор не заработает. Файлы парсятся только *.xls, а что *.xlsx уже не в моде? До кучи, функция работает только на 32-разрядной ОС, для 64х потребуется патч и изменения строки подключения, вообщем у Step'а все по-интересней сделано.
//
Возьмите на вооружение, если хотите получить плюсов, раскройте детально класс ADOX. Колонки, заголовки, ячейки, обрамления, выравнивания, программирование формул, фон и т.п. MSDN иногда в лом читать, так что будет на достойном уровне.
Плюс авансом, для дальнейшего развития! Успехов! :)
4. Андрей Сябренко (AzagTot) 11.06.13 15:35
(3) У StepByStep в 3-х вариантах описана загрузка из Экселя в 1С, а у меня выгрузка в Эксель.
Пока не вижу смысла делать выгрузку в *.xlsx, т.к. *.xls замечательно везде открывается и, при желании, преобразуется в *.xlsx средствами Excel-2007 и выше.
Класс ADOX у меня в процессе детального изучения и освоения. Если будут по нему интересные наработки, то обязательно поделюсь с сообществом))

Индикатор не мой, взял у tormozit. Текст функций выложил, т.к. они немного изменены под себя.
5. Владимир Чаклин (vec435) 17.06.13 09:17
почеиу все данные в строке? можно ведь сразу установить формат колонки Excel по типу значения в ТЗ. Или делать это как доп опция
6. Роман Романов (romansun) 17.06.13 19:04
(0) ага, респект

вообще, _выгрузка_ больших файлов тема-то не совсем тривиальная, как может показаться.

Классическая выгрузка через Excel.Application дюже медленная. Есть вариант с выгрузкой через Построитель, метод отличный - и быстрый и простой, но не работающий в регламентном задании.

Рекомендую копнуть в сторону ухода от инсёртов в цикле в сторону одного портянистого запроса. Да, конкатенация строк в 1С "по-простому" - слабое место. Но есть, по крайне мере, два быстрых способа сложения строк:

- через методы xml (записьXML и чтение XML вроде... ссылку сходу не нашел)
- через ADO.Stream (тынц)


еще ссылка - http://aitika.ru/otvety/2352-1c-Effektivniy-sposob-konkatenatsii-strok-v-1S?p=1
7. Андрей Сябренко (AzagTot) 18.06.13 00:13
(5) vec435
Да, можно установить тип значения колонки отличный от Строки и в тексте функции есть описание параметров метода Append для этих целей:
Параметры метода Append объекта Columns: Name, Type (не обязательный), DefinedSize (не обязательный)
Type - Целое число (long), тип данных параметра (строка, число, булево и т.д.):
- adDouble, 5
- adDate, 7
- adCurrency, 6
- adBoolean, 11
- adVarWChar, 202
- adLongVarWChar («memo»), 203

Для моей задачи, в результате которой родилась данная функция, было достаточно типа Строка.
И для большинства подобных задач этого типа будет достаточно, т.к. 1С отлично справляется с автоматическим преобразованием типов.
Трудность при использовании данной функции может возникнуть при выгрузке очень длинных строк, например, строк неограниченной длины. Для таких колонок нужно использовать тип adLongVarWChar (значение параметра Type = 203).
8. Андрей Сябренко (AzagTot) 18.06.13 00:18
(6) Роман, спасибо!
По возможности подумаю над этим.
Пока все устраивает, скорость выгрузки замечательная - таблица 20х10000 выгружается секунды за 3-4.
9. Роман Романов (romansun) 18.06.13 11:25
(8)
50-60тыщ у меня минуты за полторы вроде выгружалось - коллега писал.. также, инсёрт в цикле

оптимизировать я сам не пробовал, но вот как раз посмотреть на результаты - был бы не против )))
10. Георгий Калиберда (Аон) 18.06.13 22:24
У меня при записи ТЗ выдает: Ошибка при вызове метода контекста (Open): Произошла исключительная ситуация (Microsoft JET Database Engine): Потеряна связь с Microsoft Excel для просмотра присоединенных листов
11. Андрей Сябренко (AzagTot) 18.06.13 23:17
12. Георгий Калиберда (Аон) 19.06.13 00:05
13. rasswet (rasswet) 26.03.15 11:00
c xlt будет работать? Microsoft Excel Template
14. Константин Юрин (kostyaomsk) 25.06.15 17:45
Статья очень интресная - использовал наработку с индикатором процесса, т.к. стояла задача выгрузки больших прайсов из 1С 8.
Теперь вопрос: Можно ли с помощью ADO вызвать метод AddComment() для работы с примечанием Excel.
Также можно ли с помощью технологии прямых запросов вставить картинку в прайс? Перерыл интернет и не нахожу решения, кроме как использовать методы COM-объекта "Excel.Application" или Макросов VBA. Отчасти понимаю что все завязано на типы данных запросов SQL (с меньшим функционалом), но все-таки есть ли такая возможность?

15. Ezh Ezhilo (Ezhilo) 14.09.16 11:21
Привет. При выгрузке таблиц в которых есть строки с символом апострофа ( ' ) - валится. Как решить?
16. Андрей Сябренко (AzagTot) 14.09.16 13:26
(15) Ezhilo, Перед выгрузкой таблицы заменить символ апострофа на другой символ (или последовательность символов). Затем, при загрузке провести обратную замену.