Excel Import with Dialog

static void FixedAssetImport(Args _args)
{
    Num serialnumber;
    AssetTable assetTable;
    AssetId assetId;
    AssetGroupId assetGroupId;
    Dialog dialog;
    Filename filename;
    DialogField dialogFilename;
    //To filter the files while selecting
    container conFilter = ["Microsoft Excel 97-2003 Worksheet (.xls)" ,"*.xlsx"];
    
    //Excel
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;
    int row=1;
    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    dialog = new dialog();
    dialog.caption("select a file");
    dialogFilename = dialog.addField(extendedTypeStr(FilenameOpen));
    //To filter the files while selecting
    dialog.filenameLookupFilter(conFilter);
    dialog.run();
    
    if(dialog.closedOk())
    {
        filename = dialogFileName.value();
    }
    
    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();
    
    do
    {
        row++;
        assetGroupId = cells.item(row,1).value().bStr();
        assetId = cells.item(row,2).value().bStr();
        serialnumber = cells.item(row,3).value().bStr();
        
        if( assetGroupId && assetId && serialnumber)
        {
            select forupdate AssetGroup,AssetId,Recid from AssetTable
                                    where assetTable.SerialNum == "" &&
                                    assetTable.AssetGroup == assetGroupId &&
                                    assetTable.AssetId == assetId;
        
            if(AssetTable.RecId)
            {
                assetTable.SerialNum = serialnumber ;
                assetTable.update();
            }
            else
            {
                error(strFmt("Please check the values for AssetId:%1",assetId));
            }
            
        }
        else
        {
            error(strFmt("Please check the values for AssetId:%1",assetId));
        }
        
        type = cells.item(row+1, 1).value().variantType();
    }
    while(type != COMVariantType::VT_EMPTY);
    
    workbooks.close();
    application.quit();
    
    info("Operation/Processing Completed");

}
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