Code for Excel import by choosing file in Axapta

his is a very useful code for importing excel into DAX table. Developer can add/modify their logics while importing excel into DAX table. This is a simple and compact code. Developers have to change this code as per their requirements.
Note: Here excel file will be 3 columns, 1. Vendor Code, 2. vendor Group and 3. vendor Currency. You can add or less fields in excel but same incorporation should be done in this job (bold area). While you incorporate this type of procedure then I recommend to all do this code into a class instead of job.

static void ExcelUploadByFile(Args _args)
{
#AviFiles
FilenameOpen filename;
dialogField dialogFilename;
Dialog dialog= new Dialog("Excel Upoad");
Container vendCont[];

int rowIdx;
Counter linesImported;
int lastRow;
boolean ok = true;
str c1,c2,c3;
str input;

SysExcelApplication application;
SysExcelWorkBooks workBooks;
SysExcelWorkSheets workSheets;
SysExcelWorkSheet workSheet;
SysExcelCells cells;
SysOperationProgress progress;


#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(typeId(FilenameOpen));
dialog.filenameLookupFilter(["@SYS28576",#XLS]);
dialog.filenameLookupTitle("Upload from Excel");
dialogFilename.value(filename);

if(!dialog.run())
return;

filename = dialogFilename.value();

ttsbegin;

application = SysExcelApplication::construct();
workBooks = application.workbooks();
workBooks.open(filename,0,true);

workSheets = workBooks.item(1).worksheets();
// this.importExcel("Sheet1");

input = "Sheet1";
workSheet = workSheets.itemFromName(input);
cells = workSheet.cells();
lastRow = findLastRow(workSheet);
rowIdx = 1;

progress = new SysOperationProgress();
progress.setCaption("Vendor Importing");
progress.setTotal(lastRow);
progress.setAnimation(#AviTransfer);
setprefix("Vendor Import");

while (rowIdx
{
rowIdx++;
setPrefix(strfmt("Excel Row: %1", rowIdx));

c1 = COMVariant2Str(cells.item(rowIdx,1).value()); //Vendor Account in excel asigning value into dax variable
c2 = COMVariant2Str(cells.item(rowIdx,2).value()); //vendor Group
c3 = COMVariant2Str(cells.item(rowIdx,3).value()); // vendor Currency

linesImported++;
vendCont[linesImported] = conins(vendCont[linesImported] , 1 ,c1, c2, c3); // records inserted into container

progress.setText("Importing " + c1);
progress.setCount(linesImported);
}

for(rowIdx = 1 ; rowIdx
{
info(strfmt("Vendor Code: %1, Name: %2, Group: %3.", conpeek(vendCont[rowIdx], 1),conpeek(vendCont[rowIdx], 2), conpeek(vendCont[rowIdx], 3)));
}

application.quit();
application = null;

ttscommit;
}

Rajdip Das Kolkata