Export ADF Table To Excel

Recently a colleague of mine came up with a blog about exporting data shown in an af:table-component to Excel using the Apache POI API.

Some time ago I’ve accomplished the same behaviour without using any additional API or framework because I wanted to stay ‘independend’ and not use another framework on top of ADF. So I’ve done some research on OTN and finally figured out we couls accomplish the same behaviour using the Response of our HttpServlet to stream the data as Excel to our Browser.

How did I accomplish this?


I’ve created a Backing Bean for the jspx-page to be able to add my own business logic in a java class. In the jspx-document I’ve added the actionListener-attribute to my component and this attribute is bound to a method in my backing bean.

In my backing bean I’m delegating the action to my java class that holds the generic business logic. This method accepts the DCIteratorBinding as parameter to be able to browse the table-data for my Excel-file.

public void exportToExcel(ActionEvent actionEvent) throws
IOException {
ExportToExcel.exportHtmlTableToExcel((DCIteratorBinding)bindings.get(“findAllIter”)); }

The Java Class that actually performs the transformation to Excel uses the HttpServletResponse to output the data as Excel.

public static void exportHtmlTableToExcel(DCIteratorBinding tableContent) throws IOException {

//Set the filename DateTime dt = new
DateTime();
DateTimeFormatter fmt =
DateTimeFormat.forPattern(“yyyy-MM-dd_HHmmss”);
String filename = dt.toString(fmt) + “.csv”;

//Setup the output

String contentType = “application/vnd.ms-excel”;
FacesContext fc = FacesContext.getCurrentInstance();
HttpServletResponse response = (HttpServletResponse)fc.getExternalContext().getResponse();
response.setHeader(“Content-disposition”, “attachment; filename=” + filename);

response.setContentType(contentType);
PrintWriter out = response.getWriter();

RowSetIterator rsi = tableContent.getRowSetIterator();

String[] attNames = rsi.getRowAtRangeIndex(0).getAttributeNames();

for (int i = 0; i

out.print(attNames[i] + “;”);

}

out.println();

for (int i = 0; i

Row currentRow = rsi.getRowAtRangeIndex(i);

Object[] attValues = currentRow.getAttributeValues();

for (int j = 0; j

out.print(attValues[j] + “;”);

}

out.println();

}
out.close();

fc.responseComplete();

}

Oracle Data Integrator – Part 2 – Working with Excel Data

What have I learned today …

Today I took a closer look at declaring or using my Excel file as a source model for the ODI for which I’ve used the ODI User Guide and the help-documentation in ODI itself.


What do I need to do to get ODI to talk to my Excel file?

  • Create a new ODBC datasource for my Excel files using the Microsoft ODBC Data Source Administrator (run from the Configuration Panel)
  • Make sure you’ve defined a name for the data in the Excel spreadsheet. You can accomplish this by defining a name for the data shown in every worksheet. You have to mark the data in a specific worksheet (select all the rows and columns with data) and choose to insert a name for this data using the insert-menu.

For the configuration of ODI I’ve used different kinds of guidance, help then only the User Guide. To define my ODBC Datasource in the ODI I’ve followed the topic ‘Create a Microsoft Excel Data Server’ (written in the help-documentation of ODI) . This topic has a more detailed approach then the topic ‘Creating a Data Server’ explained in the User Guide.

The steps I’ve performed:

  • Connect to the Topology Manager (first open up the Designer from your Oracle Data Integrator menu)

  • Select Topology > Physical Architecture > Technologies > Microsoft Excel in the tree

  • Right click and select Insert Data Server
  • Fill in the necessary fields in the Definition and JDBC tab, such as the name and the JDBC Driver (sun.jdbc.odbc.JdbcOdbcDriver) and JDBC URL (jdbc:odbc: => the name you defined for your ODBC data source)

  • You can test your connection, test if the connection was succesfull by clicking the test-button in your Data Server properties screen.

The next step we need to perform is to create the physical schema for our Excel file to be able to map this ‘source’ schema to the ‘target’ schema.

  • Right-click on your newly created Data Server and choose to insert a new physical schema:
  • Leave the defaults as is for the Physical schema and choose the wright context in the context-tab which means you need to specify the logical schema you want to use. Specify a name and click apply and ok.

  • Let’s reverse engineer the Excel file into a new model. Go to the designer and choose to insert a new model. In the technology-dropdown choose ‘Microsoft Excel’ and choose your Logical Schema.
  • In the reverse-tab make sure to select the context you want to define the model in.
  • In the ‘Selective Reverse’ tab choose all checkboxes and the data you’ve defined names for in the spreadsheet will be listed. Click OK to reverse-engineering the defined data into your model.
  • In the model-tree you can see the different tables that where created inside the model depending on the data you’ve defined in the excel sheet.

  • In the different DataStores that were created (depending on the data you’ve defined in the spreadsheet) you can check the colums that were defined in your spreadsheet.

The next step is to start creating Interfaces to define the transformation that’s needed from our source-model to our target-model.

Stay Tuned