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 fms_kanha_ReadExcel(Args _args)

{

    SysExcelApplication                     application;

    SysExcelWorkbooks                       workbooks;

    SysExcelWorkbook                        workbook;

    SysExcelWorksheets                      worksheets;

    SysExcelWorksheet                       worksheet;

    SysExcelCells                           cells;

    COMVariantType                          type;

    FilenameOpen                            filename;

    FMS_Kanha                                  test;

    

    int row = 1; // if the excel has the header

    ;

    application = SysExcelApplication::construct();

    workbooks = application.workbooks();

    filename  =  "C:\\Users\\pankk\\Desktop\\Book2.xlsx"; // file path

    try

    {

        workbooks.open(filename);

    }

    catch (Exception::Error)

    {

        throw error("File not found");

    }

    workbook = workbooks.item(1);

    worksheets = workbook.worksheets();

    worksheet = worksheets.itemFromNum(1);

    cells = worksheet.cells();

    //Iterate through cells and get the values

    do

    {

    //Incrementing the row line to next Row

    row++;

        ttsBegin;

        test.clear();

        test.Truck_               = cells.item(row,1).value().bStr();

        test.Generation_         = str2int(cells.item(row,2).value().bStr());

        test.Year_                = cells.item(row,3).value().date();

        test.Model_              = cells.item(row,4).value().bStr();

        test.Gear_                = (cells.item(row,5).value().int());

        test.Month_               = (cells.item(row,6).value().date());

        test.Total_maintenance_   = cells.item(row,7).value().double();

       test.TyreCost_           = cells.item(row,8).value().double();

        test.Lub_cost_            = cells.item(row,9).value().double();

        test.SpareCost_           = cells.item(row,10).value().double();

   //     test.fuelliters_          = cells.item(row,11).value().int();

       test.FuelCost_            = cells.item(row,12).value().double();

        test.Km_                  =cells.item(row,13).value().int();

        test.Trips_               = cells.item(row,14).value().int();

        test.Nupeng_              = cells.item(row,15).value().int();

        test.RoadExpense_         = cells.item(row,16).value().double();

        test.Eng_Overhaul_        = cells.item(row,17).value().int();

        test.M_Stickers_          = cells.item(row,18).value().int();

        test.M_Insurance_         = cells.item(row,19).value().double();

        test.Salary_km_           = (cells.item(row,20).value().double());

        test.Depreciation_        =(cells.item(row,21).value().int());

        test.Revenue_            =(cells.item(row,22).value().int());

        test.Gps_Airtime_         =(cells.item(row,23).value().int());

        test.Rental_details_     =(cells.item(row,24).value().int());

        test.Standing_Cost_       =(cells.item(row,25).value().double());

        test.Running_cost_        =(cells.item(row,26).value().double());

        test.insert();

        ttsCommit;

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

    info("data inserted successfully");

}

********************************************************************************************

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 .