Create RunbaseBatch Class with Query window selection in Ax 2009,Ax 2012

Step 1: Create a runbasebatch class with all the needed methods

Step 2: Override the initParmDefault() method

public void initParmDefault()
{
Query query = new Query();
QueryBuildRange queryBuildRange;
QueryBuildDataSource queryBuildDataSource;
;
// query.addDataSource(tablenum(CustInvoiceJour));
queryBuildDataSource=query.addDataSource(tableNum(CustInvoiceJour)); //Add Query
queryBuildRange=queryBuildDataSource.addRange(FieldNum(CustInvoiceJour,OrderAccount));
queryrun = new SysQueryRun(query);

super();
}

QueryRun queryrun()
{
return queryrun;
}

Step 3: In Run method you can apply your logic

public void run()
{
;
queryRun = this.queryRun();
queryRun.query().dataSourceNo(1).addRange(fieldnum(CustInvoiceJour,InvoiceDate)).value(queryValue(fromDate)+ ‘..’+ queryValue(toDate));
while(queryRun.next())
{
custInvoiceJour = queryRun.get(tablenum(CustInvoiceJour));
info(strfmt(“%1,%2”,custInvoiceJour.InvoiceId,custInvoiceJour.InvoiceDate));

}
}

Excel Export with all formatting , Get destination path from user and adding image to cells in Ax 2012

class JKG_CustomerPriceDownload extends RunBaseBatch
{
 DialogField customer;
 DialogField dlgFilePath;
 Dialog dialog;

 str dlgFilePathVal;
 CustAccount custAccount;

 #define.CurrentVersion(1)
 #define.Version1(1)
 #localmacro.CurrentList
 custAccount,
 dlgFilePathVal
 #endmacro

}

protected Object dialog()
{
 ;
 dialog = super();
 dialog.caption("@JKG6832");

 customer = dialog.addField(typeid(CustAccount),'@SYS74095','@SYS302');
 dlgFilePath = dialog.addField(typeid(FileNameSave),'@SYS89006','@SYS72080');
 //dialog.filenameLookupInitialPath('C:\\');
 dialog.filenameLookupFilter(['@SYS28576', '*.xls']);
 return dialog;
}

public boolean getFromDialog()
{
 ;
 custAccount = customer.value();
 dlgFilePathVal = dlgFilePath.value();
 return super();

}

void insertToExcelFile()
{
 COM comApplication;
 COM comWorkbooks;
 COM comWorkbook;
 COM comWorksheet;
 COM comShapes;
 COMVariant variant = new COMVariant();
 COMVariant xpos = new COMVariant();
 COMVariant ypos = new COMVariant();
 COMVariant state = new COMVariant();
 COMVariant width = new COMVariant();
 COMVariant height = new COMVariant();
 SysExcelStyles styles;
 SysExcelStyle style;
 SysExcelFont font;
 SysExcelCells cells;
 SysExcelWorkSheet sysExcelWorkSheet;
 int row = 1;
 int rowcount;
 CustTable custTable;
 InventTable inventTable;
 ItemOwningLocationJKG itemOwningLocationJKG;
 JKG_TemperatureCodes JKG_TemperatureCodes;
 JKG_ItemCodes JKG_ItemCodes;
 // B2AnalysisTableJKG b2AnalysisTableJKG;
 PriceDiscTable priceDiscTable,priceDiscTable1;
 // ItemSalesAnalysisCodesJKG itemSalesAnalysisCodesJKG;

 ItemSalesAnalysisTableJKG analysisTable;
 SalesAnalysisNumBaseJKG analysisNum;
 Description description;
 ResourceNode resourceNode;
 FilePath imagename;

 boolean workSheetAdded = false;

 ;

 //Create the Excel app and grab the workbooks
 comApplication = new COM('Excel.application');
 comWorkBooks = comApplication.workbooks();

 //Wrap the rest in an exception to make sure excel is closed
 try
 {
 //Create a new workbook and get a reference to it
 variant.int(-4167);
 comWorkBook = comWorkBooks.add(variant);
 comWorkSheet = comApplication.activeSheet();

 //Add some text to the worksheet
 sysExcelWorkSheet = SysExcelWorkSheet::construct(MSOfficeVersion::Office2007, comWorksheet);

 styles = comWorkbook.styles();
 style = styles.add('@SYS4925');
 font = style.font();
 font.bold(true);
 //font.color(255);

 cells = sysExcelWorkSheet.cells();
 cells.range('A:A').numberFormat('@');
 // Make a title row
 //sysExcelWorkSheet.name('@JKG6826');
 sysExcelWorkSheet.cells().item(6,1).value('@SYS59617');
 sysExcelWorkSheet.cells().item(6,2).value(Date2Str(systemDateGet(),123,2,2,2,2,4));
 sysExcelWorkSheet.cells().item(7,1).value('@JKG6827');
 sysExcelWorkSheet.cells().item(8,1).value('@SYS24704');
 sysExcelWorkSheet.cells().item(9,1).value('@SYS80109');
 sysExcelWorkSheet.cells().item(10,1).value('@SYS7572');
 sysExcelWorkSheet.cells().item(11,1).value('@SYS30257');
 sysExcelWorkSheet.cells().item(12,1).value('@SYS26890');
 sysExcelWorkSheet.cells().item(13,1).value();
 sysExcelWorkSheet.cells().item(8,2).value(custAccount);
 select custTable where custTable.AccountNum==custAccount;
 sysExcelWorkSheet.cells().item(8,4).value(custTable.Name);
 sysExcelWorkSheet.cells().item(9,2).value('@SYS1150');
 select priceDiscTable where priceDiscTable.AccountRelation==custAccount;
 sysExcelWorksheet.cells().item(10,2).value(priceDiscTable.Currency);

 sysExcelWorkSheet.cells().item(14,1).value('@SYS80470');
 sysExcelWorksheet.cells().item(14,2).value('@SYS8631');
 sysExcelWorksheet.cells().item(14,3).value('@SYS59620');
 sysExcelWorksheet.cells().item(14,4).value('@JKG86');
 sysExcelWorksheet.cells().item(14,5).value('@JKG6828');
 sysExcelWorksheet.cells().item(14,6).value('@SYS74604');
 sysExcelWorksheet.cells().item(14,7).value('@JKG6829');
 sysExcelWorksheet.cells().item(14,8).value('@JKG4935');
 sysExcelWorksheet.cells().item(14,9).value('@JKG6830');
 sysExcelWorksheet.cells().item(14,10).value('@SYS79913');
 sysExcelWorksheet.cells().item(14,11).value('@SYS6437');
 sysExcelWorksheet.cells().item(14,12).value('@JKG6831');
 sysExcelWorksheet.cells().item(14,13).value('@SYS7437');
 sysExcelWorksheet.cells().item(14,14).value('@SYS28731');
 sysExcelWorksheet.cells().item(14,15).value('@JKG1552');
 sysExcelWorksheet.cells().item(14,16).value('@JKG1417');
 sysExcelWorksheet.cells().item(14,17).value('@SYS41043');

 sysExcelWorksheet.rows().item(14).style('@SYS4925');

 row = 14;


 while select inventTable exists join priceDiscTable1
 where priceDiscTable1.ItemRelation==inventTable.ItemId &&
 priceDiscTable1.relation==PriceType::PriceSales &&
 priceDiscTable1.AccountCode==TableGroupAll::Table &&
 priceDiscTable1.AccountRelation==custAccount
 {
 select priceDiscTable order by FromDate desc
 where priceDiscTable.ItemRelation==inventTable.ItemId
 && priceDiscTable.relation==PriceType::PriceSales
 && priceDiscTable.AccountCode==TableGroupAll::Table
 && priceDiscTable.AccountRelation==custAccount ;

 row++;
 sysExcelWorksheet.cells().item(row,3).value(inventTable.itemName);
 sysExcelWorksheet.cells().item(row,10).value(inventTable.ItemOwningLocationJKG);
 sysExcelWorksheet.cells().item(row,13).value(inventTable.OrigCountryRegionId);
 sysExcelWorksheet.cells().item(row,14).value(inventTable.Intracode);
 sysExcelWorksheet.cells().item(row,4).value(inventTable.B2CodeJKG[1]);
 sysExcelWorksheet.cells().item(row,2).value(priceDiscTable.ItemRelation);
 sysExcelWorksheet.cells().item(row,6).value(priceDiscTable.UnitId);
 sysExcelWorksheet.cells().item(row,15).value(priceDiscTable.Amount);

 select firstOnly analysisTable
 where analysisTable.ItemId == inventTable.ItemId;
 analysisNum = analysisTable.AnalysisNum[1];
 description = ItemSalesAnalysisCodesJKG::find(0,analysisNum).Description;
 sysExcelWorksheet.cells().item(row,1).value(description);

 select JKG_ItemCodes where JKG_ItemCodes.ItemId==inventTable.ItemId;
 sysExcelWorksheet.cells().item(row,7).value(enum2str(JKG_ItemCodes.CEMark_JKG));
 sysExcelWorksheet.cells().item(row,9).value(enum2str(JKG_ItemCodes.HazardousMaterialFlag_JKG));

 select JKG_TemperatureCodes where JKG_TemperatureCodes.Code==JKG_ItemCodes.TransitTempCode_JKG;
 sysExcelWorksheet.cells().item(row,8).value(JKG_TemperatureCodes.Description);

 select itemOwningLocationJKG where itemOwningLocationJKG.LocationId==inventTable.ItemOwningLocationJKG;
 sysExcelWorksheet.cells().item(row,12).value(itemOwningLocationJKG.Description);
 sysExcelWorksheet.cells().item(row,5).value(inventTable.B2CodeDescriptionJKG());

 select custTable where custTable.AccountNum==custAccount;
 sysExcelWorksheet.cells().item(row,11).value(custTable.InventLocation);


 sysExcelWorksheet.cells().item(row,16).value();
 sysExcelWorksheet.cells().item(row,17).value(strfmt('=O%1*P%1',row));
 sysExcelWorksheet.cells().item(row,17).value();

 rowcount++;
 }

 cells.range('O1:O'+int2str(14+rowcount)).numberFormat('0.00');
 cells.range('P1:P'+int2str(14+rowcount)).numberFormat('0.00');
 cells.range('Q1:Q'+int2str(14+rowcount)).numberFormat('0.00');



 sysExcelWorkSheet.cells().item(11,2).value(strfmt('=SUM(Q1:Q%1)',int2str(14+rowcount)));
 sysExcelWorkSheet.cells().item(12,2).value(strfmt('=SUM(P1:P%1)',int2str(14+rowcount)));

 //Set up image parameters
 resourceNode = SysResource::getResourceNode(resourcestr(JKG_CompanyLogo));
 if (resourceNode)
 {
 resourceNode. AOTload();
 imagename = SysResource::saveToTempFile(resourceNode);
 }
 else
 {
 throw Error('@AXT51164');
 }
 variant.bStr(imagename);
 xpos.int(1);
 ypos.int(1);
 state.int(1);
 width.int(200);
 height.int(050);
 //Add the image to the worksheet
 comShapes = comWorkSheet.shapes();
 comShapes.addPicture(variant,xpos,ypos,state,state,width,height);

 //Autofitt and protect the sheet
 sysExcelWorksheet.columns().autoFit();
 sysExcelWorksheet.protect('',false,false);

 //Save the sheet and close the app
 comWorkBook.saveas(dlgFilePathVal);
 comWorkBooks.close();
 comApplication.quit();
 }
 catch(Exception::Error)
 {
 //Force app to quit
 comWorkBook.saveas(dlgFilePathVal);
 comWorkBooks.close();
 comApplication.quit();
 }

}

 public container pack()
{
 return [#CurrentVersion,#CurrentList];
}

public boolean unpack(container packedClass)
{
 Version version = RunBase::getVersion(packedClass);
;
 switch (version)
 {
 case #CurrentVersion:
 [version,#CurrentList] = packedClass;
 break;
 default:
 return false;
 }

 return true;
}

public void run()
{
 #OCCRetryCount
 if (! this.validate())
 throw error("");

 try
 {
 ttsbegin;
 this.insertToExcelFile();
 Info("@JKG6833");


 ttscommit;
 }
 catch (Exception::Deadlock)
 {
 retry;
 }
 catch (Exception::UpdateConflict)
 {
 if (appl.ttsLevel() == 0)
 {
 if (xSession::currentRetryCount() >= #RetryNum)
 {
 throw Exception::UpdateConflictNotRecovered;
 }
 else
 {
 retry;
 }
 }
 else
 {
 throw Exception::UpdateConflict;
 }
 }

}

public boolean validate()
{
 if (!custAccount)
 return checkFailed(strfmt("@SYS26332",'@SYS24704'));
 if (!dlgFilePathVal)
 return checkFailed(strfmt("@SYS26332",'@SYS124357'));

 return true;
}

static void main(Args _args)
{
 JKG_CustomerPriceDownload JKG_customerPriceDownload = new JKG_CustomerPriceDownload();
 ;
 if (JKG_customerPriceDownload.prompt())
 JKG_customerPriceDownload.run();

}