You may have seen it before, but here’s our take:
Case: The end-user has a spreadsheet they want to
import, and creating a csv-file is not an option.
Challenge: In Excel the end user needs to set a “ ’ ”
in front of the numbers if they are to behave as strings e.g. an account no
“123435”. When read in AX the value will be read as 123.435,0000 since is
interpreted as a real. To overcome this, the end user must set the ‘ in front
of the string e.g. ‘123435. Since there may be several hundred or thousands of
records, it is not an option for the end user.
How to import from Excel and format the cells’ content
when assigning to variables in AX.
Suggested solution below:
So, you create a dialog etc. for the file import and
in the actual method you read the sheet, the code looks like this:
private void readExcelFile()
{
SysExcelApplication
application;
SysExcelWorkbooks
workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets
worksheets;
SysExcelWorksheet
worksheet;
SysExcelCells
cells;
COMVariantType type;
COMVariant
variant;
int
row=1,errors = 0;
smmBusRelTable
smmBusRelTable;
smmBusRelAccount
smmBusRelAccount;
XXX_SortingId
sortingId;
container
errorCon;
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
ttsBegin;
try
{
workbooks.open(filenameopen);
}
catch
(Exception::Error)
{
throw
error("@SYS19358.");
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells =
worksheet.cells();
type = cells.item(row+1,1).value().variantType();
while
(type != COMVariantType::VT_EMPTY)
{
row++;
//
find variant of cell
variant = cells.item(row, 1).value();
//
set variant type to smmBusRelAccount
smmBusRelAccount =
this.variant2str(variant);
if(smmBusRelAccount)
{
smmBusRelTable = smmBusRelTable::find(smmBusRelAccount,true);
// if there is a prospect proceed
if(smmBusRelTable.RecId)
{
variant = cells.item(row, 3).value();
sortingId = this.variant2str(variant);
if(!sortingId)
{
sortingId = cells.item(row, 3).value().toString();
}
if(XXX_Table::exist(sortingId,8))
{
smmBusRelTable.XXX_Field =
sortingId;
smmBusRelTable.update();
}
else
{
errorCon = conIns(errorCon,maxInt(),strFmt("Error with sorting %1",row,sortingId));
}
}
// write to error log
else
{
errorcon = conIns(errorCon,maxInt(),strFmt("Error with prospect %1",row));
}
}
type = cells.item(row+1, 1).value().variantType();
}
application.quit();
ttsCommit;
info('Import done');
// Header was counted as
successful import. 1 is substracted from row to reflect that headers should not
count
info(strFmt('Number of items imported %1',(row-1)-conLen(errorCon)));
setprefix(strfmt('@SYS344649',conLen(errorCon)));
while
(errors < conLen(errorCon))
{
errors++;
info(conPeek(errorCon,errors));
}
}
In the
while (type != COMVariantType::VT_EMPTY)
we check if there is something the cell read.
Then you assign the variant of the cell
variant
= cells.item(row, 1).value();
which you then cast to string:
private str variant2str(COMVariant
_variant)
{
str valueStr;
;
switch(_variant.variantType())
{
case
COMVariantType::VT_EMPTY :
valueStr = '';
break;
case
COMVariantType::VT_BSTR :
valueStr = _variant.bStr();
break;
case
COMVariantType::VT_R4 :
case
COMVariantType::VT_R8 :
if(_variant.double())
{
valueStr = num2Str0(_variant.double(),0);
}
break;
default
:
throw error(strfmt("@SYS26908",
_variant.variantType()));
}
return valueStr;
}
