Importing Data From Excel to Form

(0) Share

Report

Posted on by munsif

1,536

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.ledgerDimensions=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.

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (0)

MbokJamu 140 on at

Like (0)

Report
RE: Importing Data From Excel to Form

To import customer balance you must specify Account Type. Maybe Posting Profile and invoice to.

journalTrans.parmAccountType(LedgerJournalACType::Cust); //journalTrans.parmPostingProfile(yourcustpostingprofile); //journalTrans.parmInvoice(custinvnumber); //journalTrans.parmDue(invdue); journalTrans.parmLedgerDimension(DimensionStorage::getDynamicAccount( _demnsions1, LedgerJournalACType::Cust));

Was this reply helpful? Yes No
Heinz Schweda 1,367 on at

Like (0)

Report

RE: Importing Data From Excel to Form

Hello,

i think at least this line is wrong, because AmountdebitCur is never read from the file

ledgerjournalTrans.AmountCurCredit=AmountdebitCur;

Was this reply helpful? Yes No
Xainu Khan 165 on at

Like (0)

Report

RE: Importing Data From Excel to Form

Hi Munsif,

You can refer this blog for importing the journal lines: dax365fo.blogspot.com/.../import-general-journal-lines-using.html

Thanks

Zain

Was this reply helpful? Yes No
munsif 1,536 on at

Like (0)

Report

RE: Importing Data From Excel to Form

thank for reply, now only customer Account is not populating in LegerJournalTrans Table, all my other fields are populating correctly..

my update Code is following ,one thing more i am inserting new records..

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++;

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

//TOI_ImportExcelForLegerJournalTrans.insert();

type = cells.item(row+1, 1).value().variantType();

Was this reply helpful? Yes No