Yes i used SysExcelApplication.
ImportMovementJournal()
private void importMovementJournal()
{
ABC_MovementJournalTmp movementJournal;
InventJournalTrans inventJournalTrans;
InventJournalTable inventJournalTable;
InventDim inventDim;
Voucher voucherNum;
boolean insertSuccess = false;
int noOfLines = 0;
InventItemPrice inventItemPrice;
ABC_MovementJournalAccountRelation movementJournalAccountRelation;
InventJournalTable_IN inventJournalTable_IN;
InventJournalTrans_IN inventJournalTrans_IN;
InventJournalTransTaxExtensionIN inventJournalTransTaxExtensionIN;
TaxInformation_IN taxInformation_IN;
LogisticsPostalAddress logisticsPostalAddress;
LogisticsLocation logisticsLocation;
int linenum;
inventJournalTable.clear();
inventJournalTable.initFromInventJournalName(inventJournalName);
inventJournalTable.JournalId = NumberSeq::newGetNum(LedgerParameters::numRefJournalNum()).num();
if (inventJournalTable.validateWrite())
{
inventJournalTable.insert();
inventJournalTable_IN.InventJournalTable = inventJournalTable::find(inventJournalTable.JournalId).RecId;
inventJournalTable_IN.validateWrite();
inventJournalTable_IN.insert();
while select movementJournal
{
select firstOnly inventDim
where inventDim.InventLocationId == movementJournal.InventLocationId
&& inventDim.InventSiteId == movementJournal.InventSiteId;
if (!inventDim)
{
inventDim.initFromInventTable(InventTable::find(movementJournal.ItemId));
inventDim.InventLocationId = movementJournal.InventLocationId;
inventDim.InventSiteId = movementJournal.InventSiteId;
inventDim.inventDimId = inventDim::newDimId();
inventDim.insert();
}
inventJournalTrans.clear();
inventJournalTrans.initValue();
inventJournalTrans.initFromInventJournalTable(inventJournalTable);
inventJournalTrans.JournalId = inventJournalTable.JournalId;
voucherNum = new JournalVoucherNum(JournalTableData::newTable(inventJournalTable)).getNew(false);
inventJournalTrans.Voucher = voucherNum;
inventJournalTrans.TransDate = movementJournal.TransDate;
inventJournalTrans.WarehouseLocation_IN = inventJournalTrans.nttGetLogisticsLocation_IN(movementJournal.InventLocationId).RecId;
inventJournalTrans.PostalAddress_IN = LogisticsPostalAddress::findByLocation(inventJournalTrans.WarehouseLocation_IN).RecId;
inventJournalTrans.ItemId = movementJournal.ItemId;
inventJournalTrans.DefaultDimension = InventTable::find(movementJournal.ItemId).DefaultDimension;
inventJournalTrans.Qty = movementJournal.Qty;
inventJournalTrans.InventDimId = inventDim.inventDimId;
inventJournalTrans.ExciseType_IN = ExciseType_IN::Manufacturer;
inventjournalTrans.ABC_Remarks = movementJournal.ABC_Remarks; // MovementJournal_Remarks
itemGroup = InventItemGroupItem::itemGroupByItemIdLegalEntity(movementJournal.ItemId,curext()).ItemGroupId;
if (inventJournalName.NTTConsumptionJournal == NoYes::Yes)
{
select firstOnly movementJournalAccountRelation where movementJournalAccountRelation.ItemGroupId == itemGroup
&& movementJournalAccountRelation.MovementType == ABC_MovementType::Consumption;
if (movementJournalAccountRelation)
{
inventJournalTrans.LedgerDimension = movementJournalAccountRelation.OffSetAccount;
}
}
else if (inventJournalName.NTTConsumptionJournal == NoYes::No)
{
select firstOnly movementJournalAccountRelation where movementJournalAccountRelation.ItemGroupId == itemGroup
&& movementJournalAccountRelation.MovementType == ABC_MovementType::Produce;
if (movementJournalAccountRelation)
{
inventJournalTrans.LedgerDimension = movementJournalAccountRelation.OffSetAccount;
}
}
if (inventJournalTrans.Qty >=0 )
{
select firstOnly inventItemPrice
order by ActivationDate desc
where inventItemPrice.ItemId == inventJournalTrans.ItemId;
if (inventItemPrice)
{
inventJournalTrans.CostPrice = inventItemPrice.Price;
inventJournalTrans.CostAmount = round((inventItemPrice.Price * inventJournalTrans.Qty),0.01);
}
}
if (inventJournalTrans.validateWrite())
{
inventJournalTrans.insert();
linenum ;
//Get Invent journal trans info for India
select InventJournalTrans
where inventJournalTrans.ItemId == movementJournal.ItemId
&& InventJournalTrans.JournalId == inventJournalTable.JournalId
&& inventJournalTrans.LineNum == linenum;
inventJournalTrans_IN.InventJournalTrans = InventJournalTrans.RecId;
//Insert Invent journal trans info for India
inventJournalTrans_IN.validateWrite();
inventJournalTrans_IN.insert();
//Relate Invent journal trans info to Tax info for India
inventJournalTransTaxExtensionIN.InventJournalTrans = InventJournalTrans.RecId;
//Get Tax info for India
select logisticsLocation
where LogisticsPostalAddress::getLocation(inventJournalTrans.PostalAddress_IN) == logisticsLocation.RecId;
inventJournalTransTaxExtensionIN.TaxInformation_IN
= taxInformation_IN::findDefaultbyLocation(logisticsLocation.RecId).RecId;
//Insert Tax info for India
inventJournalTransTaxExtensionIN.validateWrite();
inventJournalTransTaxExtensionIN.insert();
noOfLines ;
this.setTaxExciseInfo(inventJournalTrans, inventJournalTrans.PostalAddress_IN);
insertSuccess = true;
}
}
this.updateInventJournalTab(inventJournalTable, noOfLines);
}
if (insertSuccess == true)
{
info("Moment Journal Import successfully");
}
}
readImportFile()
public void readImportFile()
{
#AviFiles
str COMVariant2Str(COMVariant _cv,
int _decimals = 1,
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, DateFlags::None);
case COMVariantType::VT_EMPTY:
return "";
default:
throw error(strfmt("@SYS26908",
_cv.variantType()));
}
return "";
}
;
row = 1;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error("@SYS19358");
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
ttsBegin;
do
{
try
{
row ;
this.insertMovementTmp(str2Date(COMVariant2Str(cells.item(row,1).value()), 123), //Transdate
COMVariant2Str(cells.item(row,2).value()), //ItemId
COMVariant2Str(cells.item(row,3).value()), //Site
COMVariant2Str(cells.item(row,4).value()), //Warehouse
str2num( COMVariant2Str( cells.item(row,5).value(), 2 ) ), //Qty
COMVariant2Str(cells.item(row,6).value())); //Remarks
}
catch
{
Error(strfmt("@SYS92842"));
}
type = cells.item(row 1, 1).value().variantType();
} while (type!= COMVariantType::VT_EMPTY);
ttscommit;
application.quit();
}
