Importing Multiple SalesOrders & Multiple SalesLins from Excel


static void MultiSalesOrdersLinefromExcel(Args _args)
{
    SalesTable      salesTable;
    SalesLine       salesLine;
    InventDim       inventDim;
    NumberSeq        num;
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet,worksheet1;
    SysExcelCells cells,cells1;
    COMVariantType type,type1;
    int row=1,row1=1;
    ;
    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    workbooks.open('C:\\Users\\shiva.garapalli\\Desktop\\SalesOrderCreate.xlsx');
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromName("Header");
    worksheet1 = worksheets.itemFromName("Lines");
    cells = worksheet.cells();
    cells1 = worksheet1.cells();
     
    ttsBegin;
    
    do
    {
        row1++;
        salesTable.clear();
         
        num = NumberSeq::newGetNum(SalesParameters::numRefSalesId());
        salesTable.SalesId = num.num();
         
        salesTable.CustAccount = cells.item(row1,1).value().bStr();
        salesTable.initValue(SalesType::Sales);
        salesTable.initFromCustTable();
        salesTable.insert();
     
    do
    {
        row++;
        if( salesTable.CustAccount == cells1.item(row, 1).value().bStr())
        {
            inventDim.clear();
            inventDim.InventSiteId = cells1.item(row,5).value().bStr();//int2str(real2int(cells1.item(row,5).value().double()));
            inventDim.InventLocationId = cells1.item(row,6).value().bstr();//int2str(real2int(cells1.item(row,6).value().double()));
             
            salesLine.clear();
            salesLine.initValue(salesTable.SalesType);
            salesLine.initFromSalesTable(salesTable);
            salesLine.ItemId = cells1.item(row, 2).value().bStr();
            salesLine.initFromInventTable(InventTable::find(cells1.item(row, 2).value().bStr()));
            salesLine.InventDimId = InventDim::findOrCreate(inventDim).inventDimId;
            salesLine.SalesQty = any2int(cells1.item(row,3).value().toString());
            salesLine.RemainSalesPhysical = salesLine.SalesQty;
            salesLine.SalesUnit = cells1.item(row,4).value().bStr();
            salesLine.DlvMode = cells1.item(row,7).value().bStr();
            salesLine.QtyOrdered = salesLine.calcQtyOrdered();
            salesLine.RemainInventPhysical = salesLine.QtyOrdered;
            salesLine.setPriceDisc(InventDim::find(salesLine.InventDimId));
         
            if (salesLine.validateWrite())
            {
                salesLine.insert();
            }
        }
        type = cells1.item(row+1, 1).value().variantType();
    }
    while (type != COMVariantType::VT_EMPTY);
        row = 0;
        type1 = cells.item(row1+1, 1).value().variantType();
    }
    while(type1 != COMVariantType::VT_EMPTY);
     
  
    
    workbooks.close();
    application.quit();
     
    info("Done!");
      ttsCommit;
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s