Import Data from Excel Using Dialog (X++)

Hi all,

Today I am going to tell you guys how to Import Data from an Excel file into Ax Table using a Dialog.

Step 1 : Create a Table (ProductType) with three fields as following :

Step 2: Create a class "ImportExcel" that extends Runbase framework and with the following methods :

class ImportExcel extends RunBase
{
   #excel
   #file

   FileNameOpen               fileNameOpen;
   DialogField                     dialogfileNameOpen;

   COMVariant                  comVariant1;
   COMVariantType          comVariant2;
   SysExcelApplication      app;
   SysExcelWorkbooks      workbooks;
   SysExcelWorkbook       workbook;
   SysExcelWorksheets     worksheets;
   SysExcelWorksheet       worksheet;
   SysExcelCells                 cells;
   SysExcelCell                   rcell;

   #define.CurrentVersion(1)
   #localMacro.CurrentList
       fileNameOpen
   #endMacro
}
 

public Object dialog()
{
    DialogRunBase       dialog = super();
;
    dialog.caption("@SYS7444");
    dialogfileNameOpen = dialog.addFieldValue(typeid(FileNameOpen), fileNameOpen);
    dialog.filenameLookupFilter(["@SYS28576",#XLSX, "@SYS28576",#XLS]);
    return dialog;
}


public boolean getFromDialog()
{
    boolean ret;


    ret = super();

    fileNameOpen = dialogfileNameOpen.value();

    return ret;
}


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 boolean validate(Object calledFrom)
{
    boolean ret;

    ret = super(calledFrom);

    if(!fileNameOpen)
        ret = checkFailed(strfmt('File name cannot be left blank'));

    if(fileNameOpen && !WinApi::fileExists(fileNameOpen))
        ret = checkfailed('File not found');

    return ret;
}

public static void main(Args args)
{
    ImportExcel      importMapping;
    ;

    importMapping  =   new ImportExcel();

    if (importMapping.prompt())
    {
        importMapping.run();
    }
}

public void run()
{
    try
    {
        ttsbegin;
        this.importExcel();
      
        ttscommit;
    }
    catch (Exception::Error)
    {
        ttsabort;
        exceptionTextFallThrough();//to avoid Compiler error
    }
}

And finally the ImportExcel method of the class :

void importExcel()
{
    #AviFiles
    ProductType                 productType;
    int                         row;
    SysOperationProgress        progress1;
    ;
    //Define parameters
    app = SysExcelApplication::construct();
    Workbooks = app.Workbooks();
    COMVariant1 = new COMVariant();
    COMVariant1.bStr(fileNameOpen);
    Workbook = Workbooks.add( COMVariant1);
    Worksheets = Workbook.worksheets();
    Worksheet = Worksheets.itemFromNum(1);
    Cells = Worksheet.Cells();

    progress1   =   new SysOperationProgress();
    progress1.setCaption("Excel import");
    progress1.setAnimation(#AviTransfer);

    delete_from  productType ;

    row = 1;
    do
    {
       progress1.setText(strfmt("Importing row %1", row));
       productType.ID   = cells.item(row,1).value().bStr();
       productType.Type = cells.item(row,2).value().bStr();
       productType.Description = cells.item(row,3).value().bStr();

       productType.insert();
       row++;
       COMVariant2 = cells.item(row, 1).value().variantType();

    }
    while( COMVariant2!= COMVariantType::VT_EMPTY);
    info(strfmt("%1 rows imported", row-1));
} 








Happy Daxing:)