Shekhar: Dynamics AX 2009 : Exporting data to Excel (Part2)

[Blog bot]

Источник: http://shekhardiptiman.spaces.live.c...B978!433.entry
==============

In the previous post “Exporting data to excel (Part1)” I suggested one way to achieve the subject objective. For readers who have missed to see the ‘Part1’ pls check out the following link.

Exporting data to Excel ( Part1) 

As there are merits of a solution which depend on the situations and conditions in which they are used, I am suggesting one more alternate for achieving the objective i.e. Exporting data to Excel. In this solution we will make use of standard AX’s Reports and the concept of exporting the report data to an Excel file. I assume that we all understand that any standard AX report can be sent to a variety of Output devices such as Printer,Screen,Fax,File ( CSV,TXT,PDF,EXCEL etc) . The disadvantage in this is that the report headers is repeated after every page break and that is an undesired part . In the following walkthrough we will get over this problem as well as restrict the output to a file so that it gives a look and feel of an data export functionality.

We will create a sample report for exporting the Price agreements data to Excel

Start creating a report in AOT as per the following steps :

Step 1: Create a class as shown below in which we would declare objects.



public class ReportRun extends ObjectRun
{
    boolean                     printHeading ;

    DialogField                 dlgFileName;
    FileName                    fileName;
}

“printHeading” is for checking whether the report heading has been printed already. If so, then do not not print them on the page. So headings will be printed on page one but not on next pages. The other two variables are for getting the filename provided by users from dialog 

Step 2:  Override the following two methods as shown below to set the PrinterSettings and PrintMedium ( i.e. File)

public boolean printerSettings(int _showWhat=-1)
{
    boolean ret;

    ret = super(_showWhat);

    return ret;
}

 

public PrintMedium setTarget(PrintMedium _target)
{
    PrintMedium ret;

    ret = super(PrintMedium::File);

    return ret;
}

Step3 :Add an init method to initialise the report

public void init()
{
    super();

    printHeading    = true;  // initially the heading will be printed
    this.printJobSettings().setTarget(PrintMedium::File);  // setting the report output option to “file”
    this.printJobSettings().preferredFileFormat(PrintFormat::ASCII); // Print format is set to ASCII
    this.printJobSettings().fileName(fileName);                             //"c:\\PriceAgrements.xls");

}



Step 4: Add a report dialog (Override dialog method)

public Object dialog(Object _dialog)
{
    Dialog dialog = super(_dialog);

    dlgFileName = dialog.addFieldValue(typeid(FileNameSave), "");

    return dialog;
}

public boolean getFromDialog()
{

;
    fileName    = dlgFileName.value();   // get values for file name from the dialog

    return true;
}

Step 5 : The followiing two methods are mandatory to add when we need to provide a button for browsing the file and selecting the file name on dialog

// AOSRunMode::client
str fileNameLookupTitle()
{
    return  'Select file to save';
}

 

container fileNameLookupFilter()
{
    return [".CSV", ".xls"];
}

Step 6 : Finally the override the run method

public void run()
{;
    this.printJobSettings().fileName(fileName);

    super();
}

Step 7 : In addition to the above, add methods for getting the fields  which we wish to export or choose in the fields’ properties in the design “body” section . I have provided a link for the sample xpo below for download. not sure if it will work . But in case it does not then send me an email.

SD_ExportToExcel



e.g. for QtyAmount field the field property can be set to fetch data from PriceDiscTable.QuantityAmount



similarly for other fields shown in “Body”also we can set the field property appropriately.

Step 8 : In the report design add the following to prevent the printing of report heading in subsequent pages.

public void executeSection()
{
    if(printHeading)
    {
        super();
    }

    printHeading = false;
}



The other methods shown in the report for fetching the field data are for reference/educational purpose only that there is  another way of getting the field values.

e.g. we can get the QuantityAmount field by adding following method also and then in the field property in the report‘s “Body” section use the DataMethod instead of Table and Data field properties. 

display Amount qtyAmount()
{
    return priceDiscTable.QuantityAmount;
}

The text fields added in the report design “ Page header are for printing the column headings



Step 9 : Test the report. When run the following dialog will open



Select the file name and path using the browse button .





Hit OK. The file will be saved at the location specified. Open the file using MS Excel. It will prompt that the file is in different format and if you still wish to open it using excel, Accept and go ahead.









Источник: http://shekhardiptiman.spaces.live.c...B978!433.entry