ADO доступ к таблице Excel
Интеграция - Загрузка и выгрузка в Excel
После небольшого ознакомления с документацией по ADO,чтением MSDN возникла идея
обратиться к таблице excel как к обыкновенной таблице какой-нибудь БД через запрос.
Единственное условиедля такого рода задачи таблица должна быть одна на листе.
Привожу код используемый в функции, в моей обработке пользовательсам выбирал
список колонок для загрузки, поэтому в запросе использовались системные имена,
вы можете скорректировать код под себя.Код хорошопрокомментирован,так что
суть должна быть понятна.
Опишу вкратце текущий алгоритм:
1.Создаю COM объект .
2.Получаю список листов файла.(будет использоваться в дальнейшем запросе).
3.Формирую строку необходимых полей(по выбранным пользователем колонкам)
4.Собираю запрос.
5.Выполняю запрос к файлу.
6.Собираю таблицу для дальнейшей обработки.
Непосредственно код:
//Созданиеобъектадляустановкисвязисисточникомданных
Connection =Новый COMОбъект("ADODB.Connection");
//сформируемстрокуподключения
//Еслинеобходимо вдальнейшемоперироватьцифровымизначениямиколонок,тоставимпризнак HDR = NO
//еслинеобходимопроизводитьзагрузкусучетомпсевдонимовв 1йстроке,тоставим HDR = YES
СтрокаПодключения="Provider=Microsoft.Jet.OLEDB.4.0; Data Source = "+ИмяФайлаЗагрузки;
СтрокаПодключения=СтрокаПодключения+"; Extended Properties = "+"""Excel 8.0"+";HDR=NO;IMEX=1"";";
//подключениекисточникуданных
Попытка
Connection.Open(СтрокаПодключения);
Исключение
Сообщить(ОписаниеОшибки());
Возврат;
КонецПопытки;
//созданиеобъектавыполнениякоманды
Command =Новый COMОбъект("ADODB.Command");
//созданиеобъектаподключениякфайлу
axCatalog =Новый COMОбъект("ADOX.Catalog");
axCatalog.ActiveConnection = Connection;
//получимлистыдокумента,
//дляобработкиколоноклистаможнообратитсякколлекцииЛист.Columns,которуютожеможнообойтипосредствомциклаДлякаждого
счЛиста=1;
ДлякаждогоЛистИЗaxCatalog.Tables Цикл
ЕслисчЛиста=ЭлементыФормы.НомерЛиста.ЗначениеТогда
//получимимят
аблицы,затембудемиспользоватьвзапросе
//ИмяТаблицы = Сред(Строка(Лист.Name),2,СтрДлина(Строка(
>Лист.Name))-1);
ИмяТаблицы=Лист.Name;
Прервать;
КонецЕсли;
счЛиста=счЛиста+1;
КонецЦикла;
//отсортируемтаблицуколонок,которыенеобходимозагрузить
//чтобывдальнейшемприобработке RecordSet моглинебеспокоитсяопоследовательностиобработки
ТаблицаИспользуемыхКолонок.Сортировать("НомерКолонкиВозр");
//соберемчастьзапроса,аименноименаиспользуемыхколонок
//поумолчаниюимяколонкивыглядитследющимобразом F1,F2 ит.д
ТекстЗапросаКолонки="";
СтрокаКолонки="";
ДлякаждогоСтрокаТаблицыИЗТаблицаИспользуемыхКолонокЦикл
ТекстЗапросаКолонки=ТекстЗапросаКолонки+?(ТекстЗапросаКолонки="","F"+СтрокаТаблицы.НомерКолонки,",F"+СтрокаТаблицы.НомерКолонки);
//добавимколонкувтаблицузагрузки
ТаблицаЗагрузки.Колонки.Добавить(СтрокаТаблицы.ИмяКолонки);//соотв. колонкиидутвпорядкевозрастания,т.кмыотсортировалитаблицуранее
//соберемстрокуиспользуемыхколонокдлязаписиврегистрЖурналОбновленийСерийныхНомеров,вконцезагрузки
СтрокаКолонки=СтрокаКолонки+?(СтрокаКолонки="",СтрокаТаблицы.ИмяКолонки,","+СтрокаТаблицы.ИмяКолонки);
КонецЦикла;
//Созданиеобъектанаборазаписей
RecordSet =Новый COMОбъект("ADODB.RecordSet");
//Указаниеактивногосоединения
Command.ActiveConnection = Connection;
//получимколичествостроквдокументе
Command.CommandText ="SELECT COUNT(*)FROM ["+ИмяТаблицы+"]";
//определениетипакоманды
Command.CommandType =1;
//Выполнениеиполучениенабораданных
RecordSet = Command.Execute();
//перваязаписьэтоколичество
КоличествоСтрок= RecordSet.Fields(0).Value;
//опредлелениетекстакоманды
Command.CommandText ="SELECT "+ТекстЗапросаКолонки+" FROM ["+ИмяТаблицы+"]";
//определениетипакоманды
Command.CommandType =1;
//Выполнениеиполучениенабораданных
Попытка
RecordSet = Command.Execute();
Исключение
Сообщить(ОписаниеОшибки());
КонецПопытки;
RecordSet.MoveNext();//т.кперваястрокаэтозаголовкитаблиц,небудемееучитывать
Пока RecordSet.EOF()=0Цикл
СтрокаТабличнойЧасти=ТаблицаЗагрузки.Добавить();
сч=0;
ДлякаждогоКолонкаИЗТаблицаЗагрузки.КолонкиЦикл
СтрокаТабличнойЧасти[Колонка.Имя]=СокрЛП(Строка(RecordSet.Fields(сч).Value));
сч=сч+1;
КонецЦикла;
RecordSet.MoveNext();
КонецЦикла;
RecordSet.Close();
Connection.Close();
Итак на выходе мы получили ТаблицуЗагрузки ,которую можно обработать по своему желанию.Стоит
отметить что все значения получаются строкового типа, поэтому необходимо потом их правильно обработать.
Но овчинка стоит выделки, прирост скорости действительно существенный. Что самое интересное в процессе работы
с файлом excel как с COM объектом можно получать все необходимые свойства и обращаться к нужным листами.
Запрос можно построитьпотяжелее с фильтрами и объединениями, так что поле для действий огромно.
Скажу честно данный механизмсоветую использовать при работе исключительно с большими файлами,
уменьшение времени обработки с 12 минут до 20 секунд.
Создано в Microsoft Office OneNote 2007
Вступайте в нашу телеграмм-группу Инфостарт
Вы можете заказать платную адаптацию этой статьи под ваши задачи на «Бирже заказов».
- 0% комиссии — оплата напрямую исполнителю;
- Исполнители любого масштаба — от отдельных специалистов до команд под проект;
- Прямой обмен контактами между заказчиком и исполнителем;
- Безопасная сделка — при необходимости;
- Рейтинги, кейсы и прозрачная система откликов.
См. также
5490 руб.
12.08.2021 48615 656 71
6000 руб.
11.12.2019 68165 1163 3
12444 руб.
29.10.2014 238135 777 544
6000 руб.
26.11.2020 51370 865 4
6000 руб.
01.03.2021 13537 92 0