ADF 11g : Import From Excel Into an ADF Table

ADF 11g provides an “export to excel” feature that allows you to take data from an ADF table into an excel sheet. But what about the other way around. Sometimes you need to work with excel sheets or comma separated values, and load the content of such a file into a database table. You could use the external table feature of the Oracle Database for that, or you just use any tool to load the csv contents into a database table. The use case I have is that the data in the csv needs to be validated and changed before it is committed into the database. In this post I show you how to load the csv contents into an ADF table component so you can edit the data before committing

Setting up the application

This post is based on a simple table and on a simple csv file. The Staff table definition is here:


And the csv file


First step in the process is the creation of Business Components for the ALS_STAFF table.
Next I created a TaskFlow, a PageFragment, and a Page in which I implement the csvLoader functionallity.

ADF 11g : Import From Excel Into an ADF Table Application

Next I drop the AlsStaff collection from the datacontrol on the page as an ADF table component. This table is the one that I want to load with the data form a CSV file. To refresh the table after the file is loaded, I bind the table component so I can add it as a partialTartget. To upload the file I use an ADF inputfile component. This will open a dialog for me to select the file. Finally I add a commit button in order to actually save the table contents to the database.
The ’empty’ page now looks like this:

ADF 11g : Import From Excel Into an ADF Table beforeUpload

Setting up CSV2ADF

After uploading the file, I need it to be processed immediately, so I set the autosubmit property of the inputFile component to true and I add a valueChangeListener.

public void fileUploaded(ValueChangeEvent valueChangeEvent) {
// Add event code here...
UploadedFile file = (UploadedFile)valueChangeEvent.getNewValue();
try {
} catch (IOException e) {

In this valueChangeListener I call the a method (parseFile()) which takes care of my CSV2ADF transformation. Here is the step by step guide.

First I create a new BufferReader that I use to read the file line by line.
Next I have to find the iterator associated with the component in which I want to lead the contents of the file.
The table is bound, so I can find the iterator via (CollectionModel)staffTable.getValue() and (JUCtrlHierBinding)_tableModel.getWrappedData().

Now we really can get started processing the file line by line. I assume that the first line of the file contains header information, so I always skip that line. For every line in the file I create a new row in the table. Then I use a StringTokenizer that splits the line after every comma so every line is processed token by token. Every attribute in the row now can be assigned the value of the corresponding token. Any exception are handled and communicated back to the user.

public void parseFile( file) {
BufferedReader reader = new BufferedReader(new InputStreamReader(file));
String strLine = "";
StringTokenizer st = null;
int lineNumber = 0, tokenNumber = 0;
Row rw = null;

CollectionModel _tableModel = (CollectionModel)staffTable.getValue();
//the ADF object that implements the CollectionModel is JUCtrlHierBinding. It
//is wrapped by the CollectionModel API
JUCtrlHierBinding _adfTableBinding =
//Acess the ADF iterator binding that is used with ADF table binding
DCIteratorBinding it =

//read comma separated file line by line
while ((strLine = reader.readLine()) != null)
// create a new row skip the header (header has linenumber 1)
if (lineNumber>1) {
rw = it.getNavigatableRowIterator().createRow();

//break comma separated line using ","
st = new StringTokenizer(strLine, ",");
while (st.hasMoreTokens())
//display csv values

String theToken = st.nextToken();
System.out.println("Line # " + lineNumber + ", Token # " +
tokenNumber +
", Token : " + theToken);
if (lineNumber>1){
// set Attribute Values
switch (tokenNumber) {
case 1: rw.setAttribute("Id", theToken);
case 2: rw.setAttribute("FirstName", theToken);
case 3: rw.setAttribute("LastName", theToken);
case 4: rw.setAttribute("EmailAddress", theToken);
//reset token number
tokenNumber = 0;
catch (IOException e) {
// TODO add more
FacesContext fctx = FacesContext.getCurrentInstance();
fctx.addMessage(staffTable.getClientId(fctx), new FacesMessage(FacesMessage.SEVERITY_ERROR,
"Content Error in Uploaded file", e.getMessage()));
catch (Exception e) {
FacesContext fctx = FacesContext.getCurrentInstance();
fctx.addMessage( null, new FacesMessage(FacesMessage.SEVERITY_ERROR,
"Data Error in Uploaded file", e.getMessage()));

When I run the application, I can select a file to upload, the content of the file is immediately displayed in the table, I can edit the data, and once I’m ready I can commit the data to the database by hitting the commit button.

ADF 11g : Import From Excel Into an ADF Table afterUpload

This example just loads the content of a pre-defined csv file into a predefined ADF table component in order to save the content to a database table. A possible next step could be to load any csv file into an ADF table component that is created at runtime.


A demo workspace can be downloaded here.


  1. vinoth February 10, 2012
  2. Julieb January 11, 2012
  3. Brian January 11, 2012
  4. SAN January 9, 2012
  5. A_khair January 5, 2012
  6. Narayan January 2, 2012
  7. Bipul November 4, 2011
  8. Amit Jarad October 31, 2011
  9. Kitty October 13, 2011
  10. Luc Bors March 6, 2011
  11. priyanka February 28, 2011
  12. Tiah December 20, 2010