AX 2012: Excel import/Converting the excel cells value to AX type

The below code helps when converting the excel cells type (variant) to actual AX type.

Below example shows how we can trigger the method:

invoiceNumber = this.formatValue(Types::String,cells.item(row, 1).value());

public anytype formatValue(     Types _types,     COMVariant _variant) {     #TimeConstants     FreeText excelText;     Qty realValue;     Integer intValue;     TimeHour24 timeHour24;     switch (_types)     {         case Types::UtcDateTime :         // Time             switch (_variant.variantType())             {                 case COMVariantType::VT_R4 :                 realValue = _variant.float();                 break;                 case COMVariantType::VT_R8 :                 realValue = _variant.double();                 break;                 case COMVariantType::VT_DECIMAL :                 realValue = _variant.decimal();                 break;                 case COMVariantType::VT_BSTR :                 timeHour24 = str2time(_variant.bStr());                 break;                 case COMVariantType::VT_EMPTY:                 break;                 default:                 throw error(strfmt("@SYS26908", _variant.variantType()));             }         timeHour24 = any2int(#secondsPerDay * realValue);         return timeHour24;         case Types::Integer :         // Integer             switch (_variant.variantType())             {                 case COMVariantType::VT_EMPTY:                 return 0;                 case COMVariantType::VT_I1:                 return _variant.char();                 case COMVariantType::VT_I2:                 return _variant.short();                 case COMVariantType::VT_I4:                 intValue = _variant.int();                 if (intValue == 0)                 {                     intValue = _variant.long();                 }                 return intValue;                 case COMVariantType::VT_UI1:                 return _variant.byte();                 case COMVariantType::VT_UI2:                 return _variant.uShort();                 case COMVariantType::VT_UI4:                 intValue = _variant.uInt();                 if (intValue == 0)                 {                     intValue = _variant.uLong();                 }                 return intValue;                 case COMVariantType::VT_R4 :                 realValue = _variant.float();                 return realValue;                 case COMVariantType::VT_R8 :                 realValue = _variant.double();                 return realValue;                 case COMVariantType::VT_DECIMAL :                 realValue = _variant.decimal();                 return realValue;                 default:                 throw error(strfmt("@SYS26908", _variant.variantType()));             }             break;         case Types::Real :         // Real         if (_variant.bStr())         {             return str2num(_variant.bStr());         }         else         {             switch (_variant.variantType())             {                 case COMVariantType::VT_EMPTY:                 realValue = 0;                 break;                 case COMVariantType::VT_R4 :                 realValue = _variant.float();                 break;                 case COMVariantType::VT_R8 :                 realValue = _variant.double();                 break;                 case COMVariantType::VT_DECIMAL :                 realValue = _variant.decimal();                 break;                 default :                 throw error(strfmt("@SYS26908", _variant.variantType()));             }             return realValue;         }         case Types::String :         case Types::RString :         case Types::VarString :         // String         switch (_variant.variantType())         {             case COMVariantType::VT_BSTR :             return _variant.bStr();             case COMVariantType::VT_EMPTY:             return '';             case COMVariantType::VT_I1:             return _variant.char();             case COMVariantType::VT_I2:             return int2str(_variant.short());             case COMVariantType::VT_I4:             intValue = _variant.int();             if (intValue == 0)             {                 intValue = _variant.long();             }             return int2str(intValue);             case COMVariantType::VT_UI1:             return int2str(_variant.byte());             case COMVariantType::VT_UI2:             return int2str(_variant.uShort());             case COMVariantType::VT_UI4:             intValue = _variant.uInt();             if (intValue == 0)             {                 intValue = _variant.uLong();             }             return int2str(intValue);             case COMVariantType::VT_R8 :             realValue = _variant.double();             return num2str(realValue, 1, numOfDec(realValue), 0, 0);             case COMVariantType::VT_R4 :             case COMVariantType::VT_DECIMAL :             return '';         }         case Types::Date :         // Date         switch (_variant.variantType())         {             case COMVariantType::VT_BSTR :             excelText = _variant.bStr();             return str2date(excelText, 213);             default :             return _variant.date();         }         case Types::Guid :         // Guid         return str2guid(_variant.bStr());         case Types::Int64 :         // Int64         return str2int64(_variant.bStr());         }     return ''; }