Строка в Excel - DAX: Программирование

Antoncheg · 12.03.2009, 10:50

Всем доброго времени суток! Третий день бьюсь над проблемой экспорта большого количества символов в Excel. Суть проблемы следующая. При формировании отчетов, берется набор серийных номеров (с помощью метода) с группировкой по номенклатуре (серийные номера выводятся в строку через запятую). Длина одного серийного номера-13 символов. При попытке экспорта в Excel информация выводится корректно, но до достижения количества символов строки определенной длины (например, если продано 100 экземпляров одной номенклатуры, по сути должно быть 1300 символов серийных номеров+пробелы+запятые...). Читал топик "Поговорим об ADO". Попытался в коде

X++:

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
        case 'memo': return 203; // adLongVarChar
    }
    return 203;
}

.

прописать данный набор данных.Если указать набор серийных номеров как str - оставляет строки (более 1300 симв) пустыми. Если указать, как мемо выдает ошибку Метод 'Append' в COM-объекте класса 'Fields' возвратил код ошибки 0x800A0BB9 (<неизвестно>), который означает: Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
Есть мнения? Заранее благодарен!

**Gustav** · 12.03.2009, 11:17

А поле рекордсета какой тип имеет? 8 ?

Antoncheg · 12.03.2009, 11:19

Цитата:

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

А поле рекордсета какой тип имеет? 8 ?

203

**Gustav** · 12.03.2009, 11:22

а 8 пробовали?

Antoncheg · 12.03.2009, 11:28

Цитата:

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

а 8 пробовали?

Вот полностью код, думаю, что Class Declaration не интересует. Строка под названием "Серийные номера". Если описать ее как str - некоторые строки остаются пустыми, если как мемо, вылетает ошибка, указанная выше (Кстати AX 3.0, SP3, SQL 2005).

X++:

void Exec(str Path,SnQueryTableTmp SnQ)
{
   ;


    xlApp = new COM('Excel.Application');
    xlApp.Visible(true);

    wbks = xlApp.Workbooks();
    wbk = wbks.Add();
    wkss = wbk.Worksheets();
    wks = wkss.Add();

    wks  = wkss.Item(1);
    //wks2 = wkss.Item(2);
    //wks3 = wkss.Item(3);

    wks.Name('Итог');
    //wks2.Name('Суммы по дилерам');
    //wks3.Name('Склад');

    rng = wks.Range('A1');
    //rng2= wks2.Range('A1');
    //rng3= wks3.Range('A1');

//==================================Первая вкладка=======================
    rstAxa = new COM('ADODB.Recordset');
    flds = rstAxa.Fields();

    flds.Append('Дата закрытия'                  ,this.adoTypeToExcel('date'));
    flds.Append('Отгрузочная накладная'          ,this.adoTypeToExcel('str'));
    flds.Append('Заказ'                          ,this.adoTypeToExcel('str'));
    flds.Append('Безналичная накладная'          ,this.adoTypeToExcel('str'));
    flds.Append('Менеджер'                       ,this.adoTypeToExcel('str'));
    flds.Append('Код клиента'                    ,this.adoTypeToExcel('str'));
    flds.Append('Дата отгрузочной накладной'     ,this.adoTypeToExcel('date'));
    flds.Append('Город'                          ,this.adoTypeToExcel('str'));
    flds.Append('Краткое наименование'           ,this.adoTypeToExcel('str'));
    flds.Append('Номенклатура'                   ,this.adoTypeToExcel('str'));
    flds.Append('Код дет. в спецификации'        ,this.adoTypeToExcel('str'));
    flds.Append('Название'                       ,this.adoTypeToExcel('str'));
    flds.Append('Склад'                          ,this.adoTypeToExcel('str'));
    flds.Append('Цена ед.'                       ,this.adoTypeToExcel('num'));
    flds.Append('Себестоимость'                  ,this.adoTypeToExcel('num'));
    flds.Append('Количество'                     ,this.adoTypeToExcel('num'));
    flds.Append('Сумма по позиции'               ,this.adoTypeToExcel('num'));
    flds.Append('Прибыль'                        ,this.adoTypeToExcel('num'));
    flds.Append('Каталожный номер'               ,this.adoTypeToExcel('str'));
    flds.Append('Серийные номера'                ,this.adoTypeToExcel('memo'));
    flds.Append('REF'                            ,this.adoTypeToExcel('str'));
    flds.Append('Квота'                          ,this.adoTypeToExcel('str'));
    flds.Append('Конечный клиент'                ,this.adoTypeToExcel('str'));
    flds.Append('Номер партии'                   ,this.adoTypeToExcel('str'));

    rstAxa.Open();

   // SnQ.setTmpData(SnQ);

    while select SnQ

    {
        rstAxa.AddNew();

     //   fld = flds.Item('Каталожный номер');            fld.Value(SelloutMsTable.A_PartNumber);
    fld = flds.Item('Дата закрытия');                   fld.Value(SnQ.DeliveryDate);
    fld = flds.Item('Отгрузочная накладная');           fld.Value(SnQ.PickingListId);
    fld = flds.Item('Заказ');                           fld.Value(SnQ.SalesId);
    fld = flds.Item('Безналичная накладная');           fld.Value(SnQ.InvoiceIdElect);
    fld = flds.Item('Менеджер');                        fld.Value(SnQ.SalesResponsible);
    fld = flds.Item('Код клиента');                     fld.Value(SnQ.CustAccount);
    fld = flds.Item('Дата отгрузочной накладной');      fld.Value(SnQ.PickingListDate);
    fld = flds.Item('Город');                           fld.Value(SnQ.City);
    fld = flds.Item('Краткое наименование');            fld.Value(SnQ.NameAlial);
    fld = flds.Item('Номенклатура');                    fld.value(SnQ.ItemId);
    fld = flds.Item('Код дет. в спецификации');         fld.Value(SnQ.ChosenPartNumber);
    fld = flds.Item('Название');                        fld.Value(SnQ.ItemName);
    fld = flds.Item('Склад');                           fld.Value(SnQ.InventLocationId);
    fld = flds.Item('Цена ед.');                        fld.Value(SnQ.A_SalesPrice);
    fld = flds.Item('Себестоимость');                   fld.Value(SnQ.SalesCostPrice);
    fld = flds.Item('Количество');                      fld.Value(SnQ.Qty);
    fld = flds.Item('Сумма по позиции');                fld.Value(SnQ.Amount);
    fld = flds.Item('Прибыль');                         fld.Value(SnQ.SalesProfit);
    fld = flds.Item('Каталожный номер');                fld.Value(SnQ.A_PartNumber);
    fld = flds.Item('Серийные номера');                 fld.Value(SnQ.findSerialNumbersFromWMS(SnQ));
    fld = flds.Item('REF');                             fld.Value(SnQ.REF);
    fld = flds.Item('Квота');                           fld.value(SnQ.InventQuotationId);
    fld = flds.Item('Конечный клиент');                 fld.Value(SnQ.EndPartnerCode);
    fld = flds.Item('Номер партии');                    fld.Value(SnQ.InventBatchId);

    rstAxa.Update();
    }

    iMax = flds.Count() - 1;
    for (i = 0; i <= iMax; i++)
    {
        fld = flds.Item(i);
        cell = rng.Offset(0,i);
        cell.Value2(fld.Name());
    }
    cell = rng.Offset(1,0);
    cell.CopyFromRecordset(rstAxa);

    rstAxa.close();
    actWin = xlApp.ActiveWindow();
  //  actWin.FreezePanes(true);

}

**Gustav** · 12.03.2009, 11:41

Следующий код у меня нормально выводит 2400 символов в ячейку:

X++:

static void Job148(Args _args)
{
    COM rstAxa;
    COM flds, fld;
    COM xlApp;
    COM wbks, wbk;
    COM wkss, wks;
    COM rng;

    str strValue;

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

    strValue = '1234567890123, 1234567890123, 1234567890123, 1234567890123, 1234567890123, 1234567890123, 1234567890123, 1234567890123, 1234567890123, 1234567890123, ';
    strValue += strValue; // 20 значений
    strValue += strValue; // 40 значений
    strValue += strValue; // 80 значений
    strValue += strValue; // 160 значений (2400 символов)

    rstAxa = new COM('ADODB.Recordset');
    flds = rstAxa.Fields();
    flds.Append('strValue', adoTypeToExcel('str' ));
    rstAxa.Open();

    rstAxa.AddNew();
    fld = flds.Item('strValue'); fld.Value(strValue);
    rstAxa.Update();

    xlApp = new COM('Excel.Application');
    wbks = xlApp.Workbooks();
    wbk  = wbks.Add();
    wkss = wbk.Worksheets();
    wks  = wkss.Item(1);
    rng  = wks.Range('A1');

    rng.CopyFromRecordset(rstAxa);
    rstAxa.Close();
    xlApp.Visible(true);
}

Может быть, с методом формирования строки серийников какие-нибудь проблемы? Может, попробовать параллельно выводить серийники в инфолог?

Antoncheg · 12.03.2009, 11:46

X++:

str findSerialNumbersFromWMS(SnQueryTableTmp _SnQ)
{

 WMSRoute     WMSRoute;
    SalesLine   SalesLine;
    InventSerialNumbers InventSerialNumbers;
    ItemFreeTxt txt = "";
    ;
    while select InventSerialNumbers
    exists join WMSRoute
        where (WMSRoute.WMSShippingId like InventSerialNumbers.DocId)
        && (WMSRoute.LineNum == InventSerialNumbers.DocLineNum)
        && (WMSRoute.ItemId == InventSerialNumbers.ItemId)
    exists join SalesLine
        where (SalesLine.RecId == WMSRoute.SalesLineRecId)
        && (SalesLine.ItemId == _SnQ.Itemid)
        && (SalesLine.SalesId == _SnQ.SalesId)
    {
        txt += ","+InventSerialNumbers.SerialId ;//+ " -> " + num2str(InventSerialNumbers.Qty,1,0,1,0) + "шт. ";
    }
    return txt;

}

Вот метод, который ищет серийники. С выводом их проблем нет. Все таки, я грешу на то, что не хватает длины строки. Тем более, что в отгрузку может попасть партия из 300 единиц одной номенклатуры и 500, что подразумевает более объемного формата строки.

**Gustav** · 12.03.2009, 12:11

Хм... интересный какой эффект: если добавить еще пару полей (в моем джобе):

X++:

flds.Append('strValue2', adoTypeToExcel('str' ));
flds.Append('strValue3', adoTypeToExcel('str' ));
.........
fld = flds.Item('strValue2'); fld.Value(strValue);
fld = flds.Item('strValue3'); fld.Value(strValue);

то вылетает с ошибкой. Если уменьшить кол-во серийников до 40, то нормально проходят все 3 поля.

Дальше - больше: если оставить одно поле, но добавить пару записей, то тоже вылетает с ошибкой (пока не закомментируем строки, где 80 и 160 серийников). Получается, что чувствительно и к "горизонтальным" и к "вертикальным" добавлениям.

НО в тоже время позволяется вывести в одно поле одной записи очень много символов (до ограничения Excel в 32 767 символов в одной ячейке).

Antoncheg · 12.03.2009, 12:17

Цитата:

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

Хм... интересный какой эффект: если добавить еще пару полей (в моем джобе):

X++:

flds.Append('strValue2', adoTypeToExcel('str' ));
flds.Append('strValue3', adoTypeToExcel('str' ));
.........
fld = flds.Item('strValue2'); fld.Value(strValue);
fld = flds.Item('strValue3'); fld.Value(strValue);

то вылетает с ошибкой. Если уменьшить кол-во серийников до 40, то нормально проходят все 3 поля.

Дальше - больше: если оставить одно поле, но добавить пару записей, то тоже вылетает с ошибкой (пока не закомментируем строки, где 80 и 160 серийников). Получается, что чувствительно и к "горизонтальным" и к "вертикальным" добавлениям.

НО в тоже время позволяется вывести в одно поле одной записи очень много символов (до ограничения Excel в 32 767 символов в одной ячейке).

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

Antoncheg · 12.03.2009, 12:25

Есть предположение попробовать Excel 2007. Буду копать.

**Gustav** · 12.03.2009, 13:26

Как выясняется, CopyFromRecordset загибается при длине строки более 911 символов (просто какой-то телефонный "привет" от службы спасения!

)

О проблеме знают в мире: http://www.google.ru/search?hl=ru&q=...1&lr=&aq=f&oq=
но пока ничего утешительного я не нашёл.

Antoncheg · 12.03.2009, 13:33

Цитата:

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

Как выясняется, CopyFromRecordset загибается при длине строки более 911 символов (просто какой-то телефонный "привет" от службы спасения!

)

О проблеме знают в мире: http://www.google.ru/search?hl=ru&q=...1&lr=&aq=f&oq=
но пока ничего утешительного я не нашёл.

Вот что пишут по этому поводу в первой гугловской ссылке
Using ADO (ActiveX 2.8 Object Lib) with Excel 2003, I extract data from a
worksheet and copy it to another worksheet in the same workbook.
In Office 2007 everything is fine, but in Office XP and Office 2003 the
CopyFromRecordset fails if a field has more than 911 bytes.

Т.е. в 2007 офисе эта проблема исправлена...

Antoncheg · 12.03.2009, 13:42

Но использовать 2007 офис - дополнительные затраты на лицензию для конечного пользователя, что крайне нежелательно, да еще и при нынешней экономической обстановке. Может быть попробовать какой-нибудь другой метод экспорта? Есть мнения?

**Gustav** · 12.03.2009, 13:50

Цитата:

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

Т.е. в 2007 офисе эта проблема исправлена...

Получается, что так. Интересно, что ее также нет в более ранней Excel 2000 - я проверил, запустив там такой VBA-код с http://support.microsoft.com/kb/818808/en-us :

Код:

Sub XLTest()
Dim aValues(4)
 
  aValues(0) = "Test1"
  aValues(1) = "Test2"
  aValues(2) = "Test3"
 
  MsgBox "First the Good range set."
  aValues(3) = String(911, 65)
  
  Range("A1:D1").Value = aValues
 
  MsgBox "Now the bad range set."
  aValues(3) = String(912, 66)
  Range("A2:D2").Value = aValues 'вызывает ошибку
 
End Sub

Отмечу, что проблема существует лишь для массивов (очевидно, CopyFromRecordset тоже ими оперирует). Если выполнить оператор с простым (не "массивным") присвоением, то всё ОК:

Код:

Range("A2").Value = String(912, 66) ' нормально поместит 912 букв B в ячейку

**Gustav** · 12.03.2009, 13:57

Цитата:

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

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

Что если попробовать нарезать ваши 500 серийников на 10 колонок по 60, а в 11-й собрать при помощи формулы?

Цитата:

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

Может быть попробовать какой-нибудь другой метод экспорта? Есть мнения?

Любой, который пишет поячеечно. Например, при помощи класса ComExcelDocument_RU. Если у вас не десятки тысяч записей (иначе будет долго), то можно попробовать.

Antoncheg · 12.03.2009, 14:08

Цитата:

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

Что если попробовать нарезать ваши 500 серийников на 10 колонок по 60, а в 11-й собрать при помощи формулы?
Любой, который пишет поячеечно. Например, при помощи класса ComExcelDocument_RU. Если у вас не десятки тысяч записей (иначе будет долго), то можно попробовать.

ок, спасибо, буду пробовать. Нет, такого количества строк не набирается, отчет снимается ежедневно, поэтому ComExcelDocument_RU можно рассмотреть как альтернативу.

**Gustav** · 12.03.2009, 15:13

Цитата:

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

Что если попробовать нарезать ваши 500 серийников на 10 колонок по 60, а в 11-й собрать при помощи формулы?

Вот, воплотил идею в примере. По окончании работы в ячейках K1 и K2 (11-я колонка) - честные строки из 600 серийников (9000 символов).

X++:

static void Job148(Args _args)
{
    COM rstAxa;
    COM flds, fld;
    COM xlApp;
    COM wbks, wbk;
    COM wkss, wks;
    COM rng;

    str strValue;
    str strFormula;
    COMVariant sep;

    #define.xlPasteValues(-4163)
    #define.xlListSeparator(5)

    int adoTypeToExcel(str _type)
    {
        switch (_type)
        {
            case 'num' : return   5; // adDouble
            case 'str' : return   8; // adBSTR
            case 'date': return 133; // adDBDate
        }
        return 8;
    }
    ;

    rstAxa = new COM('ADODB.Recordset');
    flds = rstAxa.Fields();
    flds.Append('str1' , adoTypeToExcel('str' ));
    flds.Append('str2' , adoTypeToExcel('str' ));
    flds.Append('str3' , adoTypeToExcel('str' ));
    flds.Append('str4' , adoTypeToExcel('str' ));
    flds.Append('str5' , adoTypeToExcel('str' ));
    flds.Append('str6' , adoTypeToExcel('str' ));
    flds.Append('str7' , adoTypeToExcel('str' ));
    flds.Append('str8' , adoTypeToExcel('str' ));
    flds.Append('str9' , adoTypeToExcel('str' ));
    flds.Append('str10', adoTypeToExcel('str' ));
    flds.Append('str11', adoTypeToExcel('str' ));
    rstAxa.Open();

    xlApp = new COM('Excel.Application');

    // создаем формулу с правильным разделителем списка (запятой или точкой с запятой)
    sep = xlApp.International(#xlListSeparator);
    strFormula = strFmt('=CONCATENATE(RC[-10]%1RC[-9]%1RC[-8]%1RC[-7]%1RC[-6]%1RC[-5]%1RC[-4]%1RC[-3]%1RC[-2]%1RC[-1])',
                 sep.bStr());

    strValue = strRep('1234567890123, ', 60);

    rstAxa.AddNew();
    fld = flds.Item('str1' ); fld.Value(strValue);
    fld = flds.Item('str2' ); fld.Value(strValue);
    fld = flds.Item('str3' ); fld.Value(strValue);
    fld = flds.Item('str4' ); fld.Value(strValue);
    fld = flds.Item('str5' ); fld.Value(strValue);
    fld = flds.Item('str6' ); fld.Value(strValue);
    fld = flds.Item('str7' ); fld.Value(strValue);
    fld = flds.Item('str8' ); fld.Value(strValue);
    fld = flds.Item('str9' ); fld.Value(strValue);
    fld = flds.Item('str10'); fld.Value(strValue);
    fld = flds.Item('str11'); fld.Value(strFormula);
    rstAxa.Update();

    strValue = strRep('ABCDEFGHIJKLM, ', 60);

    rstAxa.AddNew();
    fld = flds.Item('str1' ); fld.Value(strValue);
    fld = flds.Item('str2' ); fld.Value(strValue);
    fld = flds.Item('str3' ); fld.Value(strValue);
    fld = flds.Item('str4' ); fld.Value(strValue);
    fld = flds.Item('str5' ); fld.Value(strValue);
    fld = flds.Item('str6' ); fld.Value(strValue);
    fld = flds.Item('str7' ); fld.Value(strValue);
    fld = flds.Item('str8' ); fld.Value(strValue);
    fld = flds.Item('str9' ); fld.Value(strValue);
    fld = flds.Item('str10'); fld.Value(strValue);
    fld = flds.Item('str11'); fld.Value(strFormula);
    rstAxa.Update();

    wbks = xlApp.Workbooks();
    wbk  = wbks.Add();
    wkss = wbk.Worksheets();
    wks  = wkss.Item(1);
    rng  = wks.Range('A1');

    rng.CopyFromRecordset(rstAxa);

    // оживление формул и вычисление значений
    rng  = wks.Range('K1:K2');
    rng.FormulaR1C1( rng.Value2() );
    rng.Copy();
    rng.PasteSpecial(#xlPasteValues);
    xlApp.CutCopyMode(false);

    rng = wks.Columns();
    rng = COM::createFromVariant(rng.Item('A:J'));
    rng.Delete();

    rstAxa.Close();
    xlApp.Visible(true);
}

P.S. Ну, и чтобы совсем скрыться от пользователя с этими манипуляциями, можно после "оживления формул" удалить 10 колонок - от A до J:

X++:

    rng = wks.Columns();
    rng = COM::createFromVariant(rng.Item('A:J'));
    rng.Delete();

P.P.S. Добавил этот фрагмент в джоб. Так же как и фрагмент, определяющий разделитель списка Excel (cм. ниже). Теперь результаты окончательно находятся не в ячейках K1 и K2, а в ячейках A1 и A2 (из-за удаления колонок A:J).

Antoncheg · 12.03.2009, 16:19

Метод 'FormulaR1C1' в COM-объекте класса 'Range' возвратил код ошибки 0x800A03EC (<неизвестно>), который означает: <неизвестно>.

**Gustav** · 12.03.2009, 16:32

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

X++:

    str strFormula;
    COMVariant sep;
    ..........
    xlApp = new COM('Excel.Application');

    // создаем формулу с правильным разделителем списка (запятой или точкой с запятой)
    sep = xlApp.International(#xlListSeparator);
    strFormula = strFmt('=CONCATENATE(RC[-10]%1RC[-9]%1RC[-8]%1RC[-7]%1RC[-6]%1RC[-5]%1RC[-4]%1RC[-3]%1RC[-2]%1RC[-1])',
                 sep.bStr());
    ..........
    fld = flds.Item('str11'); fld.Value(strFormula);

У меня в хозяйстве есть даже отдельный статический метод (для Global):

X++:

// определение разделителя списка Excel
// нужно для диапазонов вида Range('C:C,F:F')
// Excel берем параметром из контекста задачи,
// чтобы не загружать отдельную сессию из-за единственного символа
static str excelListSeparator(COM _excelApplication)
{
    #define.xlListSeparator(5)
    COMVariant sep = _excelApplication.International(#xlListSeparator);
    ;
    return sep.bStr();
}

Antoncheg · 12.03.2009, 17:28

То Gustav,
огромное спасибо, все работает за исключением последнего куска, выводится файл .xls, но данные остаются в ячейках A1 и A2

Похожие темы
Тема	Автор	Раздел	Ответов	Посл. сообщение
Исследование скорости экспорта данных из Axapta в Excel (коллективный эксперимент)	Gustav	DAX: База знаний и проекты	79	13.02.2014 13:18
[Excel] - Несколько версий Excel на машине клиента	Андре	DAX: Программирование	11	07.08.2007 13:45
Вызов Item() для коллекций Excel	Владимир Максимов	DAX: Программирование	15	17.08.2006 19:47
"Такая строка уже сущ." при переносе из Excel!	Zelenhof	DAX: Программирование	2	13.01.2003 13:41