Import excel sheet in AX 2009 using RunbaseBatch class.

Requirement: Import excel sheet in AX 2009 using RunbaseBatch class.

Brief Steps:

1.       Create a table or you can use existing if possible.

2.       Create a class which performs all operations.

3.       .xls file pattern.

Elaborated Steps:

1.       Create a table with 5 fields:

Create a table in AOT with name “TestImportTable” having required field which you want to print in your report.

Field	Type(EDT, if available)
EmplId	String (EmplId)
Name	String(Name)
WorkingDate	Date(TransDate)
WorkingHrs	Real
ProjectNo	String

2.       Create a Class which performs all operations.

class testImport extends RunbaseBatch {         DialogField                 dialogFileName;         FileNameOpen            fileName;         FileIOPermission          permission;                 TestImportTable            importTable;                 #File         #avifiles                 #define.CurrentVersion(1)        #define.Version1(1)        #localmacro.CurrentList             fileName        #endmacro }

public Object dialog() {         DialogRunbase       dialog = super();         ;         dialogFileName = dialog.addField(typeid(FileNameOpen));         return dialog; }

public Object dialog() {         DialogRunbase       dialog = super();         ;         dialogFileName = dialog.addField(typeid(FileNameOpen));         return dialog; }

public boolean getFromDialog() {         boolean ret;                 ret = super();         fileName =  dialogFileName.value();         return ret; }

// BP Deviation documented void importFromXSLFile() {         Sysexcelapplication   excelapp=sysexcelapplication::construct();         Sysexcelworksheet         excelworksheet;         Sysexcelrange                 excelrange;         Sysexcelcells                  excelcells;                SysOperationProgress    simpleProgress;         int                                    j =1;         ;         excelapp.workbooks().open(fileName);         excelworksheet = excelapp.worksheets().itemFromNum(1);         excelcells =excelworksheet.cells();                simpleProgress  = SysOperationProgress::newGeneral(#AviUpdate,"Import is in progress",100);         startLengthyOperation();         do        {                importTable .EmplId =  (excelcells.item(j,1).value().bStr());                importTable .Name =  (excelcells.item(j,1).value().bStr());                importTable .WorkingDate =  (excelcells.item(j,1).value().Date());                importTable .WorkingHrs =  (excelcells.item(j,1).value().Double());                importTable .ProjectNo =  (excelcells.item(j,1).value().bStr());              }        // exclude first line of excel         while(excelcells.item(j+1 ,1).value().bStr());         endLengthyOperation(); }

public container pack() {     return [#CurrentVersion,#CurrentList];  }

public boolean unpack(container packedClass) {         Version version = RunBase::getVersion(packedClass);         ;         switch (version)     {                 case #CurrentVersion:         [version,#CurrentList] = packedClass;         break;         default:         return false;    }    return true; }

public void run() {     #OCCRetryCount     ;     if (! this.validate())           throw error("Import has been cancel");         try        {                 this.importFromXLSFile(); //Import records of xls file                 }         catch(Exception::Deadlock)         {             retry;         }         catch(Exception::Error)         {                         info ('Import cancelled');         }         catch (Exception::UpdateConflict)         {                         if (appl.ttsLevel() == 0)             {                              if (xSession::currentRetryCount() >= #RetryNum)                  {                         throw Exception::UpdateConflictNotRecovered;                  }                  else                  {                         retry;                  }              }              else              {                     throw Exception::UpdateConflict;              }         }        }

public boolean validate() {     boolean     ret = true;     Container   checkType;     if (! filename)     {                 ret = checkFailed("Please specify file");     }     checkType =  Docu::splitFilename(filename);     if(Conpeek(checkType,2) != 'xls' || Conpeek(checkType,2) != 'XLS')     {           ret = checkFailed("file format is incorrect");     }      return ret; }

static void main(Args args) {     TestImport             importClass;     ;         importClass = new TestImport ();     if(TestImport.prompt());             TestImport.run(); }

     

     

3.       .xls file pattern.
You can make desire pattern or for this code you can use below one.