Tuesday 12 August 2014

Export to excel in AX using X++

Export AX data to excel file using X++ code and with a directory select option.
Use the following code:

static void Export_to_excel(Args _args)
{
   //Excel classes
   SysExcelApplication          xlsApplication;
   SysExcelWorkBooks            xlsWorkBookCollection;
   SysExcelWorkBook             xlsWorkBook;
   SysExcelWorkSheets           xlsWorkSheetCollection;
   SysExcelWorkSheet            xlsWorkSheet;
   SysExcelRange                xlsRange;
   //tables used
   CustTable                    custTable;
   BILLDetailsTable             billdetailstable;
   CustTransHistoryTable        custtranshisttable;
   CustTransHistoryLines        custtranshistlines;
   //variables
   int                          row = 1;
   str                          fileName;
   transdate                    frmdate,todate;
   Area                          area;
   //Dialog instances
   Dialog                       dlg;
   DialogGroup                  dlgGroup;
   DialogField                  digfield,digfield1,digfield2;
   DialogField                  dialogFilename;
   ;
 
    dlg         = new Dialog("AX Export");
    dlgGroup    = dlg.addGroup("Enter details");
    digfield    = dlg.addField(TypeID(transdate),"From Date");
    digfield1   = dlg.addField(TypeID(transdate),"To Date");
    digfield2   = dlg.addField(TypeID(MIDCarea),"Area");
     //this will provide directory window where you can save the file.Don't forget to write a filename at the end of the path.
     //e.g C:\newexcel.xls
    dialogFilename = dlg.addFieldValue(typeid(Filepath),filename);

    dlg.run();
    frmdate     =digfield.value();
    todate      =digfield1.value();
    area        =digfield2.value();

    if(!frmdate||!todate||!area)//validating dialog values
    {
     throw error("Enter complete data");
    }

     //Filename
   fileName = dialogFilename.value();
   //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("CustAccount");
   xlsWorkSheet.cells().item(row,2).value("PaymentVoucher");
   xlsWorkSheet.cells().item(row,3).value("ReceiptDate");
   row++;
   //newcode


    while select  custtranshisttable
    order by custtranshisttable.CustAccount
    where custtranshisttable.Area == area
    && custtranshisttable.ReceiptDate >= frmdate
    && custtranshisttable.ReceiptDate <= todate
    {
    while select sum(amountsettled),ItemId,InvoiceId from custtranshistlines
    group by custtranshistlines.ItemId
    where custtranshistlines.PaymentReceiptID == custtranshisttable.PaymentReceiptID
    && custtranshistlines.InvoiceId == custtranshisttable.InvoiceNo//current
            {
                    xlsWorkSheet.cells().item(row,1).value(custtranshisttable.CustAccount);
                    xlsWorkSheet.cells().item(row,2).value(custtranshisttable.PaymentVoucher);
                    xlsWorkSheet.cells().item(row,3).value(custtranshisttable.ReceiptDate);
                    row++;//current row is complete now incrementing to new row.
            }
    }
   //Check for duplicate files
   if(WinApi::fileExists(fileName))
      WinApi::deleteFile(fileName);//if found delete it
   //Save Excel document
   xlsWorkbook.saveAs(fileName);
   //Open Excel document
   xlsApplication.visible(true);
   //Close Excel
   xlsApplication.quit();
   xlsApplication.finalize();
}

Sorry for bad editting :P

Thanks

No comments:

Post a Comment