Export Data to Excel

Below two methods would:

• Create an excel file
• Export the data from AX table to Excel file
• Save the excel file

void export2Excel()

{

    boolean                 excelInit;

    str                     lFilepath,lFilename,lFileExt;

    tmpProjectOpenPOs    tmpProjectOpenPOs = this.parmTmpProjectOpenPOs();

    #define.csv(".csv")

    ;
    progressTotal = 100;//TODO: SysQuery::countLoops(queryRun);

    [lFilepath, lFilename, lFileExt]   = Global::fileNameSplit(filename);

    WHILE SELECT tmpProjectOpenPOs 

    {

        if (!excelInit)

        {

            row = 1;

            this.initExcel(); // Intiate the headings

            excelInit = true;

        }

        i++;

        Cell = cells.item(i,1);

        Cell.value(tmpProjectOpenPOs.ProjId);

        Cell = Cells.item(i,2);

        Cell.value(tmpProjectOpenPOs.PurchId);

        Cell = cells.item(i,3);

        Cell.value(tmpProjectOpenPOs.LineNum);

        Cell = cells.item(i,4);

        Cell.value(enum2str(tmpProjectOpenPOs.LineStatus));//Enum field

        Cell = cells.item(i,5);

        Cell.value(tmpProjectOpenPOs.NetAmount);
         Cell = cells.item(i,6);

        Cell.value(Date2Str(tmpProjectOpenPOs.DeliveryDate,213,2,4,2,4,2)); // Date field

    }

    if (!excelInit)

    {

        info(strFmt("No records available"));

    }

    else

    {

       excel.visible(false);

        excel.displayAlerts(false);

        If (lFileExt == #csv)

        {

            book.saveAs(fileName,6);

        }

        Else

        {

             book.saveAs(fileName);

        }

        book.saved(true);

        books.close();

        excel.quit();

        info("Records exported successfully");

    }

    books = null;

    book = null;

    sheet = null;

    cell = null;

    excel = null;

}

----- Intiate the heading labels (called in the method above) ---------


void initExcel()

{

    int locRow = 1;

    ;

    i=1;

    excel   = SysExcelApplication::construct();

    books   = excel.workbooks();

    excel.visible(false);

    excel.displayAlerts(false);

    books.close();

    books.add();

    book    = books.item(1);

    sheets  = excel.worksheets();

    sheet   = sheets.itemFromNum(1);

    sheet.name(strFmt("Purchase orders"));

    cells   = sheet.cells();
    Cell = cells.item(i,1);

    Cell.value("Project");

    Cell = cells.item(i,2);

    Cell.value("Purchase order");

    Cell = cells.item(i,3);

    Cell.value("Line no");

    Cell = cells.item(i,4);

    Cell.value("Line status");

    Cell = cells.item(i,5);

    Cell.value("Net amount");

}