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

Основная функция: ВыгрузитьТЗв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, не буду повторяться.