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
    #Excel 
    ;

    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.filenameLookupFilter(["@SYS28576",#XLSX,"@SYS28576",#XLS]);
    dialog.filenameLookupTitle("Excel Import");

    dialog.run();

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

        isOk = true;
    }
    else
    {
        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(_cv.date(),123,2,1,2,1,4);
        case (COMVariantType::VT_EMPTY):
            return "";
        default:
            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
    {
		// try to open using the path stored to
		// local variable returned from getValues()
        workbooks.open(path);
    }
    catch
    {
        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();

    ttsbegin;

    try
    {
        do
        {
			// 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
            row++;

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

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

    application.quit();

    ttscommit;

    info("Import completed.");
}