Search This Blog

Wednesday, January 18, 2012

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();

}

18 comments:

  1. Thanks so much

    ReplyDelete
  2. Hi Krishna ..

    by using above Job ,How to import excel data from dialog ? please suggest me?

    ReplyDelete
  3. Hi Sunny,

    Dialog d;
    DialogField df1, df2;
    ;
    d = new Dialog("Import ExchangeRates");
    df1 = d.addField(ExtendedTypeStr("FilenameOpen"));

    if (d.run())
    {
    workbooks.open(df1.value());
    }

    You can add another field in dialog for sheetname as well and pass that value to sheet name for the above sample in the post.

    I hope you got the solution to use from dialog in job.

    Regards,
    krishna.
    krishna.dynamics@gmail.com

    ReplyDelete
  4. Hi Krishna,

    thanks for quick response .

    now i have 2 jobs to import excels.
    for example one is for hour transactions , and another for iteam transaction.

    how to cal those in one dialog class.?

    ReplyDelete
    Replies
    1. Hi Sunny,
      You cant run at time once for the both. you have to run for one file one time.
      If you want to do for all the files...then you have to write a class where you have to read each file in the speficific folder and insert your data accordingly...

      I hope you understand.

      Regards,
      krishna.

      Delete
    2. Hi Krishna,

      thanks,

      i have excle with two tabs (sheet1 and sheet2) and so now how import one bye one , i have two jobs for those, and now i want one dialog class for that . how will get?

      Delete
    3. Hi Sunny,
      You can use itemFromNum(1) or 2 like that
      worksheet = worksheets.itemFromName(sheetName);

      so if you want to do that do in the forloop and pass that value to the itemFromNum(i)
      for(i=1;i<2;i++)
      {
      worksheet = worksheets.itemFromNum(i);


      you can do this way in one dialog.

      Regards,
      krishna.

      Delete
  5. Hi Krishna Reddy,

    can you post here the sample excel sheet

    ReplyDelete
  6. hi Krishna,

    How to export the data through excel using x++ code with button click in ax 2012...

    Thanks & Regards
    Arun Joseph

    ReplyDelete
    Replies
    1. http://theaxapta.blogspot.dk/2013/03/exporting-data-to-excel-from-axapta-x.html

      Delete
    2. This comment has been removed by the author.

      Delete
  7. Hi Krishna,

    How to set the borders in excel sheet using x++ code,please help me..

    please send the code.

    Thanks,

    Arun joseph A

    ReplyDelete
  8. Do we need microsoft excel add-in for writing this job.

    ReplyDelete
  9. i want to import bank statements through excel like we do through xml, plz tell me the procedure how to do dis....

    ReplyDelete
  10. How to import excel data to ax table please help me

    ReplyDelete
  11. How to import excel data to ax dbtable... plse help me i m new to AX

    ReplyDelete
    Replies
    1. Please see this post, you will know how to do it.
      http://krishhdax.blogspot.dk/2012/01/imports-data-from-excel-in-ax2012-using.html

      Delete

Thanks for visiting my blog,
I will reply for your comment within 48 hours.

Thanks,
krishna.