Статистика

Участников проекта 105
Опубликовано статей 78
Отчет по карме. Топ 20

Новости блога

1 29.11.2013  Сегодня самым активным участникам newblog'а был выплачен доход с sape.
7 02.11.2012  Ура! Свешилось, нашему сайту дали тИЦ 10. Спасибо всем кто принимает участие в развитии нашего блога.
8 21.08.2012  Интеграция с sape.ru. Теперь каждый автор статей на newblog автоматически зарабатывает на рекламе.
Все новости

Топ 5 категорий

Программирование 46
Операционные системы 9
Базы данных 4
Туризм 2
Заметки 2

Последние 5 заметок (90)

gullyar - Закладки gullyar
gullyar - Ваша первая закладка
osadchaya - Закладки osadchaya
Ira0231188 - Закладки Ira0231188
Ira0231188 - Закладки Ira0231188

Ссылки

www.freedev.asia

Загрузка данных в 1С 8 из файлов формата Excel без установленного офиса MS Office и любого другого (Open Office, Libre Office)

14.03.2014 16:24 | Просмотров: 10922 | Доход: 267.75 руб. | Комментариев: 2
[Программирование] 
Рейтинг: 5/2

Загрузка из Excel в 1С 8 c помощью стандартного провайдера Windows  - Microsoft Jet OLEDB

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

 

// Функция возвращает список значений, содержащий имена листов книги EXCEL или Пустой СписокЗначений.
//
// Параметры:
//     ФайлEXCEL - Полное имя файла (путь к файлу с именем файла и расширением).
//     ПодключениеADODB - Тип драйвера ADODB для подключения к EXCEL.
//
// Возвращаемые значения:
//     СписокЛистов - Список имен листов в файле, например: Лист1, Лист2, Лист3.
//
Функция ФайлExcelПолучитьСписокЛистов(Знач ФайлEXCEL, Знач ПодключениеADODB = "MicrosoftJetOLEDB40")
    Перем СписокЛистов, ВсегоЛистов, ИмяЛиста, МассивЛистов;
    Перем ServiceManager, Desktop, Properties1, Properties2, Arguments, Book, Sheets;
    Перем СonnectionString, ADODBConnection, ADOXCatalog;
  
    СписокЛистов = Новый СписокЗначений;
    
    Файл = Новый Файл(ФайлEXCEL);
    
	// Для файлов типа .XLS .XLSX используем MS ADODB.
    Если ВРег(Файл.Расширение) = ".XLS" ИЛИ ВРег(Файл.Расширение) = ".XLSX" Тогда        
        
        // Строка соединения - определение драйвера, который будет использован для подключения к файлу 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) + ";";
            
        КонецЕсли;
        
        Попытка
            ADODBConnection = Новый COMОбъект("ADODB.Connection");       // Инициализация ADODB.
            ADODBConnection.Open(СonnectionString);                                      // Открытие соединения.
            
            // 1-ый способ Формирования списка листов файла EXCEL.
            ADOXCatalog = Новый COMОбъект ("ADOX.Catalog");
            ADOXCatalog.ActiveConnection = ADODBConnection;
            МассивЛистов = ADOXCatalog.Tables;
            
            Для Каждого ЛистМассива ИЗ МассивЛистов Цикл
                ИмяЛиста = ЛистМассива.Name;
                Если ИмяЛиста = "Excel_BuiltIn_Database" Тогда                    // Исключаем лист "по-умолчанию".
                    Продолжить;
                КонецЕсли;
                 // Добавляем в список имя листа без знака $. Исключительно для удобвства восписятия при выборе листа из списка.
                СписокЛистов.Вставить(0, Лев(ИмяЛиста, СтрДлина(ИмяЛиста)-1));
            КонецЦикла;
            //// 2-ой способ Формирования списка листов файла EXCEL.
            //ADODBRecordset = Новый COMОбъект("ADODB.Recordset");
            //ADODBRecordset = ADODBConnection.OpenSchema(20);
            //Пока НЕ ADODBRecordset.EOF Цикл
            //    ИмяЛиста = ADODBRecordset.Fields("TABLE_NAME").Value;
            //    Если ИмяЛиста = "Excel_BuiltIn_Database" Тогда                    // Исключаем лист "по-умолчанию".
            //        ADODBRecordset.MoveNext();
            //        Продолжить;
            //    КонецЕсли;
             //// Добавляем в список имя листа без знака $. Исключительно для удобвства восписятия при выборе листа из списка.
            //    СписокЛистов.Добавить(Лев(ИмяЛиста, СтрДлина(ИмяЛиста)-1));
            //    ADODBRecordset.MoveNext();
            //КонецЦикла;
            //ADODBRecordset.Close();
            
            // Завершение работы.
            // Закрытие Объектов.
            ADOXCatalog = Неопределено;
            ADODBConnection.Close();
            ADODBConnection = Неопределено;
        Исключение
            Сообщить(НСтр("ru = '"+ОписаниеОшибки()+"'"), СтатусСообщения.Внимание);
            Возврат Новый СписокЗначений;    // В случае ошибки возвращаем пустой список значений.
        КонецПопытки;
        
    КонецЕсли;
    
    Возврат СписокЛистов;
    
КонецФункции

 

Затем уже можно приступать к загрузке, для этого пользуемся следующей функцией:

 

// Метод "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) Драйвер подключения 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
2) Драйвер подключения 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

Важно! Что бы открывать файлы типа XLSX то необходимо пользоваться драйвером 12-ой версии (Microsoft.ACE.OLEDB.12.0).


© Tiger
| Комментировать статью |
  • GM +2587 (15.03.2014 00:16)
    Как-то недавно сталкивался с проблемой извлечения данных из xlsx без windows, выяснил что xlsx - это zip архив, в котором по полочкам разложены xml. Поэтому есть еще один альтернативный вариант - парсить xml из xlsx, но это конечно уже крайний случай :)
    | Ответить |
  • Аноним 0 (24.04.2014 09:31)
    Подскажите пожалуйста где взять этот самый драйвер? Что нужно для него установить?
    | Ответить |