Вспомогательный класс для импорта из 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]

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

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-слой Обновленная версия класса идет вложением к первому сообщению темы.

[gl00mie]

На днях обнаружил интересную особенность в именовании листов Excel и том, в каком порядке возвращаются данные о листах (таблицах ADOX.Catalog). Во-первых, если в названиях листов есть какие-то неположеные символы, названия берутся в одинарные кавычки, причем просто лист обзывается 'Sheet 1', а вот именованный диапазон автофильтра возвращается как 'Sheet 1$'Smth, т.е. во втором случае кавычки идут после '$'. В связи с этим кроме символа $ и всего, что идет после него, из имени теперь вырезаются и одинарные кавычки. Во-вторых, как выяснилось, данные о листах (таблицах ADOX.Catalog) возвращаются отсортированные без учета регистра по названиям листов, а вовсе не в том порядке, в каком листы представлены в книге Excel. Поскольку класс по умолчанию использует в выборке имя листа, первого с точки зрения ADOX.Catalog, это может иметь нежиданный эффект - обратите внимание...
Обновленная версия класса, вырезающая кавычки из имен листов, выложена в первом сообщении темы.

[blokva]

Цитата:

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

На днях обнаружил интересную особенность ....

Да есть такая штука, прошу меня простить, я это тоже обнаружил, исправил у себя, а обществу не сообщил...забыл в запарке

[kashperuk]

Посмотрите файл.
Запускаю импорт из него.
Вместо первой сразу начинает импорт со второй строки.

Какие варианты исправления и объяснения происходящего?

Помимо этого, иногда неверно определяется число столбцов Excel. (больше 100 пишет)

Код job для импорта, который использую:

static void WrongADOFileImport(Args _args)
{
    ComExcelImportADO_UAI   excel;
    Counter                 fieldCount;
    Counter                 curField;
    FileNameOpen            fileName;
    ;
    
    filename = WinAPI::getOpenFileName(infolog.hWnd(), ["Microsoft Excel","*.xls"], '', "Выберите файл");
    if (!filename)
        throw error("@SYS26757");
    excel = new ComExcelImportADO_UAI(fileName);
    if (excel.openFile())
    {
        fieldCount = excel.getFieldsCount();
        box::info(int2str(fieldCount));
        while (!excel.eof())
        {
            info(excel.getFieldValue(1, true));
            info(excel.getFieldValue(2, true));
            info(excel.getFieldValue(3, true));
            info(excel.getFieldValue(4, true));
            info(excel.getFieldValue(5, true));
            for (curField = 6; curField < fieldCount; curField++)
            {
                info(excel.getFieldValue(curField, true));
            }
            return; //только первую строку
            //excel.moveNext();
        }
    }
}

Сам файл: