AX 2012: Upload Number Sequence Data Using Excel

This class is used to read number sequence data from an excel file and upload it within AX. File format for excel is given below:

The following are different methods used in the class for selecting an excel file, reading data from an excel file , validating data and then creating records in the number sequence table:

// Class declaration

public class ImportNumberSeqFromExcel extends RunBaseBatch

{

// Packed variables

FilenameOpen       filename;

// Dialog fields

DialogField     dlgFileName;

// Excel data fields

NumberSequenceCode numberSequenceCode;

NumberSequenceFormat numberSequenceFormat;

NumberSequenceRange largest, Nextrec, smallest;

Name txt,formatTxt;

container readCell;

int row,success, fail, exist;

 

#define.CurrentVersion(1)

#define.Version1(1)

#localmacro.CurrentList

filename

#endmacro

}

 

// Main method

static void main(Args args)

{

ImportNumberSeqFromExcel    importNumberSeqFromExcel;

;

importNumberSeqFromExcel = ImportNumberSeqFromExcel::construct();

 

if (importNumberSeqFromExcel.prompt())

importNumberSeqFromExcel.run();

}

 

/// <summary>

///    Contains the code that does the actual job of the class.

/// </summary>

public void run()

{

this.readExcel();

info(strFmt(“Total Records: %1,Success: %2, Failed: %3, Already exist: %4 “,row,success,fail, exist));

}

 

//Construct

server static ImportNumberSeqFromExcel construct()

{

return new ImportNumberSeqFromExcel();

}

 

 

/// <remarks>

///    A dialog box can be either built by using the <c>Dialog</c> class or by using a class that is

///    created in the Application Object Tree (AOT).

/// </remarks>

public Object dialog()

{

DialogRunbase       dialog = super();

#resAppl

;

dlgFileName = dialog.addFieldValue(extendedTypeStr(FilenameOpen),FileName,”FilePath”);

 

return dialog;

}

 

/// get filename/file from dialog

public boolean getFromDialog()

{

;

filename   = dlgFileName.value();

return super();

}

 

//this method is use to read data from excel file and store it as comma separated values in container

void readExcel()

{

SysExcelApplication application;

SysExcelWorkbooks workbooks;

SysExcelWorkbook workbook;

SysExcelWorksheets worksheets;

SysExcelWorksheet worksheet;

SysExcelCells cells;

COMVariantType type;

Str localstr;

container localContainer;

#AviFiles

SysOperationProgress progressbar;

;

success = 0;

fail = 0;

exist = 0;

application = SysExcelApplication::construct();

workbooks = application.workbooks();

try

{

workbooks.open(filename);

}

catch (Exception::Error)

{

throw error(“File cannot be opened.”);

}

workbook = workbooks.item(1);

worksheets = workbook.worksheets();

worksheet = worksheets.itemFromNum(1);

cells = worksheet.cells();

//progress bar initialization

progressbar = new SysOperationProgress();

progressbar.setCaption(“Importing number sequence”);

progressbar.setAnimation(#AviUpdate);

do

{

row++;

progressbar.setTotal(row);

progressbar.setText(strfmt(“Importing number sequence for row %1″, row));

//read comma deliminated row value and then convert it to value container

readCell += cells.item(row, 1).value().bStr();

localStr= con2Str(readCell);

localContainer = str2con(localstr);

//validate data and create number sequences record

if(this.validateData(localContainer))

this.createNumSeq();

type = cells.item(row+1, 1).value().variantType();

readCell = conNull();

 

}

while (type != COMVariantType::VT_EMPTY);

application.quit();

 

}

 

//This method is used to validate data from excel

boolean validateData(container _localContainer)

{

//Assign container values to varibales and validate data

txt = conPeek(_localContainer, 1);

if(!txt)

{

fail++;

return false;

}

formatTxt = conPeek(_localContainer, 2);

if(!formatTxt)

{

fail++;

return false;

}

numberSequenceFormat = conPeek(_localContainer, 3);

if(!numberSequenceFormat)

{

fail++;

return false;

}

smallest = conPeek(_localContainer, 4);

if(smallest <= 0)

{

fail++;

return false;

}

largest = conPeek(_localContainer, 5);

if(largest <= 0)

{

fail++;

return false;

}

Nextrec = conPeek(_localContainer, 6);

if(Nextrec <= 0)

{

fail++;

return false;

}

 

return true;

}

 

//This method is use to create number sequence code based on given value in excel

void createNumSeq()

{

NumberSequenceTable     numberSeqTable,numberSeqTableLoc;

NumberSequenceScope     numberSeqScope;

container               segments;

str                     annotatedFormat, format;

;

select  numberSeqScope

where numberSeqScope.DataArea == curext();

//create number sequence code based on given value and suffix 1st letter of DataAreaId

numberSequenceCode = formatTxt+’_’+strUpr(substr(curext(),1,1));

segments += [[0, formatTxt]];

segments += [[-1,numberSequenceFormat]];

 

annotatedFormat  = NumberSeq::createAnnotatedFormatFromSegments(segments);

format           = NumberSeq::createAnnotatedFormatFromSegments(segments, false);

 

numberSeqTable.clear();

numberSeqTable.NumberSequence           = numberSequenceCode;

numberSeqTable.Txt                      = txt;

numberSeqTable.AnnotatedFormat          = annotatedFormat;

numberSeqTable.NumberSequenceScope      = numberSeqScope.RecId;

numberSeqTable.Format                   = format;

numberSeqTable.Highest                  = largest;

numberSeqTable.Lowest                   = smallest;

numberSeqTable.NextRec                  = Nextrec;

numberSeqTable.Continuous               = NoYes::Yes;

//validate if number sequence code already exist

select numberSeqTableLoc where numberSeqTableLoc.NumberSequence == numberSequenceCode;

if(!numberSeqTableLoc)

{

numberSeqTable.insert();

success++;

}

else

{

exist++;

}

}

 

//Pack

public container pack()

{

return [#CurrentVersion,#CurrentList];

}

 

//Unpack

public boolean unpack(container packedClass)

{

Version version = RunBase::getVersion(packedClass);

;

switch (version)

{

case #CurrentVersion:

[version,#CurrentList] = packedClass;

break;

default:

return false;

}

 

return true;

}

 

The post AX 2012: Upload Number Sequence Data Using Excel appeared first on Arbela Tech.