X++ code for Importing general journals through excel in ax 2012

static void Journal(Args _args)

{

    str         mBusinessUnit,mLOB, mCLOB, mDept, mLocation, mOrigin,mProject, mEmployee,mIntercompany;

    str         oBusinessUnit,oLOB, oCLOB, oDept, oLocation, oOrigin, oProject, oEmployee,oIntercompany;

    str         invoice;

    str         TDSgroup,salesTaxGroup,itemSalesTax;

    date        documentdate;

    Voucher     voucher;

    TaxOnItem                       TaxOnItem;

    TaxGroupData                    TaxGroupData;

    real                            TaxAmount = 0, TaxAmount_1 = 0;

    TaxValue                        TaxValue = 0, TaxValue_1 = 0;

    SysExcelApplication application;

    SysExcelWorkbooks workbooks;

    SysExcelWorkbook workbook;

    SysExcelWorksheets worksheets;

    SysExcelWorksheet worksheet;

    SysExcelCells cells;

    COMVariantType type;

    Name name;

    FileName filename;

    str path;

    str filetype;

    int i;

    real Debit,Credit;

    NoYes   NoYes;

    FileIoPermission perm;

    DocuValue docuvalue;

    #define.FileMode('W')

    TransDate transdate;

    str Currency,accountType,Accountnumb,text,OffsetAccounttype,offsetAccountnum;

    ledgerJournalTrans ledgerJournalTrans;

    ledgerJournalTable ledgerJournalTable;

    Numbersequencetable numSeqTable;

    NumberSeq numberseq;

    LedgerJournalACType LedgerJournalACType;

    container cont1,cont2,ledgerDimension,offSetAcctPattern;

    DimensionDefault        DimensionDefault;   //EDT

DimensionDynamicAccount ledgerDim, offsetledgerDim;

    int                 row = 0,cnt;

     filename = "D:\\jourimport1sample-APINV-DEV(1).xlsx";

         perm = new FileIOPermission(filename, #FileMode);

        perm.assert();

        application = SysExcelApplication::construct();

        workbooks = application.workbooks();

    try

    {

        workbooks.open(filename);

    }

    catch (Exception::Error)

    {

        throw error("@SYS19358");

    }

    workbook    = workbooks.item(1);

    worksheets  = workbook.worksheets();

    worksheet   = worksheets.itemFromNum(1);

    cells       = worksheet.cells();

    row = 1;

    startLengthyOperation();

    //progress.setCaption("@KPM1054");

    //progress.setAnimation(#AviUpdate);

    do

    {

        row++;

        //progress.setText(strfmt("@KPM1055", row));

        transdate       = cells.item(row, 1).value().date();

        Currency        = cells.item(row, 2).value().bStr();

        accountType     = cells.item(row, 3).value().bStr();

        AccountNumb = strFmt("%1", cells.item(row, 4).value().bStr());

        switch(cells.item(row, 4).value().variantType())

        {

            case COMVariantType::VT_BSTR:

                AccountNumb = strFmt("%1", cells.item(row, 4).value().bStr());

                break;

            case COMVariantType::VT_DECIMAL, COMVariantType::VT_R4, COMVariantType::VT_R8:

                AccountNumb = strFmt("%1", any2int(cells.item(row, 4).value().double()));

                break;

            case COMVariantType::VT_I1, COMVariantType::VT_I2, COMVariantType::VT_I4:

                AccountNumb = strFmt("%1", cells.item(row, 4).value().int());

                break;

            case COMVariantType::VT_UI1, COMVariantType::VT_UI2, COMVariantType::VT_UI4:

                AccountNumb = strFmt("%1", cells.item(row, 4).value().uLong());

                break;

            case COMVariantType::VT_EMPTY:

                AccountNumb = '';

                break;

            default:

                throw error(strfmt("@KPM1056", cells.item(row+1, 1).value().variantType()));

        }

        mbusinessUnit       = cells.item(row, 5).value().bStr();

        mLOB                = cells.item(row, 6).value().bStr();

        mCLOB               = cells.item(row, 7).value().bStr();

        mDept               = cells.item(row, 8).value().bStr();

        mLocation           = cells.item(row, 9).value().bStr();

        mOrigin             = cells.item(row, 10).value().bStr();

        mProject            = cells.item(row, 11).value().bStr();

        mEmployee           = cells.item(row, 12).value().bStr();

        mIntercompany       = cells.item(row, 13).value().bStr();

        //invoice         = cells.item(row, 14).value().bStr();

        Text            = cells.item(row, 15).value().bStr();

        Debit           = any2real(cells.item(row, 16).value().double());

        Credit          = any2real(cells.item(row, 17).value().double());

        OffsetAccounttype = cells.item(row, 18).value().bStr();

        switch(cells.item(row, 19).value().variantType())

        {

            case COMVariantType::VT_BSTR:

                offsetAccountnum = strFmt("%1", cells.item(row, 19).value().bStr());

                break;

            case COMVariantType::VT_DECIMAL, COMVariantType::VT_R4, COMVariantType::VT_R8:

                offsetAccountnum = strFmt("%1", any2int(cells.item(row, 19).value().double()));

                break;

            case COMVariantType::VT_I1, COMVariantType::VT_I2, COMVariantType::VT_I4:

                offsetAccountnum = strFmt("%1", cells.item(row, 19).value().int());

                break;

            case COMVariantType::VT_UI1, COMVariantType::VT_UI2, COMVariantType::VT_UI4:

                offsetAccountnum = strFmt("%1", cells.item(row, 19).value().uLong());

                break;

            case COMVariantType::VT_EMPTY:

                offsetAccountnum = '';

                break;

            default:

                throw error(strfmt("@KPM1056", cells.item(row, 19).value().variantType()));

        }

        obusinessUnit       = cells.item(row, 20).value().bStr();

        oLOB                = cells.item(row, 21).value().bStr();

       oCLOB               = cells.item(row, 22).value().bStr();

        oDept               = cells.item(row, 23).value().bStr();

        oLocation           = cells.item(row, 24).value().bStr();

        oOrigin             = cells.item(row, 25).value().bStr();

        oProject            = cells.item(row, 26).value().bStr();

        oEmployee       = cells.item(row, 27).value().bStr();

        oIntercompany   = cells.item(row, 28).value().bStr();

        TDSgroup        = cells.item(row, 29).value().bStr();

        salesTaxGroup   = cells.item(row, 30).value().bStr();

        itemSalesTax    = cells.item(row, 31).value().bStr();

        documentdate    = cells.item(row, 32).value().date();

        //In Excel cell should be in Text format

        try

        {

            ttsbegin;

            voucher = '';

            ledgerJournalTrans.clear();

            ledgerJournalTrans.initValue();

            ledgerJournalTrans.JournalNum   = "INMF-00485";//ledgerJournalTable.JournalNum;

            ledgerJournalTrans.TransDate    = transdate;

            ledgerJournalTrans.Approved          = NoYes::Yes;

            ledgerJournalTrans.Approver          = HcmWorker::userId2Worker(curuserid());

            select firstOnly numSeqTable

                where numSeqTable.RecId  == LedgerJournalName::find("GenJN").NumberSequenceTable;

            if (numSeqTable && !voucher)

            {

                numberseq = numberseq::newGetVoucherFromCode(numSeqTable.NumberSequence);

                voucher = numberseq.voucher();

            }

            ledgerJournalTrans.Voucher              = voucher;

            ledgerJournalTrans.AccountType          = str2enum(LedgerJournalACType, accountType);

            // Main account dimensions

            cont1=conNull();

            cont2=conNull();

            ledgerDimension =conNull();

            cnt=0;

            //Account type

            if(mBusinessUnit != '')

            {

                cnt++;

                cont2+=['BU',mBusinessUnit];

            }

            if(mLOB != '')

            {

                cnt++;

                cont2+=['LOB',mLOB];

            }

            if(mCLOB != '')

            {

                cnt++;

                cont2+=['CLOB',mCLOB];

            }

            if(mDept != '')

            {

                cnt++;

                cont2+=['Department',mDept];

            }

            if(mLocation != '')

            {

                cnt++;

                cont2+=['Location',mLocation];

            }

            if(mOrigin != '')

            {

                cnt++;

                cont2+=['Origin',mOrigin];

            }

            if(mProject != '')

            {

                cnt++;

                cont2+=['Projects',mProject];

            }

            if(mEmployee != '')

            {

                cnt++;

                cont2+=['Worker',mEmployee]; //Employee

            }

            if(mIntercompany != '')

            {

                cnt++;

                cont2+=['Intercompany',mIntercompany];

            }

            if(ledgerJournalTrans.AccountType  == LedgerJournalACType::Ledger)

            {

                cont1+=['MainAccount',AccountNumb,cnt];

                cont1+=cont2;

                ledgerDim = AxdDimensionUtil::getLedgerAccountId(cont1);

                if(ledgerDim==0)

                {

                        offSetAcctPattern = [AccountNumb,AccountNumb];

                        ledgerDim = AxdDimensionUtil::getLedgerAccountId( offSetAcctPattern);

                }

                ledgerJournalTrans.LedgerDimension  = ledgerDim;

            }

            else

            {

                ledgerDim = DimensionStorage::getDynamicAccount( AccountNumb,ledgerJournalTrans.AccountType);

                ledgerDimension +=cnt;

                ledgerDimension +=cont2;

                DimensionDefault = AxdDimensionUtil::getDimensionAttributeValueSetId(ledgerDimension);

                ledgerJournalTrans.LedgerDimension  = ledgerDim;

                LedgerJournalTrans.modifiedField(fieldNum(LedgerJournalTrans,LedgerDimension));

                ledgerJournalTrans.DefaultDimension = DimensionDefault;

            }

            ledgerJournalTrans.Txt                  = Text;

            ledgerJournalTrans.CurrencyCode         = Currency;

            ledgerJournalTrans.AmountCurDebit       = Debit;

            ledgerJournalTrans.AmountCurCredit      = Credit;

            if(offsetaccountType)

            {

                ledgerJournalTrans.OffsetAccountType    = str2enum(LedgerJournalACType, offsetaccountType);

            }

            else

            {

                   ledgerJournalTrans.OffsetAccountType    = LedgerJournalACType::Ledger;

            }

            cont1=conNull();

            cont2=conNull();

            ledgerDimension =conNull();

            cnt=0;

            //Offset Account Type

            if(oBusinessUnit != '')

            {

                cnt++;

                cont2+=['BU',oBusinessUnit];

            }

            if(oLOB != '')

            {

                cnt++;

                cont2+=['LOB',oLOB];

            }

            if(oCLOB != '')

            {

                cnt++;

                cont2+=['CLOB',oCLOB];

            }

            if(oDept != '')

            {

                cnt++;

                cont2+=['Department',oDept];

            }

            if(oLocation != '')

            {

                cnt++;

                cont2+=['Location',oLocation];

            }

            if(oOrigin != '')

            {

                cnt++;

                cont2+=['Origin',oOrigin];

            }

            if(oProject != '')

            {

                cnt++;

                cont2+=['Projects',oProject];

            }

            if(oEmployee != '')

            {

                cnt++;

                cont2+=['Worker',oEmployee];

            }

            if(oIntercompany != '')

            {

                cnt++;

                cont2+=['Intercompany',oIntercompany];

            }

            if (ledgerJournalTrans.OffsetAccountType  == LedgerJournalACType::Ledger && offsetAccountnum)

            {

                cont1+=['MainAccount',offsetAccountnum,cnt];

                cont1+=cont2;

                offsetledgerDim =AxdDimensionUtil::getLedgerAccountId(cont1);

                if(offsetledgerDim == 0)

                {

                    offSetAcctPattern = [offsetAccountnum,offsetAccountnum];

                    offsetledgerDim = AxdDimensionUtil::getLedgerAccountId( offSetAcctPattern);

                }

                ledgerJournalTrans.OffsetLedgerDimension = offsetledgerDim;

            }

            else

            {

                if(offsetAccountnum)

                {

                    offsetledgerDim = DimensionStorage::getDynamicAccount(offsetAccountnum,ledgerJournalTrans.OffsetAccountType);

                    ledgerDimension +=cnt;

                    ledgerDimension +=cont2;

                    DimensionDefault = AxdDimensionUtil::getDimensionAttributeValueSetId(ledgerDimension);

                    ledgerJournalTrans.OffsetLedgerDimension = offsetledgerDim;

                    LedgerJournalTrans.modifiedField(fieldNum(LedgerJournalTrans,OffsetLedgerDimension));

                    ledgerJournalTrans.OffsetDefaultDimension = DimensionDefault;

                }

            }

            ledgerJournalTrans.TaxGroup     = salesTaxGroup;

            ledgerJournalTrans.TaxItemGroup = itemSalesTax;

             //tax1 = Tax::calcTaxAmount(ledgerJournalTrans.TaxGroup, ledgerJournalTrans.TaxItemGroup, Systemdateget(), ledgerJournalTrans.CurrencyCode, ledgerJournalTrans.AmountCurCredit, TaxModuleType::);

       // TaxLedgerJournalCalculate::totalTaxAmountSingleLine(TaxJournalCall::Journal, _ledgerJournalTrans, 0, 0);

            //TaxAmount_1 = 0;

            //if(ledgerJournalTrans.TaxItemGroup && ledgerJournalTrans.TaxGroup) //&& ledgerJournalTrans.AmountCurCredit != 0)

            //{

                //while select TaxOnItem

                    //where TaxOnItem.TaxItemGroup == ledgerJournalTrans.TaxItemGroup

                //{

                    //if(TaxOnItem)

                    //{

                        //while select TaxGroupData

                            //where TaxGroupData.TaxGroup == ledgerJournalTrans.TaxGroup

                                //&& TaxGroupData.TaxCode  == TaxOnItem.TaxCode

                        //{

                            //if(TaxGroupData)

                            //{

                                //TaxAmount = 0;

                                //TaxValue  =  TaxData::find(TaxOnItem.TaxCode, Systemdateget(), 0).TaxValue;

                                //TaxValue_1  += TaxValue;

                                //if(ledgerJournalTrans.AmountCurCredit != 0)

                                    //TaxAmount = (ledgerJournalTrans.AmountCurCredit* TaxValue)/100;

                                //else if(ledgerJournalTrans.AmountCurDebit != 0)

                                    //TaxAmount = (ledgerJournalTrans.AmountCurCredit* TaxValue)/100;

                                //TaxAmount_1 += TaxAmount;

                            //}

                        //}

                    //}

                //}

            //}

            ledgerJournalTrans.DocumentDate = documentdate;

            ledgerJournalTrans.TDSGroup_IN  = TDSgroup;

            if (ledgerJournalTrans.validateWrite())

            {

                ledgerJournalTrans.insert();

                 info(strFmt('journal inserted %1 - %2',ledgerJournalTable.JournalNum,row));

            }

            ttscommit;

            type = cells.item(row+1, 1).value().variantType();

        }

        catch(Exception::Error)

        {

            info(strFmt('Catched an error in row: %1',row));

        }

    }

    while (type != COMVariantType::VT_EMPTY);

    application.quit();

    endLengthyOperation();

}

excel format :