Hi Techies,
Hope you are doing good. Recently due to INDIA GST Update in Microsoft dynamics AX most of the clients want to export data in excel to prepare GSTR excel files.
I designed a good excel worksheet with X++ and exported data as per requirement. I am sharing with you how can you design excel sheets with the help of X++ code. Please check below.
Step 1. Create a Class. This is class declaration method.
public class MKS_ExportToExcel extends Runbase
{
#AviFiles
DialogField dlgFromDate,dlgToDate;
TransDate fromDate,toDate;
//Excel file declaration
SysExcelApplication xlsApplication;
SysExcelWorkBooks xlsWorkBookCollection;
SysExcelWorkBook xlsWorkBook;
SysExcelWorkSheets xlsWorkSheetCollection;
SysExcelWorkSheet xlsWorkSheet;
SysExcelRange xlsRange;
int row;
str fileName;
VendInvoiceJour vendInvoiceJour;
#DEFINE.CurrentVersion(1)
#LOCALMACRO.CurrentList
fromDate,
toDate
#ENDMACRO
}
Step 2. Create Dialog method.
protected Object dialog()
{
Dialog dialog = super();
#resAppl
;
// Set a title for dialog
dialog.caption('Inward Supply Register Report');
dialog.addTabPage('Selection Criteria');
dialog.addGroup('Select date range');
// Add a new field to Dialog
dlgFromDate = dialog.addFieldValue(typeid(TransDate), fromDate,"From date");
dlgToDate = dialog.addFieldValue(typeid(TransDate), toDate, 'To date');
return dialog;
}
Step 3. Create getFromDialog method.
public boolean getFromDialog()
{
;
// Retrieve values from Dialog
fromDate = dlgfromDate.value();
toDate = dlgToDate.value();
return super();
}
Step 4. main Method
public static void main(Args _args)
{
MKS_ExportToExcel reportClass = new MKS_ExportToExcel();
// Prompt the dialog, if user clicks in OK it returns true
if (reportClass.prompt())
{
reportClass.run();
}
}
Step 5. run method.
public void run()
{
;
this.setExcelInitialsAndHeader();
this.fetchDataIntoExcel();
this.endExcel();
}
Step 6. setExcelInitialsAndHeader - To setup Excel report heading section.
public void setExcelInitialsAndHeader()
{
COM range;
COM autoFit;
COM merge;
COM WrapText;
COM ColumnWidth;
COM RowHeight;
SysExcelStyles styles;
SysExcelStyle style;
SysExcelFont font;
SysExcelCells cells;
FileNameFilter filter = ["Excel file", "*.xlsx"];
;
row = 1;
fileName = winapi::getSaveFileName(infolog.hWnd(), filter , @"c:\...\desktop", "Save as Excel file","xlsx","Report name");
//fileName ="C:\\Windows\\Temp\\test.xlsx";
if(!fileName)
return;
//Check whether the document already exists
if(WinApi::fileExists(fileName))
WinApi::deleteFile(fileName);
//throw error("File already exist");
//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);
Styles = xlsWorkBook.styles();
// Create new style
style = styles.add('Header');
// Set font for this style to bold
font = style.font();
font.bold(true);
font.color(51);
//Excel Report Heading
range = xlsWorkSheet.range('A1:F1').comObject();
range.merge();
RowHeight = range.RowHeight(30);
xlsWorkSheet.rows().item(1).style('Title');
range = xlsWorkSheet.range('A2:BE2').comObject();
xlsWorkSheet.rows().item(2).style('Output');
xlsWorkSheet.rows().item(2).horizontalAlignment(-4108);
wrapText = range.wraptext(true);
ColumnWidth = range.ColumnWidth(14.71);
xlsWorkSheet.cells().item(row,1).value('GST Report');
row++;
//Excel column caption
xlsWorkSheet.cells().item(row,1).value('Sr. No.');
xlsWorkSheet.cells().item(row,2).value('Vendor Code');
xlsWorkSheet.cells().item(row,3).value('Vendor Name');
row++;
}
Step 6. endExcel - To Setup process while closing excel.
public void endExcel()
{
;
// Set the column width to autoFit
xlsWorkSheet.columns().autoFit();
//Save Excel document
xlsWorkbook.saveAs(fileName);
xlsWorkbook.comObject().save();
xlsWorkbook.saved(true);
//Open Excel document
xlsApplication.visible(true);
}
Step 7. fetchDataIntoExcel - Used to put data into excel rows.
public void fetchDataIntoExcel()
{
VendTable vendTable;
CompanyInfo companyInfo = CompanyInfo::find();
TaxWithholdTrans_IN tdsTable;
LedgerJournalTrans ledgerJournalTrans;
;
progress = new RunbaseProgress();
progress.setCaption("Export To Excel in progress...");
progress.setAnimation(#AviTransfer);
while select vendInvoiceJour
order by vendInvoiceJour.InvoiceDate
where vendInvoiceJour.InvoiceDate >= fromDate
&& vendInvoiceJour.InvoiceDate <= toDate
{
vendTable = VendTable::find(vendInvoiceJour.InvoiceAccount);
progress.setText(strfmt("Vendor %1", vendTable.Name));
xlsWorkSheet.cells().item(row,1).value(row-2);
xlsWorkSheet.cells().item(row,2).value(vendInvoiceJour.InvoiceAccount);
xlsWorkSheet.cells().item(row,3).value(vendTable.name);
row++;
}
}
Step 8. PACK and UnPack methods.
container pack()
{
return [#CurrentVersion,#CurrentList];
}
public boolean unpack(container _packedClass)
{
int version = conPeek(_packedClass,1);
switch (version)
{
case #CurrentVersion:
[version,#CurrentList] = _packedClass;
break;
default:
return false;
}
return true;
}
