Import Data from excel and show on Form.

Finance and Operations/AX Developer Forum
1

Hi Expert, i have query, i want to load data from excel to a form name ledgerjournalTransVendPaym, basically we have excel file and we want to import that file into system and the system auto fills entries in payment journal, i.e when we click on new payment journal and go to details then there should be a button through which system automatically fill following Fields of same form.

Date Account Credit Txt
02/12/2018 C-xxxx 58787 Cust Credit
02/12/2018 C-yyy 5454 det
02/12/2018 C-zzzz 5454 test debit

i have following code, its work fine for date and Txt but for the credit and Account it does not work,

void clicked()
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
COMVariant value;
Name name;
FileName filename;
TOI_ImportExcelForLegerJournalTrans esh_sampletable; //Declaring Table Name
int row=1;
int64 Dm,_DebitCredit;
real AmountdebitCur;
DimensionDynamicAccount Dim;
date _Date ;


str _Date1,_debitCredit1,_demnsions1,_Descriptions,instNum;
TOI_ImportExcelForLegerJournalTrans TOI_ImportExcelForLegerJournalTrans;
;

application = SysExcelApplication::construct();
workbooks = application.workbooks();
//delete_from TOI_ImportExcelForLegerJournalTrans;
//specify the file path that you want to read
filename ='C:\\Insert\\Testing127.xlsx'; //ExcelSheet File Name

try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error('File cannot be opened');
}

workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1); //Here 1 is the worksheet Number
cells = worksheet.cells();
do
{

row++;

_Date1 = cells.item(row, 1).value().toString();
_Date=str2Date(_Date1,123);

_demnsions1=cells.item(row,2).value().bStr();
Dim=str2int64(_demnsions1);

// info(_debitCredit1);
_Descriptions=cells.item(row,4).value().bStr();
instNum=cells.item(row,5).value().bStr();
//info(_Descriptions);

//TOI_ImportExcelForLegerJournalTrans.TransDate=_Date;
ledgerjournalTrans.TransDate=_Date;
LedgerJournalTrans.ledgerdimension=Dim;
ledgerjournalTrans.txt=_Descriptions;

ledgerjournalTrans.InstNumber=instNum;

ledgerjournalTrans.AmountCurCredit=AmountdebitCur;
//TOI_ImportExcelForLegerJournalTrans.Amount=_DebitCredit;


ledgerjournalTrans.insert();


//TOI_ImportExcelForLegerJournalTrans.insert();

type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
super();
ledgerjournalTrans_ds.refresh();
ledgerjournalTrans_ds.research();

}

plz guide where i am making mistake.

Thanks and Regards.

2

First of all, which version of AX it’s about? Please always attach a tag with AX version; it’s often a critical piece of information.

3

Hi Munsifur,

Just use this piece of code in ur Clicked method.

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

And for credit and Account fields just get the values from excel by adding this line,

credit = COMVariant2str(cells.item(row, ).value());
Account = COMVariant2str(cells.item(row, ).value());

after this try inserting those fields in the Table.

Hope this will fix your prob.

Thanks!

4

Sorry for not mentioning the Ax Version, i am using ax 2012 R3.

5

this is my code now except Account all data is inserting correctly & thanks for your response, now plz tell me where is the issue

Dialog _dialog;
DialogField _file;
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
COMVariant value;
Name name;
FileName filename;
DimensionStorage dimensionStorage;
LedgerDimensionAccount ledgerDim;
//Declaring Table Name
int row=10;

int _DebitCredit;
real AmountdebitCur;
DimensionDynamicAccount Dim;
date _Date ;
int64 Dim64;

str _Date1,_debitCredit1,_demnsions1,_Descriptions,instNum;
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 “”;
}
;

//Prompt to import excel
_dialog = new Dialog(“Please select the file to load”);
_dialog.addText(“Select file:”);
_file = _dialog.addField(ExtendedTypeStr(“FilenameOpen”));

_dialog.run();

if (_dialog.closedOK())
{

application = SysExcelApplication::construct();
workbooks = application.workbooks();

//specify the file path that you want to read
filename =_file.value();//‘C:\Insert\insertMunsif.xlsx’; //ExcelSheet File Name

try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error(‘File cannot be opened’);
}

workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1); //Here 1 is the worksheet Number
cells = worksheet.cells();
do
{

row++;
ledgerDim=0;
instNum=cells.item(row,6).value().bStr();
AmountdebitCur=cells.item(Row,7).value().double();
_Date = cells.item(row, 8).value().date();
//_Date=str2Date(_Date1,123);

_demnsions1=cells.item(Row, 9).value().bStr();//cells.item(row,9).value().bStr();

dim64=str2IntOk(_demnsions1);

// info(_debitCredit1);
_Descriptions=cells.item(row,10).value().bStr();

//info(_Descriptions);

//TOI_ImportExcelForLegerJournalTrans.TransDate=_Date;
ledgerjournalTrans.TransDate=_Date;
LedgerJournalTrans.LedgerDimension=dim64;

ledgerjournalTrans.txt=_Descriptions;

ledgerjournalTrans.InstNumber=instNum;

ledgerjournalTrans.AmountCurCredit=AmountdebitCur;
//TOI_ImportExcelForLegerJournalTrans.Amount=_DebitCredit;

ledgerjournalTrans.insert();

6

Can’t you simply use the Data Import Export Framework already included in AX 2012? It supports Excel files.