İsmail Ozcan

As there are merits of a solution which depend on the situations and conditions in which they are used, I am suggesting one more alternate for achieving the objective i.e. Exporting data to Excel. In this solution we will make use of standard AX’s Reports and the concept of exporting the report data to an Excel file. I assume that we all understand that any standard AX report can be sent to a variety of Output devices such as Printer,Screen,Fax,File ( CSV,TXT,PDF,EXCEL etc) . The disadvantage in this is that the report headers is repeated after every page break and that is an undesired part . In the following walkthrough we will get over this problem as well as restrict the output to a file so that it gives a look and feel of an data export functionality. We will create a sample report for exporting the Price agreements data to Excel Start creating a report in AOT as per the following steps : Step 1: Create a class as shown below in which we would declare objects. ...

Writing Data to Excel file How it works 1. Use SysExcelApplication class to create excel file. 2. Use SysExcelWorkbooks and SysExcelWorkbook to create a blank workbook(by default 3 worksheets will be available). 3. Use SysExcelWorkSheets to select worksheet for writing data. 4. SysExcelCells to select the cells in the excel for writing the data. 5. SysExcelCell to write the data in the selected cells. 6. Once you done with write operation use SysExcelApplication.visible to open file. static void Write2ExcelFile(Args _args) { InventTable inventTable; SysExcelApplication application; SysExcelWorkbooks workbooks; SysExcelWorkbook workbook; SysExcelWorksheets worksheets; SysExcelWorksheet worksheet; SysExcelCells cells; SysExcelCell cell; int row; ; application = SysExcelApplication::construct(); workbooks = application.workbooks(); workbook = workbooks.add(); worksheets = workbook.worksheets(); worksheet = worksheets.itemFromNum(1); cells = worksheet.cells(); cells.range('A:A').nu...

static void exportVendorToExcelTEST1(Args _args) { SysExcelApplication SysExcelApplication; SysExcelWorksheet SysExcelWorksheet; SysExcelWorksheets SysExcelWorksheets; SysExcelWorkbooks SysExcelWorkbooks; SysExcelWorkbook SysExcelWorkbook; SysExcelRange SysExcelRange; SysExcelCell SysExcelCell; SysExcelCells SysExcelCells; COMVariant COMVariant1; Kriterler kriterler; VendTable vtab; Dialog dialog = new Dialog(); DialogField dflItem = dialog.addField(typeid(VendAccount)); #excel ; while select vtab where vtab.AccountNum like "S0000*" { try { sysExcelApplication = SysExcelApplication::construct(); sysExcelApplicatioN.displayAlerts(FALSE); sysExcelApplication.visible(FALSE); sysExcelWorkbooks = sysExcelApplication.workbooks(); // for filename sysExcelApplication.workbooks().open("C:\\"+vtab.AccountNum+".XLS"); sysExcelWorkbook = sysExcelApplication.workbooks().item(1); SysExcelWorksheets = sysExcelWorkbook.worksheets(); SysExcelWorksheet = SysExcelWorksheets....

While importing string values from MS Excel we use SysExcelWorksheet.cells().item(8,j).value().bstr(); But, if cell value is numeric, Excel returns blank value. So you have to control the value by a function below : str valuestr(SysExcelCell _cell) { if (_cell.value().bStr()) return _cell.value().bStr(); else { if (_cell.value().double()) return strltrim(num2str(_cell.value().double(),20,0,0,0)); return ""; } }

static void MovementJournalImportExcel(Args _args) {      InventJournalTrans         inventJournalTrans;      InventDim                  inventDim;      InventJournalTable         inventJournalTable;      InventJournalCheckPost     journalCheckPost;      InventJournalId            journalId;      journalTableData           journalTabledata;      InventBatch                inventBatch;      InventBatch          ...

static void MovementJournalImportExcel(Args _args) { InventJournalTrans inventJournalTrans; InventDim inventDim; InventJournalTable inventJournalTable; InventJournalCheckPost journalCheckPost; InventJournalId journalId; journalTableData journalTabledata; InventBatch inventBatch; InventBatch localInventBatch; NumberSeq numberSeq; NumberSequenceReference numberSequenceReference; InventSerial inventSerial; InventSerial localinventSerial; int j,countno=0,i,k; real Scarp; FilenameOpen filename; Sysexcelapplication excelapp=sysexcelapplication::construct(); sysexcelworksheet excelworksheet; sysexcelrange excelrange; sysexcelcells excelcells; // comvariant cellvalue=new comvariant(); ; // Creating Journal Header inventJournaltable.initValue(); inventJournalTable.JournalNameId = 'ERecover'; numberSeq = new NumberSeq(); n...

This 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 Raj_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; S...

static void CreateExcelDocument(Args _args) { SysExcelApplication xlsApplication; SysExcelWorkBooks xlsWorkBookCollection; SysExcelWorkBook xlsWorkBook; SysExcelWorkSheets xlsWorkSheetCollection; SysExcelWorkSheet xlsWorkSheet; SysExcelRange xlsRange; CustTable custTable; int row = 1; str fileName; ; //Filename fileName = "C:\\Test.xlsx"; //Initialize Excel instance xlsApplication = SysExcelApplication::construct(); //Open Excel document //xlsApplication.visible(true); //Create Excel WorkBook and WorkSheet xlsWorkBookCollection = xlsApplication.workbooks(); xlsWorkBook = xlsWorkBookCollection.add(); xlsWorkSheetCollection = xlsWorkBook.worksheets(); xlsWorkSheet = xlsWorkSheetCollection.itemFromNum(1); //Excel columns captions xlsWorkSheet.cells().item(row,1).value("Account Num"); x...