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

3 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. Very nice blog.people who are looking for this info it is very helpful to them
    Sharepoint Online Training | Microsoft Dynamics AX Training

    ReplyDelete