Share
ReportHii,
I have one excel import tool for multiple purchase order same invoices posting its runs well but one problem is there
its take a system date as a posting date and invoiced date because i am not passing through it from excel.
I want to add those two dates in my excel n want to pass while posting so how to do that...
CODE:
public void run()
{
//FILE Format
//SAC,Description
#AviFiles
FilenameOpen filename;
dialogField dialogFilename,DialogJournalType;
Dialog dialog= new Dialog("Excel Upoad");
Container excelCont[];
DirPartyPostalAddressView postaladdressview;
int rowIdx;
Counter linesImported;
int lastRow,dimcount;
boolean ok = true;
str c1,c2,c3;
InvoiceId invoiceNo;
boolean isReceivedAll;
SysOperationProgress progressLoc;
str input;
PurchFormLetter purchFormLetter;
//PurchTable _purchTable = PurchTable::find("000592");
PurchTable purchTable;
PurchLine purchLine;
Map mapPurchTable;
Set unprocessedPurchaseIds = new Set(Types::String);
SetEnumerator purchIdEnumerator;
SysExcelApplication application;
SysExcelWorkBooks workBooks;
SysExcelWorkSheets workSheets;
SysExcelWorkSheet workSheet;
SysExcelCells cells;
struct struct= new Struct();
ServiceAccountingCodeTable_IN ServiceAccountingCodeTable_IN;
AccountingDate _postingDate;
DlvDate _invoiceDate;
#define.CurrentVersion(1)
#localmacro.CurrentList
filename
#endmacro
#Excel
#define.Star('*')
#define.Space(' ')
// convert into str from excel cell value
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 "";
}
// Find last row from excel
int findLastRow(SysExcelWorkSheet _workSheet)
{
SysExcelRange range;
;
range = _workSheet.cells().range(#ExcelTotalRange);
try
{
// Finds the row where the first contents is found.
range = range.find(#Star, null, #xlFormulas, #xlWhole, #xlByRows, #xlPrevious);
}
catch (Exception::Error)
{
error("@SYS59926");
return 0;
}
if (range)
{
return range.row();
}
else
{
return 0;
}
}
;
dialogFilename = dialog.addField(extendedtypestr(FilenameOpen));
dialog.filenameLookupFilter(["@SYS28576",#XLS,#XLSX]);
dialog.filenameLookupTitle(" - Upload from Excel");
dialogFilename.value(dialogFilename.value());
if(!dialog.run())
return;
try
{
filename = dialogFilename.value();
application = SysExcelApplication::construct();
workBooks = application.workbooks();
workBooks.open(filename,0,true);
workSheets = workBooks.item(1).worksheets();
workSheet = workSheets.itemFromNum(1);
cells = workSheet.cells();
lastRow = findLastRow(workSheet);
rowIdx = 1;
progressLoc = new SysOperationProgress();
progressLoc.setCaption("Excel Importing");
progressLoc.setTotal(lastRow);
progressLoc.setAnimation(#AviTransfer);
setprefix("Excel Import");
ttsbegin;
while (rowIdx < lastRow)
{
linesImported++;
rowIdx++;
progressLoc.setText("Importing " + int2str(rowIdx));
progressLoc.setCount(linesImported);
// ttsbegin;
invoiceNo = cells.item(rowIdx,1).value().bStr();
if(invoiceNo)
{
if (!unprocessedPurchaseIds.in(invoiceNo))
{
select firstOnly firstFast PurchPrice from purchLine where purchLine.PurchId == PurchTable.PurchId
&& purchLine.PurchPrice == 0;
if(!purchLine)
{
unprocessedPurchaseIds.add(invoiceNo);
}
}
}
}
purchIdEnumerator = unprocessedPurchaseIds.getEnumerator();
while (purchIdEnumerator.moveNext())
{
purchTable.clear();
isReceivedAll = true;
purchFormLetter = purchFormLetter::construct(DocumentStatus::Invoice);
mapPurchTable = new Map(typeName2Type(extendedTypeStr(RecId)), Types::Record);
while select purchTable where purchTable.SAPInvoiceNo == purchIdEnumerator.current()
{
if(purchTable.PurchStatus == PurchStatus::Backorder)
{
isReceivedAll = false;
break;
}
mapPurchTable.insert(purchTable.RecId, purchTable);
}
if(isReceivedAll)
{
purchTable.clear();
select * from purchTable order by RecId desc where purchTable.SAPInvoiceNo == purchIdEnumerator.current();
purchFormLetter.parmDataSourceRecordsPacked(mapPurchTable.pack());
purchFormLetter.initNewPurchParmUpdate();
purchFormLetter.sumBy(AccountOrder::Account);
purchFormLetter.reArrangeNow(true);
purchFormLetter.update(purchTable,
purchTable.SAPInvoiceNo,
systemDateGet(), PurchUpdate::All);
}
}
info('Done');
ttsCommit;
}
catch(Exception::Error)
{
application.quit ();
application.finalize ();
ttsabort;
info("Unable to process the excel import ");
}
}
plz suggest the code modification for ax 2012
*This post is locked for comments
All responses (
Answers (
291,280
