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();
}