Monday, 7 December 2015

Excel Importing from MSD AX using x++ code

static void Testing_ExcelImporting(Args _args)
{
    //Excel Classes Declaration
   SysExcelApplication          xlsApplication;
   SysExcelWorkBooks            xlsWorkBookCollection;
   SysExcelWorkBook             xlsWorkBook;
   SysExcelWorkSheets           xlsWorkSheetCollection;
   SysExcelWorkSheet            xlsWorkSheet;
   SysExcelRange                xlsRange;

   //Tables Buffer
   TestingTable                 testingTable;
   tmpTestingTable              tmpTestingTable;

   //Variables
   int                          row = 1;
   counter                      c=0;
   str                          fileName;
   transdate                    fromdate,todate;
   FileName                     fileNametoSave;

   //Dialog instances
   Dialog                       dlg;
   DialogGroup                  dlgGroup;
   DialogField                  digfield,digfield1,digfield2;
   DialogField                  dialogFilename;
   ;

    Box::warning("Please save your work and close all excel sheets and run this Excel report");
    dlg         = new Dialog("AX Export to Excel");
    dlg.addText("Please save your work and close all excel sheets and run this Excel report");
    dlgGroup    = dlg.addGroup("Enter details");
    digfield    = dlg.addField(TypeID(transdate),"From Date");
    digfield1   = dlg.addField(TypeID(transdate),"To Date");
    dialogFilename = dlg.addFieldValue(typeid(Filepath),filename);
    dlg.run();
    if (dlg.closedOk())
    {
       fromdate     =   digfield.value();
       todate       =   digfield1.value();
       filename     =   dialogFilename.value();
    }
    else
    {
    continue;
    }
    if(!fromdate||!todate||!filename)//validating dialog values
        {
        throw error("Enter complete data");
        }
   filename = dialogFilename.value();

    //Initialize Excel instance
   xlsApplication           = SysExcelApplication::construct();

   //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("ID");
   xlsWorkSheet.cells().item(row,2).value("Name");

   row++;

   tmpTestingTable.clear();

   // Inserting record from main table to temporary table
   insert_recordset tmpTestingTable(ID,Name)
   select ID,Name from  testingTable where TestingTable.Date >= fromDate
                               && TestingTable.Date <= toDate
                               && testingtable.ID != "";


   row=2;
   try
   {
     while select tmpTestingTable
     {

        xlsWorkSheet.cells().item(row,1).value(tmpTestingTable.ID);
        xlsWorkSheet.cells().item(row,2).value(tmpTestingTable.Name);
        row++;
      }
    }
   catch
    {
     if(Exception::Error)
        info("Something went Wrong, Please close all the Excel sheets and try agin");
        else
        continue;
    }


//Validation before saving into excel
   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();
   }