web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Blogs / Vishal’s blog on MS Dynamics 365 - Technical / AX 2012: Excel import/Conve...

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

Views (4102)
Vishal Tiwari Profile Picture Vishal Tiwari 393
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 '';
}



This was originally posted here.

Comments

*This post is locked for comments