How to import data into LedgerJournals through Excel in AX2012

 

The following code helps to import LedgerJournals data in AX2012

 

 

class  GeneralLedgersDataUpload  extends  Runbase

{

    DialogField                     dialogFilename;

    FilenameOpen                fileName;

 

 

    #define.CurrentVersion(1)

    #localmacro.CurrentList

        filename

    #endmacro

}

 

protected Object dialog()

{

    DialogRunBase   dialog = new DialogRunBase("Ledgers Data import", this);

 

    ;

    dialogFilename = dialog.addField(extendedTypeStr(FilenameOpen));

    dialog.filenameLookupFilter(["@SYS28576","*.xlsx"]);

    dialog.filenameLookupTitle("Upload from Excel");

    dialogFilename.value(filename);

    dialog.caption("Excel Upload");

    return dialog;

}

 

 

public boolean getFromDialog()

{

;

    filename        = dialogFilename.value();

 

    if(!filename)

    {

    throw error("@SYS112406");

    }

    return true;

}

 

 

public container pack()

{

    ;

    return [#CurrentVersion,#CurrentList];

} 

 

 

public boolean unpack(container _packedClass)

{

    Integer     version     = conpeek(_packedClass,1);

    ;

    switch (version)

    {

    case #CurrentVersion:

    [version,#CurrentList]      = _packedClass;

    break;

 

    default :

 

    return false;

    }

    return true;

}

 

 

public static GL_LedgersDataUpload construct()

{

    return  new  GeneralLedgersDataUpload();

}

 

 

public static void main(Args _args)

{

    GeneralLedgersDataUpload   ExcelUpload  =  GeneralLedgersDataUpload::construct();

    ;

    if (ExcelUpload.prompt())

    {

        //Call function ExcelUpload.

        ExcelUpload.ImportDatafromExcel();

 

    }

}

 

 

Public void ImportDatafromExcel()

{

    SysExcelApplication application;

    SysExcelWorkbooks   workbooks;

    SysExcelWorkbook    workbook;

    SysExcelWorksheets  worksheets;

    SysExcelWorksheet   worksheet;

    SysExcelCells       cells;

    COMVariantType      type;

    int                                row;

    str                               c,test; 

 

    AxLedgerJournalTable ledgerheader           = new AxLedgerJournalTable();

    AxLedgerJournalTrans ledgertransactions  = new AxLedgerJournalTrans();

 

    boolean                                            temp,duplicateRecCheck;

    NoYes                                                noYesEnum;

 

    LedgerJournalId                               oldnum, newnum;

    LedgerJournalACType                     ledgerJournalACType,offsetACType;

    ledgerJournalACType                      tmpACType,tmpOffsetACType;

    AccountNum                                     tmpAccount,offsetAccount;

    LedgerDimensionAccount              ledgerDim;

    CustTable                                              _custTable;

    AssetTable                                        _assetTable;

    AssetBookTable                               _assetBookTable;

    AssetBook                                         _assetBook;

    str                                                     department;

    LedgerAccountContract               ledgerAccountContract;

 

    DimensionAttributeValueContract attributeValueContract;

    DimensionStorage        dimensionStorage;

 

 

    ;

    duplicateRecCheck = false;

    temp    = false;

    row     = 2;

 

    application = SysExcelApplication::construct();

    workbooks = application.workbooks();

    workbooks.open(fileName,0,true);

 

    try

    {

        workbooks.open(filename,0,0,2);

    }

    catch (Exception::Error)

    {

        throw error("File cannot be opened.");

    }

 

    workbook = workbooks.item(1);

    worksheets = workbook.worksheets();

    worksheet = worksheets.itemFromNum(1);

    cells = worksheet.cells();

 

    //do

   // breakpoint;

    while(cells.item(row,1).value().toString()!= "VT_EMPTY")

   // while (type != COMVariantType::VT_EMPTY)

    {

 

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

        if(oldnum != newnum)

        {

            ledgerheader.parmJournalName( cells.item(row,2).value().bStr());

            ledgerheader.parmJournalNum( cells.item(row,3).value().bStr());

            ledgerheader.parmName( cells.item(row,4).value().bStr());

            ledgerheader.currentRecord().insert();

        }

 

            ledgertransactions.parmJournalNum(cells.item(row,3).value().bStr());

            ledgertransactions.parmTransDate(cells.item(row,5).value().date());

            ledgertransactions.parmVoucher( cells.item(row,6).value().bStr());

            tmpACType = str2enum(ledgerJournalACType,cells.item(row,7).value().bStr());

            ledgertransactions.parmAccountType(tmpACType);

 

            ledgertransactions.parmCurrencyCode(cells.item(row,10).value().bStr());

            ledgertransactions.parmExchRate(cells.item(row,11).value().double());

            ledgertransactions.parmAmountCurDebit(cells.item(row,12).value().double());

            ledgertransactions.parmAmountCurCredit(cells.item(row,13).value().double());

            ledgertransactions.parmTxt(cells.item(row,14).value().bStr());

            tmpOffsetACType =  str2enum(offsetACType,cells.item(row,15).value().bStr());

            ledgertransactions.parmOffsetAccountType(tmpOffsetACType);

 

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

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

            ledgerDim = 0;

 

            if(tmpACType == LedgerJournalACType::Cust)

            {

                    ledgerDim = DimensionStorage::getDynamicAccount(tmpAccount,LedgerJournalACType::Cust);

                    ledgertransactions.parmLedgerDimension(ledgerDim);

                    _custTable = CustTable::find(tmpAccount);

                    ledgertransactions.parmDefaultDimension(_custTable.DefaultDimension);

 

            }

 

            else if(tmpACType == LedgerJournalACType::Vend)

            {

                ledgerDim = DimensionStorage::getDynamicAccount(tmpAccount, LedgerJournalACType::Vend);

                ledgertransactions.parmLedgerDimension(ledgerDim);

                ledgertransactions.parmExchRate(cells.item(row,11).value().double() * 100);

            }

 

            else if(tmpACType == LedgerJournalACType::FixedAssets)

            {

                ledgerDim = DimensionStorage::getDynamicAccount(tmpAccount, LedgerJournalACType::FixedAssets);

                ledgertransactions.parmLedgerDimension(ledgerDim);

 

                _assetTable = AssetTable::find(tmpAccount);

 

                select * from _assetBook where _assetBook.AssetId   == _assetTable.AssetId;

                select _assetBookTable where _assetBookTable.BookId == _assetBook.BookId;

                ledgertransactions.parmDefaultDimension(_assetTable.defaultDimension(_assetBook.BookId));

            }

 

            if(tmpACType == LedgerJournalACType::Ledger)

            {

                ledgerAccountContract = new LedgerAccountContract();

                ledgerAccountContract.parmValues(new List(Types::Class));

                department = 'MainAccount';

 

                ledgerAccountContract.parmMainAccount(tmpAccount);

               if (department)

                {

                    attributeValueContract = new DimensionAttributeValueContract();

                    attributeValueContract.parmName("MainAccount");

                    attributeValueContract.parmValue(tmpAccount);

                    ledgerAccountContract.parmValues().addEnd(attributeValueContract);

                }

 

                dimensionStorage = DimensionServiceProvider::buildDimensionStorageForLedgerAccount(ledgerAccountContract);

                ledgertransactions.parmLedgerDimension(dimensionStorage.save());

 

            }

 

            if(tmpoffsetACType == LedgerJournalACType::Ledger)

            {

                ledgerAccountContract = new LedgerAccountContract();

                ledgerAccountContract.parmValues(new List(Types::Class));

                department = 'MainAccount';

 

                ledgerAccountContract.parmMainAccount(offsetAccount);

                if (department)

                {

                    attributeValueContract = new DimensionAttributeValueContract();

                    attributeValueContract.parmName("MainAccount");

                    attributeValueContract.parmValue(offsetAccount);

                    ledgerAccountContract.parmValues().addEnd(attributeValueContract);

                }

 

                dimensionStorage = DimensionServiceProvider::buildDimensionStorageForLedgerAccount(ledgerAccountContract);

                ledgertransactions.parmOffsetLedgerDimension(dimensionStorage.save());

 

            }

 

            ledgertransactions.currentRecord().insert();

            temp = true;

            oldnum =  ledgerheader.parmJournalName( cells.item(row,2).value().bStr());

 

        row++;

       }

 

    if(temp == true)

    {

    info(strfmt("Uploaded successfully."));

    }

    else

    {

    info(strfmt("Upload fail."));

    }

 

    application.quit();

}