Поговорим об ADO

[Roman N. Krivov]

Как передать в Excel (через ADO) число типа real так, чтобы оно не преобразовывалось в дату? Я указывают тип поля adDouble, а Excel всё равно преобразует в дату.

[S.Kuskov]

Цитата:

Сообщение от Roman N. Krivov

Я указывают тип поля adDouble, а Excel всё равно преобразует в дату.

Не воспроизвелось

static void Job20100506(Args _args)
{
    #define.adDouble(5)
    COM Recordset;
    COM Fields;
    COM Field;
    COM Application;
    COM Workbooks;
    COM Workbook;
    COM Worksheets;
    COM Worksheet;
    COM Range;
    ;
    
    Recordset = new COM('ADODB.Recordset');
    Fields = Recordset.Fields();
    Fields.Append('Field1', #adDouble);
    
    Recordset.Open();
    Recordset.AddNew();
    Field = Fields.Item('Field1');
    Field.Value(123.45);
    Recordset.Update();
 
    Application = new COM('Excel.Application');
    Workbooks = Application.Workbooks();
    Workbook = Workbooks.add();
    Worksheets = Workbook.Worksheets();
    Worksheet  = Worksheets.Item(1);
    Range  = Worksheet.Range('A1');
    
    Range.CopyFromRecordset(Recordset);

    Recordset.Close();
    Application.Visible(true);
}

[Gustav]

Тоже не воспроизвелось

static void Job298(Args _args)
{
    #CCADO

    COM rng  = SysExcelApplication::construct().workbooks().add().worksheets().itemFromNum(1).range('A1').comObject();
    COM rst  = new COM('ADODB.Recordset');
    COM flds = rst.Fields();
    COM fld;
    int i;

    flds.Append('MyReal', #adDouble);
    rst.Open();

    fld = flds.Item('MyReal');

    for (i=1; i<=10; i++)
    {
        rst.AddNew();
            fld.Value(100 + i/10);
        rst.Update();
    }
    rng.CopyFromRecordset(rst);
    COM::createFromObject(rng.Application()).Visible(true); // отобразим Excel
}

На чистом листе Excel не воспроизводится. Где-то у вас форматирование всё же выполняется, наверное...

[Stainless]

Как перейти к конкретной записи recordset?
Почитал help на эту тему, там есть метод move, но заставить его работать у меня не получилось.

Ответ нашелся очень просто:

recordset.move( NumRecords, Start );

более подробно: http://www.devguru.com/Technologies/...dset_move.html

[Gustav]

хм, в хелпе тоже самое, вроде:

Цитата:

Сообщение от файл ADO210.CHM

Move Method

Moves the position of the current record in a Recordset object.

Syntax

recordset.Move NumRecords, Start

Или отсутствие скобок смутило? В хелпе синтаксис для Visual Basic приводится.

[Gustav]

Испытываю возобновление интереса к теме и вот в связи с чем. ADODB.Recordset имеет полезнейшие свойства Filter и Sort, которыми можно эффективно пользоваться, фильтруя исходный набор или изменяя порядок записей исходного Recordset'а и далее перебирая в цикле нужные записи в нужном порядке.

Резонно было бы предположить, хотя про это нигде не сказано в справке, что методом CopyFromRecordset должен выгружаться набор записей с учетом фильтрации и сортировки. Увы, при управлении Excel из Аксапты этого не происходит:

static void Job103(Args _args)
{
    #CCADO

    ComExcelDocument_RU doc = new ComExcelDocument_RU();
    COM                 rst = new COM('ADODB.Recordset');

    COM     xlApp;
    COM     wbook;
    COM     activeSheet;
    COM     range;
    COM     flds,fld;

    int     i;
    ;

    doc.NewFile('',false);
    wbook = doc.getComDocument();

    xlApp = wbook.Parent();
    activeSheet = xlApp.ActiveSheet();

    flds = rst.Fields();

    flds.Append('myField1', #adVarChar, 50);
    flds.Append('myField2', #adInteger);
    
    rst.Open();

    for (i=1;i<=1000;i++)
    {
        rst.AddNew();
            fld = flds.Item(0); fld.Value(int2str(i));
            fld = flds.Item(1); fld.Value(i*2);
        rst.Update();
    }

    rst.Filter("myField1 Like '*24*'");
    rst.Sort('myField1 DESC');

    rst.MoveFirst();

    range = activeSheet.Range('A1');
    range.CopyFromRecordset(rst, 300);

    range = activeSheet.Range('A1000');
    range.CopyFromRecordset(rst, 300);

    doc.visible(true);

    
    // чтобы показать, что набор действительно фильтруется и сортируется
    rst.MoveFirst();
    while (! rst.EOF())
    {
        info(strFmt('%1 --- %2', 
                    new CCADOField( flds.Item(0) ).value(),
                    new CCADOField( flds.Item(1) ).value()));
        rst.MoveNext();
    }
}

Поведение метода CopyFromRecordset в этом примере не соответствует ожиданиям. Он не учитывает не только сортировку и фильтрацию, но и декларированное в справке продвижение указателя текущей записи. Так, по здравой логике, первый оператор "range.CopyFromRecordset(rst, 300);" должен вывести в Excel первые 300 записей и установить указатель на 301-ю. Второй же оператор "range.CopyFromRecordset(rst, 300);" должен вывести следующие 300 записей: с 301-й по 600-ю и остановиться на 601-й.

Но этого не происходит. Вместо этого - и начиная с ячейки A1, и начиная с ячейки A1000 - одинаково выводятся первые 300 записей исходного набора. А хотелось бы, чтобы вывелись только 20 записей с ячейки A1, т.е. те записи, которые пример выводит в окно Infolog.

Возможно мои претензии необоснованы и CopyFromRecordset должен себя вести именно так, как ведет себя в этом примере? Я уже было засомневался, вернее, почти отказался от своих "претензий", но решился на еще одну проверку - в самом Excel на VBA:

 
Sub Job103_VBA()

    Dim i As Integer
    
    Dim rst As Object
    Dim flds As Object
    
    Dim xlApp As Excel.Application

    Set rst = CreateObject("ADODB.Recordset")
    'я сознательно создал rst через CreateObject, а не как New ADODB.Recordset, 
    'чтобы не искать ссылку на ADO через Tools\References    
    
    Set flds = rst.Fields
    
    flds.Append "myField1", 200, 50 '200 = adVarChar
    flds.Append "myField2", 3 '3 = adInteger
    
    rst.Open
    
    For i = 1 To 1000
        rst.AddNew
            rst.Fields(0).Value = i
            rst.Fields(1).Value = i * 2
        rst.Update
    Next i
    
    rst.Filter = "myField1 Like '*24*'"
    rst.Sort = "myField1 DESC"
    
    rst.MoveFirst
    
    'Set xlApp = New Excel.Application
    Set xlApp = Application
    
    xlApp.Workbooks.Add
      
    xlApp.Range("A1").CopyFromRecordset rst, 300
    xlApp.Range("A1000").CopyFromRecordset rst, 300
    
    xlApp.Visible = True
    
End Sub

Что скажете о результате? Он такой, как хотелось! Ура? Увы, нет. Закомментируйте строку "Set xlApp = Application" и раскомментируйте строку выше, т.е. должно стать вот так:

 
    Set xlApp = New Excel.Application
    'Set xlApp = Application

Таким образом, теперь мы из Excel создаем другой Excel и выводим рекордсет в этот другой. Результат такой же, как при выводе из Аксапты - не такой, как хочется.

Есть ли у кого какие-нибудь соображения? Может, кто-нибудь интересующийся продвинуто заглянет какими-нибудь крутыми вьюерами в тайны происходящих процессов COM-автоматизации? Может быть удастся победить ситуацию использованием каких-нибудь COMDispFunction?


P.S. Проделал даже такой финт. Создал шаблон xlt, в который зашил VBA-процедуру:

 
Sub myCopyFromRecordset(ByVal rng As Range, ByVal rst As ADODB.Recordset, ByVal maxRows As Integer)
    rng.CopyFromRecordset rst, maxRows
End Sub

Из Аксапты создал новый файл на базе этого шаблона и вызвал процедуру строкой:

    xlApp.Run('myCopyFromRecordset', range, rst, 300);

Результат - тоже не тот

[sukhanchik]

Цитата:

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

Резонно было бы предположить, хотя про это нигде не сказано в справке, что методом CopyFromRecordset должен выгружаться набор записей с учетом фильтрации и сортировки.

Для тех, кто программировал в Access еще в DAO (а в общем-то DAO можно считать прародителем ADO) такое предположение отнюдь не резонно. Методы Filter и Sort действовали ПОСЛЕ выборки, а метод CopyFromRecordset копировал именно все записи выборки (он совершенно не собирался смотреть на методы Filter и Sort). Именно поэтому в Access приветствовалось программирование через SQL-запросы, а не через методы работы с таблицами.

Я удивлен другим. Я удивлен - что CopyFromRecordset все-таки может копировать с учетом фильтрации - но об этом видимо "помнит" только текущее приложение - т.е. поддержка реализована не на уровне "ядра" ADO

[gl00mie]

А я во вспомогательном канале вывода в Excel зашел с другого конца: при создании структуры ADO.Recordset я указываю опциональный признак того, надо ли в соотв. колонке избавиться от "незначащих" значений, и после вывода просто делают замену средствами самого Excel таких значений на то, что возвращает COMVariant::createNoValue()

// очистка ячеек с "пустыми" значениями (ноль для чисел либо 01.01.1900 для дат), чтоб не надо было докручивать шаблон для их сокрытия
// _cell - ячейка, на которой в outputReportBody() вызывается метод CopyFromRecordset()
protected void clearEmptyCells(COM _cell)
{
    COMVariant  cvSrcValue;
    COMVariant  cvDstValue;
    str         strAddr;
    container   conFieldInfo;
    boolean     bClearEmpty;
    Counter     cnRows;
    Counter     n;
    COM         oRng;                                           // область, в которой будет производиться замена
    ;
    cnRows      = rstAxa.RecordCount();                         // количество выведенных строк данных
    cvDstValue  = COMVariant::createNoValue();                  // создаем пустое значение
    for (n = 1; n <= arrFields.lastIndex(); n++)
    {
        conFieldInfo = arrFields.value( n );
        if (conlen(conFieldInfo) >= #ConPosFieldInfoClearEmpty) // по умолчанию этого поля в контейнеере быть не должно
        {
            bClearEmpty = conpeek( conFieldInfo, #ConPosFieldInfoClearEmpty );
            if (!bClearEmpty)
                continue;
            cvSrcValue  = this.getEmptyVariantValue( conpeek( conFieldInfo, #ConPosFieldInfoType ) );
            if (!cvSrcValue)                                    // если вернулся null, значит, тип не поддерживается
                continue;
            // формируем адрес диапазона ячеек *относительно* _cell
            strAddr = ComExceldocument_RU::numToNameCell( n, 1 );
            if (cnRows > 1)
                strAddr += @':' + ComExceldocument_RU::numToNameCell( n, cnRows );
            oRng = _cell.range( strAddr );                      // получаем столбец внутри диапазона _cell
            oRng.Replace( cvSrcValue, cvDstValue, #xlWhole, #xlByColumns );
        }
    }
}
// возвращает COMVariant, представляющий "пустое" значение для указанного типа ADO, либо null, если указанный тип не поддерживается
protected COMVariant getEmptyVariantValue( Integer _adoType )
{
    COMVariant  ret;
    ;
    switch( _adoType )
    {
        case #adDate :
        case #adDBDate :
        case #adDBTimeStamp :
            ret = COMVariant::createFromDateAndTime( datenull(), 0 );
            break;
        case #adSingle :
        case #adDouble :
        case #adCurrency :
        case #adDecimal :
        case #adNumeric :
            ret = COMVariant::createFromReal( 0.0 );
            break;
        case #adTinyInt :
        case #adSmallInt :
        case #adInteger :
        case #adBigInt :
        case #adUnsignedTinyInt :
        case #adUnsignedSmallInt :
        case #adUnsignedInt :
        case #adUnsignedBigInt :
            ret = COMVariant::createFromInt( 0 );
            break;
        default :
            ret = null;
            break;
    }
    return ret;
}

Хотя, конечно, просто не устанавливать значение ячейки в Recordset, пожалуй, как-то "прямее"
PS. По ходу реализации наткнулся на одни "грабли": оказалось, что COMVariant::createFromDate( datenull() ) возвращает COMVariant не со значением 01.01.1900 00:00, как можно было бы ожидать, а со значением 01.01.1900 <текущее_время>!

[Gustav]

Цитата:

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

PS. По ходу реализации наткнулся на одни "грабли": оказалось, что COMVariant::createFromDate( datenull() ) возвращает COMVariant не со значением 01.01.1900 00:00, как можно было бы ожидать, а со значением 01.01.1900 <текущее_время>!

Аналогичные грабли наблюдаются при выводе, если тип поля даты рекордсета задать #adDate (7). Причем, текущее время добавляется хвостом к нормальным ненулевым датам! А вот dateNull() выводится без "хвоста" в виде 0 (!) января 1900 года (00.01.1900).

Вот почему мы любим тип #adDBDate (133), который, как положено, сохраняет только дату без времени

[Владимир Максимов]

Извиняюсь, что поднял старую тему, просто что-то поиском не нашел ответа на вроде бы очевидный вопрос, как при экспорте в Excel через RecordSet оставить ячейки не заполненными? Ну, например, если не указана дата, то в ячейке Excel должно быть пусто, а не 00.01.1900.

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

Суть сводится к тому, что соответствующее поле RecordSet должно содержать значение NULL на момент создания новой строки. Вообще-то, по умолчанию, при создании новой строки в RecordSet, если явно не указано значение, то и предпринимается попытка присвоить значение NULL. Однако, опять же по умолчанию, поле RecordSet создается с невозможностью принимать значение NULL и, как следствие, присваивает "пустое" значение

Допустимость использования значения NULL задается 4 параметром в методе Fields.Append(). 3 параметр - это размерность поля в байтах. Есть еще 5 параметр - значение по умолчанию, но практического смысла в данном случае 5 параметр не имеет.

Тогда код будет выглядеть примерно так

static void Job_ADORecordSet2Excel_EmptyCells(Args _args)
{
    Com comRecordSet;
    Com comFields;
    Com comField;

    ComExcelDocument_RU excel;
    Com                 comRange;
    ;

    #define.adFldIsNullable(32)  // Поле может принимать значение NULL
    #define.adFldMayBeNull(64) // Из поля можно прочитать значение NULL

    comRecordSet = new COM('ADODB.Recordset');
    // формируем структуру Recordset
    // где каждое поле может принимать значение NULL
    comFields = comRecordSet.Fields();
    comFields.Append("FieldStr",    COMVariantType::VT_BSTR,    50, #adFldIsNullable + #adFldMayBeNull);
    comFields.Append("FieldReal",   COMVariantType::VT_R8,      8,  #adFldIsNullable + #adFldMayBeNull);
    // Если нужна только даты без части со временем, то следует указать значение 133
    // Такого значения нет в Base Enum с именем COMVariantType
    comFields.Append("FieldDate",   COMVariantType::VT_DATE,    8,  #adFldIsNullable + #adFldMayBeNull);

    // Открываем RecordSet для заполнения
    comRecordSet.open();

    // Формируем первую строку
    comRecordSet.AddNew();

    // Наполняем ее данными для сравнения
    comField = comFields.Item("FieldStr");
    comField.value(comVariant::createFromStr("Первая строка"));

    comField = comFields.Item("FieldReal");
    comField.value(comVariant::createFromReal(123.456));

    comField = comFields.Item("FieldDate");
    comField.value(comVariant::createFromDate(systemDateGet()));

    // Формируем вторую строку
    // В которой нет никаких данных. Ячейки будут пустыми
    comRecordSet.AddNew();

    // Формируем третью строку
    comRecordSet.AddNew();

    // Наполняем ее данными для сравнения
    comField = comFields.Item("FieldStr");
    comField.value(comVariant::createFromStr("Третья строка"));

    comField = comFields.Item("FieldReal");
    comField.value(comVariant::createFromReal(789.012));

    comField = comFields.Item("FieldDate");
    comField.value(comVariant::createFromDate(systemDateGet()+2));

    // Открываем Excel с пустым листом и сразу делаем его видимым
    excel = new ComExcelDocument_RU();
    excel.newFile("",true);

    // Определяем ячейку, откуда будет осуществляться вывод
    comRange = excel.findRange("A1");

    // Выводим данные
    comRange.CopyFromRecordset(comRecordSet);

}

Собственно, это все работает, если ничего не вводить в поле RecordSet. Но, к сожалению не нашел, а как записать в поле RecordSet значение null ? Единственный, не очень хороший вариант - это присвоение исходного (оригинального) значения, исходя из предположения, что это значение NULL

    comField = comFields.Item("FieldDate");
    comField.value(comField.OriginalValue());

[AndyD]

Например, так

    ComVariant var;
...
    var = new ComVariant(COMVariantInOut::In_out, ComVariantType::VT_BOOL);
    var.variantType(ComVariantType::VT_NULL);
    comField.value(var);
...

Тип создаваемого ComVariant может быть любым. Напрямую VT_NULL в конструкторе можно задать, но создается переменная с типом VT_EMPTY. По-этому, нужен дополнительный вызов

[refined]

У меня такой вопрос возник. Вроде по теме.

После сбора данных из нескольких таблиц в ADODB.Recordset мне нужно вывести их форму на grid, чтобы потом можно было их просмотреть/редактировать в самой DAX. Как это можно сделать?
Ну естественно напрашивается вариант со временной таблицей, ну а будет ли это быстро? И как вообще ее заполнять?

[Gustav]

Цитата:

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

После сбора данных из нескольких таблиц в ADODB.Recordset мне нужно вывести их форму на grid, чтобы потом можно было их просмотреть/редактировать в самой DAX. Как это можно сделать?

Если не зацикливаться на grid'е, то можно посадить на форму ActiveX OWC Spreadsheet и в нем смотреть/редактировать. Spreadsheet поддерживает метод CopyFromRecordset.

[refined]

Я об этом как раз подумал. Но я к сожалению недавно с Axapta знаком. Как вывести значение на форму? Скажем rstAxa (это сам рекордсет).
Уточню вопрос, допустим я делаю

    ExcelSheet = tabExcelSheet.addControl(formControlType::ActiveX, "ExcelSheet");

что дальше то?

[Gustav]

Цитата:

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

Я об этом как раз подумал. Но я к сожалению недавно с Axapta знаком. Как вывести значение на форму? Скажем rstAxa (это сам рекордсет).
Уточню вопрос, допустим я делаю

    ExcelSheet = tabExcelSheet.addControl(formControlType::ActiveX, "ExcelSheet");

что дальше то?

Вам надо сначала посадить ActiveX на форму, а потом населить его данными (CopyFromRecordset). Как посадить этот контрол в диалоговую форму, можно примерно посмотреть здесь: Использование OWC Spreadsheet в диалоге или здесь: Табличный процессор

Но лучше помещать на нормальную форму, сохраненную в AOT (не диалог) - тогда можно будет воспользоваться его событиями. В диалоге же это не получается: События FormActiveXControl не получается использовать в диалоге

[refined]

Да, я как раз пытаюсь через нормальную форму. Я делаю следующим образом:

  reportEngine = element.args().caller();

    super();    
    ExcelSheet = tabExcelSheet.addControl(formControlType::ActiveX, "ExcelSheet");
    ExcelSheet.widthMode(formwidth::ColumnWidth);
    ExcelSheet.heightMode(formHeight::ColumnHeight);
    ExcelSheet.CopyFromRecordset(reportEngine.recordset());

где reportEngine.recordset() это:

COM recordset()
{
    ;
    return rstAxa;
}

но почему-то вываливается ошибка Unable to call method on activeX, wait until object is fully initialized
Всё, я уже разобрался. Добавил еще строчек кода из http://axforum.info/forums/blog.php?b=26
Просто забыл код закомментировать.

[Gustav]

ExcelSheet это FormActiveXControl? Конечно, у него такого метода нет. Метод есть у Range. Надо перейти к нему как-то так:

    COM range;

    range = ExcelSheet.Range("A1");
    range.CopyFromRecordset(...);

Кстати, ExcelSheet у вас точно Spreadsheet? Есть в коде что-то типа такого?:

    ExcelSheet = tabExcelSheet.addControl(formControlType::ActiveX, "ExcelSheet");

    ExcelSheet.className('{0002E541-0000-0000-C000-000000000046}');     // Microsoft Office Spreadsheet 10.0

По моим ссылкам, вроде, всё это расписано...

P.S. ОК! Хорошо, что разобрались.

[refined]

В итоге оказалось, что намного проще сделать вывод не сразу в эксель а просто в форму. И в ней сразу делать сводную таблицу.
Так вот у меня с ней как раз и проблема. Она не отрабатывает как Сводная. Как сделать так чтобы при перетаскивании столбцов, они *схлопывались* автоматически? т.е. осуществлялась группировка по полю (одному, двум). Ну и соответсвенно если это число то выводилась сумма?

И так, вот сам метод выполняемый на форме:
PivotTable = grpPivotTableHolder.addControl(formControlType::ActiveX, "PivotTable");

    className = CCUtil::getOfficePivotTableVersion(true);
    PivotTable.className(className);
    PivotTable.WidthMode(formwidth::ColumnWidth);
    PivotTable.HeightMode(formHeight::ColumnHeight);
    PivotTable.allowFiltering(true);
    PivotTable.AllowGrouping(true);
    PivotTable.DataSource(reportEngine.ADORecordset());

где grpPivotTableHolder собственно сама вкладка на форме.
Смысл в том чтобы не использовать временную таблицу.

[Отшельник]

Господа. Прошу помочь разобраться с этим ADO.
Делаю джоб (самый первый пример из этой темы). Все отрабатывает.
Пытаюсь воспользоваться этим же кодом в Классе, отчете....
во время запуска выдает ошибку на строке

rstAxa = new COM('ADODB.Recordset');

пишет "Объект "COM" не может быть создан"

Почему один и тот же код не работает в отчете, классе?

Цитата:

static void Job_TestADO_2(Args _args)
{
// ----------------------------------------------------------------------------
// ADODB.Recordset в оперативной памяти без привязки к источнику данных
// ----------------------------------------------------------------------------
EmplTable emplTable;
COM rstAxa; // ADO: Recordset
COM flds, fld; // ADO: Fields, Field
COM xlApp; // Excel.Application
COM wbks, wbk; // Workbooks, Workbook
COM wkss, wks; // Worksheets, Worksheet
COM rng, cell, rngCR; // все Range
COM font; // Range.Font
COM entCol; // Range.EntireColumn
COM actWin; // Excel.Application.ActiveWindow
int i, iMax;
// ============================================================================
// СНАЧАЛА ВЛОЖЕННАЯ ФУНКЦИЯ
// ----------------------------------------------------------------------------
// функция для задания типа поля нашего Recordset-а в оперативной памяти
// в данном демо-джобе используется для наглядности
// для простоты используем всего 3 типа данных: число, строка и дата
int adoTypeToExcel(str _type)
{
switch (_type)
{
// используются значения констант перечисления DateTypeEnum из топика TypeProperty (ADO)
// (см. справку по ADO в файле ADO210.CHM - можно поискать на своем компе)
case 'num' : return 5; // adDouble
case 'str' : return 8; // adBSTR
case 'date': return 133; // adDBDate
}
return 8;
}
// ============================================================================
// ТЕПЕРЬ ОСНОВНОЙ ПРОЦЕСС
// Recordset создается в оперативной памяти - без Connection!
rstAxa = new COM('ADODB.Recordset');

[S.Kuskov]

Цитата:

Сообщение от Отшельник

Почему один и тот же код не работает в отчете, классе?

Возможно в одном случае код выполняется на клиенте, а в другом случае - на сервере. И, например, в одном месте компонент (excel) установлен, а в другом - нет.