Import Purchase Requisition From Excel with multiple Lines

Here am giving the code to Import PR from excel and the same PR could have multiple lines, Create an excel sheet with columns like

column 1 -> name OF PR
column 2 -> Item ID
column 3 -> Quantity 
column 4 -> Price 
column 5 -> purchreqID

if you need multiple lines just create multiple rows with same purchreqID

and use the following code snippet..

static void PREXCEL(Args _args)
{
    Dialog                  dialog;
    DialogField             dialogField;
    FilenameOpen            filename, Filename2;
    PR_temp                 prtemp,prtemplines;

    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;
    PurchReqTable   purchReqTable;
    PurchReqLine    purchReqLine;
    ProjTable       projTable = projTable::find("10002");
    SalesLine       salesLine;// = SalesLine::findInventTransId("10002");
    FileIOPermission        permission;
    int row = 0;
    int j = 0;
    // if the excel has the header
    //parameters
    str itemid,name,purchreqid;
    real price,qty;


    #File

     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_EMPTY):
    return "";
    default:
    throw error(strfmt("@SYS26908", _cv.variantType()));
    }
    return "";
    }




    //importing from excel
    dialog = new Dialog("Posting Payment Journal");
    dialogField = dialog.addField(ExtendedTypeStr("FilenameOpen"),"Source file");





    if (dialog.run())
   {
     filename = dialogField.value();
     permission = new fileIOpermission(filename,"RW");
     permission.assert();
     application = SysExcelApplication::construct();
     workbooks = application.workbooks();
     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();
      //insert without class
   // vendTable = VendTable::find("3114");


delete_from prtemp;

     do
    {
    //Incrementing the row line to next Row
    row++;

    //row++;
    name        =  COMVariant2Str(cells.item(row,1).value());
    itemid      =  COMVariant2Str(cells.item(row,2).value());
    qty         =  cells.item(row,3).value().double();
    price       =  cells.item(row,4).value().double();
    purchreqid  =   COMVariant2Str(cells.item(row,5).value());

        prtemp.Name       = name;
        prtemp.ItemId     = itemid;
        prtemp.PurchQty   = qty;
        prtemp.PurchPrice = price;
        prtemp.PurchReqId = purchreqid;
        prtemp.insert();
                //row++;
            //name1 = COMVariant2Str(cells.item(j+1,1).value());

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

     }

       //info(strFmt("%1,%2",empId,salary));
      while (type != COMVariantType::VT_EMPTY);
      application.quit();

    }

     while select  prtemp group by prtemp.PurchReqId ,prtemp.Name
        {


    purchReqTable.clear();
    purchReqTable.initValue();
    purchReqTable.PurchReqId = prtemp.PurchReqId;
    purchReqTable.PurchReqName = prtemp.Name;
    purchReqTable.ProjId = projTable.ProjId;
    purchReqTable.ProjIdDataArea = projTable.dataAreaId;
    purchReqTable.insert();
            while select prtemplines where prtemplines.PurchReqId == prtemp.PurchReqId
            {


    purchReqLine.clear();
    purchReqLine.initValue();
    purchReqLine.initFromPurchReqTable(purchReqTable);
    purchReqLine.ItemId = prtemplines.ItemId;//salesLine.ItemId;
    salesLine = SalesLine::findInventTransId(prtemplines.ItemId);
    purchReqLine.InventDimId = salesLine.InventDimId;
    purchReqLine.PurchQty =  prtemplines.PurchQty;
    purchReqLine.PurchPrice = prtemplines.PurchPrice;

    //purchReqLine.ActivityNumber = 'AO-3456789';//salesLine.ActivityNumber;
    purchReqLine.BuyingLegalEntity = CompanyInfo::find().RecId;
    purchReqLine.InventDimIdDataArea = curext();//salesLine.dataAreaId;
    purchReqLine.initFromProjTable(projTable);
    purchReqLine.insert();
            }
        }

}