Axpta table to excel

I want a method to export a table to an excel file.

All rows, all columns , all columns types

Is it possible?

Use Definition Groups or just open the table copy all the data and paste it to the Excel.

Or you can open the table and press ctrl+t (AX2012) and ctrl+e (AX 2009).

Hi Dominic,

In addition to other excellent suggestions, this can also be achieved by X++. Some examples below -

http://www.dynamics-blog.com/2011/05/exporting-data-to-excel-from-axapta-x.html

http://daxguy.blogspot.co.uk/2006/12/exporting-table-field-data-to-excel.html

Not satified with these answers guys. I said all column, all rows. No hardcoding!

These are the parameters I’ll pass to my method

public static void table2Excel(str tableName,str pathAndFileName=’’)

Thank you

Your initial ask was not explanatory. So you might not have got the answers as per your requirement.

If you don’t want to hard code(means when you don’t know the fields), you use the DictTable and DictField classes to loop through the table fields and add them to the Excel http://dynamicsuser.net/forums/t/45206.aspx

You can skip the system fields with the help of dictField.isSystem() and disabled through configuration by using dictField.isSql()

I dont want to be rude but I looking for the complete solution not just links left and right.

Ill give you guys the shell of the method and I need it to be filled in.

public static void table2Excel(str tableName,str pathAndFileName=’’,boolean withColumHeader=false)

{

Common commonFrom ;

;

commonFrom = new DictTable(tablename2id(tableName)).makeRecord();

if(withColumHeader){ //CREATE HEADERS

}

//CREATE LINES

while select common where 1==1

{

}

}

public static void table2Excel(str tableName,str pathAndFileName=’’,boolean withColumHeader=false)

{

SysExcelApplication application;

SysExcelWorkBooks workbooks;

SysExcelWorkBook workbook;

SysExcelWorksheets worksheets;

sysExcelWorksheet worksheet;

SysExcelCells cells;

SysExcelCell cell;

int row,i,j,k,countProgress,countCurrent,dimOfCount;

SysOperationProgress progress = new SysOperationProgress(2);

processInfo processInfo=new processInfo();

DictTable t,t2;

DictField f;

fieldId id,idTo;

FieldName fieldname;

FieldId fieldId;

DictTable dictTableFrom;

DictTable dictTableTo;

Common commonFrom ;

;

if(pathAndFileName==’’)pathAndFileName=‘c:\’+tableName+’.xls’;

t = new DictTable(dbutil::tableId(tableName));

commonFrom = new DictTable(tablename2id(tableName)).makeRecord();

select count(recid) from commonFrom;

processInfo.setTotalToProcess(commonFrom.recid);

progress.setTotal(0,1);

progress.setTotal(0,2);

application = SysExcelApplication::construct();

workbooks = application.workbooks(); //gets the workbook object

workbook = workbooks.add(); // creates a new workbook

worksheets = workbook.worksheets(); //gets the worksheets object

worksheet = worksheets.itemFromNum(1);

cells = worksheet.cells();

cells.range(‘A:A’).numberFormat(’@’);

if(withColumHeader){ //creation des entetes de colonnes

row++;

for (i=1; i<=t.fieldCnt(); i++)

{

id = t.fieldCnt2Id(i);

FieldName = t.fieldName(id);

f = new DictField(dbutil::tableId(tableName),t.fieldCnt2Id(i));

if (f && !f.isSystem()) //si champ exist et n’est pas un champ system tel recid

{

dimOfCount=dimOf( commonFrom.(dbUtil::fieldId(tableId2Name(commonFrom.TableId),FieldName)) );

if(dimOfCount) {

for (k=1; k<=dimOfCount ; ++k)

{

//if(k!=1)i++;

//cell = cells.item(row,i);

//cell.value(’[array]’+FieldName);

}

}

else{

cell = cells.item(row,i);

cell.value(t.fieldName( t.fieldCnt2Id(i) ));

}

}

}

}

j=1;

while select commonFrom where 1==1

{

processInfo.start();

// if(j>20){break;}

j++;

if (t)

{

row++;

for (i=1; i<=t.fieldCnt(); i++)

{

id = t.fieldCnt2Id(i);

FieldName = t.fieldName(id);

f = new DictField(dbutil::tableId(tableName),t.fieldCnt2Id(i));

if (f && !f.isSystem()) //si champ exist et n’est pas un champ system tel recid

{

dimOfCount=dimOf( commonFrom.(dbUtil::fieldId(tableId2Name(commonFrom.TableId),FieldName)) );

if(dimOfCount) {

for (k=1; k<=dimOfCount ; ++k)

{

//NEED SOME MORE WORK HERE

//if(k!=1)i++;

//cell = cells.item(row,i);

//A verifier ca il peux y avoir des array dans array

//cell.value( commonFrom.(fieldId2Ext(fieldname2id( commonFrom.TableId ,FieldName ),k) ));

}

}

else{

cell = cells.item(row,i);

cell.value(commonFrom.(dbUtil::fieldId(tableId2Name(commonFrom.TableId),FieldName)));

}

}

}

}

progress.setText( processInfo.getProcessStatus()+’ lignes copiées’);

processInfo.end();

progress.setText( processInfo.getEstimatedSecondToFinished(),2);

}

// simpleProgress.setText(‘Sauvegarde…’,1);

if(WinApi::fileExists(pathAndFileName))

WinApi::deleteFile(pathAndFileName);

workBook.saveAs(pathAndFileName);

//simpleProgress.setText(‘Fermeture…’,1);

Application.quit();

Application.finalize();

}

Hi Dominic,

Members who reply to your queries don’t get paid for their time. They do this purely to help people such as yourself and out of love for Dynamics products.

Please do not expect members of this forum to do your job for you. And please be courteous to members.