22.09.2006, 17:04 | #1 |
Участник
|
Вспомогательный класс для импорта из Excel через ADO
Я относительно часто использую наработки из темы "Поговорим об ADO" (огромное спасибо, Gustav!), но каждый раз писать все необходимые функции, скажем, в простеньком job'е стало совсем лениво, и был реализован небольшой вспомогательный класс для импорта данных из Excel с использованием ADO для доступа к ним. Если в new() не передать название листа в книге Excel, то класс задействует ADOX.Catalog, чтобы определить названия листов, и использует первый из них (спасибо, blokva!). Вариант доступа через ADO - один из самых быстрых, если не самый быстрый, и при этом он почти так же прост, как считывание обычной таблички из БД. Ниже - пример использования класса:
X++: Counter cnTotal = 0; ItemId itemId; ItemName itemName; AmountCur price; Filename strFilename; container conSheets; ExcelImportADO xlImport; ; strFilename = @"c:\import.xls"; xlImport = new ExcelImportADO(strFilename); try { // по умолчанию будет открыт первый лист в книге Excel if(!xlImport.openFile()) throw error(strfmt("Ошибка при открытии файла Excel «%1»", strFilename)); if(xlImport.getFieldsCount() < 3) throw error(strfmt("Слишком мало колонок: найдено %1, ожидалось минимум %2", xlImport.getFieldsCount(), 3)); while(!xlImport.eof()) { // поля считаются, начиная с 1, как колонки в Excel itemId = xlImport.getFieldValue(1); itemName = xlImport.getFieldValue('ItemName'); // по умолчанию данные поля форматируются как строки // явно указываем, что хотим считать значение как есть price = xlImport.getFieldValue('ItemPrice', false); // обработка данных... cnTotal++; xlImport.moveNext(); } // освобождаем используемые COM-объекты ADO xlImport.finalize(); Box::info(strfmt("считано %1 записей", cnTotal)); } catch(Exception::Error) { xlImport.finalize(); } Надеюсь, для рутинных задач импорта из Excel класс кому-нить пригодится PS. Achtung! Названия листов Excel возвращаются отсортированные по алфавиту (без учета регистра), а не в том порядке, как они идут в книге Excel! Последний раз редактировалось gl00mie; 21.01.2007 в 21:20. |
|
|
За это сообщение автора поблагодарили: Oz (1), mit (1), Morpheus (2), SHiSHok (2), kvg6 (1), Russland (1), Gustav (6), PavelSR (1), alex55 (3), Dino (0), _scorp_ (2), sgt.Pepper (1), zhan (2), Deepoint (1). |
23.09.2006, 11:05 | #2 |
Moderator
|
gl00mie, спасибо, что развиваете тему в "обратном направлении", т.е. Axapta <= Excel.
В очередной раз подумал, не пора ли затевать аналогичный "коллективный эксперимент"... По классу - пара пожеланий. Код: itemId = excelImp.getFieldValue(0); itemName = excelImp.getNamedFieldValue('Название'); price = excelImp.getFieldValue(2, false); У меня в "инструментальном ящичке" в некотором классе есть противоположный метод - setFieldValue, привожу его в качестве подспорья-иллюстрации. Воспользуйтесь при желании. Код: void setFieldValue(anytype _fldName, anytype _fldValue, int _ordNum = 0) { // _fldName - можно текстовое имя, а можно числовое, начиная с 1 (!), а не 0 как в самом ADO // _ordNum - дополнительный способ нумерации, если используются текстовые названия полей (чисто для наглядности самого кода) anytype fldName; if (typeof(_fldName) == Types::Integer) { fldName = _fldName - 1; } else { fldName = _fldName; // текстовое представление поля } fld = flds.Item(fldName); fld.Value(_fldValue); } Ну, и еще раз спасибо! |
|
|
За это сообщение автора поблагодарили: gl00mie (2). |
25.09.2006, 13:59 | #3 |
Участник
|
Цитата:
Сообщение от Gustav
По классу - пара пожеланий. Первое. Я бы объединил методы getFieldValue и getNamedFieldValue в один универсальный getFieldValue. И плюс к этому нумерацию полей начал бы с единицы, невзирая на то, что в самом ADO первое поле - 0. Понимаю, что вопрос концептуально-идеологический. Я сам метался между 0 и 1, но в конце концов остановился на 1. Ну и что, что лишняя операция вычитания, зато получается нормальный наглядный натуральный ряд (блин, ненавижу циклы от 0 до Count-1).
Цитата:
У меня в "инструментальном ящичке" в некотором классе есть противоположный метод - setFieldValue, привожу его в качестве подспорья-иллюстрации. Воспользуйтесь при желании.
PHP код:
PHP код:
Цитата:
Второе пожелание. В методе getRecordCount я бы не торопился возвращать -1 в случае невозможности определения кол-ва записей через ADO. Всё же класс посвящен Excel'ю, а он нам не чужой. Можно, например, воспользоваться в Excel методом Range.CurrentRegion и далее Rows.Count минус первая заголовочная строка (если она есть). Ну, как-то так...
PHP код:
В общем, мне кажется, идеологически правильнее использовать для определения количества записей средства ADO, а не обходные маневры с использованием COM-интерфейсов Excel. Во вложении - тестовый job, использованный для измерения скорости. Обновленный класс можно найти в первом сообщении темы. Последний раз редактировалось gl00mie; 08.01.2007 в 23:45. |
|
|
За это сообщение автора поблагодарили: blokva (2), Hans (1), konopello (1), demon46 (1). |
25.09.2006, 14:43 | #4 |
Moderator
|
Цитата:
Это означает, что ПРИ ЖЕЛАНИИ в случае больших списков полей можно написать так: Код: setFieldValue('НужноеПоле' , valNeed , 1 ); setFieldValue('ОченьНужноеПоле' , valVeryNeed , 2 ); .............................................................. setFieldValue('НаинужнейшееПоле' , valNeedest , 56 ); setFieldValue('НеТакоеВажноеПоле', valNotSoImportant , 57 ); Код: setFieldValue('НужноеПоле' , valNeed ); // 1 setFieldValue('ОченьНужноеПоле' , valVeryNeed ); // 2 .............................................................. setFieldValue('НаинужнейшееПоле' , valNeedest ); // 56 setFieldValue('НеТакоеВажноеПоле', valNotSoImportant ); // 57 |
|
19.10.2006, 11:48 | #5 |
Пенсионер
|
Получился тут маленький эксперимент:
1. фай XLS имеет 5 листов на которых по 32 значащих колонки и суммарно 70000 строк. 2. Пробегаем по всем строкам каждого листа и грузим в таблицу Аксапты только уникальные строки (порядка 3000). 2.1 СОМ делает это за ~160 минут 2.2 ADO (класс от gl00mie) делает за ~790 секунд. итого разница составила 12 раз и это есть гуд!!! спасибо авторам и соавторам идеи и ее развития зы: один минус, список имен листов приходится делать через СОМ, что не есть хорошо. Может есть возможность в ADO это сделать?
__________________
Законы природы еще никто не отменял! А еще у меня растет 2 внучки!!! Кому интересно подробности тут: http://www.baby-shine.com/ |
|
19.10.2006, 13:29 | #6 |
Moderator
|
790 секунд - надеюсь, это включая пробег по 70 тыс. строк и поиск 3 тыс. уникальных? Надеюсь, это не время только загрузки уже найденных 3 тыс.??
Последний раз редактировалось Gustav; 20.10.2006 в 08:48. |
|
19.10.2006, 14:46 | #7 |
Пенсионер
|
Блин точно просю пардону, на волне впечатления и радости не обратил внимания на направление!!!
если админы могут пусть перенесут в эту ветку последних 3 сообщения... Да это тупое последовательное открытие листов, пробегание по строкам, проверка на наличие такой строки в таблице Аксапты и в случае отсутствия, инсерт строки в таблицу.
__________________
Законы природы еще никто не отменял! А еще у меня растет 2 внучки!!! Кому интересно подробности тут: http://www.baby-shine.com/ |
|
19.10.2006, 14:54 | #8 |
Пенсионер
|
Запостил результат работы данного класса не в ту ветку (да простят меня админы, модераторы, посетители, гости и все остальные...аминь) и задал вопрос про список листов...но покопал сам и залабудил метод для этих целей, вроде работает:
PHP код:
__________________
Законы природы еще никто не отменял! А еще у меня растет 2 внучки!!! Кому интересно подробности тут: http://www.baby-shine.com/ |
|
|
За это сообщение автора поблагодарили: gl00mie (2). |
19.10.2006, 19:24 | #9 |
Moderator
|
2 blokva: предлагаю радикально иной подход
Цитата:
Код: rstExcel.Open(@"SELECT * FROM [" + strSheetName + @"$]", cnnExcel, nCursorType) Код: rstExcel.Open(@"SELECT * FROM [Лист1$] UNION " + @"SELECT * FROM [Лист2$] UNION " + @"SELECT * FROM [Лист3$] UNION " + @"SELECT * FROM [Лист4$] UNION " + @"SELECT * FROM [Лист5$] ", cnnExcel, nCursorType) Последний раз редактировалось Gustav; 20.10.2006 в 08:49. |
|
19.10.2006, 23:06 | #10 |
Участник
|
Цитата:
Сообщение от blokva
если админы могут пусть перенесут в эту ветку последних 3 сообщения...
|
|
20.10.2006, 08:53 | #11 |
Moderator
|
|
|
20.10.2006, 10:29 | #12 |
Пенсионер
|
__________________
Законы природы еще никто не отменял! А еще у меня растет 2 внучки!!! Кому интересно подробности тут: http://www.baby-shine.com/ |
|
20.10.2006, 10:33 | #13 |
Пенсионер
|
Цитата:
Сообщение от Gustav
Я думаю, что можно обойтись без "тупого перебора" пяти листов, а воспользоваться всей мощью нормального SQL по отношению к листам Excel и получить весь желаемый рекордсет перед загрузкой в Аксапту - сразу по всем листам и в уникально-сгруппированном виде. ...
...В общем, стратегия такая. Воплотите и будет вам хорошее счастье, думаю, существенно более быстрое, чем 790 секунд Кстати метод определения количества листов, что я привел выше, еще секунд на 40-50 убыстрил процесс загрузки, даже не понятно почему...
__________________
Законы природы еще никто не отменял! А еще у меня растет 2 внучки!!! Кому интересно подробности тут: http://www.baby-shine.com/ |
|
13.11.2006, 14:13 | #14 |
Пенсионер
|
Наметилось пара глюков и один вообще не могу решить, может кто подскажет?
1. При определении колмчества листов в книге в цикле While(!sheet) надо поставить while(!sheets.EOF()), иначе почему-то выдает ошибку 2.А вот тут совсем проблеммы: Если в Ecxel файле установить автофильтр, а потом снять его и пробовать загрузить, то при ситывании перечень листов увеличивается на количество фильтров. Причем открыть нет никакой возможности естественно, как побороть это даже не знаю...
__________________
Законы природы еще никто не отменял! А еще у меня растет 2 внучки!!! Кому интересно подробности тут: http://www.baby-shine.com/ |
|
13.11.2006, 14:57 | #15 |
Moderator
|
Цитата:
Открываем хелп по ADO и читаем оттуда: Цитата:
Сообщение от туда
OpenSchema Method
Obtains database schema information from the provider. Syntax Set recordset = connection.OpenSchema (QueryType, Criteria, SchemaID) Return Values Returns a Recordset object that contains schema information. The Recordset will be opened as a read-only, static cursor. Parameters QueryType --- The type of schema query to run. Can be any of the constants listed below. Criteria --- Optional. An array of query constraints for each QueryType option, as listed below. QueryType values --- Criteria values adSchemaTables --- TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE Удивительно, как оно у Вас работало до этого... (или до этого еще толком не работало?) Цитата:
Может, ну его, такой "разбор полётов", и попробуем наконец классическим SQL'ем через UNION ? |
|
14.11.2006, 10:04 | #16 |
Пенсионер
|
1. Я писал про мой глюк, а Вам спасибо за разъяснение, теперь мне столо понятно почему....
2. Да согласен, я покопался немного ничего не нашел...только вот классическим SQL не всегда обойтись, к примеру у меня несколько листов (т.е. таблиц) с разными структурами и каждую я гружу по своему, поэтому мне крайне необходимо получит перечень таблиц. Я понимаю, что "Автофильтр - это Excel.Application" но почему тогда через ADODB я его получаю как таблицу? Более того, автофильтр однажды включенный создает эту коллизию и даже если я его потом выключаю, сиравно получаю лишнюю таблицу в ADODB... еслиб какое свойство этой псевдотаблицы получать и анализировать...
__________________
Законы природы еще никто не отменял! А еще у меня растет 2 внучки!!! Кому интересно подробности тут: http://www.baby-shine.com/ |
|
14.11.2006, 11:01 | #17 |
Moderator
|
Цитата:
Сообщение от blokva
Я понимаю, что "Автофильтр - это Excel.Application" но почему тогда через ADODB я его получаю как таблицу? Более того, автофильтр однажды включенный создает эту коллизию и даже если я его потом выключаю, сиравно получаю лишнюю таблицу в ADODB... еслиб какое свойство этой псевдотаблицы получать и анализировать...
---------------------------------------------------------------- 1. Создадим в Excel новую рабочую книгу. 2. В окне отладки (Ctrl+G) выполним команду: ? Thisworkbook.Names.Count 0 Т.е. коллекция имен (именованных диапазонов) у нас пустая 3. На первом листе создадим афтофильтр: в первой строке - заголовки колонок - Field1 и Field2 (соответственно в ячейки A1 и B1), в остальные строчки в ячейки A2:B10 введем какую-нибудь информацию для примера (допустим, числа от 1 до 9). 4. Стоя на любой ячейке диапазона, выполним команду меню: Данные / Фильтр / Автофильтр 5. В окне отладки (Ctrl+G) выполним команду: ? Thisworkbook.Names.Count 1 Т.е. в коллекции именованных диапазонов что-то появилось. 6. Что же это? ? Thisworkbook.Names(1).Name Лист1!_FilterDatabase ? Thisworkbook.Names(1).RefersTo =Лист1!$A$1:$B$10 7. Выключим автофильтр: Данные / Фильтр / Автофильтр 8. Повторим пункт 6 - всё то же самое. Таким образом, применение автофильтра создает на рабочем листе именованный диапазон со скрытым служебным именем. ADO в Excel воспринимает как "таблицы схемы" и рабочие листы, и именованные диапазоны ("схема" - рабочая книга). Возможно, есть какой-то признак, позволяющий различать листы и диапазоны в контексте ADO, но мне он пока неизвестен... |
|
|
За это сообщение автора поблагодарили: blokva (2). |
14.11.2006, 12:07 | #18 |
Пенсионер
|
Цитата:
Сообщение от Gustav
Эксперимент (Excel 2000, русский):
...... Таким образом, применение автофильтра создает на рабочем листе именованный диапазон со скрытым служебным именем. ADO в Excel воспринимает как "таблицы схемы" и рабочие листы, и именованные диапазоны ("схема" - рабочая книга). Возможно, есть какой-то признак, позволяющий различать листы и диапазоны в контексте ADO, но мне он пока неизвестен...
__________________
Законы природы еще никто не отменял! А еще у меня растет 2 внучки!!! Кому интересно подробности тут: http://www.baby-shine.com/ |
|
11.12.2006, 12:41 | #19 |
Пенсионер
|
Ну как вариает можно метод получения листов в файле ёкселя изобразить вот так:
X++: container getExcelSheets() { COM sheets, sheet; COM adoxCatalog = new COM('ADOX.Catalog'); int seetsCount, i; str sheetName; ; if(sheetsExcel) { return sheetsExcel; } try { adoxCatalog.ActiveConnection(this.getConnection()); sheets = adoxCatalog.Tables(); seetsCount = sheets.count(); for(i=0; i < seetsCount ; i++) { sheet = sheets.item(i); sheetName = conpeek(str2con_ru(sheet.name(), "$"),1); if(!confind(sheetsExcel,sheetName)) { sheetsExcel += sheetName; } } } catch (Exception::Error) { error("Ошибка получения списка листов"); } return sheetsExcel; }
__________________
Законы природы еще никто не отменял! А еще у меня растет 2 внучки!!! Кому интересно подробности тут: http://www.baby-shine.com/ |
|
08.01.2007, 23:52 | #20 |
Участник
|
После праздников собрал дополнения, предложенные blokva для считывания названий листов - теперь для доступа к книге Excel используется только объекты ADO, удалось отказаться от COM-интерфейсов Excel. Заодно отказался от str2con_ru - мало ли, может, не у всех есть российский dis-слой Обновленная версия класса идет вложением к первому сообщению темы.
|
|
|
За это сообщение автора поблагодарили: blokva (2). |