Участник
|
Не слушайте его. Он все врет.
В общем моя ошибка - пропустил один из параметров, в результате Range передавался не как FieldInfo, а как OtherChar.
Время выгрузки - 20с
X++: static void ExportExcelTXT1(Args _args)
{
LedgerTrans ledgerTrans;
LedgerTable ledgerTable;
int timeFullStart, timeFullFinish, timeFullTotal;
int cnt = 0;
str Buf, s;
ComExcelDocument_Ru excel;
COM doc;
COM app;
TextBuffer tb;
COM Workbook;
COM Workbooks;
Array fields;
Com sheet;
Com range;
str leftBrace;
str rightBrace;
str columnSeparator;
str rowSeparator;
#define.xlDelimited(0x00000001)
#define.xlDoubleQuote(0x00000001)
#define.fileName("d:\\temp\\demo.txt")
void AddField(int fldNum, TableId tbl, FieldId fld)
{
DictField dictField;
Array field;
#define.xlGeneralFormat(0x00000001)
#define.xlTextFormat(0x00000002)
#define.xlMDYFormat(0x00000003)
#define.xlDMYFormat(0x00000004)
#define.xlYMDFormat(0x00000005)
#define.xlMYDFormat(0x00000006)
#define.xlDYMFormat(0x00000007)
#define.xlYDMFormat(0x00000008)
#define.xlSkipColumn(0x00000009)
;
if (!fields)
fields = new Array(Types::Class);
dictField = new dictField( tbl, fld );
field = new Array(Types::Integer);
field.value(1, fldNum);
if (dictField)
{
switch (dictField.baseType())
{
case Types::Date:
field.value(2, #xlDMYFormat);
break;
case Types::Enum, Types::String:
field.value(2, #xlTextFormat);
break;
default:
field.value(2, #xlGeneralFormat);
}
}
else
field.value(2, #xlGeneralFormat);
fields.value((fields.lastIndex()+1), field);
}
str getFields()
{
str ret = "";
int i;
Array field;
;
if (!fields) return "";
for (i=1; i<= fields.lastIndex(); i++)
{
field = fields.value(i);
if (field)
ret += (ret ? strfmt("%1 ", rowSeparator) : "") +
strfmt("%1%3%2", field.value(1), field.value(2), columnSeparator);
}
return ret;
}
void ArrayLocal(COM xlApp)
{
COMVariant var;
str alternateArraySeparator;
str decimalSeparator;
#define.xlDecimalSeparator(0x00000003)
#define.xlLeftBrace(0x0000000C)
#define.xlRightBrace(0x0000000D)
#define.xlColumnSeparator(0x0000000E)
#define.xlRowSeparator(0x0000000F)
#define.xlAlternateArraySeparator(0x00000010)
;
if (!xlApp)
{
leftBrace = "{";
rightBrace = "}";
columnSeparator = ";";
rowSeparator = ":";
return;
}
var = xlApp.International(#xlDecimalSeparator);
if (var)
decimalSeparator = var.bStr();
else
decimalSeparator = ",";
var = xlApp.International(#xlLeftBrace);
if (var)
leftBrace = var.bStr();
else
leftBrace = "{";
var = xlApp.International(#xlRightBrace);
if (var)
rightBrace = var.bStr();
else
rightBrace = "}";
var = xlApp.International(#xlColumnSeparator);
if (var)
columnSeparator = var.bStr();
else
columnSeparator = ";";
var = xlApp.International(#xlRowSeparator);
if (var)
rowSeparator = var.bStr();
else
rowSeparator = ":";
var = xlApp.International(#xlAlternateArraySeparator);
if (var)
alternateArraySeparator = var.bStr();
else
alternateArraySeparator = "&";
columnSeparator = (columnSeparator == decimalSeparator ? alternateArraySeparator : columnSeparator);
rowSeparator = (rowSeparator == decimalSeparator ? alternateArraySeparator : rowSeparator);
}
// засекаем время
timeFullStart = timenow();
excel = new ComExcelDocument_Ru();
excel.newFile("", false);
doc = excel.getComDocument();
app = doc.application();
ArrayLocal(app);
buf = "RecId\tAccountNum\tAccountName\tAccountPlType\tBondBatchTrans_RU\tBondBatch_RU\tTransDate\tTxt\tAmountMST\tCrediting\n";
cnt++;
while select ledgerTrans
join ledgerTable
where ledgerTrans.AccountNum == ledgerTable.AccountNum
&& ledgerTrans.TransDate >= str2date('dd.mm.yy',123) && ledgerTrans.TransDate <= str2date('DD.MM.YY',123)
{
// 10 полей
buf +=
strfmt(
"%1\t%2\t%3\t%4\t%5\t%6\t%7\t%8\t%9\t%10\n",
ledgerTrans.RecId, ledgerTrans.AccountNum, ledgerTable.AccountName, ledgerTable.AccountPlType,
ledgerTrans.BondBatchTrans_RU, ledgerTrans.BondBatch_RU,
date2str(ledgerTrans.TransDate, 123, 2, 2, 2, 2, 4), ledgerTrans.Txt,
strltrim(strrem(num2str(ledgerTrans.AmountMST, 10, 2, 2, 3), "+")), ledgerTrans.Crediting);
cnt++;
if (cnt >=65000) break;
}
tb = new TextBuffer();
tb.setText(buf);
tb.toFile(#fileName);
addField( 1, ledgerTrans.TableId, fieldnum(ledgerTrans, RecId));
addField( 2, ledgerTrans.TableId, fieldnum(ledgerTrans, AccountNum));
addField( 3, ledgerTable.TableId, fieldnum(ledgerTable, AccountName));
addField( 4, ledgerTable.TableId, fieldnum(ledgerTable, AccountPlType));
addField( 5, ledgerTrans.TableId, fieldnum(ledgerTrans, BondBatchTrans_RU));
addField( 6, ledgerTrans.TableId, fieldnum(ledgerTrans, BondBatch_RU));
addField( 7, ledgerTrans.TableId, fieldnum(ledgerTrans, TransDate));
addField( 8, ledgerTrans.TableId, fieldnum(ledgerTrans, Txt));
addField( 9, ledgerTrans.TableId, fieldnum(ledgerTrans, AmountMST));
addField(10, ledgerTrans.TableId, fieldnum(ledgerTrans, Crediting));
Workbook = app.ActiveWorkbook();
sheet = app.ActiveSheet();
range = sheet.range("A1:B10");
s = strfmt("=%1%3%2", leftBrace, rightBrace, getFields());
range.FormulaArray(s);
Workbooks = app.Workbooks();
Workbooks.OpenText(#fileName, COMVariant::createFromInt(1251), 1,
#xlDelimited, #xlDoubleQuote, False, True, False, False, False, False, "",
range, True, ",", " ");
App.DisplayAlerts(false);
Workbook.Close();
App.DisplayAlerts(true);
excel.visible(true);
timeFullFinish = timenow();
timeFullTotal = timeFullFinish - timeFullStart;
info('Время выполнения, сек');
info(int2str(timeFullTotal));
} PS Исправил для поддержки английской и русской версий Excel
__________________
Axapta v.3.0 sp5 kr2
Последний раз редактировалось AndyD; 22.06.2006 в 17:26.
|