Exporting table to Excel in Oracle ADF

Exporting table to Excel in Oracle ADF

Hi,

I will continue to enhance my Working with Popups post and will add a export to excel functionality to af:table component. You will know the power of ADF with just simple clicks you are able to add this functionality to table.

What you will learn
 - Functionality of af:panelCollection
 - Working with menus
 - Adding Exporting to excel behavior to the af:table

Download this workspace change connection settings and follow steps

Adding Panel Collection
- Open the countries.jspx page and select af:table component in structure window  give this table a proper name i.e "myTab" by setting id property of the table in property Inspector. 

- Right click the af:table component and choose "Surround with..." and Choose Panel Collection
Note: A panel component that aggregates collection components like table, treeTable and tree to display standard/application menus, toolbars and statusbar items

Adding a Menu in Panel Collection
- Drop af:menu component in the Menu facet of the Panel collection set the Text property as "File"
- Drop af:menuitem on af:menu set the Text property as "Export to Excel"

Adding Export behavior to Menu
- Drop an af:exportCollectionActionListener to the Menu item. A dialog box will appear choose type as the only option available "excelHTML" Choose exportedId from the menu choose edit navigate through the nodes and find af:table "myTab" click OK and OK again.

- Complete Structure window would looks like this

- Set the properties of the af:exportCollectionActionListener as shown in the slide for more meaningful file name and title of worksheet

Run the Form
- Right click on the countries.jspx page and choose Run

- Page similar to this will appear Choose File > Export to Excel from the Menu

- Excel file save dialog will appear

I hope this post would be helpful feel free to comment

kill a session which is locked in Oracle apps

This Article is used to explain how to kill a session which is locked

Check if the Package or table are locked using the below query

SELECT b.object_name,
       a.session_id,
       a.oracle_username,
       a.os_user_name,
       a.process,
       a.locked_mode
  FROM v$locked_object a,
       all_objects b
 WHERE a.object_id = b.object_id


b.    Get the serial number for the session based on the session id got from above qyery.


SELECT SID,
       serial#,
       ownerid,
       status,
       server,
       username,
       osuser,
       process,
       machine
FROM v$session
WHERE SID = ‘Session id from above query’

c.    Command to kill the session

ALTER SYSTEM KILL SESSION 'Sid from query, Serial# from Query 2'

Import Data from csv to DB Table through ADF page

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:

1 2 3 4 5 6 7 8

CREATE TABLE ALS_STAFF ( ID NUMBER(3,0), FIRST_NAME VARCHAR2(20 BYTE), LAST_NAME VARCHAR2(30 BYTE), EMAIL_ADDRESS VARCHAR2(100 BYTE), CONSTRAINT ALS_STF_PK PRIMARY KEY (ID) ENABLE )

And the csv file

1 2 3 4 5

ID,FRST_NAME, LAST_NAME,EMAIL 100,Peter,Jones,testmail@mycompany.org 101,John,Janssen,a-testmail@mycompany.org 102,Thomas,Higgins,b-testmail@mycompany.org .............

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.

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:

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.

1 2 3 4 5 6 7 8 9 10

public void fileUploaded(ValueChangeEvent valueChangeEvent) { // Add event code here... UploadedFile file = (UploadedFile)valueChangeEvent.getNewValue(); try { parseFile(file.getInputStream()); AdfFacesContext.getCurrentInstance().addPartialTarget(staffTable); } catch (IOException e) { // TODO } }

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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66

public void parseFile(java.io.InputStream 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 = (JUCtrlHierBinding)_tableModel.getWrappedData(); //Acess the ADF iterator binding that is used with ADF table binding DCIteratorBinding it = _adfTableBinding.getDCIteratorBinding();   //read comma separated file line by line try { while ((strLine = reader.readLine()) != null) { lineNumber++; // create a new row skip the header (header has linenumber 1) if (lineNumber>1) { rw = it.getNavigatableRowIterator().createRow(); rw.setNewRowState(Row.STATUS_INITIALIZED); it.getNavigatableRowIterator().insertRow(rw); }   //break comma separated line using "," st = new StringTokenizer(strLine, ","); while (st.hasMoreTokens()) { //display csv values tokenNumber++;   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.

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.