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;

   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);;
    if (dlg.closedOk())
       fromdate     =   digfield.value();
       todate       =   digfield1.value();
       filename     =   dialogFilename.value();
    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



   // 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 != "";

     while select tmpTestingTable

        info("Something went Wrong, Please close all the Excel sheets and try agin");

//Validation before saving into excel
   WinApi::deleteFile(fileName);//if found delete it
   //Save Excel document
   //Open Excel document
   //Close Excel