Excel import and export

Hi all,

I'm trying to insert and activate an item with price from excel and when the item from the excel is not present in the item table i need to export the items into excel from ax..

Here is my code importing is fine but exporting is not happening..

void clicked()
{
    SysExcelApplication             application,app1;
    SysExcelWorkbooks               workbooks,wbooks;
    SysExcelWorkbook                workbook,wbook;
    SysExcelWorksheets              worksheets,wsheets;
    SysExcelWorksheet               worksheet,wsheet;
    SysExcelCells                   cells,cells1;
    SysExcelCell                    cell;
    COMVariantType                  type;
    System.DateTime                 ShlefDate;
    FilenameOpen                    filename;
    dialogField                     dialogFilename;
    Dialog                          dialog;

    //Table Declarations Starts
    InventItemPrice                 inventprice;
    InventDim                       _inventdim;
    InventParameters                inp;
    InventTableModule               inm,inventTableModule;
    InventItemPriceSim              iips,inventItemPriceSimulated;
    InventTable                     _inventtable;

    //Table Declartions Ends

    //field declaration
    ItemId                          _itemid;
    CostingVersionId                _versionid;
    CostingVersionPriceType         _type;
    Price                           _price;
    PriceUnit                       _unit;
    CostingVersionId                _version;
    InventDimId                     _invent;
    InventSiteId                    _site;
    TransDate                       _fromdate;
    InventItemCostingType           _costingType;
    int                             row,row1;

    #OCCRetryCount
    #Excel

    // convert into str from excel cell value

    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 "";
    }
    ;

    dialog              =   new Dialog("Excel Upoad");
    dialogFilename      =   dialog.addField(typeId(FilenameOpen));
    dialog.filenameLookupTitle("Upload from Excel");
    dialog.caption("Sales Price Excel Upload");
    dialogFilename.value(filename);
    if(!dialog.run())
    return;
    filename            =   dialogFilename.value();
    application         =   SysExcelApplication::construct();
    workbooks           =   application.workbooks();
    try
    {
        workbooks.open(filename);
    }
    catch (Exception::Error)
    {
        throw error("File cannot be opened.");
    }

    workbook            =   workbooks.item(1);
    worksheets          =   workbook.worksheets();
    worksheet           =   worksheets.itemFromNum(1);
    cells               =   worksheet.cells();

    try
    {
    ttsbegin;
    do
    {
        row++;
        _itemid           =   COMVariant2Str(cells.item(row, 1).value());
        _price            =   cells.item(row, 2).value().double();
        _site             =   COMVariant2Str(cells.item(row, 3).value());

        select Costingversionid from inp;
        _versionid=inp.Costingversionid;
        breakpoint;
        if(row > 1)
        {
            select firstonly iips;
            if(iips.ItemId==_itemid)
            {
                    iips.ItemId=_itemid;
                    iips.VersionId=_versionid;
                    iips.PriceType=CostingVersionPriceType::Sales;
                    _inventdim.InventSiteId=_site;
                    iips.InventDimId=InventDim::findOrCreate(_inventdim).inventDimId;
                    iips.Price=_price;
                    iips.PriceUnit=1;
                    iips.FromDate=systemdateget();
                    iips.insert();
                    InventItemPriceActivationJob::activateInventItemPriceSimExt(iips);
                    info(strfmt("Items inserted successfully for %1",iips.ItemId));
          }
          else
          {
                info(strfmt(" Items not inserted for %1",_itemid));
                app1 = SysExcelApplication::construct();
                wbooks = app1.workbooks(); //gets the workbook object
                wbook = workbooks.add();  // creates a new workbook
                wsheets = workbook.worksheets(); //gets the worksheets object
                wsheet = worksheets.itemFromNum(1);//Selects the first worksheet in the workbook to insert data
                cells1 = worksheet.cells();
                cells1.range('A:A').numberFormat('@');
                cell=cells1.item(row,1);
                cell.value(_itemid);
                cell=cells1.item(row,2);
                cell.value(_price);
                cell=cells1.item(row,3);
                cell.value(_site);
                app1.visible(true);
          }

        }
        type = cells.item(row+1, 1).value().variantType();
        }while (type != COMVariantType::VT_EMPTY);
        application.quit();
        ttscommit;

        }
        catch
        {
            Error("Upload Failed");
        }
}

How could i export the error items ?

thanks

Related
Recommended