Вспомогательный класс для импорта из Excel через ADO

[gl00mie]

Я относительно часто использую наработки из темы "Поговорим об ADO" (огромное спасибо, Gustav!), но каждый раз писать все необходимые функции, скажем, в простеньком job'е стало совсем лениво, и был реализован небольшой вспомогательный класс для импорта данных из Excel с использованием ADO для доступа к ним. Если в new() не передать название листа в книге Excel, то класс задействует ADOX.Catalog, чтобы определить названия листов, и использует первый из них (спасибо, blokva!). Вариант доступа через ADO - один из самых быстрых, если не самый быстрый, и при этом он почти так же прост, как считывание обычной таблички из БД. Ниже - пример использования класса:

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();
}

Из методов работы с Recordset реализованы только EOF() и MoveNext() - в большинстве случаев их хватает, чтобы пробежаться по forward-only курсору, но если надо перемещаться более нетривиально, то в классе есть метод, возвращающий используемый Recordset, и можно будет двигаться по нему самостоятельно.
Надеюсь, для рутинных задач импорта из Excel класс кому-нить пригодится

PS. Achtung! Названия листов Excel возвращаются отсортированные по алфавиту (без учета регистра), а не в том порядке, как они идут в книге Excel!

[Gustav]

gl00mie, спасибо, что развиваете тему в "обратном направлении", т.е. Axapta <= Excel.
В очередной раз подумал, не пора ли затевать аналогичный "коллективный эксперимент"...

По классу - пара пожеланий.

itemId   = excelImp.getFieldValue(0); 
itemName = excelImp.getNamedFieldValue('Название'); 
price    = excelImp.getFieldValue(2, false);

Первое. Я бы объединил методы getFieldValue и getNamedFieldValue в один универсальный getFieldValue. И плюс к этому нумерацию полей начал бы с единицы, невзирая на то, что в самом ADO первое поле - 0. Понимаю, что вопрос концептуально-идеологический. Я сам метался между 0 и 1, но в конце концов остановился на 1. Ну и что, что лишняя операция вычитания, зато получается нормальный наглядный натуральный ряд (блин, ненавижу циклы от 0 до Count-1).

У меня в "инструментальном ящичке" в некотором классе есть противоположный метод - 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);
}

Второе пожелание. В методе getRecordCount я бы не торопился возвращать -1 в случае невозможности определения кол-ва записей через ADO. Всё же класс посвящен Excel'ю, а он нам не чужой. Можно, например, воспользоваться в Excel методом Range.CurrentRegion и далее Rows.Count минус первая заголовочная строка (если она есть). Ну, как-то так...

Ну, и еще раз спасибо!

[gl00mie]

Цитата:

Сообщение от Gustav

По классу - пара пожеланий. Первое. Я бы объединил методы getFieldValue и getNamedFieldValue в один универсальный getFieldValue. И плюс к этому нумерацию полей начал бы с единицы, невзирая на то, что в самом ADO первое поле - 0. Понимаю, что вопрос концептуально-идеологический. Я сам метался между 0 и 1, но в конце концов остановился на 1. Ну и что, что лишняя операция вычитания, зато получается нормальный наглядный натуральный ряд (блин, ненавижу циклы от 0 до Count-1).

Fixed Я тоже сначала думал, как делать индексацию полей и по номерам, и по названиям колонок, но чего-то совсем забыл про anytype Кроме того, нумерация начиная с 1 применительно к Excel, конечно, куда удобнее - в нем ведь тоже можно включить нумерацию колонок ("стиль ссылок R1C1"), а там она начинается как раз с 1.

Цитата:

У меня в "инструментальном ящичке" в некотором классе есть противоположный метод - setFieldValue, привожу его в качестве подспорья-иллюстрации. Воспользуйтесь при желании.

PHP код:

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);
} 


Честно говоря, не понял две вещи: зачем нужен параметр _ordNum, если он не используется, и зачем идет дополнительное присваивание fldName = _fldName. У меня работает просто такой код:

PHP код:

if(typeof(_fldId) == Types::Integer)
    _fldId--;                           // ADO uses indexes starting 0, not 1
fld = flds.Item(_fldId); 


А так, иллюстрацией я уже воспользовался

Цитата:

Второе пожелание. В методе getRecordCount я бы не торопился возвращать -1 в случае невозможности определения кол-ва записей через ADO. Всё же класс посвящен Excel'ю, а он нам не чужой. Можно, например, воспользоваться в Excel методом Range.CurrentRegion и далее Rows.Count минус первая заголовочная строка (если она есть). Ну, как-то так...

Меня, по правде сказать, вопрос использования Excel'евских COM-интерфейсов несколько смущает... Все же упор сделан на ADO, и экселевской специфики в классе - только определение названия активного листа, которое используется как название таблицы в выборке, да еще формат строки соединения... Во-первых, меня смущают такие вещи, как производительность: надо, конечно, тесты провести, но, сдается мне, если грузить Excel для того, чтобы посчитать количество строк в диапазоне, то это вызовет заметную задержку при открытии файла. Во-вторых, количество строк в диапазоне (range) и количество строк в выборке совпадут лишь в том случае, если использовать для получения Recordset обычный select * from. Но если развивать идею дальше, то select может оказаться с where, да еще и по нескольким листам книги Excel, и тогда в Recordset.RecordCount будет вовсе не равно Range.CurrenRegion.Rows.Count-1. В-третьих, в ADO и так есть возможность получить число строк в выборке, например, использовать статический курсор, а не forward-only, который используется в классе по умолчанию. Достаточно при создании экземпляра класса написать

PHP код:

#CCADO
// ...
excelImp = new Uni_ExcelImportADO(strFileName,  #adOpenStatic); 


Правда, опять-таки, это приводит к задержкам при открытии файла, потому что приходится для подсчета считывать все данные в буфер перед тем, как начать выдавать их. Впрочем, я сейчас провел небольшой тест, и выяснилось, что в простейших случаях это не столь критично. Для файла из 4-х полей (цифровой код, текстовое описание от 30 до 120 символов, еще два цифровых поля) и 8000 записей при 5 последовательных тестах на каждый этап среднее время у меня составило:

• открытие 456 мс, считывание данных 8093 мс, курсор forward-only, имя листа определяется классом;
• открытие 456 мс, считывание данных 8172 мс, курсор статический, имя листа определяется классом;
• открытие 22 мс, считывание данных 7960 мс, курсор forward-only, имя листа указано в параметре;
• открытие 22 мс, считывание данных 8067 мс, курсор статический, имя листа указано в параметре.

Измерения велись с помощью GetTickCount(), открытие - это создание экземпляра класса + вызов openFile(), считывание данных - это цикл выборки трех полей из каждой записи.
В общем, мне кажется, идеологически правильнее использовать для определения количества записей средства ADO, а не обходные маневры с использованием COM-интерфейсов Excel.
Во вложении - тестовый job, использованный для измерения скорости. Обновленный класс можно найти в первом сообщении темы.

[Gustav]

Цитата:

Сообщение от gl00mie

Честно говоря, не понял две вещи: зачем нужен параметр _ordNum, если он не используется,

1. Ну я там пишу в комментарии "/ _ordNum - дополнительный способ нумерации, если используются текстовые названия полей (чисто для наглядности самого кода)".
Это означает, что ПРИ ЖЕЛАНИИ в случае больших списков полей можно написать так:

setFieldValue('НужноеПоле'       , valNeed           ,  1 );
setFieldValue('ОченьНужноеПоле'  , valVeryNeed       ,  2 );
..............................................................
setFieldValue('НаинужнейшееПоле' , valNeedest        , 56 );
setFieldValue('НеТакоеВажноеПоле', valNotSoImportant , 57 );

а не так:

setFieldValue('НужноеПоле'       , valNeed           ); //  1
setFieldValue('ОченьНужноеПоле'  , valVeryNeed       ); //  2
..............................................................
setFieldValue('НаинужнейшееПоле' , valNeedest        ); // 56 
setFieldValue('НеТакоеВажноеПоле', valNotSoImportant ); // 57

Цитата:

Сообщение от gl00mie

и зачем идет дополнительное присваивание fldName = _fldName.

2. VBA-шная привычка. Не более чем

[blokva]

Получился тут маленький эксперимент:
1. фай XLS имеет 5 листов на которых по 32 значащих колонки и суммарно 70000 строк.
2. Пробегаем по всем строкам каждого листа и грузим в таблицу Аксапты только уникальные строки (порядка 3000).
2.1 СОМ делает это за ~160 минут
2.2 ADO (класс от gl00mie) делает за ~790 секунд.

итого разница составила 12 раз и это есть гуд!!!
спасибо авторам и соавторам идеи и ее развития

зы: один минус, список имен листов приходится делать через СОМ, что не есть хорошо. Может есть возможность в ADO это сделать?

[Gustav]

790 секунд - надеюсь, это включая пробег по 70 тыс. строк и поиск 3 тыс. уникальных? Надеюсь, это не время только загрузки уже найденных 3 тыс.??

[blokva]

Блин точно просю пардону, на волне впечатления и радости не обратил внимания на направление!!!
если админы могут пусть перенесут в эту ветку последних 3 сообщения...
Да это тупое последовательное открытие листов, пробегание по строкам, проверка на наличие такой строки в таблице Аксапты и в случае отсутствия, инсерт строки в таблицу.

[blokva]

Запостил результат работы данного класса не в ту ветку (да простят меня админы, модераторы, посетители, гости и все остальные...аминь) и задал вопрос про список листов...но покопал сам и залабудил метод для этих целей, вроде работает:

PHP код:

// Return container with list of Sheets' names in the Excel file.
container getExcelSheets()
{
    COM     sheets, sheet, fields, field;
    int     seetsCount, i;
    ;
    if(sheetsExcel)
    {
        return sheetsExcel;
    }
    try
    {
        sheets = cnnExcel.OpenSchema(#adSchemaTables);
        seetsCount = sheets.RecordCount();
        sheet = sheets.MoveFirst();
        while(sheet)
        {
            fields = sheet.Fields();
            for(i=1;i<=fields.Count();i++ )
            {
                field = fields.Item(i);
                if(field.Name() == "TABLE_NAME") break;

            }
            sheetsExcel += strrem(strrem(this.adoValue(field.Value(), field.Type()),"$"),"'");
            sheet = sheets.MoveNext();
        }

    }
    catch (Exception::Error)
    {
        error("Ошибка получения списка листов");
    }
    return sheetsExcel;
} 


[Gustav]

Цитата:

Сообщение от blokva

Да это тупое последовательное открытие листов, пробегание по строкам, проверка на наличие такой строки в таблице Аксапты и в случае отсутствия, инсерт строки в таблицу.

Я думаю, что можно обойтись без "тупого перебора" пяти листов, а воспользоваться всей мощью нормального SQL по отношению к листам Excel и получить весь желаемый рекордсет перед загрузкой в Аксапту - сразу по всем листам и в уникально-сгруппированном виде. Правда, придется философски переосмыслить и видоизменить класс gl00mie, потому что нужно иметь возможность задания произвольной строки SQL, а в данный момент класс пока ориентирован на пробег по одному листу, имя которого подставляется в зашитый SELECT:

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)

В общем, стратегия такая. Воплотите и будет вам хорошее счастье, думаю, существенно более быстрое, чем 790 секунд

[mazzy]

Цитата:

Сообщение от blokva

если админы могут пусть перенесут в эту ветку последних 3 сообщения...

Цитата:

Сообщение от blokva

Запостил результат работы данного класса не в ту ветку...

Перенес сюда.

[Gustav]

Цитата:

Сообщение от mazzy

Перенес сюда.

Спасибо. Я подправил свои посты после переноса, удалив из них фразы, касающиеся именно переноса.

[blokva]

Цитата:

Сообщение от mazzy

Перенес сюда.

Спасибо, впредь постараюсь быть внимательнее...

[blokva]

Цитата:

Сообщение от Gustav

Я думаю, что можно обойтись без "тупого перебора" пяти листов, а воспользоваться всей мощью нормального SQL по отношению к листам Excel и получить весь желаемый рекордсет перед загрузкой в Аксапту - сразу по всем листам и в уникально-сгруппированном виде. ...
...В общем, стратегия такая. Воплотите и будет вам хорошее счастье, думаю, существенно более быстрое, чем 790 секунд

Да мысль интересная, спасибо, надо будет попробовать.
Кстати метод определения количества листов, что я привел выше, еще секунд на 40-50 убыстрил процесс загрузки, даже не понятно почему...

[blokva]

Наметилось пара глюков и один вообще не могу решить, может кто подскажет?
1. При определении колмчества листов в книге в цикле While(!sheet) надо поставить while(!sheets.EOF()), иначе почему-то выдает ошибку
2.А вот тут совсем проблеммы: Если в Ecxel файле установить автофильтр, а потом снять его и пробовать загрузить, то при ситывании перечень листов увеличивается на количество фильтров. Причем открыть нет никакой возможности естественно, как побороть это даже не знаю...

[Gustav]

Цитата:

Сообщение от blokva

1. При определении колмчества листов в книге в цикле While(!sheet) надо поставить while(!sheets.EOF()), иначе почему-то выдает ошибку

Это не глюк. "Так надо!" (с) Бриллиантовая рука.
Открываем хелп по 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

Ваш COM sheets - это рекордсет. А для рекордсета переборный цикл вида WHILE NOT recordset.EOF - святое дело. Что в DAO, что в ADO.
Удивительно, как оно у Вас работало до этого... (или до этого еще толком не работало?)

Цитата:

Сообщение от blokva

2.А вот тут совсем проблеммы: Если в Ecxel файле установить автофильтр, а потом снять его и пробовать загрузить, то при ситывании перечень листов увеличивается на количество фильтров. Причем открыть нет никакой возможности естественно, как побороть это даже не знаю...

Иееххх... Честно говоря, не стал бы я файл, предназначенный для загрузки, разукрашивать всякими продвинутыми эксельными мулечками... И тем более потом анализировать, почему что-то не так происходит... И тем более, что контекст разный: Автофильтр - это Excel.Application, a Ваш sheets - это ADODB... На сколько именно эти COM'ы "синхронизированны" между собой в данный конкретный момент времени - одному Богу известно, и, строго говоря, они и не обязаны...
Может, ну его, такой "разбор полётов", и попробуем наконец классическим SQL'ем через UNION ?

[blokva]

1. Я писал про мой глюк, а Вам спасибо за разъяснение, теперь мне столо понятно почему....
2. Да согласен, я покопался немного ничего не нашел...только вот классическим SQL не всегда обойтись, к примеру у меня несколько листов (т.е. таблиц) с разными структурами и каждую я гружу по своему, поэтому мне крайне необходимо получит перечень таблиц. Я понимаю, что "Автофильтр - это Excel.Application" но почему тогда через ADODB я его получаю как таблицу? Более того, автофильтр однажды включенный создает эту коллизию и даже если я его потом выключаю, сиравно получаю лишнюю таблицу в ADODB... еслиб какое свойство этой псевдотаблицы получать и анализировать...

[Gustav]

Цитата:

Сообщение от blokva

Я понимаю, что "Автофильтр - это Excel.Application" но почему тогда через ADODB я его получаю как таблицу? Более того, автофильтр однажды включенный создает эту коллизию и даже если я его потом выключаю, сиравно получаю лишнюю таблицу в ADODB... еслиб какое свойство этой псевдотаблицы получать и анализировать...

Эксперимент (Excel 2000, русский):
----------------------------------------------------------------
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]

Цитата:

Сообщение от Gustav

Эксперимент (Excel 2000, русский):
......
Таким образом, применение автофильтра создает на рабочем листе именованный диапазон со скрытым служебным именем. ADO в Excel воспринимает как "таблицы схемы" и рабочие листы, и именованные диапазоны ("схема" - рабочая книга). Возможно, есть какой-то признак, позволяющий различать листы и диапазоны в контексте ADO, но мне он пока неизвестен...

Д я тоже в отладчике ёкселя смотрел создаются именованные диапазоны и в аксапте они и являются новыми листам (таблицами)...чтож будем искать варианты...

[blokva]

Ну как вариает можно метод получения листов в файле ёкселя изобразить вот так:

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;
}

вроде работает и с наличием именованных диапазонов.

[gl00mie]

После праздников собрал дополнения, предложенные blokva для считывания названий листов - теперь для доступа к книге Excel используется только объекты ADO, удалось отказаться от COM-интерфейсов Excel. Заодно отказался от str2con_ru - мало ли, может, не у всех есть российский dis-слой Обновленная версия класса идет вложением к первому сообщению темы.