Reading excel file to store data in a table in Ax 2012

   

Today I will be sharing how to read excel in Ax 2012 . I will demonstrate through a simple example.

Below is my Excel Sheet .

This is my Job that reads this excel sheet.

static void ExcelJobProduct(Args _args)

{

SysExcelApplication                    application;

SysExcelWorkbooks                   workbooks;

SysExcelWorkbook                     workbook;

SysExcelWorksheets                  worksheets;

SysExcelWorksheet                    worksheet;

SysExcelCells                              cells;

COMVariantType                       type;

ProductType                                prodType;

FileName                                     filename;

Product                                         Product;

ProductId                                     prodId;

Name                                           productType;

int                                                 row =1 ;

str                                                ProductName;

real                                              Price;

str                                               _ProductCode;

application = SysExcelApplication::construct();

workbooks = application.workbooks();

//specify the file path that you want to read

filename = "C:\\Task_2_DataSet_Normalized.xlsx";

try

{

//  Adds the file as the first document in the collection.

workbooks.open(filename);

}

catch (Exception::Error)

{

throw error("File cannot be opened.");

}

workbook = workbooks.item(1);

worksheets = workbook.worksheets();

worksheet = worksheets.itemFromNum(5);   // represents the current worksheet in my case it's (5)

cells = worksheet.cells();

// Fetches data from each cell that contains data.

do

{

row++;

Price       = cells.item(row, 4).value().double();

prodId      = int642str(cells.item(row, 1).value().double());

ProductName = cells.item(row, 2).value().bstr();

productType = cells.item(row, 3).value().bStr();

// Insert data into Product table based on the ProductType from ProductType table.

select prodType

     where prodType.ProductType == productType;

if(prodType.RecId != 0)

 {

    ttsBegin;

    Product.ProductCode  =  prodId;

    Product.Name              = ProductName;

    Product.ProductType   = prodType.RecId;

    Product.Price                =  Price;

    Product.insert();

    ttsCommit;

 }

type = cells.item(row+1, 1).value().variantType();

}

// Runs until the COMVarientType doesnot contains a data field.

while (type != COMVariantType::VT_EMPTY);

// Closes the Instance of Excel.

application.quit();

}

Here ,

• SysExcelApplication  represents an instance of Excel .
• SysExcelWorkbooks provides collection of Excel documents stored in this Class.
• SysExcelWorkbook   provides a reference to the opened document.
• SysExcelWorksheets represents a collection of all the worksheets in a document.
• SysExcelWorksheet   provides a reference to a single worksheet.
• SysExcelCells             provides reference to the collection of cells within a sheet.
• COMVariantType      COMVarient Class is used to store various types of data .