Importing Journals from Excel using X++ in AX 2009
class VIKAS_ImportVendInvoiceJournalLines
{
Dialog dialog;
DialogField dialogfield;
Filename filename;
#AviFiles
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
COMVariantType typeModule;
NumberSeq numberSeq;
NumberSequenceTable numSeqTable;
str Name, num,text, currency,businessunit,account,accountType, department,offsetaccount,OffsetAccounttype;
int64 costcenter;
real debit, credit;
container Account1, offsetaccount1;
str acc;
date transdate;
LedgerJournalName ledgerJournalName;
LedgerJournalTable ledgerJournalTable;
LedgerJournalTrans ledgerJournalTrans;
container cont1,cont2,offSetAcctPattern;
int cnt;
LedgerJournalAC AccountNumb, offsetAccountnum;
container ledgerDimension;
LedgerJournalACType LedgerJournalACType;
boolean ret;
}
--------------------------------------------------------------------------------------------------------------------------
public void DataImport()
{
str
mSBU,mDepartment,mCostCenter,mPurpose;
str
oSBU,oDepartment,oCostCenter,oPurpose;
str invoice;
str
TDSgroup,salesTaxGroup,itemSalesTax;
date documentdate;
Voucher voucher;
SysOperationProgress progress = new
SysOperationProgress();
int
row = 0;
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
ledgerJournalName =
ledgerjournalname::find(ledgerjournaltable.JournalName);
row = 1;
do
{
row++;
transdate =
cells.item(row, 1).value().date();
Currency
= cells.item(row, 2).value().bStr();
accountType =
cells.item(row, 3).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('Unhandled variant type (%1).', cells.item(row+1,
1).value().variantType()));
}
mSBU
= cells.item(row, 5).value().bStr();
mDepartment =
cells.item(row, 6).value().bStr();
mCostCenter =
cells.item(row, 7).value().bStr();
mPurpose
= cells.item(row, 8).value().bStr();
invoice
= cells.item(row, 9).value().bStr();
Text
= cells.item(row, 10).value().bStr();
Debit
= any2real(cells.item(row, 11).value().double());
Credit
= any2real(cells.item(row, 12).value().double());
OffsetAccounttype = cells.item(row,
13).value().bStr();
switch(cells.item(row,
14).value().variantType())
{
case
COMVariantType::VT_BSTR:
offsetAccountnum = strFmt("%1", cells.item(row, 14).value().bStr());
break;
case
COMVariantType::VT_DECIMAL, COMVariantType::VT_R4, COMVariantType::VT_R8:
offsetAccountnum = strFmt("%1", any2int(cells.item(row,
14).value().double()));
break;
case
COMVariantType::VT_I1, COMVariantType::VT_I2, COMVariantType::VT_I4:
offsetAccountnum = strFmt("%1", cells.item(row, 14).value().int());
break;
case
COMVariantType::VT_UI1, COMVariantType::VT_UI2, COMVariantType::VT_UI4:
offsetAccountnum = strFmt("%1", cells.item(row, 14).value().uLong());
break;
case
COMVariantType::VT_EMPTY:
offsetAccountnum = '';
break;
default:
throw
error(strfmt('Unhandled variant type (%1).', cells.item(row,
1).value().variantType()));
}
oSBU
= cells.item(row, 15).value().bStr();
oDepartment =
cells.item(row, 16).value().bStr();
oCostCenter =
cells.item(row, 17).value().bStr();
oPurpose
= cells.item(row, 18).value().bStr();
TDSgroup
= cells.item(row, 19).value().bStr();
salesTaxGroup =
cells.item(row, 20).value().bStr();
itemSalesTax =
cells.item(row, 21).value().bStr();
documentdate =
cells.item(row, 22).value().date();
//In Excel cell should be in Text
format
try
{
ttsbegin;
ledgerJournalTrans.clear();
ledgerJournalTrans.initValue();
ledgerJournalTrans.JournalNum = ledgerJournalTable.JournalNum;
ledgerJournalTrans.TransDate
= transdate;
ledgerJournalTrans.Dimension[1] = mSBU;
ledgerJournalTrans.Dimension[2] = mDepartment;
ledgerJournalTrans.Dimension[3] = mCostCenter;
ledgerJournalTrans.Dimension[4] = mPurpose;
//select firstOnly
numSeqTable
// where
numSeqTable.RecId == ledgerJournalName.NumberSequenceTable;
if (!voucher)
{
numberseq = numberseq::newGetVoucherFromCode(ledgerJournalName.VoucherSeries);
voucher
= numberseq.voucher();
}
ledgerJournalTrans.Voucher =
voucher;
ledgerJournalTrans.AccountType =
str2enum(LedgerJournalACType, accountType);
ledgerJournalTrans.AccountNum = AccountNumb;
ledgerJournalTrans.Txt
= Text;
ledgerJournalTrans.CurrencyCode = Currency;
ledgerJournalTrans.AmountCurDebit = Debit;
ledgerJournalTrans.AmountCurCredit = Credit;
ledgerJournalTrans.OffsetAccountType =
str2enum(LedgerJournalACType, offsetaccountType);
ledgerJournalTrans.OffsetAccount
= offsetAccountnum;
ledgerJournalTrans.Invoice =
invoice;
ledgerJournalTrans.TaxGroup =
salesTaxGroup;
ledgerJournalTrans.TaxItemGroup = itemSalesTax;
ledgerJournalTrans.DocumentDate
= documentdate;
ledgerJournalTrans.TDSGroup_IN = TDSgroup;
if
(ledgerJournalTrans.validateWrite())
{
ledgerJournalTrans.insert();
}
ttscommit;
type =
cells.item(row+1, 1).value().variantType();
}
catch(Exception::Error)
{
info(strFmt('Catched
an error in row: %1',row));
}
info(strFmt('journal inserted %1',ledgerJournalTable.JournalNum));
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
}
-------------------------------------------------------------------------------------------------------------------------
public void run(Args _args)
{
;
LedgerJournalTable = _args.record();
dialog = new dialog('Excel Import');
dialogfield =
dialog.addField(TypeId(FilenameOpen), 'File Name');
dialog.run();
if(dialog.run())
{
filename =(dialogfield.value());
}
application = SysExcelApplication::construct();
workbooks = application.workbooks();
if(filename)
{
try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error('File
cannot be opened.');
}
this.DataImport();
}
}
-------------------------------------------------------------------------------------------------------------------------
public static void main(Args args)
{
VIKAS_ImportVendInvoiceJournalLines import = new VIKAS_ImportVendInvoiceJournalLines();
import.run(args);
}
--------------------------------------------------------------------------------------------------------------------------
void clicked()
{
FormRun callerForm;
Args args = new Args();
VIKAS_ImportVendInvoiceJournalLines VIKAS_ImportVendInvoiceJournalLines
= new VIKAS_ImportVendInvoiceJournalLines();
;
args.record(ledgerjournaltable);
VIKAS_ImportVendInvoiceJournalLines.run(args);
callerForm =
element.args().caller();
callerForm.dataSource().refresh();
callerForm.dataSource().reread();
}
No comments:
Post a Comment