March 14, 2013

Exporting data to Excel from axapta x++

Exporting data to Excel from axapta x++





Hi All!
Sometimes we need to export data from Microsoft Dynamics AX to Excel using axapta x++ code and we don't know how to do this...
Exists some differents ways to do this, but I think the best way is using the SysExcel class of Dynamics AX and its related.
The only problem I found using this class... is that it can not be used in a batch process.
Sample code:


static void TheaxaptaCreateExcel(Args _args)
{
   SysExcelApplication  xlsApplication;
   SysExcelWorkBooks    xlsWorkBookCollection;
   SysExcelWorkBook     xlsWorkBook;
   SysExcelWorkSheets   xlsWorkSheetCollection;
   SysExcelWorkSheet    xlsWorkSheet;
   SysExcelRange        xlsRange;
   CustTable            custTable;
   int                  row = 1;
   str                  fileName;
   ;
   //Filename
   fileName = "C:\\Test.xlsx";
   //Initialize Excel instance
   xlsApplication           = SysExcelApplication::construct();
   //Open Excel document
   //xlsApplication.visible(true);
   //Create Excel WorkBook and WorkSheet
   xlsWorkBookCollection    = xlsApplication.workbooks();
   xlsWorkBook              = xlsWorkBookCollection.add();
   xlsWorkSheetCollection   = xlsWorkBook.worksheets();
   xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);
   //Excel columns captions
   xlsWorkSheet.cells().item(row,1).value("Account Num");
   xlsWorkSheet.cells().item(row,2).value("Name");
   row++;
   //Fill Excel with CustTable AccountNum and Name fields (only 20 records)
   while select custTable
   {
      if(row == 20)
        break;
      xlsWorkSheet.cells().item(row,1).value(custTable.AccountNum);
      xlsWorkSheet.cells().item(row,2).value(custTable.Name);
      row++;
   }
   //Check whether the document already exists
   if(WinApi::fileExists(fileName))
      WinApi::deleteFile(fileName);
   //Save Excel document
   xlsWorkbook.saveAs(fileName);
   //Open Excel document
   xlsApplication.visible(true);
   //Close Excel
   //xlsApplication.quit();
   //xlsApplication.finalize();
}

-Harry

9 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. any idea to export excel in batch mode.

    ReplyDelete
  3. Try ti write a class for the same. Make it batch able and than try.

    ReplyDelete
  4. Thanks for sharing such useful code... Keep it up dude.... :)

    ReplyDelete
  5. i created a job and copied this code ,While running it shows error,

    Method 'saveAs' in COM object of class '_Workbook' returned error code 0x800A03EC () which means: Microsoft Excel cannot access the file 'C:\419F4170'. There are several possible reasons: pls advice meee...

    ReplyDelete
    Replies
    1. Check your following
      1. File Name and path
      2. Permissions on file
      3. MS office extension must be install
      4. MS version and File version

      Delete
    2. Mani
      I know it's 4 years later, but did you find a solution?

      Delete
  6. Thanks...its very great and helpful.........
    - Prashant K.

    ReplyDelete

Thanks