First create the Excel template with all the fields one by one and place it into Specific location .//
Using dialog we get the input and based on the input we have to generate the Report.
In Ax 2009, We have the option to create EXCEL Report. Using class we have to create the excel report. creating excel report is very simple.
Using dialog we get the input and based on the input we have to generate the Report.
Requirement is based on the customer Po and Item Id, I want to generate the Report.
I am getting input form the dialog.
//Declare all the variable in the class declaration.
class InventoryStockSheet
{
//Define the column
#Define.A(1)
#Define.B(2)
#Define.C(3)
#Define.D(4)
#Define.E(5)
#Define.F(6)
#Define.G(7)
#Define.H(8)
#Define.I(9)
#Define.J(10)
#Define.K(11)
#Define.L(12)
#Define.M(13)
#Define.N(14)
#Define.O(15)
#Define.P(16)
#Define.Q(17)
#Define.R(18)
#Define.S(19)
#Define.T(20)
#Define.U(21)
//Excel Application initialization varibles
//Start
SysExcelApplication excelApplication;
SysExcelWorksheet excelWorksheet;
SysExcelWorksheets excelWorksheets;
SysExcelWorkbooks excelWorkbooks;
SysExcelWorkbook excelWorkbook;
SysExcelRange excelRange;
SysExcelCell excelCell;
SysExcelCells excelCells;
COMVariant COMVariant1;
COM Borders;
COM Border;
SysExcelStyles styles;
SysExcelStyle style;
//End
// Variable Declaration Start
Counter row,slNo;
DialogField dialogCustPO;
DialogField dialogItemId;
ItemId itemId;
SalesIdBase salesId;
SalesLine salesLine;
BOM bom;
SalesTable salesTable;
InventTable inventTable;
BOMVersion bomVersion;
ItemControl itemControl;
CustPurchaseOrder custPo;
ObjectRun objRun;
//End
}
//This method is initialize the Excel Sheet and find the Path.
void initilizeExcel()
{
excelApplication = SysExcelApplication::construct();
excelWorkbooks = excelApplication.workbooks();
cOMVariant1 = new COMVariant();
cOMVariant1.bStr('C:\\Users\\ADeveloper2\\Desktop\\StockRep1');
excelWorkbook = excelWorkbooks.add(COMVariant1);
excelWorksheets = excelWorkbook.worksheets();
excelWorksheet = excelWorksheets.itemFromNum(1);
excelWorkSheet.name("@SYS16399");
}
//This method is for Dialog BoX
boolean promptPeriod(Caption _caption)
{
Dialog dialog = new Dialog(_caption);
DialogGroup mainGroup1;
str tmp;
;
tmp = objRun.args().parm();
mainGroup1 = dialog.addGroup("Stock Sheet");
mainGroup1.frameType(FormFrameType::Edged3D);
mainGroup1.widthMode(1);
// dialogCustPO = dialog.addFieldValue(TypeId(CustPurchaseOrder),custPo,"Cust PO");
dialogCustPo = dialog.addField(str2int(tmp));
dialogItemId = dialog.addFieldValue(TypeId(ItemId),itemId,"Item Id");
if (! dialog.run())
return false;
return true;
}
//This method is getting value form dialog Box.
void getFromDialog()
{
;
custPo = dialogCustPO.value();
itemId = dialogItemId.value();
}
//This method is printing the value in Excel Sheet.
//All the logic is written in this method. In normal report we have to write the code in fetch method. Similarly we have to write the logic here.
void getdata()
{
int Val;
boolean check,increment;
Qty totQty;
int zeroVal;
InventBatchId batch;
;
zeroVal = 0;
// in dialog we pass the customer PO and Item It means. This if loop will be executed.
if(custPo && itemId)
{
Val = 5;
check = true;
// second row and B cell display the customer Po
row = 2;
excelCell = excelWorksheet.cells().item(row,#b);
excelCell.value(custPo);
while select salesTable
where salesTable.PurchOrderFormNum == custPo
{
while select salesLine
index hint SalesLineIdx
where salesLine.SalesId == salesTable.SalesId
&& salesLine.ItemId == itemId
&& salesLine.ItemId != "100.000-RM"
&& salesLine.ItemId != "100.000"
{
row = Val;
if(check == true)
{
excelCell = excelWorksheet.cells().item(row,#a);
excelCell.value(salesTable.PurchorderFormNum);
excelApplication.visible(true);
excelCell = excelWorksheet.cells().item(row,#b);
excelCell.value(salesTable.SearchName);
excelApplication.visible(true);
check = false;
}
excelCell = excelWorksheet.cells().item(row,#c);
excelCell.value(salesLine.ItemId);
excelApplication.visible(true);
excelCell = excelWorksheet.cells().item(row,#d);
excelCell.value(salesLine.SalesQty);
excelApplication.visible(true);
Val++;
}
}
}
else if(custPO)
{
Val = 5;
check = true;
row = 2;
excelCell = excelWorksheet.cells().item(row,#b);
excelCell.value(custPo);
while select salesTable
where salesTable.PurchOrderFormNum == custPo
{
while select salesLine
index hint SalesLineIdx
where salesLine.SalesId == salesTable.SalesId
&& salesLine.ItemId != "100.000-RM"
&& salesLine.ItemId != "100.000"
{
row = Val;
if(check == true)
{
excelCell = excelWorksheet.cells().item(row,#a);
excelCell.value(salesTable.PurchorderFormNum);
excelApplication.visible(true);
excelCell = excelWorksheet.cells().item(row,#b);
excelCell.value(salesTable.SearchName);
excelApplication.visible(true);
check = false;
}
excelCell = excelWorksheet.cells().item(row,#c);
excelCell.value(salesLine.ItemId);
excelApplication.visible(true);
excelCell = excelWorksheet.cells().item(row,#d);
excelCell.value(salesLine.SalesQty);
excelApplication.visible(true);
Val++;
}
}
}
}
//we call the dialog, Initialize excel and getdata method here. And we will give the run method into the main method.
void run()
{
;
// this.getFromDialog();
this.initilizeExcel();
this.getdata();
}
//In this method we fix the font size and color.
void setFontHeader(SysExcelCell _sysExelCell)
{
COM comg,comfont;
;
comg = _sysExelCell.comObject();
comfont = comg.font();
comfont.bold(3);
comfont.size(16);
comfont.color(WINAPI::RGB2int(0,0,255));
}
//Create main method and create instance here.
public static void main(Args args)
{
InventoryStockSheet stockSheet;
;
stockSheet = new InventoryStockSheet();
if(stockSheet.promptPeriod(""))
{
stockSheet.run();
}
}
Thanks
DAX
Thanks
DAX
Comments
Post a Comment