Generate Excel file in Oracle ADF using Apache POI

Requirement:Generate excel file using open source “Apache POI” to generate excel spreadsheets from my ADF application.

Advantage of using POI over default export collectionListener of ADF –

1. Using Apache POI, you can not only export excels but also can read excel files (.xls or .xlsx) from local machines and upload them to ADF applications
2. User has more control on the excel output file (than using “Export Collection Action Listener”)
for e.g. you can put data formatting for your currency/date column, freeze panes, apply filters etc.
3. Size of generated excel is less than from export CollectionListener.

Solution-

You need to download the apache POI jar from poi.apache.org

–>> poi-3.7-20101029.jar
You need to add this jar in project libraries and classpath of project.

Creating a sample project (using Department and Employee tables) and attaching libararies.

I would like to keep it simple as we don’t want to focus on any steps other than excel here, here are quick steps to create a sample application

1. create view object using query on locations table.
2. Expose the VO to applicationModule.then drag drop VO from data control to page as table
3. Place a command button anywhere on the page and label it as “ExporToExcel”
4. Drop a “” component on this button.
Put property of af:fileDownloadActionListener as follows
-> contentType: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet or you can use “application/octet-stream” .
-> ExcelFromPoi.xls
-> Method: #{PoiBean.generateExcel} //custom method call to export data

Now writing a custom method in managed bean to export locations shown in the screen.
I am writing an generateExcel() method.

In short, follow the steps-
create a Workbook object.create a Sheet object.Get object of bindings .After that we will get page iterator and from that iterator we will get all rows in that range. Start creating row first and then cells. After completion of sheet , we will writing outputStream on the workBook.

Following is code of managed bean –

package vinay.view;

import com.sun.jmx.snmp.Timestamp;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;

import java.util.HashMap;
import java.util.Map;

import javax.faces.context.FacesContext;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import oracle.adf.model.BindingContainer;
import oracle.adf.model.BindingContext;
import oracle.adf.model.binding.DCBindingContainer;

import oracle.adf.model.binding.DCIteratorBinding;



public class PoiBean {
    public PoiBean() {
    }


        public static BindingContainer getBindingContainer() {
            //return (BindingContainer)JSFUtils.resolveExpression("#{bindings}");
            return (BindingContainer)BindingContext.getCurrent().getCurrentBindingsEntry();
        }

        public static DCBindingContainer getDCBindingContainer() {
            return (DCBindingContainer)getBindingContainer();
        }
      
      public void generateExcel(FacesContext facesContext, OutputStream outputStream) throws IOException {
          try {
         
                            
                                HSSFWorkbook workbook = new HSSFWorkbook();
                                    HSSFSheet worksheet = workbook.createSheet("POI Worksheet");
              
                                DCBindingContainer bindings = (DCBindingContainer)BindingContext.getCurrent().getCurrentBindingsEntry();
                                DCIteratorBinding dcIteratorBindings = bindings.findIteratorBinding("ViewObj1Iterator");
                                HSSFRow  excelrow = null;
              
                                        // Get all the rows of a iterator
                                        oracle.jbo.Row[] rows = dcIteratorBindings.getAllRowsInRange();
                                    int i = 0;
                             

                                            for (oracle.jbo.Row row : rows) {

                                                //print header on first row in excel
                                                if (i == 0) {
                                                    excelrow = (HSSFRow)worksheet.createRow((short)i);
                                                    short j = 0;
                                                    for (String colName : row.getAttributeNames()) {
                                                      
                                                            HSSFCell cellA1 = excelrow.createCell((short) j);
                                                            cellA1.setCellValue(colName);
                                                            j++;
                                                        
                                                    }
                                                }

                                                //print data from second row in excel
                                                ++i;
                                                short j = 0;
                                                excelrow = worksheet.createRow((short)i);
                                                for (String colName : row.getAttributeNames()) {
                                                    System.out.println("hello "+row.getAttribute(colName));
                                                    System.out.println("hello "+colName);
                                                        HSSFCell  cell = excelrow.createCell(j);
                                                    if (colName.equalsIgnoreCase("DepartmentId")) {

                                                       
                                                        cell.setCellValue(row.getAttribute(colName).toString());
                                                        System.out.println("colName "+colName+"row.getAttribute(colName).toString()"+row.getAttribute(colName).toString());
                                                    }
                                                        //logic for cell formatting
                                                        if (colName.equalsIgnoreCase("DepartmenName")) {
                                                            cell.setCellValue(row.getAttribute(colName).toString());
                                                            
                                                        }
                                                        //make it double if you want and convert accordingly
                                                        else if (colName.equalsIgnoreCase("LocationId")){
                                                            cell.setCellValue(row.getAttribute(colName).toString());
                                                        }
                                                        else if (colName.equalsIgnoreCase("ManagerId")){
                                                            if(null!=row.getAttribute(colName)){
                                                            cell.setCellValue(row.getAttribute(colName).toString());
                                                            }
                                                        } else
                                                         cell.setCellValue(row.getAttribute(colName).toString());

                                                        j++;
                                                      
                                                    }
                                                
                                                   worksheet.createFreezePane(0, 1, 0, 1);
                                               } 
              workbook.write(outputStream);
              outputStream.flush();
              
          }
                                            catch (Exception e) {
                                          e.printStackTrace();
                                            }
                                        
      }
      }

You can freeze the header like this —

worksheet.createFreezePane(0, 1, 0, 1);

Resize the column automatically –

            sheet.autoSizeColumn(0);
            sheet.autoSizeColumn(1);
            sheet.autoSizeColumn(2);
            sheet.autoSizeColumn(3);

And finally you can get the generated excel report like below

you can download the sample application from below, I have also illustrat how you can generate excel using export collectionListner in sample application.Button label as exportCollectionActionListener –

exportToExcel

Happy coding with Vinay kumar in Techartifact

generate the secure files in weblogic in Webcenter Spaces Applicaton

For security purposes, administrator user name and password details are stored in secure files.Obtain the location of these secure files from your systems administrator or generate the secure files in weblogic.
How to do that. lets find it out.

I am working on portal and spaces application.For deploying spaces application in console ,i need a secure file.Following are the steps to generate.

1. Navigate to: ORACLE_HOME/common/bin in command prompt using CD command.

2. Enter the command:
wlst.sh – for unix.
wlst.cmd – for windows.

3. At the WLST command prompt, connect to the Administration Server for Oracle WebCenter:

connect(‘‘,’‘, ‘:‘) Where admin_user is the user name of the administrator, admin_pwd is the administrator’s password, host is the WebCenter instance, and port is the Administration Server port. This is used for connecting with weblogic using command prompt. i.e

connect(‘weblogic ‘,’welcome1’, ‘192.168.0.93:8888’)

4. Run the following command to create a user configuration file and key file in a specified location:

storeUserConfig(‘‘, ‘) Where userConfigFile is the name and location of the file storing user details and userKeyFile is the name and location of the file storing passwords.

For example: storeUserConfig(‘C:/myFiles/myuserconfigfile.secure’, ‘C:/myFiles/myuserkeyfile.secure’)

you can map the entry of these files in config.properties in spaces application to deploy shared library like below

wls.userkey=C:\\JDeveloper\\mywork\\ps1userkeyfile.secure
wls.userconfig=C:\\JDeveloper\\mywork\\ps1userconfigfile.secure

Thats it. You can find the secure files in specified path.

Happy coding with Vinay in Techartifact…..

Generate id using dbsequence in Oracle ADF Without Losing it .

Requirements: Populate Primary key or ID using dbsequence in Oracle ADF only during database commit

Solution: In my previous post of generating the sequence generating sequence in ADF ,previous post ,But the problem is you will lose sequence at time of error or cancel time.

Following changes to achieve this requirement

1. Go the entity and put a default value as some -999 or -9999 (This cant be your dbsequence value, preferably negative values)

2. Generate EntityImpl.class file and add below lines of code to the file at the end.

Note: Here we are overriding doDML() method of EntityImpl.class so that it will the get the value from db sequence just before commit to database.

    public void doDML(int operation, TransactionEvent e) {
        this.setEmployeeId((Integer)(new SequenceImpl("Vinaytableid_seq",getDBTransaction()).getSequenceNumber()).intValue());
        super.doDML(operation, e);
    }

Note2: it is not a mandatory rule to modify doDML() method only, you can use prepareForDML() method as well if you like. As doDML() is the last method that gets fired before beforeCommit() method, I preferred it.

3. In UI page, users don’t want to see any negative values initially, to show it as blank, just put an ELExpression to hide it whenever the value is negative.i.e
#{bindings.ContractId.inputValue == -1 ? ” : bindings.EmployeeId.inputValue}

Happy coding with techartifact