gifts2017

ADO доступ к таблице Excel

Опубликовал Sigrlami в раздел Программирование - Практика программирования

Общепринятые методы работы с файлами Excel устраивают до тех пор, пока не встречаешься
с файлами на 30 000 строк и несколькими десятками колонок. Время выполнения и обработки
такого файла растет до ужаса.Можно конечно написать :"а теперь можете пойти покурить" как
делают некоторые разработчики, но это не наши методы. Мы боремся за оптимизацию и
производительность. Чем больше элегантных методов мы используем ,тем выше
наш уровень мастерства.А теперь по сути: появилась необходимость загружать специфические
выгрузки, с сайта клиента в excel формате, в базу системы 1с:Предприятие
.Апробирования стандартного метода последовательного считывания ,показало что 30 000 строк
загружается за 12 с небольшим минут. После использования метода с ADO
скорость загрузки уменьшилась до 20 секунд.

 

После небольшого ознакомления с документацией по 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

См. также

Подписаться Добавить вознаграждение
Комментарии
1. Владимир (vovan519) 30.01.10 01:39
А зачем КоличествоСтрок = RecordSet.Fields(0).Value ?
2. Zeldan (Sigrlami) 30.01.10 01:48
(1)Дальше необходимо было знать количество строк,для индикатора процесса.
3. Роман Ершов (MRAK) 30.01.10 09:12
Молодец, код хорошо прокомментирован
4. Игорь Исхаков (Ish_2) 30.01.10 10:34
5. artem666 Bogomaz (artem666) 30.01.10 12:22
напиши лучше про Insert и Update, с Select уже куча инфы в инете :)
6. Zeldan (Sigrlami) 30.01.10 14:40
(5)если действительно нужно можно написать. :D Впринципе то все сводится к знаниям SQL/
7. Юрий (YRAtomic) 30.01.10 16:03
(6) Очень нужно.
Еще вопрос - как быстро происходит инициализация com-объекта по сравнению с обычным Excel.Application ?
8. Виталий (VitaliySm) 30.01.10 21:56
Вообще автору стоит оформить эту статью в виде обработки-шаблона. Т.е. кроме описания стоит выложить также шаблон обработки который любой скачавший сможет быстро донастроить под свои нужды.
9. artem666 Bogomaz (artem666) 30.01.10 22:44
Excel.Application - это наимедленейшее средство работы с экселем. С Insert и Update все сводится не только к знанию SQL, есть свои хитрости, причем мне удалось побороть только Update... :|
10. Zeldan (Sigrlami) 30.01.10 22:47
(8)спасибо учту.в ближайшее время выложу :D
11. Vitaliy 1 (Valiko77) 01.02.10 07:03
Классный метод! Спасибо огромное за публикацию!
12. Виталий Евсеев (CrazyBear) 01.02.10 09:21
Спасибо)) Но хотелось бы увдиеть обработку.. ;)
13. xs19 (Oleg_nsk) 01.02.10 13:36
сделал... Действительно очень быстро, спасибо..
14. Анатолий Ситников (acsent) 01.02.10 14:43
Ничего не рассказано, про IMEX=1
15. Zeldan (Sigrlami) 01.02.10 15:12
(14)ссылка на msdn как бы намекает:
""IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text"

по-русски:этот параметр необходим чтоб воспринимать все колонки как значения типа Строка.
16. Анатолий Ситников (acsent) 01.02.10 15:56
Просто есть ограничение на данный способ загрузки. Если в колонках хранятся разнородные данные, то в лучше случае мы вытащим лишь представления
17. Анатолий Ситников (acsent) 01.02.10 15:57
//определение типа команды
Command.CommandType = 1;

Комментировать у КЭПа учился?
18. Zeldan (Sigrlami) 01.02.10 16:37
(16)в конце статьи я описал,что значения возвращаем строкового типа,независимо от того какие они в Excel.

(17)http://msdn.microsoft.com/en-us/library/aa705017(BTS.10).aspx
20. Михаил Журавлев (Gmix) 03.02.10 12:40
Все хорошо но нельзя забывать о проблеме превышения 255 символов!
Тут много нюансов есть!
собственно и моя статейка http://gmixdev.ru/index.php?option=com_content&task=view&id=14&Itemid=9
и на форуме есть сообщения на тему 255
так что дерзайте!
21. Zeldan (Sigrlami) 03.02.10 16:22
22. Леонид Лесин (Leoway) 05.03.10 12:08
Спасибо помогло. Есть только один вопрос.
Когда получаем список листов командой

Для каждого Лист ИЗ axCatalog.Tables Цикл
КонецЦикла;

Листы загружаются в какой-то странной последовательности, а не так как они расположены в файле. Я так предполагаю в порядке их создания в файле Excel.
При Com соединении такой картины не наблюдается.
Можно ли с этим бороться?
23. Zeldan (Sigrlami) 09.03.10 08:03
(22)
0.Рад,что помогло. :)
1.да,в порядке в котором они создавались в экселе.
2.Ничего не мешает вам выгрузить листы в список значений и потом их как-то обрабатывать или в другую универсальную коллекцию... :idea:
24. Леонид Лесин (Leoway) 09.03.10 08:56
(23) Этот метод понятен. Но в основном такие обработки пишутся для пользователей, у которых свои представления и порядки создания листов в Excel. Именно по этому у меня и возник мой вопрос.
25. Денис (amyd) 22.09.10 11:05
не обязательно таблица должна быть на листе
если это выгрузка с 1С и листов нет то такой запрос тоже пройдет
"select * from [A0:IV100000] "
а так респект и уважуха, плюс однозначно
26. Zeldan (Sigrlami) 22.09.10 12:07
(25) да, я знаю.Через пару недель когда пришлось плотнее работать,прочитал в MSDN, что можно так делать... ;)
Спасибо за комментарий.
27. nikolaygorbunov (nikolaygorbunov) 21.10.10 16:22
Что такое "ТаблицаИспользуемыхКолонок"?
28. Dmitriy (daho) 06.11.12 13:56
(27) nikolaygorbunov, Ну эт на всякий случай если не все колонки тебе нужно юзать..
Автору спасибо, коротко и по теме!
29. Ed5550 30.08.13 12:14
Подскажите пожалуйста:
1) Появилась ошибка Ошибка при вызове метода контекста (Execute): Произошла исключительная ситуация (Microsoft JET Database Engine): Отсутствует значение для одного или нескольких требуемых параметров.
Что не так делаю?
2)Можно поподробнее что такое ТаблицаИспользуемыхКолонок.
Заранее спасибо!
30. Ададуров Виталий (adva) 11.06.14 04:35
А через ADO возможно обновить лист excel? (определенные ячейки)
31. Дмитрий Иванов (ChessCat) 15.07.14 16:38
Почему нельзя было описать структуру "ТаблицаИспользуемыхКолонок" чтобы люди читающие статью не ломали голову наш шарадами ?
32. Дмитрий Иванов (ChessCat) 15.07.14 16:40
(29) Ed5550, "Произошла исключительная ситуация (Microsoft JET Database Engine)"
это означает что вы пытаетесь открыть файл более старшей версии Excel. У автора в примере Excel 8.0
Для Excel 2007 и выше надо использовать "Provider=Microsoft.ACE.OLEDB.12.0";
33. Лев Корл (lev6975) 13.04.16 21:28
Для каждого Лист ИЗ axCatalog.Tables Цикл

Если счЛиста=ЭлементыФормы.НомерЛиста.Значение Тогда
Как - то, странно, у меня четвертый лист в файле(визуально) идет по этой процедуре первым,не тот лист хавает, в общем. А, первый лист в файле (визуально)- седьмой
а, я, и, думаю, почему у меня запрос
Command.CommandText ="SELECT COUNT(*)FROM ["+ИмяТаблицы+"]";,
показывает 27 колонок(полей), когда у меня их 60+...
Это зависит от чего - то?От конкретного компа, винды, версии ёкселя?
Чтобы, у клиентов номера листов определял так же как и у меня
34. Лев Корл (lev6975) 13.04.16 21:29
ёксель 2010 стоит
у клиентов пока не знаю какой
35. Лев Корл (lev6975) 13.04.16 21:32
Хотя, чё я парюсь, лист можно словить по его имени,а, не по номеру))))
36. fzt fzt (fzt) 14.04.16 06:11
(30) adva, Insert и Update для файлов Excel существуют. По крайней мере с OLEDB я точно этим пользовался.
37. fzt fzt (fzt) 14.04.16 06:14
(7) YRAtomic, всяко быстрее. Это лишь подлючение DLL. Даже устанавливать Excel на сервер не нужно ну и покупать.
А вот Application - открывает полновесный Excel, открывается дольше, жрет много ресурсов.
38. fzt fzt (fzt) 14.04.16 06:16
(33) lev6975, может потому, что нумерация в массиве идет с 0?
39. Лев Корл (lev6975) 14.04.16 11:14
(38) fzt, Возможно. И, с конца.Всего листов пять(пятый - пустой, его пропускает). Тогда,четвертый - нулевой, а, следующая итерация, если оттрассировать процедуру - снова четвертый, только с допиской xlnm#_FilterDatabase
Тогда, если, по две итерации на лист, получается, что, седьмая итерация, как раз,зацепит первый лист...
Я, наверно, все - таки, сделаю по именам - загружу имена на форму в список значений, чтобы, выбирались, именно, по именам...
ЗЫ,и, правда,таблицу с килострокой записей ёкселя на 60 полей(всего 60000 полей), грузит меньше секунды! Теперь, узким местом будет раскидка всего этого по документам и справочникам и запись в базу))
Все, понял - он сортирует листы по именам по алфавиту - сначала,выводит листы с именем на латинице, потом, на кириллице. Нужный лист, как раз, с кириллическим именем,остальные на латинице, вот, его и выкидывает последним))
40. Лев Корл (lev6975) 14.04.16 11:59
(22) Leoway, как раз, у меня такое было - листы переименованы и сортируются в алфавитном порядке. Если, листы обозваны лист1,лист2, лист3 и т.д. тогда, будут так как в файле
41. Лев Корл (lev6975) 15.04.16 13:07
Вопрос, как читать поля с датами - ADO их преобразовывает(причем, через раз) к какому - то числу непонятному(номер дня с 01.01.1900года)... Меня бы устроило, чтобы считал строкой,"что вижу то и читаю", без каких - то мудростей. в 1С я сам приведу все к дате.Метод Cdate возвращает дату,но, читает не все поля колонки, по каким - то причинам пропускает
42. fzt fzt (fzt) 18.04.16 04:47
(39) lev6975, косяк нюанс есть.
"если список листов получаешь через Connection.OpenSchema(adSchemaTables), то при сложном форматировании там могут быть не только листы, но и области листа. А в результате может быть такой замечательный эффект: данные как бы получаешь. Но не всегда все :)" © fisher
С датами. ADO может рисовать null, если в одной колонке разные типы данных. Что легко достижимо форматирование шапки таблицы например.
За сим я бы как раз обращался только к области листа, в которой нужные данные, т.е. искал конкретно область таблицы (достижимо когда ексельку формируют "свои", или предварительно форматировал файлы (когда файл формируют "чужие"), выкинув все кроме данных.

Ещё можно поставить это:
https://www.microsoft.com/en-us/download/details.aspx?id=23734
и попробовать
"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + ПараметрыПодключения.ИмяФайла + "; Extended Properties=""Excel 12.0; HDR=NO; IMEX=1;"""
43. Лев Корл (lev6975) 22.04.16 17:24
(42) спасибо, попробую. Заказчик на месяц, к счастью слинял и от меня отвязался,не висит над душой, я закинул проект на потом. На той недельке вернусь к нему, попробую и отпишусь в этой ветке
44. Лев Корл (lev6975) 05.05.16 08:08
(42) fzt, Приветствую, недавно вспомнил за мой вопрос... Я понял, почему из файла затягивается белиберда - файл во время ADO запроса не должен быть открыт в excel !!!)))
Не знаю, почему, но, если файл не открыт, затягивается все идеально. Если висит в ёкселе - начинает неясным образом кривить - плывут не ток даты(как я писал выше), но и числа, тож, грузятся криво... Попросить клиентов схлопывать файл перед затягиванием его в базу я могу, поэтому, в общем, все ОК, спасибо за участие!!! А,так, в общем - то,интересно, почему эксёл таким загадочным образом влияет на запрос...
Может, кстати, кривой драйвер на моем компе, и, его надо обновить с Макрософта... Но, вот, у клиента я этого сделать не смогу, поскоку, клиента, скорее всего, и, ни разу не увижу)))...если ток заказчика просить. Проще, уж, приучить к закрыванию файла перед погрузкой
Или, О!Проверять, открыт ли файл в момент запроса и делать отказ, если открыт...так и сделаю
45. fzt fzt (fzt) 05.05.16 09:07
(44) lev6975, скопировать во временный каталог, а потом удалить?
46. Лев Корл (lev6975) 05.05.16 12:24
(45) fzt, Да, тоже идея!!Отвязать копию от ёкселя. Надо попробовать...плохо то что не знаю что у конечного клиента на компе творится, надо в темп кидать