Skip to main content

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 '';
}




Comments