Dynamics AX2012 Excel模板V2

代码如下

static void AddNewStandardCOAinAX(Args _args)
{
    str _filename=@"C:\Users\charlotte.yin\Desktop\Standard COA importing.xlsx";
    str         strTableName,strFieldName,strEndFlag,strData,strTab,PersonalNum;
    str         strMainAccountType,strExchangeAdjusted;
    str         Chartofaccounts,Translations;
    NoYes       exchangeAdjusted;
    str 1260 s=curExt();
    MainAccountNum MainAccountId;
    AccountName Name,Name_CN;
    name mainAccountCategoryName;
    int         row,intSheet;
    int         Pos,lenstr;
    SysExcelApplication   ExcelApp;
    SysExcelCells         ExcelCells;
    mainAccountCategory  mainAccountCategory;

    DimensionLedgerAccountType  accountType;
    CompanyInfo             companyInfo;

    MainAccount  MainAccount;
    MainAccountTranslation mainAccountTranslation;


    if (!WinAPI::fileExists(_filename))
        throw error('file not exists.');

    ExcelApp = SysExcelApplication::construct();
    //Opens the file
    excelapp.displayAlerts(false);
    ExcelApp.workbooks().open(_filename);

    intSheet = 9;
    row = 7;

    ExcelCells = ExcelApp.worksheets().itemFromName('MainAccounts').cells();

    strEndFlag = DSA_CExcelDataCovert::Value(ExcelCells.item(row,1).value());
    //LedgerChartOfAccounts::findByName();
    while (strEndFlag)
    {
        Chartofaccounts  = DSA_CExcelDataCovert::Value(ExcelCells.item(row,1).value());
        MainAccountId    = DSA_CExcelDataCovert::Value(ExcelCells.item(row,2).value());
        Name             = DSA_CExcelDataCovert::Value(ExcelCells.item(row,3).value());
        Translations     = DSA_CExcelDataCovert::Value(ExcelCells.item(row,4).value());
        strMainAccountType = DSA_CExcelDataCovert::Value(ExcelCells.item(row,5).value());
        strExchangeAdjusted = DSA_CExcelDataCovert::Value(ExcelCells.item(row,7).value());




        select companyInfo where companyInfo.DataArea == curext();
        select forupdate MainAccount
             where MainAccount.MainAccountId == MainAccountId &&
                   MainAccount.LedgerChartOfAccounts == Ledger::findByLegalEntity(companyInfo.RecId).ChartOfAccounts;

        if(MainAccountId == MainAccount.MainAccountId)
        {
            info(strFmt("%1 %2 %3 is duplicate with the existing account",curext(),MainAccountId,Name));
        }
        else
        {
            ttsBegin;
            /*
            MainAccountId = MainAccount.MainAccountId;
            Name          = MainAccount.Name;
            strMainAccountType   = enum2str(MainAccount.Type);
            */

              switch (strMainAccountType)
                {
                    case "Asset":
                    accountType = DimensionLedgerAccountType::Asset;
                        break;
                    case "Total":
                    accountType = DimensionLedgerAccountType::Total;
                        break;
                     case "Liability":
                    accountType = DimensionLedgerAccountType::Liability;
                        break;
                    case "Equity":
                    accountType = DimensionLedgerAccountType::Equity;
                        break;

                     case "Revenue":
                    accountType = DimensionLedgerAccountType::Revenue;
                        break;
                    case "Expense":
                    accountType = DimensionLedgerAccountType::Expense;
                        break;
                }
        if (strExchangeAdjusted == "Yes" || strExchangeAdjusted == "yes" ||strExchangeAdjusted == "YES")
            exchangeAdjusted = NoYes::Yes;
        else
            exchangeAdjusted = NoYes::No;

            MainAccount.MainAccountId = MainAccountId;
            MainAccount.Name          = Name;
            MainAccount.Type          = accountType;
            MainAccount.ExchangeAdjusted = exchangeAdjusted;
            MainAccount.insert();

            select forUpdate mainAccountTranslation
               where mainAccountTranslation.MainAccount == MainAccount.RecId &&
                     mainAccountTranslation.LanguageId == "zh-hans";
            //mainAccountTranslation.MainAccount = MainAccount::findByMainAccountId(MainAccountId).RecId;
            mainAccountTranslation.MainAccount = MainAccount.RecId;
            mainAccountTranslation.LanguageId  = "zh-hans";
            mainAccountTranslation.Name = Translations;
            //mainAccountTranslation.ExchangeAdjusted = NoYes::Yes;
            mainAccountTranslation.write();

            ttsCommit;
            //break;

        }

        row++;

        strEndFlag = DSA_CExcelDataCovert::Value(ExcelCells.item(row,1).value());


    }

    ExcelApp.workbooks().close();
    ExcelApp.quit();

}