Reading from Excel in x++

This code might actually be part of a few other posts, but to single out the concept, here is how I usually read from Excel when performing imports.

First, a getValues() method to return the path of the Excel file you’re wanting to import. This path is stored in a global:

public boolean getValues()
    Dialog              dialog;
    boolean             isOk;

    FilenameOpen        filename;
    DialogField         dcFilename;
    DialogField         dcDesc;
    DialogField         dcJType;

	// | ## 
	// V be sure to declare Excel macro, which is needed for the excel file types in the extention lookup filter

    dialog = new Dialog("Edit this to be the caption of your dialog.");
    dialog.addText("Edit this text to be the prompt for your dialog.");

    dcFilename = dialog.addField(typeId(FilenameOpen));

    dialog.filenameLookupTitle("Excel Import");;

    if (dialog.closedOk())
        path = dcFilename.value();

        isOk = true;
        isOk = false;

    return isOk;

Next, a method to evaluate the value in the Excel cell and return in string. I prefer to bring everything in as a string and work with it from there:

str COMVariant2Str(COMVariant _cv, int _decimals = 2, 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_CY):
            return num2str(_cv.currency(),_characters,_decimals,_separator1,_separator2);
        case (COMVariantType::VT_DECIMAL):
            return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);
        case (COMVariantType::VT_DATE):
            return date2str(,123,2,1,2,1,4);
        case (COMVariantType::VT_EMPTY):
            return "";
            throw error(strfmt("@SYS26908", _cv.variantType()));

    return "";

And finally, the import method:

void processExcelImport()
    InteropPermission       permission; // <-- Permission Object

    SysExcelApplication     application; 
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;

    COMVariantType          type;

	// Call construct to instantiate your application object 
    application = SysExcelApplication::construct(); 
	// set workbooks variable to the workbooks collection of the application object 
    workbooks = application.workbooks(); 				

		// try to open using the path stored to
		// local variable returned from getValues();
        throw error("Cannot open file.");

	// set the workbook variable to whichever workbook you want to get values from
	// in this case it's the first. 
	// !IMPORTANT! -- Note that, as often in x++, indexes are 1-based and not 0-based!
    workbook = workbooks.item(1);
	// set the worksheets collection and set the appropriate worksheet where the data lives
    worksheets = workbook.worksheets();
	// again, you can use itemFromNum(n) passing in a 1-based index, or
	// you can use the itemFromName(str) method passing in the name of the worksheet
    worksheet = worksheets.itemFromName("Sheet1");//itemFromNum(2);//
	// set the cells
    cells = worksheet.cells();


			// a string that is in number format, creating the need to cast to int and back to string
			// so trailing decimals are dropped
            somestring1       	= int2str(str2int(this.COMVariant2Str(cells.item(row, 1).value()))); 

			// a string
            somestring2        	= this.COMVariant2Str(cells.item(row, 2).value());    

			// this is how I usually handle number values, whether int of floating
            somenumber         	= str2num(this.COMVariant2Str(cells.item(row, 3).value()));                               

			// increment row so that you progress down the spreadsheet while looping

		// set varient type of next row
        type = cells.item(row, 1).value().variantType();

		// while not empty, keep looping
        } while (type != COMVariantType::VT_EMPTY);
        info("An error occurred processign the file.");



    info("Import completed.");