APEX offers a standard built-in to export data to a CSV (Comma Separated Value) file. A CSV file is readable by Microsoft Excel or OpenOffice Calc. Each row contains the data of a record in a table and each value in the row is separated by a comma and represents the value of a column of the table. When export to CSV is enabled, a link is shown just below the region with the data. We will show you how to add this link.
You should have a working application with at least one standard report. If you don't have a report, create one. You can find a recipe for creating a simple report in Chapter 1,
First, we will adapt the report. We will put a link on the report.
- Go to the Report page.
- In the Regions section, click on the Report link.
- In the Report Export section, select Yes in the Enable CSV output list box.
- In the Link Label text field, enter export to csv. This text appears as a link below the region.
- In the filename field, you can enter a filename. By default, the filename is the name of the region with the extension
.csv. - Click the Apply changes button.
- The link should now appear when you run the report.
When you click on the link, you will get a pop-up window asking whether you want to open the file with the program of your choice or download it to your computer.
The link on the screen points to the same page but with an extra argument in the URL:
http://localhost:8000/apex/f?p=114:12:2184716804402017:FLOW_EXCEL_OUTPUT_R435417443089863248_en
At the end of the URL, you see FLOW_EXCEL_OUTPUT_R435417443089863248_en. The large number after FLOW_EXCEL_OUTPUT_R is the region ID. You can find it in the view APEX_APPLICATION_PAGE_REGIONS. So, with the help of that view, you can make your own link. You just need the application ID and the page ID. So, for example, if you have an application with the application ID 114 and a report with the page ID 12, and you want to add an "export to csv" link in your report, enter the following:
select 'http://localhost:8000/apex/f?p='||application_id||':'||page_id||':&SESSION.:FLOW_EXCEL_OUTPUT_R'||region_id||'_en' from apex_application_page_regions where upper(source_type) = 'REPORT' and application_id = 114 and page_id = 12;
[1346_09_01.sql]
(This is provided that APEX is installed on your local machine and that it is set to port 8000). The result will be something like this:
http://localhost:8000/apex/f?p=114:12:2184716804402017:FLOW_EXCEL_OUTPUT_R435417443089863248_en.
This link also works even if you have set Enable CSV output to No.
If you have an interactive report, you have also the possibility to export to CSV. Click on the Actions button and select Download.
In the Download section you can click on the CSV icon to export the report to CSV format. To see the link, just hover over the CSV icon. In the status bar at the bottom of your screen you will see something like this:
http://localhost:8000/apex/f?p=108:15:2184716804402017:CSV:
So here it's even simpler to generate a download to CSV link.