//Apply this method in any Button Override Method.
// This method is used to Import the Excel Data to AX Table
//
//

// Excel File Contains 3 Columns which is EquipmentId, Cost, EffectiveDate
// Same way here we have created Code For the particular Three field....

public void ImportData()
{
    SysExcelApplication             application;
    SysExcelWorkbooks               workbooks;
    SysExcelWorkbook                workbook;
    SysExcelWorksheets              worksheets;
    SysExcelWorksheet               worksheet;
    SysExcelCells                   cells;
    COMVariantType                  type;
    System.DateTime                 ShlefDate;
    FilenameOpen                    filename;
    dialogField                     dialogFilename;
    Dialog                          dialog;
  #AviFiles
    // Progress Bar
    SysOperationProgress progress = new SysOperationProgress(1, NoYes::Yes);


    TestXls                         _TestXls;  // Table name and decleration
    str                             equipId;
    str                             cost;
    str                             effectiveDate;
    int                             datevalue, row = 0;

    real                            costr;
    Date                            dater;

    #Excel

    str COMVariant2Str(COMVariant _cv,
                       int _decimals = 1,
                       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 "";
    }
    ;

    dialog = new Dialog("ExcelUpload");
    dialogFilename      =   dialog.addField(typeId(FilenameOpen));
    dialog.filenameLookupFilter(["@SYS28576",#XLS, "@SYS28576",#XLSX]);
    dialog.filenameLookupTitle("Upload from Excel");
    dialog.caption("Excel Upload");
    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();

    // Progress Caption & Animation
    progress.setCaption("Copying..");
    progress.setAnimation(#AviUpdate);

    do
    {
        try
        {
            ttsbegin;
            row++;
            // Getting EquipmentId value form Excel Row wise and assigning as str
            equipId = COMVariant2Str(cells.item(row,1).value());

            // Getting Cost value form Excel Row wise and assigning as str
            cost    = COMVariant2Str(cells.item(row,2).value());

            // Converting str to Real
            costr    = any2Real(cost);

            // Getting Date value form Excel Row wise and assigning as str
            effectiveDate = COMVariant2Str(cells.item(row,3).value());

            //  Converting String to Date
            dater = str2Date(effectiveDate,123);

           // While Revecing datas from Excel Sheet If invalid value presents it wil not allowe to insert and the     
            //   last  record also deleted
            if (!equipId || !dater || costr==0.00)
            {


                ttsbegin;

                TestXls.delete();

                ttscommit;


                box::warning(strfmt("Check the value in Excel Sheet row %1", row));

                return;


            }

            else
            {

                if (row > 1)
                {
                    // Progress bar Text and Total
                    progress.setText(strfmt("Importing to Ax Table : %1", row));
                    progress.setTotal(row, 1);

                    _TestXls.initValue();
                    _TestXls.EquipId    = equipId;
                    _TestXls.Cost       = costr;
                    _TestXls.EffectiveDate = dater;
                    _TestXls.insert();

                }
            }

            ttscommit;
        }

        catch
        {
            Error(strfmt("Upload Failed in row %1", row));
        }

        type = cells.item(row + 1, 1).value().variantType();

    } while (type!= COMVariantType::VT_EMPTY);


    info(strfmt("Equipments Details Uploaded Successfully"));

    application.quit();


}




Leave a Reply.