Imports data from Excel in AX2012 using X++ programming

Import Exchange Rates from Excel in AX2012 using X++

Following job is used to import the Exchange rates into the  ExchangeRate Table using ExchangeRateCurrencyPair for default currency type

Input-Excel file Name,ExcelSheetName


static void krishh_readExchangeRatesExcel(Args _args)
{
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;
    int row;

    // Currency code variables for loading into exchange Rates.
    CurrencyCode  _fromCurrencyCode;
    CurrencyCode  _toCurrencyCode=CompanyInfo::standardCurrency();
    TransDate     _exchangeRateDate;
    CurrencyExchangeRate _exchangeRate;
    ExchangeRateTypeRecId   _exchangeRateTypeRecId
    ;

    application = SysExcelApplication::construct();
    workbooks = application.workbooks();

    // gets the default ExchangeType RecId
    _exchangeRateTypeRecId=ExchangeRateType::findByName('default').RecId;
    try
    {
        workbooks.open(filename);
    }
    catch (Exception::Error)
    {
        throw error("File not found");
    }
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromName(sheetName);
    cells = worksheet.cells();

    //Iterate through cells and get the values
    do
    {
    //Incrementing the row line to next Row
    row++;
    _fromCurrencyCode = cells.item(row, 1).value().bStr();
    _exchangeRateDate = cells.item(row,2).value().date();
    _exchangeRate     = cells.item(row,3).value().double();

    //Imports the Exchange Rates
    ExchangeRate::importExchangeRate(_fromCurrencyCode,_toCurrencyCode,_exchangeRateTypeRecId,_exchangeRateDate,_exchangeRate,true,false);

    // Loads the next row into the variant type and validating that its is empty or not
    type = cells.item(row+1, 1).value().variantType();
    }
    while (type != COMVariantType::VT_EMPTY);

    // quits the application
    application.quit();

}