How to Generate Template From Table in Ax 2012

Description:

In this article I have export table data to excel file or else you can dynamically export table header to excel file using X++ code.

Here I have created sample code to dynamically generate excel template for import data into table in ax 2012.

For that you have to create table “CustGroup_Custom”. Here I have given our custom table name like that you can give what you want.

Add fields like SrNo, Description both should be in String data type.

1. CustGroup_Custom

• SrNo
• Description 

Now in SrNo field you can give your Number Sequence and in Description Field you can give what you want in Header.

Example:

SR No	Description
1	Customer Group
2	Name
3	PaymTermId
4	TaxGroupId

Now you are ready to Generate Template for Export into excel file.Here I have set for Path so where you can by default save your excel file or else you can open directly.

Here I have also add some header format so you can see when your excel file generate.

static void BlankTemplate(Args _args)
{
    SysExcelApplication application;
    SysExcelWorkbooks   workbooks;
    SysExcelWorkbook    workbook;
    SysExcelWorksheets  worksheets;
    SysExcelWorksheet   sysExcelWorkSheetToBeDeleted;
    SysExcelWorksheet   worksheet;
    SysExcelCells       cells;
    SysExcelCell        cell;
    SysExcelFont        font;
    #define.STYLE_BOLD('AX_Bold')
    SysExcelInterior    interior;
    SysExcelStyles      styles;
    SysExcelStyle       style;
    int                 row,column;
    str                 filepath;
    COM                 comg,comfont;
    str                 dateValue, dateFormat;
    DialogButton        dialogButton;
    CustGroup_Custom    ObjCustGroup_Custom;
    
    
    dialogButton= Box::yesNo("ARE YOU SURE WANT TO GENERATE CUSTOMER GROUP TEMPLATE", DialogButton::No,"CONFIRMATION FOR CUSTOMER GROUP TEMPLATE");
    if (dialogButton == DialogButton::Yes)
    {
        startLengthyOperation();
        //Filename start
        dateValue   = date2str( systemDateGet(),123,DateDay::Digits2,DateSeparator::None,DateMonth::Digits2,DateSeparator::None,
                            DateYear::Digits4,
                            DateFlags::FormatAll);
        dateFormat = strFmt("%1%2%3",dateValue, '_',subStr( strRem(time2Str(timeNow(), TimeSeparator::Space, TimeFormat::Auto), ' '),0,4));

        filepath = @"PATH" + "ALL CUSTOMER GROUP TEMPLATE " + dateFormat + ".xlsx";
        //Filename end

        application     = SysExcelApplication::construct();
        workbooks       = application.workbooks();
        workbook        = workbooks.add();
        worksheets      = workbook.worksheets();
        worksheet       = worksheets.itemFromNum(1);
        cells           = worksheet.cells();
        cells.range('A:A').numberFormat('@');
        row=1;
        column=1;

        while select ObjCustGroup_Custom order by ObjCustGroup_Custom.SrNo asc
        {
            cell = cells.item(row, column);
            cell.value(ObjCustGroup_Custom.Description);
            font = cell.font();
            font.bold(true);
            comg = cell.comObject();
            comfont=comg.font();
            comfont.bold(2);
            comg.style("Note");
            comfont.color(WINapi::RGB2int(0,0,0));
            column++;
        }
        application.visible(false);
        if (application.workbooks().count()>1)
        {
            application.workbooks().close();
        }
        while(worksheets.count() > 1)
        {
            sysExcelWorkSheetToBeDeleted = worksheets.itemFromNum(2);
            sysExcelWorkSheetToBeDeleted.delete();
        }
        worksheet.columns().autoFit();
        //workbook.saveAs(filepath);
        application.visible(true);
        //workbook.close();
        endLengthyOperation();
    }
}

Author : Umesh Patel

Related Posts