Import from Excel sheet to AX

public   void insertEmplIqamaIdentificationTable()
{
    SysExcelApplication             application;
    SysExcelWorkbooks               workbooks;
    SysExcelWorkbook                workbook;
    SysExcelWorksheets              worksheets;
    SysExcelWorksheet               worksheet;
    SysExcelCells                   cells;
    COMVariantType                  type;
    System.DateTime                 ShlefDate;
    FilenameOpen                    filename;
    dialogField                     dialogFilename;
    Dialog                          dialog;
    int row=0;
    #Excel
    SysOperationProgress            progress;
    #AVIFiles

    HRMEmplIdentification   HRMEmplIdentification,findIdentification,checkrecordexitsornot,checkpassportdetailsexitsornot;
    HRMIdentification       IqamaIdentification,passIdentification;
    PAYFDateHijri           ValidUnTillIqamaDateHijri,ActualIqamaDateHijri,ValidUnTillPassDateHijri,ActualPassDateHijri;
    HRMIdentificationTypeId IdentificationTypeId;
    Emplid                  emplid;
    TransDate               ValidUntiliqamaDate,ValidUntilpassDate;
    PAYFEmplIdentificationGroup IdentificationGroup;
    PAYFEmplIdentificationId    IdentificationId;
    Projid                      projid;
    Empltable   Empltable;




    str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)
    {
        switch (_cv.variantType())
    {
        case (COMVariantType::VT_BSTR):
            return _cv.bStr();

        case (COMVariantType::VT_R4):
            return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);

        case (COMVariantType::VT_R8):
            return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);

        case (COMVariantType::VT_DECIMAL):
            return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);

        case (COMVariantType::VT_DATE):
            return date2str(_cv.date(),123,2,1,2,1,4);

        case (COMVariantType::VT_EMPTY):
            return "";

        default:
            throw error(strfmt("@SYS26908", _cv.variantType()));
    }
        return "";
    }

;
    Progress = new SysOperationProgress();
    Progress.setCaption("Uploading Employee  Details");
    Progress.setAnimation(#AviStopWatch);

    dialog              =   new Dialog("Excel Upload Details");
    dialogFilename      =   dialog.addField(typeId(FilenameOpen));
    dialog.filenameLookupFilter(["@SYS28576",#XLSX,"@SYS28576",#XLS]);
    dialog.filenameLookupTitle("Upload from Excel");
    dialog.caption("Excel Upload Employee  Details");
    dialogFilename.value(filename);
    if(!dialog.run())
    return;
    filename            =   dialogFilename.value();
    application         =   SysExcelApplication::construct();
    workbooks           =   application.workbooks();

    try
    {
        workbooks.open(filename);
    }
    catch (Exception::Error)
    {
        throw error("File cannot be opened.");
    }
    workbook            =   workbooks.item(1);
    worksheets          =   workbook.worksheets();
    worksheet           =   worksheets.itemFromNum(1);
    cells               =   worksheet.cells();

     do
    {
        try
        {
            ttsbegin;
            row++;
            IqamaIdentification         = COMVariant2Str(cells.item(row+1, 9).value());
            ValidUnTillIqamaDateHijri   = COMVariant2Str(cells.item(row+1, 2).value());
         
           ValidUntiliqamaDate         = global::toGregorianDateNew(ValidUnTillIqamaDateHijri);
            ActualIqamaDateHijri        = CalendarDateConvert::construct().toHijriDateOld(ValidUntiliqamaDate);

            ValidUnTillPassDateHijri    = COMVariant2Str(cells.item(row+1, 3).value());
       
          ValidUntilpassDate          = global::toGregorianDateNew(ValidUnTillPassDateHijri);

             ActualPassDateHijri         = ValidUnTillPassDateHijri;
            passIdentification          = COMVariant2Str(cells.item(row+1, 4).value());

            if(IqamaIdentification)
            {
                Empltable = empltable::findEmplIqamaNo(IqamaIdentification,true);

                if(Empltable.RecId != 0)
                {
                    checkrecordexitsornot = HRMEmplIdentification::findEmplIdentBytypeId(Empltable.EmplId,"Iqama",true);

                    if(checkrecordexitsornot.RecId == 0 || checkrecordexitsornot.ValidUntilDateHijri != ActualIqamaDateHijri)
                    {
                        HRMEmplIdentification.Identification = IqamaIdentification;
                        HRMEmplIdentification.ValidUntilDate   = ValidUntiliqamaDate;
                        HRMEmplIdentification.ValidUntilDateHijri =  ActualIqamaDateHijri;

                        HRMEmplIdentification.EmplId =Empltable.EmplId;
                        HRMEmplIdentification.ProjId =EMpltable.Dimension[2];
                        HRMEmplIdentification.IdentificationTypeId = "Iqama";

                        HRMEmplIdentification.IdentificationGroup =PAYFEmplIdentificationGroup::Employee;
                        HRMEmplIdentification.IdentificationId =Empltable.EmplId;
                        HRMEmplIdentification.insert();

                     }


                  //check passport details exits or not in HRMemplIdentification table
                    checkpassportdetailsexitsornot = HRMEmplIdentification::findEmplIdentBytypeId(Empltable.EmplId,"Passport",true);
                    if(checkpassportdetailsexitsornot.RecId != 0 && checkpassportdetailsexitsornot.ValidUntilDateHijri == ActualPassDateHijri
                    && checkpassportdetailsexitsornot.Identification == passIdentification )
                    {
                    checkpassportdetailsexitsornot.Identification = passIdentification;
                    checkpassportdetailsexitsornot.ValidUntilDate   = ValidUntilpassDate;
                    checkpassportdetailsexitsornot.ValidUntilDateHijri =  ActualPassDateHijri;

                    checkpassportdetailsexitsornot.EmplId =Empltable.EmplId;
                    checkpassportdetailsexitsornot.ProjId =EMpltable.Dimension[2];
                    checkpassportdetailsexitsornot.IdentificationTypeId = "Passport";

                    checkpassportdetailsexitsornot.IdentificationGroup =PAYFEmplIdentificationGroup::Employee;
                    checkpassportdetailsexitsornot.IdentificationId =Empltable.EmplId;
                    checkpassportdetailsexitsornot.doUpdate();
                    }
                    else
                    {
                    checkpassportdetailsexitsornot.Identification = passIdentification;
                    checkpassportdetailsexitsornot.ValidUntilDate   = ValidUntilpassDate;
                    checkpassportdetailsexitsornot.ValidUntilDateHijri =  ActualPassDateHijri;

                    checkpassportdetailsexitsornot.EmplId =Empltable.EmplId;
                    checkpassportdetailsexitsornot.ProjId =EMpltable.Dimension[2];
                    checkpassportdetailsexitsornot.IdentificationTypeId = "Passport";

                    checkpassportdetailsexitsornot.IdentificationGroup =PAYFEmplIdentificationGroup::Employee;
                    checkpassportdetailsexitsornot.IdentificationId =Empltable.EmplId;
                    checkpassportdetailsexitsornot.doinsert();
                    }
                  }
                  else
                  {
                  Error(strfmt("Upload Failed of iqama No %1 ,because employee details does not exits in employee master table.",IqamaIdentification));
                  }
            }
            type = cells.item(row+2, 1).value().variantType();
            progress.setText(strfmt("Uploading Employee New Iqama / passport Details %1",row+1));
            ttscommit;
        }

        catch
        {
            Error(strfmt("Upload Failed in row %1 iqama No %2", row+1,IqamaIdentification));
        }

    }
    while (type != COMVariantType::VT_EMPTY );
    info(strfmt("Employee New Iqama Details uploaded successfully"));
    progress.kill();
    application.quit();


}