Вспомогательный класс для импорта из Excel через ADO - Страница 2 - DAX: База знаний и проекты

gl00mie · 21.01.2007, 21:45

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

blokva · 22.01.2007, 09:06

Цитата:

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

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

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

kashperuk · 30.01.2007, 19:53

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

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

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

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

X++:

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

Сам файл:

AndyD · 30.01.2007, 20:59

Иван, так первая строка интерпретируется как заголовок столбцов.

X++:

static void CorrectADOFileImport(Args _args)
{
    ExcelImportADO   excel;
    Counter                 fieldCount;
    Counter                 curField;
    FileNameOpen            fileName;
    COM adoxCatalog = new COM(@"ADOX.Catalog");
    COM Tables;
    COM Table;
    COM Columns;
    COM Column;
    int i;
    ;
    filename = WinAPI::getOpenFileName(infolog.hWnd(), ["Microsoft Excel","*.xls"], '', "Выберите файл");
    if (!filename)
        throw error("@SYS26757");
    excel = new ExcelImportADO(fileName);
    if (excel.openFile())
    {
        fieldCount = excel.getFieldsCount();

        adoxCatalog.ActiveConnection(excel.getConnection());
        Tables   = adoxCatalog.Tables();
        Table = Tables.Item(0);
        if (Table)
        {
            Columns = Table.Columns();
            for (i=0;i<Columns.Count();i++)
            {
                Column = Columns.Item(i);
                if (Column)
                    info(Column.Name());
            }
            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();
            }
        }
    }
}

Класс для импорта взял у gl00mie

AndyD · 31.01.2007, 00:38

Интересно
Согласно BUG: Excel ODBC Driver Disregards the FirstRowHasNames or Header Setting для Microsoft Jet OLE DB Provider должна работать возможность отключения с помощью параметра HDR=No этой возможности. Но у меня с такой строкой подключения "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + %1 + ";Extended Properties='Excel 8.0;Ndr=No;IMEX=1'" все равно первая строка принимается как заголовок (MDAC 2.7, ExcelXP)

Код, который я привел выше, не совсем корректно отображает данные в таких ячейках. Во-первых, сортировка идет в алфавитном порядке. Во-вторых, для ячеек, содержащих цифровые данные, имена будут отображаться в видет F1, F2 и т.д. В третьих, если встретятся одинаковые наименования (например, "дата"), то имена будет идти как "дата", "дата1" и т.д.

Сортировку можно побороть с помощью такого метода

X++:

{
    ExcelImportADO   excel;
    Counter                 fieldCount;
    Counter                 curField;
    FileNameOpen            fileName;
    COM Conn;

    COM SchemaTables;
    COM TableFields;
    COM SchemaColumns;
    COM Fields;
    COM Item;
    int i;
    ComVariant var;
    Array arr = new Array(Types::Class);
    Array values;
    str name;
    #define.adSchemaColumns(4)
    #define.adSchemaTables(20)
    ;

    filename = WinAPI::getOpenFileName(infolog.hWnd(), ["Microsoft Excel","*.xls"], '', "Выберите файл");
    if (!filename)
        throw error("@SYS26757");
    excel = new ExcelImportADO(fileName);
    if (excel.openFile())
    {
        fieldCount = excel.getFieldsCount();
        Conn = excel.getConnection();
        SchemaTables = Conn.OpenSchema(#adSchemaTables);
        while (!SchemaTables.EOF())
        {
            TableFields = SchemaTables.Fields();
            Item = TableFields.Item("TABLE_NAME");
            var = Item.value();
            info(strfmt("Table name : '%1'", var.bStr()));

            arr.value(1, new ComVariant());
            arr.value(2, new ComVariant());
            arr.value(3, var);
            arr.value(4, new ComVariant());
            SchemaColumns = Conn.OpenSchema(#adSchemaColumns, ComVariant::createFromArray(arr));
// к сожалению, сортировка для Excel не работает
//            SchemaColumns.sort("ORDINAL_POSITION"); 
            values = new Array(Types::String);
            while (!SchemaColumns.Eof())
            {
                Fields = SchemaColumns.Fields();
                Item = Fields.Item("COLUMN_NAME");
                var = Item.value();
                name = var.bStr();

                Item = Fields.Item("ORDINAL_POSITION");
                var = Item.value();
                values.value(var.double(), name);
                SchemaColumns.MoveNext();
            }
            for (i=1;i<=values.lastIndex();i++)
                info(values.value(i));
            break;
            //SchemaTables.MoveNext();
        }
    }
}

Для цифровых значений для правильной интерпретации необходимо указывать апостроф в начале. Простого указания формата ячейки как текст недостаточно

Общая рекомендация - всегда оставлять первую строку для заголовка (в принципе, наименования столбцов можно не задавать. В этом случае будут использоваться мена F1, F2 и т.д.)

raz · 31.01.2007, 09:09

Цитата:

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

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + %1 + ";Extended Properties='Excel 8.0;Ndr=No;IMEX=1'"

может из-за опечатки?
Ndr=No, а сам пишешь о Hdr=No

AndyD · 31.01.2007, 09:58

Мэй би. Но я пробовал и с FirstRowHasNames=0 - результат тот же.

kashperuk · 31.01.2007, 11:57

Вообще чудеса.

Провел исследование:

1. Установил HDR=No в строке подключения, в результате получил такую строку:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + %1 + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1'"
При этом первая строка не прочиталась. (То есть неверно сработало)
2. Установил HDR=No и FirstRowHasNames=0 в строке подключения, в результате получил такую строку:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + %1 + ";Extended Properties='Excel 8.0;HDR=No;FirstRowHasNames=0;IMEX=1'"
При этом первая строка прочиталась. (То есть верно сработало)
3. Убрал HDR=No из предыдущего варианта, в результате получил такую строку:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + %1 + ";Extended Properties='Excel 8.0;FirstRowHasNames=0;IMEX=1'"
При этом первая строка не прочиталась. (То есть неверно сработало)
4. Вернул на место. Получил строку, как в варианте 2.
НО, при этом первая строка не прочиталась. (То есть неверно сработало)

Тут я уже начал задумываться, а не случайным ли образом выдается этот параметр

5. Убрал FirstRowHasNames=0 и вернул HDR=Yes, в результате получил строку:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + %1 + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
А теперь внимание!: Первая строка НЕ прочиталась (То есть верно сработало, точнее, сработало то неверно, но с требуемым результатом)

Попробовав еще несколько различных комбинаций установил, что он этих двух параметров результат ЗАВИСИТ, но как именно - не установлено.

Исследуемая система:
MS Windows XP Pro
MS Office Excel 2003
MDAC 2.81

gl00mie · 31.01.2007, 15:25

Цитата:

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

Провел исследование:
1. Установил HDR=No в строке подключения, в результате получил такую строку:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + %1 + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1'"
При этом первая строка не прочиталась. (То есть неверно сработало)
2. Установил HDR=No и FirstRowHasNames=0 в строке подключения, в результате получил такую строку:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + %1 + ";Extended Properties='Excel 8.0;HDR=No;FirstRowHasNames=0;IMEX=1'"
При этом первая строка прочиталась. (То есть верно сработало)
3. Убрал HDR=No из предыдущего варианта, в результате получил такую строку:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + %1 + ";Extended Properties='Excel 8.0;FirstRowHasNames=0;IMEX=1'"
При этом первая строка не прочиталась. (То есть неверно сработало)
4. Вернул на место. Получил строку, как в варианте 2.
НО, при этом первая строка не прочиталась. (То есть неверно сработало)

Тут я уже начал задумываться, а не случайным ли образом выдается этот параметр

5. Убрал FirstRowHasNames=0 и вернул HDR=Yes, в результате получил строку:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + %1 + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
А теперь внимание!: Первая строка НЕ прочиталась (То есть верно сработало, точнее, сработало то неверно, но с требуемым результатом)

Докладываю свои результаты

1-я строка не читается при 'HDR=Yes' (так и задумано)
1-я строка не читается при 'HDR=Yes;FirstRowHasNames=1' (так и задумано)
1-я строка не читается при 'HDR=Yes;FirstRowHasNames=0' (HDR, видимо, главнее)
1-я строка не читается при 'FirstRowHasNames=1' (так и задумано)
1-я строка не читается при 'FirstRowHasNames=0' (no comments)
1-я строка читается при 'HDR=No' (так и задумано, доки не врут!)
1-я строка читается при 'HDR=No;FirstRowHasNames=0' (так и задумано)
1-я строка читается при 'HDR=No;FirstRowHasNames=1' (HDR, видимо, главнее)

Таким образом, можно сделать выводы, что при доступе к листам книг Excel через Jet OLEDB:
1) По умолчанию используется значение HDR=Yes;
2) Значение FirstRowHasNames вообще ни на что не влияет.

Исследуемая система:
MS Windows Server 2003 SP1
MS Office Excel 2003 SP2 Rus
MDAC 2.81 (вроде он штатно идет с w2k3, по крайней мере, файлы odbc*.dll версии 3.526.1830.0)

Цитата:

Попробовав еще несколько различных комбинаций установил, что он этих двух параметров результат ЗАВИСИТ, но как именно - не установлено.

Hint: после изменения макроса имеет смысл не просто сохранить classDeclaration, но и перекомпилировать весь класс (ну или метод, где этот макрос используется), а то есть вероятность, что новое значение макроса не подхватится

PS. В ходе раскопок обнаружилось, по какому числу строк определяются типы колонок:
HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows, по умолчанию стоит восемь, как и написано в документации (раздел «A Caution about Mixed Data Types»). Впрочем, там же упоминается параметр MaxScanRows, которым это можно контролировать: установить значения от 1 до 16, либо поставить 0 для сканирования всех строк.

**Gustav** · 31.01.2007, 16:08

Цитата:

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

PS. В ходе раскопок обнаружилось, по какому числу строк определяются типы колонок:
HKLM\Software\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows, по умолчанию стоит восемь, как и написано в документации (раздел «A Caution about Mixed Data Types»). Впрочем, там же упоминается параметр MaxScanRows, которым это можно контролировать: установить значения от 1 до 16, либо поставить 0 для сканирования всех строк.

Совсем недавно всё это перетирали: Проблемы с ADO

MaxScanRows не работает

polygris · 14.12.2009, 19:16

Кто нибудь портировал этот класс на AX 2009?

**Gustav** · 15.12.2009, 14:35

В этом классе, вроде, не должно быть ничего такого, что помешало бы ему портироваться. А Вы пробовали и не получилось или что?

polygris · 15.12.2009, 15:31

при компиляции класса в 2009 появляется такое сообщение Best Practise: TwC: объявление об использовании API COM.Fields, поскольку он защищен с применением разграничения доступа кода.

propeller · 20.05.2010, 11:19

X++:

int getRecordCount()
{
    if(rstExcel && rstExcel.State() != #adStateClosed)
        return rstExcel.RecordCount();
    return -1;
}

Использую метод excel.getRecordCount() для получения количества строк. Проверил дебагером, условие проходит попадает в stExcel.RecordCount(). но все равно возращает "-1". Хотя строки в файлике есть, и они грузятся.
Непонятно почему метод их не считает...?

gl00mie · 20.05.2010, 12:37

Метод RecordCount() вроде бы возвращает осмысленное значение лишь для курсора типа static, в то время как в импорте по умолчанию используется курсор типа forward-only (он немного быстрее работает) - см. метод new() класса.

**sukhanchik** · 20.05.2010, 13:35

Цитата:

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

Проверил дебагером, условие проходит попадает в stExcel.RecordCount(). но все равно возращает "-1". Хотя строки в файлике есть, и они грузятся.
Непонятно почему метод их не считает...?

Давно-давно, на заре появления ADO я читал, что RecordCount() может не возвращать полное количество строк для повышения быстродействия. А чтобы заставить этот метод вернуть правильное количество записей предлагалось сделать .MoveLast(), а затем .MoveFirst(), т.е. фактически пролистать до конца выборку (=утянуть ее всю на клиента) и вернуться в начало. Причем такой метод однозначно неприменим для forward-only курсоров (см пост от gl00mie).

**Gustav** · 20.05.2010, 17:29

А что если радикально поменять метод на примерно такой:

X++:

public int getRecordCount()
{
    COM         rstCount;
    COM         fldCount;
    COMVariant  fldValue;
    ;
    rstCount = new COM(@"ADODB.Recordset");
    rstCount.Open(@"SELECT Count(*) FROM [" + strSheetName + @"$]", this.getConnection());

    fldCount = rstCount.Fields();
    fldCount = fldCount.Item(0);

    fldValue = fldCount.Value();
    return fldValue.int();
}

propeller · 21.06.2010, 16:48

Пользуюсь классом ExcelImportADO в 2009 AX. Создаю общие журналы (через axLedgerJournalTrans)
Очень удобно, все хорошо.
Только когда файл грузишь где строк тысяч 10, заметно что первые 1000 строк грузит гораздо быстрей чем следущие 1000 и так по нарастающей. Можно ли как то оптимизировать? может быть кэш очистить или еще что-то?
На классе который грузит RunOn - Called from.

gl00mie · 21.06.2010, 17:31

Из того, что относится к собственно классу импорта, в голову приходит только тип курсора: у конструктора есть опциональный параметр int _cursorType = #adOpenForwardOnly, можно попробовать поставить #adOpenStatic (макрос из CCADO), тогда открываться файл будет чуть дольше, но скорость выборки строк должна стать одинаковой. Впрочем, я бы еще прошелся профилировщиком по коду импорта в целом - весьма вероятно, что замедление происходит где-то вне упомянутого класса. Причем оно может быть обусловлено совершенно безобидными на первый взгляд конструкциями, см., например, Channel9: Peter Villadsen and Gustavo Plancarte: X++ to MSIL:

Цитата:

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

Было одно место при разноске больших журналов, где существенную долю составляло простое присваивание типа a = b, где b - ссылка на объект с кучей связанных объектов. Именно из-за детерминированного сборщика мусора, который обязан собрать весь мусор прямо в момент его появления (кто-то блогпост еще писал по этому поводу).

Zabr · 24.08.2010, 10:58

Проблема: класс ExcelImportADO при открытии файла Excel 2007 дает ошибку. Работаю в AX 4.0 SP2 под Windows 7. При открытии того же файла, сохраненного в формате Excel 1997-2003 ошибки нет, все прекрасно читается.

Цитата:

Метод "Open" в COM-объекте класса "ADODB.Connection" возвратил код ошибки 0x80004005 (E_FAIL), который означает: Внешняя таблица не имеет предполагаемый формат.
Набор таблиц недоступен. Приложение Excel инициализировано?
Метод "Open" в COM-объекте класса "ADODB.Connection" возвратил код ошибки 0x80004005 (E_FAIL), который означает: Внешняя таблица не имеет предполагаемый формат.
Невозможно открыть файл «c:\2007.xlsx»

Подозреваю, что дело в объявлении макроса в ExcelImportADO, может версию Excel тут нужно поправить? если да, то как именно? или же дело в чем-то другом?

Цитата:

#localmacro.ADODBExcelConnString
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + %1 + ";Extended Properties='Excel 8.0;HDR=No;FirstRowHasNames=0;IMEX=1'"
#endmacro

Похожие темы
Тема	Автор	Раздел	Ответов	Посл. сообщение
gl00mie: Read Excel table via ADO	Blog bot	DAX Blogs	2	09.04.2010 08:32
dynamicsusers: DL Tips And Tricks: ADO Database Reader (Jet way) - Excel Example	Blog bot	DAX Blogs	0	02.08.2007 03:50
casperkamal: Using ADO to read from Excel in Microsoft Dynamics Ax	Blog bot	DAX Blogs	2	14.05.2007 11:59