Excel to Ax Multiple lines are not creating in ax 2012
It is creating only one record in table remaining records are showing in debug level but in table level its not storing please help us further in logic
private void importJournal()
{
// Read from Excel file
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
InventJournalTable inventJournalTable;
InventJournalTrans inventJournalTrans;
InventDim frominventDim, toInventDim;
PdsCatchWeightItem pdsCatchWeightItem;
InventBatch inventBatch;
UnitOfMeasure unitOfMeasure;
InventTable inventTable;
int i1;
Struct struct;
DimensionDefault dimensionDefault;
container conDimValue;
container readCon;
TransDate transferJourDate;
ItemId itemNumber;
EcoResConfigurationName fromConfiguration, toConfiguration;
EcoResSizeName fromSize, toSize;
InventSiteId fromSite, toSite;
InventLocationId fromWarehouse, toWarehouse;
InventBatchSerialId fromBatchNumber, toBatchNumber, fromSerialNumber, toSerialNumber;
QTy qty1, cwQty;
UnitOfMeasureSymbol cwUnit;
NoYesId copyBatchattribute,batchdispositionStatus;
PdsDispositionCode batchdispositionCode;
str costcentre, department, region, revenue1, revenue2, store;
int row = 1;
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 “”;
}
application = SysExcelApplication::construct();
workbooks = application.workbooks();
try
{
workbooks.open(filename);
}
catch
{
throw error(“File cannot be opened.”);
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
startLengthyOperation();
//inventJournalTable = this.createHeader();
try
{
do
{
row++;
//Line information
transferJourDate = cells.item(row, 1).value().date();
itemNumber = cells.item(row, 2).value().bstr();
fromConfiguration = cells.item(row, 3).value().bStr();
toConfiguration = cells.item(row, 4).value().bStr();
fromSize = cells.item(row, 5).value().bStr();
toSize = cells.item(row, 6).value().bStr();
fromSite = cells.item(row, 7).value().bStr();
toSite = cells.item(row, 8).value().bStr();
fromWarehouse = cells.item(row, 9).value().bStr();
toWarehouse = cells.item(row, 10).value().bStr();
fromBatchNumber = cells.item(row, 11).value().bStr();
toBatchNumber = cells.item(row, 12).value().bStr();
fromSerialNumber = cells.item(row, 13).value().bStr();
toSerialNumber = cells.item(row, 14).value().bStr();
qty1 = cells.item(row, 15).value().double();
cwQty = cells.item(row, 16).value().double();
cwUnit = cells.item(row, 17).value().bStr();
copyBatchattribute = cells.item(row, 18).value().boolean();
batchdispositionStatus = cells.item(row, 19).value().boolean();
batchdispositionCode = cells.item(row, 20).value().bStr();
//Dimensions
costcentre = cells.item(row, 21).value().bStr();
department = cells.item(row, 22).value().bStr();
region = cells.item(row, 23).value().bStr();
revenue1 = cells.item(row, 24).value().bStr();
revenue2 = cells.item(row, 25).value().bStr();
store = COMVariant2Str(cells.item(row, 26).value());
conDimValue = [costcentre, department, region, revenue1, revenue2, store];
select inventTable
where inventTable.ItemId == itemNumber
&& inventTable.ItemType == ItemType::Item;
if (inventTable)
{
inventJournalTable = this.createHeader();
inventJournalTrans.clear();
inventJournalTrans.initFromInventJournalTable(inventJournalTable);
inventJournalTrans.TransDate = transferJourDate;
inventJournalTrans.ItemId = itemNumber;
inventJournalTrans.initFromInventTable(InventTable::find(inventJournalTrans.ItemId));
//From warehouse
frominventDim.configId = fromConfiguration;
frominventDim.InventSizeId = fromSize;
frominventDim.InventSiteId = fromSite;
frominventDim.InventLocationId = fromWarehouse;
frominventDim.inventBatchId = fromBatchNumber;
frominventDim.inventSerialId = fromSerialNumber;
//To warehourse
toInventDim.configId = toConfiguration;
toInventDim.InventSizeId = tosize;
toInventDim.InventSiteId = toSite;
toInventDim.InventLocationId = toWarehouse;
toInventDim.inventBatchId = toBatchNumber;
toInventDim.inventSerialId = toSerialNumber;
conDimValue = [costcentre, department, region, revenue1, revenue2, store];
toInventDim = InventDim::findOrCreate(toInventDim);
frominventDim = InventDim::findOrCreate(frominventDim);
inventJournalTrans.InventDimId = frominventDim.inventDimId;
inventJournalTrans.ToInventDimId = ToinventDim.inventDimId;
inventJournalTrans.Qty = qty1;
inventJournalTrans.PdsCWQty = cwQty;
inventJournalTrans.PdsCopyBatchAttrib = copyBatchattribute;
inventJournalTrans.PdsCopyBatchAttrib = batchdispositionStatus;
inventJournalTrans.DefaultDimension = this.createDefaultDimension(conDimValue);
inventJournalTrans.insert();
}
else
{
info(strFmt(" Please check the item id : %1", itemNumber));
}
type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
{
application.quit();
info(strFmt("%1 No lines Imported Successfully to the Journal Id : %2", row - 1, inventJournalTable.JournalId));
}
}
catch
{
info(strFmt("%1 - Error is @Row No %2 ",Exception::Error, row - 1 ));
if (application)
{
application.quit();
}
}
}
Logic is above please tell me any correction on this