hariprasadp: Import Excel file data in to dynamics AX through Dynamics AX

[Blog bot]

Источник: http://hariprasadp.spaces.live.com/B...B91F!208.entry
==============

//--HariPattela--Dynamics AX import table data form Excel to AX table
static void ExcelFile_Import(Args _args)
{
    int i;
    //Excel related declarations
    #excel
    SysExcelApplication SysExcelApplication;
    SysExcelWorksheet SysExcelWorksheet;
    SysExcelWorksheets SysExcelWorksheets;
    SysExcelWorkbooks SysExcelWorkbooks;
    SysExcelWorkbook SysExcelWorkbook;
    SysExcelRange SysExcelRange;
    SysExcelCell SysExcelCell;
    SysExcelCells SysExcelCells;
    SysExcelRange range;
    COMVariant file;

    //progress bar
    #avifiles
    SysOperationProgress simpleProgress;

    //variables used while importing data
    int j, imported,  noofrows, firstrow;
    real ratio;
    str strExample;
    int intExample;

    ;

    //check if file has been selected
    //open the selected excel
    try{
        sysExcelApplication = SysExcelApplication::construct();
        sysExcelApplication.visible(false);
        sysExcelWorkbooks = sysExcelApplication.workbooks();
        file = new COMVariant();
        file.bStr('C:/fg.xls');

        sysExcelWorkbook = sysExcelWorkbooks.add(file);
        SysExcelWorksheets = sysExcelWorkbook.worksheets();
        SysExcelWorksheet = SysExcelWorksheets.itemFromNum(1);
    }
    catch(Exception::Error)
    {
         throw error("cannot open the excel file");
    }

    try
    {

        SysExcelRange = SysExcelWorksheet.cells().range(#ExcelDataRange);

        //Get the number of rows in excel file
        range = SysExcelRange.find("*", null, #xlFormulas, #xlWhole,
                                    #xlByRows, #xlPrevious);
        if(range)
        {
            noofrows = range.row();
        }
        else
        {
            sysExcelApplication.workbooks().close();
            sysExcelApplication.quit();
            throw error('fg.xls');
        }

        //Get the number of columns in excel file
        range = SysExcelRange.find("*", null, #xlFormulas, #xlWhole,
                                    #xlByColumns, #xlPrevious);

        if(range)
        {
            sysExcelApplication.workbooks().close();
            sysExcelApplication.quit();
            throw error('C:/fg.xls');
        }
    }
    catch(Exception::Error)
    {
       sysExcelApplication.workbooks().close();
       sysExcelApplication.quit();
       throw error("Hata!");
    }


    sysExcelCells = sysExcelWorksheet.cells();
    //initialize the progress bar
    ratio = noofrows / 100;
    simpleProgress = SysOperationProgress::newGeneral(#aviUpdate, 'file is importing', 100);

    //importing part
    //Chck currency

    ttsbegin;

    //read rows of excel
    for(j=3; j<=noofrows; j++)
    {
            imported++;
        print(imported);
       
        strExample           = sysExcelCells.item(j, 1).value().bStr();
        intExample           = sysExcelCells.item(j, 4).value().double();
    }
    pause;
    ttscommit;

    //display info
    info(int2str(imported) + " records imported.");

    //close the excel file
    sysExcelApplication.workbooks().close();
    sysExcelApplication.quit();
}