Загрузка из EXCEL в 1С. MS ADODB.Connection

 В данной статье приведен функционал, с помощью которого в обработке
"Импорт из EXCEL и др.источников (xls,xlsx,ods,sxc,dbf,mxl,csv,sql) в 1С": //infostart.ru/public/120961
производится считывание данных из файлов табличного типа *xls, *.xlsx.

Методы загрузки из внешнего источника:
- Метод "MS ADO" (Чтение файлов xls, xlsx средствами Microsoft ADO): //infostart.ru/public/163640/
- Метод "MS EXCEL" (Чтение файлов xls, xlsx с картинками средствами Microsoft Office): //infostart.ru/public/163641/
- Метод "LO CALC" (Чтение файлов xls, xlsx, ods, sxc с картинками средствами LibreOffice): //infostart.ru/public/163642/
- Метод "NativeXLSX" (Чтение файлов xlsx с картинками средствами 1С. ПостроительDOM): //infostart.ru/public/300092/
- Метод "NativeXLSX". Предыдущий вариант (Чтение файлов xlsx средствами 1С. ЧтениеXML)://infostart.ru/public/225624/
- Метод "Excel1C" (Загрузка на платформе 8.3.6 с картинками. Чтение файлов xls, xlsx, ods): //infostart.ru/public/341855/
- Список листов файла: //infostart.ru/public/163724/

Публикаций на тему загрузки из EXCEL - множество, но
"
— Вам билетёр нужен?
— Был нужен, да уже взяли.
— Может и я на что сгожусь?
— Может и сгодишься, если скалиться не будешь ...
"
"НЕУЛОВИМЫЕ МСТИТЕЛИ" (1966).

// Метод "Microsoft ADODB"/
//
// Параметры:
//        ФайлEXCEL - Полное имя файла (путь к файлу с именем файла и расширением).
//        ИмяЛиста - Имя выбранного листа файла EXCEL.
//        СтрокаЗаголовка (по умолчанию = 1) - Номер строки EXCEL, в которой расположены заголовки колонок.
//            Не используется, т.к. в СтрокеСоединения указано HDR=YES, а не HDR=NO.
/ /       В обработке 1-я строка анализируется для сопоставления колонок EXCEL с реквизитами 1С (справочники, докуметны, регистры).
//        НачСтрока (по-умолчанию = 0) - Номер начальной строки, начиная с которой считываются данные из EXCEL.
//        КонСтрока (по-умолчанию = 0) - Номер конечной строки, которой заканчиваются считываемые данные из EXCEL.
//            Если НачСтрока=0 и КонСтрока=0, то считывается вся таблица, находящаяся на листе EXCEL.
//        КолвоСтрокExcel - Количество строк на листе "ИмяЛиста" EXCEL. Возвращается в вызываемую процедуру.
//        ПодключениеADODB - тип драйвера ADODB для подключения к EXCEL.
//
// Возвращаемые значения:
//         ТаблицаРезультат - Результат считывания с листа "ИмяЛиста" EXCEL.
//
Функция ЗагрузитьМетодом_MSADODB(Знач ФайлEXCEL, Знач ИмяЛиста, Знач СтрокаЗаголовка = 1, НачСтрока = 0, КонСтрока = 0, КолвоСтрокExcel, 
 Знач ПодключениеADODB = "MicrosoftJetOLEDB40") Экспорт

    Перем СonnectionString, ADODBConnection, ADODBRecordset, ТекстЗапроса;
    Перем КолвоКолонокExcel, Поле, Колонка, ИмяКолонки;
    Перем НоваяСтрока, НомерСтроки;
    Перем ТаблицаРезультат;
    
    // Нумерация MS ADODB начинается с 1.
    
    // Переменная "СтрокаЗаголовка", не используется, т.к. HDR=YES, а не HDR=NO.
    // HDR=YES:
    // 1. Считывание заголовков колонок с 1-ой строки.
    // 2. Считываемые данные со 2-ой и последующих строк типизированы. Для варианта HDR=NO: считываемые данные - строка.
    
    // Строка соединения - определение драйвера, который будет использован для подключения к файлу EXCEL.
    Если ПодключениеADODB = "MicrosoftACEOLEDB12" Тогда
        
        // ACE.OLEDB.12.0 - Для использования данного подключения необходимо дополнительное ПО:
        // Microsoft Access Database Engine 2010 Redistributable 32/64 bit.
        СonnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + СокрЛП(ФайлEXCEL) + ";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;""";
        
        // Еще один вариант.
        //СтрокаСоединения = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Dbq=" + СокрЛП(ФайлEXCEL) + ";";
        
    Иначе
        
        // Jet.OLEDB.4.0 - Стандартное подключение, как правило, не требующее установки дополнительного ПО. 
        // Рекомендуется установить последний Service Pack Windows.
        СonnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "  + СокрЛП(ФайлEXCEL) + ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""";
        
        // Еще один вариант.
        //СтрокаСоединения = "Driver={Microsoft Excel Driver (*.xls)};Dbq=" + СокрЛП(ФайлEXCEL) + ";";
        
    КонецЕсли;
    
    Попытка
        // Инициализация основного объекта ADODB.Connection. Открытие соединения.
        ADODBConnection = Новый COMОбъект("ADODB.Connection");
        ADODBConnection.ConnectionString =  СonnectionString;
        ADODBConnection.Open();
        // Импирически определенный параметр для правильного определения количества строк листа.
        ADODBConnection.CursorLocation = 3;    // По-умолчанию 2.
    Исключение
        Сообщить(НСтр("ru = '"+ОписаниеОшибки()+"'"), СтатусСообщения.Внимание);
        Возврат Новый ТаблицаЗначений;    // В случае ошибки возвращаем пустую таблицу значений.
    КонецПопытки;
    
    ТекстЗапроса = "SELECT * FROM [" + ИмяЛиста + "$]";
    
    // Создание Recordset. Дочерний объект ADODBConnection. Набор записей по запросу.
    Попытка
        ADODBRecordset = Новый COMОбъект("ADODB.Recordset");
        ADODBRecordset.Open(ТекстЗапроса, ADODBConnection);
        
        // Проверка заполненности листа.
        Если (ADODBRecordset.EOF ИЛИ ADODBRecordset.BOF) Тогда
            КолвоСтрокExcel = 0;
            Сообщить(НСтр("ru = '" + ИмяЛиста + ": не содержит данных.'"), СтатусСообщения.Внимание);
            
            // Завершение работы.
            // Закрытие Объектов.
            ADODBRecordset.Close();
            ADODBConnection.Close();
            ADODBRecordset   = Неопределено;
            ADODBConnection = Неопределено;
            
            Возврат Новый ТаблицаЗначений;    // В случае ошибки возвращаем пустую таблицу значений.
        КонецЕсли;
        
        // Импирически определенные параметры для правильного определения количества строк листа.
        ADODBRecordset.AbsolutePage     = 1;
        ADODBRecordset.AbsolutePosition = 1;
    Исключение
        Сообщить(НСтр("ru = '"+ОписаниеОшибки()+"'"), СтатусСообщения.Внимание);
        Возврат Новый ТаблицаЗначений;    // В случае ошибки возвращаем пустую таблицу значений.
    КонецПопытки;
    
    // Параметр, возвращаемый в вызывающую процедуру.
    КолвоСтрокExcel = ADODBRecordset.RecordCount + 1;    // (+1) - учет Строки-Заголовока, которая "съедается".
    КолвоКолонокExcel = ADODBRecordset.Fields.Count;
    
    // Проверка заполненности листа.
     Если КолвоСтрокExcel <= 2 Тогда
        КолвоСтрокExcel = 0;
        Сообщить(НСтр("ru = '" + ИмяЛиста + ": не содержит данных.'"), СтатусСообщения.Внимание);
        
        // Завершение работы.
        // Закрытие Объектов.
        ADODBRecordset.Close();
        ADODBConnection.Close();
        ADODBRecordset   = Неопределено;
        ADODBConnection = Неопределено;
        
        Возврат Новый ТаблицаЗначений;    // В случае ошибки возвращаем пустую таблицу значений.
    КонецЕсли;
    
    // Создание результирующей таблицы, в которую будут записываться считанные из EXCEL данные.
    ТаблицаРезультат = Новый ТаблицаЗначений;
    
    // Формирование колонок результирующей таблицы.
    
    // "НомерСтроки" - для наглядности и удобства.
    // В зависимости от разрабатываемой обработки.
    // "Сопоставлено" - может быть другим.
    // Здесь же могут быть добавлены другие колонки, не формируемые из содержимого файла EXCEL.
    ТаблицаРезультат.Колонки.Добавить("НомерСтроки", Новый ОписаниеТипов("Число"), "№", 4);
    ТаблицаРезультат.Колонки.Добавить("Сопоставлено", Новый ОписаниеТипов("Булево"), "Сопоставлено", 1);
    Для ит = 1 ПО КолвоКолонокExcel Цикл
        
        Поле = ADODBRecordset.Fields.Item(ит - 1);
        ИмяКолонки = "К_" + ит;
        Колонка = ТаблицаРезультат.Колонки.Добавить(ИмяКолонки, , СокрЛП(СтрЗаменить(Поле.Name, "#", ".")));
        // Замена "#" на ".", т.к. при считывании ADODB "." в имени колонки заменяется на "#".
        
    КонецЦикла;
    
    // ТаблицаРезультат: 1-я строка - Строка-Заголовок.
    
    // Добавление этой строки обусловлено исключительно из соображений идентичности содержимого файла EXCEL и ТаблицыЗначений,
    // выводимой на форме Обработки, и дальнейшей обработки строки заголовка
    // с целью сопоставления колонок EXCEL и реквизитов 1С: для Справочников, ПВХ, Регистров, Документов.
    
    // Если в Вашей обработке в результирующей таблице в качестве 1-ой строки не нужна Строка-Заголовок, то
    // следует закомментировать следующий цикл:
    НоваяСтрока = ТаблицаРезультат.Добавить();
    НоваяСтрока.НомерСтроки = 1;
    Для ит = 1 ПО КолвоКолонокExcel Цикл
        
        ИмяКолонки = "К_" + ит;
        Колонка = ТаблицаРезультат.Колонки.Найти(ИмяКолонки);
        НоваяСтрока[ИмяКолонки] = Колонка.Заголовок;
        
    КонецЦикла;
    
    // ТаблицаРезультат: Формирование строк по указанному диапазону: НачСтрока - КонСтрока.
    
    НомерСтроки = 1;
    Пока ADODBRecordset.EOF() = 0 Цикл
        
        НомерСтроки = НомерСтроки + 1;
        
        Если НомерСтроки < НачСтрока Тогда    // Номер строки вне диапазона считываемых строк.
            ADODBRecordset.MoveNext();             // Следующая строка.
            Продолжить;
        КонецЕсли;
        
        Если КонСтрока > 0 И НомерСтроки > КонСтрока Тогда    // Номер строки вне диапазона считываемых строк.
            Прервать;
        КонецЕсли;
        
        НоваяСтрока = ТаблицаРезультат.Добавить();
        НоваяСтрока.НомерСтроки = НомерСтроки;
        
        Для ит = 1 ПО КолвоКолонокExcel Цикл
            
            Поле = ADODBRecordset.Fields.Item(ит - 1);
                
            Если Поле.ActualSize = 0 Тогда        // Пустое поле EXCEL.
                Продолжить;
            КонецЕсли;
                
            ЗначениеЯчейки = Поле.Value;        // Учитывая параметр HDR=YES в строке соединения, данные считываются в соответствии с их типом.
            
            ИмяКолонки = "К_" + ит;
            НоваяСтрока[ИмяКолонки] = ЗначениеЯчейки;
            
            // Используется при формировании таблицы на форме обработки.
            ШиринаКолонки = ТаблицаРезультат.Колонки[ИмяКолонки].Ширина;
            ДлинаСтроки      = СтрДлина(СокрЛП(ЗначениеЯчейки));
            ТаблицаРезультат.Колонки[ИмяКолонки].Ширина = ?(ШиринаКолонки < ДлинаСтроки, ДлинаСтроки, ШиринаКолонки);
                
        КонецЦикла;
        
        ADODBRecordset.MoveNext();   // Следующая строка.
        
    КонецЦикла;
    
    //УдалитьКолонкиСНулевойШириной(ТаблицаРезультат);
    
    // Завершение работы.
    // Закрытие Объектов.
    ADODBRecordset.Close();
    ADODBConnection.Close();
    ADODBRecordset   = Неопределено;
    ADODBConnection = Неопределено;
    
    Возврат ТаблицаРезультат;
    
КонецФункции

Преимущества:

1.«В общем случае» метод «Microsoft ADODB» работает и в файловом и в клиент-серверном варианте.
2. Самый быстрый из трех рассматриваемых.

Особенности и Ограничения:

1.Для функционирования метода «Microsoft ADODB» необходимо:

 Драйвер подключения Provider=Microsoft.Jet.OLEDB.4.0:

- Установленный Microsoft MDAC, как правило специальная установка не требуется, достаточно последнего Service Pack-а операционной системы..
  Microsoft MDAC 2.8 SP1 10.05.2005: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=5793

 Драйвер подключения Provider=Microsoft.ACE.OLEDB.12.0:
- Установленный Microsoft Access Database Engine 2010 Redistributable (16/12/2010) 32 и 64 - разрядные версии:
  Microsoft ADE 2010 16/12/2010:  http://www.microsoft.com/en-us/download/details.aspx?id=13255

2. 1-я строка файла EXCEL - строка, содержащая заголовки колонок. Можно изменить, изменив HDR=YES на HDR=NO + некоторые изменения в функции.

С уважением к сообществу МА!