Creating Purchase Order Header and Purchase lines By Excel data in ax 2012 Using X++

Hi guys ,Today we see how to create  Purchase order header and Lines by Excel data in ax 2012 using X++.  

I write the code in job for testing purpose.i share that code to  you.Just write this code in job and test it.

Here vendor account and Delivery date value are hot coded so for that values i create a macro.

static void ReadExcelfile1(Args _args)

{

    #define.Vendor("US-105")

    #define.DeliveryDate("1/1/2014")

    SysExcelApplication         application;

    SysExcelWorkbooks           workbooks;

    SysExcelWorkbook            workbook;

    SysExcelWorksheets          worksheets;

    SysExcelWorksheet           worksheet;

    SysExcelCells               cells;

    Filename                    filename;

    COMVariantType              type;

    int                         rowNo;

    PurchTable      purchTable;

    PurchLine       purchLine;

    InventDim       inventDim;

    str _itemid;

    real                   comvariant2real;

    

    str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)

    {

        switch (_cv.variantType())

        {

            case (COMVariantType::VT_BSTR):

            return _cv.bStr();

            case (COMVariantType::VT_R4):

            return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);

            case (COMVariantType::VT_R8):

            return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);

            case (COMVariantType::VT_DECIMAL):

            return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);

            case (COMVariantType::VT_DATE):

            return date2str(_cv.date(),123,2,1,2,1,4);

            case (COMVariantType::VT_INT):

            return num2str( _cv.int(),_characters,_decimals,_separator1,_separator2);

            case (COMVariantType::VT_EMPTY):

            return "";

            default: throw error(strfmt("@SYS26908", _cv.variantType())); } return "";

    }

    rowNo   = 1;//If the excel file NOt having header.

    //rowNo   =2;//If the excel file having header.

    application = SysExcelApplication::construct();

    workbooks = application.workbooks();

    filename = @"C:\Users\harika.m\Documents";//Excel file path.

    try

    {

        workbooks.open(filename);

    }

    catch (Exception::Error)

    {

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

    }

    workbook = workbooks.item(1);

    worksheets = workbook.worksheets();

    worksheet = worksheets.itemFromNum(1);//which shows excel sheet number.

    cells = worksheet.cells();

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

    while (type != COMVariantType::VT_EMPTY)//loop through given excel column.

    {

        type = cells.item(rowNo+1, 1).value().variantType();//It will find variant type of column from given Excel sheet(ReadIntegerFile.xlsx) tiil it get empty.

         ttsbegin;

        //Order header

        purchTable.clear();

        purchTable.initValue(PurchaseType::Purch);

        purchTable.PurchId = NumberSeq::newGetNum(PurchParameters::numRefPurchId()).num();

        purchTable.DeliveryDate = str2Date(#DeliveryDate, 213);

        purchTable.initFromVendTable(VendTable::find(#Vendor));

        if (purchTable.validateWrite())

        {

            purchTable.insert();

        //Order line

            inventDim.clear();

            inventDim.InventSiteId = COMVariant2Str(cells.item(rowNo, 4).value());

            inventDim.InventLocationId = COMVariant2Str(cells.item(rowNo,5).value());

            _itemid = COMVariant2Str(cells.item(rowNo, 1).value());

            purchLine.clear();

            purchLine.initValue(purchTable.PurchaseType);

            purchLine.initFromPurchTable(purchTable);

            purchLine.initFromInventTable(InventTable::find(_itemid));

            purchLine.InventDimId = InventDim::findOrCreate(inventDim).inventDimId;

            purchLine.PurchQty = str2int(cells.item(rowNo, 2).value().bStr());

            purchLine.RemainPurchPhysical = purchLine.PurchQty;

            purchLine.PurchUnit = COMVariant2Str(cells.item(rowNo, 3).value());

            purchLine.QtyOrdered = purchLine.calcQtyOrdered();

            purchLine.RemainInventPhysical = purchLine.QtyOrdered;

            purchLine.setPriceDisc(InventDim::find(purchLine.InventDimId));

            if (purchLine.validateWrite())

            {

                purchLine.insert();

            }

            else

                throw error("Order line");

        }

        else

            throw error("Order header");

        ttscommit;

       info(strFmt("ItemID %1", purchLine.ItemId));

        rowNo++;//To get next column number in the loop.

    }

    application.quit();

}

Keep Daxing !!