Translate

Sunday, February 24, 2013

Read/Write data using Excel

static void Write2ExcelFile(Args _args)
    InventTable             inventTable;
    SysExcelApplication     application; 
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    SysExcelCell            cell;
    int                     row;
    ;

    application         = SysExcelApplication::construct();
    workbooks           = application.workbooks();
    workbook            = workbooks.add();
    worksheets          = workbook.worksheets();
    worksheet           = worksheets.itemFromNum(1);
    cells               = worksheet.cells();
    cells.range('A:A').numberFormat('@');

    cell                = cells.item(1,1);
    cell.value("Item");
    cell                = cells.item(1,2);
    cell.value("Name");
    row                 = 1;
    
    while select inventTable
    {
        row++;
        cell = cells.item(row, 1);
        cell.value(inventTable.ItemId);
        cell = cells.item(row, 2);
        cell.value(inventTable.ItemName);
    }
    
    application.visible(true);
}

Reading Data from Excel File

static void ReadExcel(Args _args)
    SysExcelApplication     application;
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    COMVariantType          type;
    int                     row;
    ItemId                  itemid;
    Name                    name;
    FileName                filename;

    ;

    application             = SysExcelApplication::construct();
    workbooks               = application.workbooks();
    //specify the file path that you want to read
    filename                = "C:\\item.xls";
    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
    {
        row++;
        itemId = cells.item(row, 1).value().bStr();
        name = cells.item(row, 2).value().bStr();
        info(strfmt('%1 - %2', itemId, name));
        type = cells.item(row+1, 1).value().variantType();
    }
    while (type != COMVariantType::VT_EMPTY);
        application.quit();
}

4 comments:

  1. Nice post...I have a doubt Mr,Mukesh.

    I have created SSRS report in AX 2012. There I have defined parameters in visual studio.Then I ran Report by extending SrsReportRunController class.

    Now I want to use parameters which I made in Visual stuudio in an AX method.Any Suggestions..?

    ReplyDelete
  2. Hi Mukesh,

    Nice post is it possible to do so with a Word Document too?

    ReplyDelete
  3. Hello Webmaster. I truly just like your article and the web site all in all! The piece of writing is actually very plainly written and without difficulty understandable. Your current Blog style is amazing as well! Would definitely be awesome to learn exactly where I can get that. If possible maintain up the excellent job. We all need much more these website owners such as you on the web and also much less spammers. Fantastic man!
    Latest jobs

    ReplyDelete
  4. Very nice blog.people who are looking for this info it is very helpful to them
    Sharepoint Online Training | Microsoft Dynamics AX Training

    ReplyDelete