https://vasanthax.wordpress.com/2010/08/19/import-excel-data-into-dynamics-ax-2009/
Success One:: Class:
Class Declaraion:
public class KR_CustPaymentImport extends objectRun
{
}
//////////////////////////////////////////////////////////////////////////
2: Run:
void run()
{
LedgerJournalTable ledgerJournaltable;
LedgerjournalTrans ledgerjournalTrans;
LedgerJournalName ledgerJournalName;
NumberSeq numberSeq;
COMVariantType type,typeLoc;
LedgerJournalACType ledgerJournalACType;
Dialog dialog;
DialogField dialogField;
Filename filename;
// NumberSeq numSeq;
str jourName;
int lineNum;
COMVariant COMVariant1;
SysExcelApplication app;
SysExcelWorkbooks Workbooks;
SysExcelWorkbook Workbook;
SysExcelWorksheets Worksheets;
SysExcelWorksheet Worksheet;
SysExcelCells Cells;
SysExcelCell RCell;
int row;
int temp;
#Excel
str COMVariant2Str(COMVariant _cv,
int _decimals = 0,
int _characters = 0,
int _separator1 = 0,
int _separator2 = 0)
{
switch(_cv.variantType())
{
case (COMVariantType::VT_BSTR):
return _cv.bStr();
case (COMVariantType::VT_R4):
return num2str(_cv.float(),
_characters,
_decimals,
_separator1,
_separator2);
case (COMVariantType::VT_R8):
return num2str(_cv.double(),
_characters,
_decimals,
_separator1,
_separator2);
case (COMVariantType::VT_DECIMAL):
return num2str(_cv.decimal(),
_characters,
_decimals,
_separator1,
_separator2);
case (COMVariantType::VT_DATE):
return date2str(_cv.date(),
123,
2,
1,
2,
1,
4);
case (COMVariantType::VT_EMPTY):
return "";
default:
throw error(strfmt("@SYS26908",
_cv.variantType()));
}
return "";
}
;
dialog = new Dialog("Import Customer Payment Journal.");
dialogfield = dialog.addField(typeid(Filenameopen), "Select File");
dialog.run();
if (dialog.run())
{
filename = (dialogfield.value());
}
app = SysExcelApplication::construct();
app.visible(false);
Workbooks = app.Workbooks();
Workbooks.open(filename,0);
Workbook = Workbooks.item(1);
Worksheets = Workbook.worksheets();
Worksheet = Worksheets.itemFromNum(1);
Cells = Worksheet.Cells();
row = 2;
RCell = Cells.Item(row, 1);
select firstonly ledgerjournalName where ledgerJournalName.JournalName == 'ARPay';
ledgerJournalTable.JournalName = LedgerJournalName.JournalName;
ledgerJournalTable.initFromLedgerJournalName();
ledgerJournalTable.insert();
numberSeq = NumberSeq::newGetVoucherFromCode(ledgerJournalName.VoucherSeries);
// numberSeq = NumberSeq::newGetVoucherFromCode(ledgerJournalName.VoucherSeries);
ttsbegin;
// while(RCell.Value().double() > 0)
do
{
ledgerJournalTrans.clear();
ledgerJournalTrans.voucher = numberSeq.voucher();
ledgerJournalTrans.LineNum = lineNum;
ledgerJournalTrans.AccountType = LedgerJournalACType::Cust;
//ledgerjournalTable.JournalNum =(Cells.Item(row, 1).value().bStr());
ledgerJournalTrans.JournalNum = ledgerJournalTable.JournalNum;
ledgerJournalTrans.initValue();
ledgerjournalTrans.Company= curext();
ledgerjournalTrans.TransDate =(Cells.Item(row, 1).value().date()); //any2date(Cells.Item(row, 1).value());//
ledgerJournalTrans.AccountNum =COMVariant2Str(Cells.Item(row, 2).value());
ledgerjournalTrans.MarkedInvoice = Cells.Item(row, 3).value().bStr();
ledgerjournalTrans.Txt =Cells.Item(row, 4).value().bStr();
ledgerjournalTrans.AmountCurDebit = (Cells.Item(row, 5).value().double());
ledgerjournalTrans.AmountCurCredit =(Cells.Item(row, 6).value().double());
ledgerJournalTrans.OffsetAccountType = str2enum(LedgerJournalACType,cells.item(row, 7).value().bStr());//cells.item(row, 7).value().variantType(); //LedgerJournalACType::Bank;// LedgerJournalACType::Bank;
ledgerJournalTrans.OffsetAccount =Cells.Item(row, 8).value().bStr();//'PNCUS'; //ledgerJournalTrans.findOffsetAccount();
ledgerJournalTrans.PaymMode =Cells.Item(row, 9).value().bStr();
ledgerjournalTrans.CurrencyCode =Cells.Item(row, 10).value().bStr();
ledgerjournalTrans.PaymReference=Cells.Item(row, 11).value().bStr();
ledgerjournalTrans.krDeductionType=Cells.Item(row, 12).value().bStr();
ledgerjournalTrans.Dimension[4]=Cells.Item(row, 2).value().bStr();
ledgerjournalTrans.insert();
// RCell = Cells.Item(row, 1);
type = cells.item(row+1, 1).value().variantType();
row++;
}
while (type != COMVariantType::VT_EMPTY);
ttscommit;
info("Customer payment lines imported Successfuly");
info(strfmt("journal Number %1" , ledgerJournalTable.JournalNum));
app.quit();
}
///
Static LedgerJournalTable createLedgerJournalTable(LedgerJournalNameID _ledgerJournalNameID)
{
LedgerJournalName ledgerJournalName;
LedgerJournalTable ledgerJournaltable;
NumberSeq numberSeqLoc;
;
/*elect firstonly ledgerjournalName where ledgerJournalName.JournalName == 'ARPay';
ledgerJournalTable.JournalName = LedgerJournalName.JournalName;
ledgerJournalTable.initFromLedgerJournalName();
ledgerJournalTable.insert();*/
// numberSeqLoc = NumberSeq::newGetVoucherFromCode(ledgerJournalName.VoucherSeries);
return ledgerJournaltable;
}
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////
static void Main(Args _args)
{
KR_CustPaymentImport krCustPaymentImport;
krCustPaymentImport = new KR_CustPaymentImport();
krCustPaymentImport.run();
}
Success One:: Class:
Class Declaraion:
public class KR_CustPaymentImport extends objectRun
{
}
//////////////////////////////////////////////////////////////////////////
2: Run:
void run()
{
LedgerJournalTable ledgerJournaltable;
LedgerjournalTrans ledgerjournalTrans;
LedgerJournalName ledgerJournalName;
NumberSeq numberSeq;
COMVariantType type,typeLoc;
LedgerJournalACType ledgerJournalACType;
Dialog dialog;
DialogField dialogField;
Filename filename;
// NumberSeq numSeq;
str jourName;
int lineNum;
COMVariant COMVariant1;
SysExcelApplication app;
SysExcelWorkbooks Workbooks;
SysExcelWorkbook Workbook;
SysExcelWorksheets Worksheets;
SysExcelWorksheet Worksheet;
SysExcelCells Cells;
SysExcelCell RCell;
int row;
int temp;
#Excel
str COMVariant2Str(COMVariant _cv,
int _decimals = 0,
int _characters = 0,
int _separator1 = 0,
int _separator2 = 0)
{
switch(_cv.variantType())
{
case (COMVariantType::VT_BSTR):
return _cv.bStr();
case (COMVariantType::VT_R4):
return num2str(_cv.float(),
_characters,
_decimals,
_separator1,
_separator2);
case (COMVariantType::VT_R8):
return num2str(_cv.double(),
_characters,
_decimals,
_separator1,
_separator2);
case (COMVariantType::VT_DECIMAL):
return num2str(_cv.decimal(),
_characters,
_decimals,
_separator1,
_separator2);
case (COMVariantType::VT_DATE):
return date2str(_cv.date(),
123,
2,
1,
2,
1,
4);
case (COMVariantType::VT_EMPTY):
return "";
default:
throw error(strfmt("@SYS26908",
_cv.variantType()));
}
return "";
}
;
dialog = new Dialog("Import Customer Payment Journal.");
dialogfield = dialog.addField(typeid(Filenameopen), "Select File");
dialog.run();
if (dialog.run())
{
filename = (dialogfield.value());
}
app = SysExcelApplication::construct();
app.visible(false);
Workbooks = app.Workbooks();
Workbooks.open(filename,0);
Workbook = Workbooks.item(1);
Worksheets = Workbook.worksheets();
Worksheet = Worksheets.itemFromNum(1);
Cells = Worksheet.Cells();
row = 2;
RCell = Cells.Item(row, 1);
select firstonly ledgerjournalName where ledgerJournalName.JournalName == 'ARPay';
ledgerJournalTable.JournalName = LedgerJournalName.JournalName;
ledgerJournalTable.initFromLedgerJournalName();
ledgerJournalTable.insert();
numberSeq = NumberSeq::newGetVoucherFromCode(ledgerJournalName.VoucherSeries);
// numberSeq = NumberSeq::newGetVoucherFromCode(ledgerJournalName.VoucherSeries);
ttsbegin;
// while(RCell.Value().double() > 0)
do
{
ledgerJournalTrans.clear();
ledgerJournalTrans.voucher = numberSeq.voucher();
ledgerJournalTrans.LineNum = lineNum;
ledgerJournalTrans.AccountType = LedgerJournalACType::Cust;
//ledgerjournalTable.JournalNum =(Cells.Item(row, 1).value().bStr());
ledgerJournalTrans.JournalNum = ledgerJournalTable.JournalNum;
ledgerJournalTrans.initValue();
ledgerjournalTrans.Company= curext();
ledgerjournalTrans.TransDate =(Cells.Item(row, 1).value().date()); //any2date(Cells.Item(row, 1).value());//
ledgerJournalTrans.AccountNum =COMVariant2Str(Cells.Item(row, 2).value());
ledgerjournalTrans.MarkedInvoice = Cells.Item(row, 3).value().bStr();
ledgerjournalTrans.Txt =Cells.Item(row, 4).value().bStr();
ledgerjournalTrans.AmountCurDebit = (Cells.Item(row, 5).value().double());
ledgerjournalTrans.AmountCurCredit =(Cells.Item(row, 6).value().double());
ledgerJournalTrans.OffsetAccountType = str2enum(LedgerJournalACType,cells.item(row, 7).value().bStr());//cells.item(row, 7).value().variantType(); //LedgerJournalACType::Bank;// LedgerJournalACType::Bank;
ledgerJournalTrans.OffsetAccount =Cells.Item(row, 8).value().bStr();//'PNCUS'; //ledgerJournalTrans.findOffsetAccount();
ledgerJournalTrans.PaymMode =Cells.Item(row, 9).value().bStr();
ledgerjournalTrans.CurrencyCode =Cells.Item(row, 10).value().bStr();
ledgerjournalTrans.PaymReference=Cells.Item(row, 11).value().bStr();
ledgerjournalTrans.krDeductionType=Cells.Item(row, 12).value().bStr();
ledgerjournalTrans.Dimension[4]=Cells.Item(row, 2).value().bStr();
ledgerjournalTrans.insert();
// RCell = Cells.Item(row, 1);
type = cells.item(row+1, 1).value().variantType();
row++;
}
while (type != COMVariantType::VT_EMPTY);
ttscommit;
info("Customer payment lines imported Successfuly");
info(strfmt("journal Number %1" , ledgerJournalTable.JournalNum));
app.quit();
}
///
Static LedgerJournalTable createLedgerJournalTable(LedgerJournalNameID _ledgerJournalNameID)
{
LedgerJournalName ledgerJournalName;
LedgerJournalTable ledgerJournaltable;
NumberSeq numberSeqLoc;
;
/*elect firstonly ledgerjournalName where ledgerJournalName.JournalName == 'ARPay';
ledgerJournalTable.JournalName = LedgerJournalName.JournalName;
ledgerJournalTable.initFromLedgerJournalName();
ledgerJournalTable.insert();*/
// numberSeqLoc = NumberSeq::newGetVoucherFromCode(ledgerJournalName.VoucherSeries);
return ledgerJournaltable;
}
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////
static void Main(Args _args)
{
KR_CustPaymentImport krCustPaymentImport;
krCustPaymentImport = new KR_CustPaymentImport();
krCustPaymentImport.run();
}