Excel import

Case Study: Reading cell content from excel template for COM variant type VT_R4 or VT_R8 is always little tricky.
Observation: Reading real value can be done in following ways

1) num2Str0(_variant.double(), 0);

2) num2str(_variant.double(), 0, numOfDec(_variant.double()), 1, 0);

            Here is the output which is generated where the first function value is always a round-off value compared with the second function which returns the exact content with correct scale and precision.

COMVariantType Functions output for Real values.

 

/* 
Build excel template as following 
     and specify the path @ excel
=======================================
Column     Integer     Real                
=======================================
Rows(1)    123         60.9756097560976
Rows(2)    234         5.69105691056911
=======================================
*/

static void SR_VariantType(Filename excel = @'C:\Projects\Data.xlsx')
{
    int rows; int columns; COMVariant variant; SysExcelCells sysExcelCells; SysExcelWorkbook sysExcelWorkbook; SysExcelWorkbooks sysExcelWorkbooks; SysExcelWorksheet sysExcelWorksheet; SysExcelWorksheets sysExcelWorksheets; SysExcelApplication sysExcelApplication; str variant2Str(COMVariant _variant) { str valueStr; ; switch(_variant.variantType()) { case COMVariantType::VT_EMPTY : valueStr = ''; break; case COMVariantType::VT_BSTR : valueStr = _variant.bStr(); break; case COMVariantType::VT_R4 : case COMVariantType::VT_R8 : if(_variant.double()) { valueStr = strFmt("@SYS311964", num2Str0(_variant.double(), 0), num2str(_variant.double(), 0, numOfDec(_variant.double()), 1, 0)); } break; default : throw error(strfmt("@SYS26908", _variant.variantType())); } return valueStr; } ; sysExcelApplication = SysExcelApplication::construct(); sysExcelWorkbooks = sysExcelApplication.workbooks(); try { sysExcelWorkbooks.open(excel, false /*Update links*/, true /*Read only*/); } catch (Exception::Error) { throw error(strFmt("@SYS76826", excel)); } sysExcelWorkbook = sysExcelWorkbooks.item(1); sysExcelWorksheets = sysExcelWorkbook.worksheets(); // Only considering Sheet 1 sysExcelWorksheet = sysExcelWorksheets.itemFromNum(1); sysExcelCells = sysExcelWorksheet.cells(); // Since in first row there will be field names. for ( rows = 2; rows <= 3; rows++) { for (columns = 1; columns <= 2; columns++) { variant = sysExcelCells.item(rows, columns).value(); print variant2Str(variant); pause; } } // Close Excel sysExcelApplication.quit(); variant = null; sysExcelWorkbooks = null; sysExcelWorkbook = null; sysExcelWorksheet = null; sysExcelCells = null; sysExcelApplication = null; }

转载于:https://www.cnblogs.com/lingdanglfw/p/6693870.html