Use EDT to get the file browse option "FileNameOpen"
1. Take String Edit control and in EDT property specify FileNameOpen
write one method in this EDT control
like below
public void gotFocus()
{
fileNameControl = this;
super();
}
2.Form Methods
public class FormRun extends ObjectRun
{
SysFormSplitter_Y _formSplitterVertical;
FormStringControl _FileOpen;
FormStringControl fileNameControl;
}
str fileNameLookupFilename()
{
Filename filepath;
Filename fileName;
Filename fileType;
[filepath, fileName, fileType] = fileNameSplit(fileNameControl.text());
return fileName + fileType;
}
container fileNameLookupFilter()
{
#File
Filename filepath;
Filename fileName;
Filename fileExtention;
[filepath, fileName, fileExtention] = Global::FileNameSplit(fileNameControl.text());
if (!fileExtention)
{
fileExtention = '.xlsx';
}
return [WinApi::fileType(fileExtention),#AllFilesName+fileExtention, #AllFilesExt, #AllFilesType];
}
str fileNameLookupInitialPath()
{
#WinAPI
Filename filepath;
Filename fileName;
Filename fileType;
[filepath, fileName, fileType] = Global::FileNameSplit(fileNameControl.text());
return FilePath;
}
// AOSRunMode::client
str fileNameLookupTitle()
{
return 'Lookup Title: ' + fileNameControl.label();
}
3.Add button and override clicked method and write the logic.
void clicked()
{
SysExcelWorkSheet excelWorksheet;
SysexcelCells excelCells;
SysExcelApplication excelApp;
int i,j;
int row;
Name findExcelSheet;
str 250 error;
JournalId journalId;
Voucher Voucher;
CICJournalTable JournalTable,journalTableOrg;
LedgerJournalTrans ledgerJournalTrans;
LedgerJournalTable ledgerJournalTable;
int WrkShtNum;
COMVariantType type;
Name formatValue;
boolean value;
int paymentreference;
name formatcheck(int val)
{
formatValue = "";
switch(excelcells.item(row, val).value().variantType())
{
case COMVariantType::VT_BSTR:
formatValue = strFmt("%1", excelcells.item(row, val).value().bStr());
break;
case COMVariantType::VT_DECIMAL, COMVariantType::VT_R4, COMVariantType::VT_R8:
formatValue = strFmt("%1", any2int(excelcells.item(row, val).value().double()));
break;
case COMVariantType::VT_I1, COMVariantType::VT_I2, COMVariantType::VT_I4:
formatValue = strFmt("%1", excelcells.item(row, val).value().int());
break;
case COMVariantType::VT_UI1, COMVariantType::VT_UI2, COMVariantType::VT_UI4:
formatValue = strFmt("%1", excelcells.item(row, val).value().uLong());
break;
case COMVariantType::VT_EMPTY:
formatValue = '';
break;
default:
throw error(strfmt("Issue in file coloum type.", excelcells.item(row+1, 1).value().variantType()));
}
return formatValue;
}
excelApp = SysExcelApplication::construct();
startLengthyOperation();
if(StrLTrim(StrRTrim(OpenExcelFile.text()))=="")
{
ExcelCells = NULL;
excelWorksheet = NULL;
excelApp.quit();
throw error("Empty File Name");
}
else
{
excelApp.workbooks().open(OpenExcelFile.text());
excelWorksheet = excelApp.worksheets().itemFromNum(1);
excelCells = excelWorksheet.cells();
excelCells.range('A:A').numberFormat('@');
row=1;
findExcelSheet = excelcells.item(row,1).value().bStr();
info(findExcelSheet);
row=2;
if(row == 0)
{
ExcelCells = NULL;
excelWorksheet = NULL;
excelApp.quit();
throw error("ERROR: Excel upload aborted...");
}
do
{
try
{
ttsBegin;
if(row >= 1)
{
if (findExcelSheet == 'Journal Number')
{
journalId = excelcells.item(row,1).value().bStr();
if(journalId)
{
select ledgerJournalTrans
where ledgerJournalTrans.JournalNum == journalId;
select journalTableOrg
where journalTableOrg.JournalID == journalId;
if(ledgerJournalTrans && !journalTableOrg)
{
JournalTable.JournalID = ledgerJournalTrans.JournalNum;
JournalTable.JournalDescription = ledgerJournalTable::find( JournalTable.JournalID).Name;
JournalTable.insert();
}
}
}
else if(findExcelSheet == 'Voucher Number')
{
Voucher = excelcells.item(Row,1).value().bStr();
select journalTableOrg
where journalTableOrg.JournalID == Voucher;
if (!journalTableOrg)
{
JournalTable.voucherNumber = excelcells.item(Row,1).value().bStr();
JournalTable.CustomerCode = excelcells.item(Row,2).value().bStr();
JournalTable.Date = excelcells.item(Row,3).value().date();
JournalTable.ReasonCode = excelcells.item(Row,4).value().bStr();
JournalTable.ReasonDescription = excelcells.item(Row,5).value().bStr();
JournalTable.Reversed = NoYes::No;
JournalTable.insert();
}
else
{
error(strFmt("Voucher number %1 is already exist",Voucher));
}
}
}
if (value)
{
type = excelcells.item(row+1, 1).value().variantType();
ttscommit;
break;
}
else
{
type = excelcells.item(row+1, 1).value().variantType();
ttscommit;
}
}
catch
{
error(strfmt("Upload Failed in row %1", row));
}
type = excelcells.item(row+1, 1).value().variantType();
Row++;
}
while (type != COMVariantType::VT_EMPTY);
info(strfmt("Journals imported successfully"));
excelapp.quit();
endLengthyOperation();
CICJournalTable_ds.executeQuery();
}
super();
}
Here in this code OpenExcelFile.text() file string edit EDT(FileNameOpen) contol name
No comments:
Post a Comment