Загрузка из EXCEL в 1С. MS ADODB.Connection
ПРАКТИЧЕСКОЕ ПОСОБИЕ РАЗРАБОТЧИКА: Метод "MS ADODB" (ADODB.Connection).
Поддерживаемые типы: *.xls,*.xlsx.
- Описание
- Подробнее
Описание
В данной статье приведен функционал, с помощью которого в обработке
«Импорт из EXCEL и др.источников (xls,xlsx,ods,sxc,dbf,mxl,csv,sql) в 1С»: //sale.itcity.ru/public/120961
производится считывание данных из файлов табличного типа *xls, *.xlsx.
Методы загрузки из внешнего источника:
— Метод «MS ADO» (Чтение файлов xls, xlsx средствами Microsoft ADO): //sale.itcity.ru/public/163640/
— Метод «MS EXCEL» (Чтение файлов xls, xlsx с картинками средствами Microsoft Office): //sale.itcity.ru/public/163641/
— Метод «LO CALC» (Чтение файлов xls, xlsx, ods, sxc с картинками средствами LibreOffice): //sale.itcity.ru/public/163642/
— Метод «NativeXLSX» (Чтение файлов xlsx с картинками средствами 1С. ПостроительDOM): //sale.itcity.ru/public/300092/
— Метод «NativeXLSX». Предыдущий вариант (Чтение файлов xlsx средствами 1С. ЧтениеXML)://sale.itcity.ru/public/225624/
— Метод «Excel1C» (Загрузка на платформе 8.3.6 с картинками. Чтение файлов xls, xlsx, ods): //sale.itcity.ru/public/341855/
— Список листов файла: //sale.itcity.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 + некоторые изменения в функции.
С уважением к сообществу МА!
