Export to excel Search Result with search query parameter.

This requirement may come up sometime, but there is not out of box feature in ADF to export result set with search parameters in af:query component. But this can be done using Apache POI  classes.

You need to download apcahe POI jars from apache website

You can follow Vinay's blog to get and idea how to use apache classes to export result set from the af:table component.

You can make a generic/reusable class as put the below method to be used across applications.

############################################################################
JSF code :

<af:commandButton text="ExportToExcel" id="cb2">
                        <af:fileDownloadActionListener contentType="application/vnd.ms-excel"
                                                       filename="ExcelFromPoi.xls" method="#{PoiBean.downloadExcel}"/>
</af:commandButton>

############################################################################


    private String reportName = "Department Search";
    private String[] reportParams;
    private String[] allAttributeNames;
    private String[] attributes;
    private String[] allAttributeLabels;
 
 public static final String DEPARTMENT_REPORT_ATTRIBUTES = "DepartmentId,DepartmentName"; -- This constant i have used because sometimes we need to export only selected columns instead of all attributes from VO


/**
   * downloadExcel is called Export to excel button is pressed
  */
 public void downloadExcel(FacesContext facesContext, OutputStream outputStream) throws IOException {
            this.generateExcel("Department Search report", "Hr Sheet", "DepartmentVO1Iterator", "FirstVCQuery",DEPARTMENT_REPORT_ATTRIBUTES, facesContext, outputStream);
                                         
    }


/**
   * generateExcel is a generic method and is called from the consuming class
   * reportName : Specify Report Name or can be Null
   * workSheetName : Specify Report Name or can be null
   * iteratorName : Name of the iterator from binding section from which data is exported
   * queryCriteriaName : get the criteria name from the binding section, this will be used to get the                                           current applied view criteria.
    * attributeNames : If null will export all attributes from VO, else specify what attributes needs to            be exported
  */
 private void generateExcel(String reportName,String workSheetName,String iteratorName,String queryCriteriaName,String attributeNames,FacesContext facesContext, OutputStream outputStream) throws IOException {
     
            try {

                               
                                  HSSFWorkbook workbook = new HSSFWorkbook();
                                  HSSFSheet worksheet = workbook.createSheet(workSheetName);
                 
                                  DCBindingContainer bc = (DCBindingContainer)BindingContext.getCurrent().getCurrentBindingsEntry();
                                  DCIteratorBinding dcIteratorBindings = bc.findIteratorBinding(iteratorName);
                                  ViewObjectImpl vo = (ViewObjectImpl)dcIteratorBindings.getViewObject();
         
                                  Object execBinding = bc.findExecutableBinding(queryCriteriaName);
                                  String appliedCriteria = JUSearchBindingCustomizer.getCriteriaName((DCBindingContainer)execBinding);
                               
                                  ViewCriteria appliedVC = vo.getViewCriteriaManager().getViewCriteria(appliedCriteria);
                               
                                  boolean headerStamped = false;
             

                                  HSSFRow  excelrow = null;
                                  HSSFCellStyle boldStyle = workbook.createCellStyle();
                                  HSSFFont boldFont = workbook.createFont();
           
                                  boldFont.setColor(HSSFColor.BLACK.index);
                                  boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                                  boldFont.setFontHeightInPoints((short)11);
                                  boldStyle.setFont(boldFont);
                                  boldStyle.setFillBackgroundColor(HSSFColor.CORNFLOWER_BLUE.index);
             
                                          // Get all the rows of a iterator as we need all rows to be exported
                                          dcIteratorBindings.setRangeSize(-1);
                                          oracle.jbo.Row[] rows = dcIteratorBindings.getAllRowsInRange();
                                       
                                          int i = 0;
                                      if (reportName != null) {
                                          excelrow = (HSSFRow)worksheet.createRow(i);
                                          excelrow.createCell(0).setCellValue(reportName);
                                          excelrow.getCell(0).setCellStyle(boldStyle);
                                          i = i + 2;
                                      }
                             //Collects all view criteria items
                                          Map criteriaValues = getAppliedVCItems(appliedVC);
                                          System.out.println("map size:"+criteriaValues.size());
                                      if (criteriaValues.size() > 0){
                                          Iterator it = criteriaValues.entrySet().iterator();
                                          int itrIndx = 0;
                                          while (it.hasNext()) {
                                                 excelrow = worksheet.createRow(i+itrIndx);
                                                 Map.Entry pair = (Map.Entry)it.next();
                                                 System.out.println(pair.getKey() + " = " + pair.getValue());
                                                 excelrow.createCell(0).setCellValue(pair.getKey() + " : " + pair.getValue());
                                                 it.remove(); // avoids a ConcurrentModificationException
                                                 itrIndx = itrIndx + 1;
                                             }
             
                                              i   = i +  itrIndx + 2;
                                          }
             
                                     
                                              for (oracle.jbo.Row row : rows) {
                                                 
                                                  //print header on first row in excel
                                                  if (!headerStamped) {
                                                   
                                                      excelrow = (HSSFRow)worksheet.createRow((short)i);
                                                   
                                                      if (attributeNames != null){
                                                             // attributes = commaSeparatedToStringArray(attributeNames);
                                                              allAttributeNames = commaSeparatedToStringArray(attributeNames);
                                                              AttributeDef[] attrSupplied = dcIteratorBindings.getAttributeDefs(allAttributeNames);
                                                              for (int j = 0; j < attrSupplied.length; j++) {
                                                                  HSSFCell cellA1 = excelrow.createCell((short) j);
                                                                  AttributeHints hints = attrSupplied[j].getUIHelper();
                                                                  String label = hints.getLabel(getLocaleContext());
                                                                  cellA1.setCellValue(label);
                                                                  cellA1.setCellStyle(boldStyle);
                                                              }
                                                           
                                                      }else{
                                                           
                                                              AttributeDef[] attr = dcIteratorBindings.getAttributeDefs();                                              
                                                              for (int j = 0; j < attr.length; j++) {
                                                                  worksheet.setColumnWidth(j, 5000);
               
                                                                  byte attrKind = attr[j].getAttributeKind();
                                                                      HSSFCell cellA1 = excelrow.createCell((short) j);
                                                                      AttributeHints hints = attr[j].getUIHelper();
                                                                      String label = hints.getLabel(getLocaleContext());
                                                                      System.out.println("sanjeeb :"+label);
                                                                      if (attrKind != AttributeDef.ATTR_ASSOCIATED_ROW && attrKind != AttributeDef.ATTR_ASSOCIATED_ROWITERATOR
                                                                          && attrKind != AttributeDef.ATTR_ROWSET) {
                                                                          cellA1.setCellValue(label);
                                                                          cellA1.setCellStyle(boldStyle);
                                                                      }
                                                             
                                                               }
                                                      }
                                                      headerStamped= true;
                                                  }

                                                  //print data from second row in excel
                                                  ++i;
            //
                                                  excelrow = worksheet.createRow((short)i);
                                               
                                                  if (attributeNames != null){
                                                         allAttributeNames = commaSeparatedToStringArray(attributeNames);
                                                         for (int k = 0; k < allAttributeNames.length; k++) {
                                                             HSSFCell  datacell = excelrow.createCell(k);
                                                             Object value = row.getAttribute(allAttributeNames[k]);
                                                               if(value != null){
                                                                 
                                                                    setConvertedCellValue(workbook, datacell, value);
                                                         
                                                                }
                                                         }
                                                       
                                                  }else{
                                                      AttributeDef[] attr = dcIteratorBindings.getAttributeDefs();
                                                   
                                                      for (int k = 0; k < attr.length; k++) {
                                                         
                                                          byte attrKind = attr[k].getAttributeKind();
                                                             HSSFCell  datacell = excelrow.createCell(k);
                                                           
                                                             if (attrKind != AttributeDef.ATTR_ASSOCIATED_ROW && attrKind != AttributeDef.ATTR_ASSOCIATED_ROWITERATOR
                                                                 && attrKind != AttributeDef.ATTR_ROWSET) {
                                                                  Object value = row.getAttribute(attr[k].getName());
                                                                    if(value != null){
                                                                       
                                                                         setConvertedCellValue(workbook, datacell, value);
                //                                                       datacell.setCellValue( row.getAttribute(attr[k].getName()).toString());
                                                                       
                                                                     }
                                                                }
                                                        }
                                                  }
                                                    worksheet.createFreezePane(0, 1, 0, 1);
                                              }
                            workbook.write(outputStream);
                            outputStream.flush();
             
            }
            catch (Exception e) {
            e.printStackTrace();
            }
     
        }




    private String[] commaSeparatedToStringArray(String commaString) {
        String[] splittArray = null;
        if (commaString != null || !commaString.equalsIgnoreCase("")) {
            splittArray = commaString.split(",");
        }
        return splittArray;
    }
     
//thsi is for value conversion
    private void setConvertedCellValue(HSSFWorkbook wb, HSSFCell cell,
                                       Object value) {
         if (value instanceof oracle.jbo.domain.Number) {
            oracle.jbo.domain.Number number = (oracle.jbo.domain.Number)value;
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            // cell.setCellValue((Double)number.getValue());
             HSSFCellStyle cellStyle = wb.createCellStyle();
            cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("(#,##0.00_);[Red](#,##0.00)"));
            cell.setCellStyle(cellStyle);
            cell.setCellValue(number.getValue());
        }
        else if (value instanceof Date) {
            Date adfdate = (Date)value;
            java.util.Date date = adfdate.getValue();
            HSSFCellStyle cellStyle = wb.createCellStyle();
            cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cell.setCellValue((java.util.Date)date);
            cell.setCellStyle(cellStyle);
        }
        else if (value instanceof Integer) {
            Integer integerVal = (Integer)value;
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            // cell.setCellValue((Double)number.getValue());
             HSSFCellStyle cellStyle = wb.createCellStyle();
//            cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("(#,##0.00_);[Red](#,##0.00)"));
            cell.setCellStyle(cellStyle);
            cell.setCellValue(integerVal);
        }
        else if (value instanceof String) {
            String string = (String)value;
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue((String)string);
        }
    }


    private Map getAppliedVCItems(ViewCriteria vc) {
        Map<String, Object> fieldValuePair = new HashMap<String, Object>();

        List list = vc.getRows();
        Iterator iter = list.iterator();

        while (iter.hasNext()) {
          ViewCriteriaRowImpl row = (ViewCriteriaRowImpl)iter.next();
          ViewCriteriaItem[] vcitems = row.getCriteriaItemArray();
          for (ViewCriteriaItem vcitem : vcitems) {
            if (vcitem != null) {
                vcitem.getAccessorVO(vcitem.getViewCriteria().getViewObject());

              if (vcitem.getValueCount() > 1) {
                String appendedValue = null;
                ArrayList<Object> codes = new ArrayList<Object>();
                int codeIdx = 0;

                ArrayList<ViewCriteriaItemValue> values = vcitem.getValues();
                for (ViewCriteriaItemValue vcItemValue : values) {
                  System.out.println(" [[ Multi select value :" + vcItemValue.getValue() + " ]]");
                 
                    codes.add(vcItemValue.getValue());
                    codeIdx ++;
//                    appendedValue = (appendedValue == null? vcItemValue.getValue().toString() :  ( appendedValue + " | " + vcItemValue.getValue())  );
                }
//                  ArrayList arrayListSales = getCodeDescriptions(codes);
//                  fieldValuePair.put(vcitem.getLabel(getLocaleContext()), appendedValue);
                  fieldValuePair.put(vcitem.getLabel(getLocaleContext()), getCodeDescriptions(codes).toString());
              } else if ((vcitem.getValue()) instanceof ViewCriteria) {
                System.out.println("<Nested VC is found...>");
                //Call recursively
                //getAppliedVCItems((ViewCriteria)vcitem.getValue());
              }else{
                   System.out.println("[ VC Item :" + vcitem.getLabel(getLocaleContext()) + " Value: " + vcitem.getValue() + " ]");
                   fieldValuePair.put(vcitem.getLabel(getLocaleContext()), vcitem.getValue());
               }
            }
          }
        }
        return fieldValuePair;
      }
   
          private LocaleContext getLocaleContext() {
              Locale locale =
                  FacesContext.getCurrentInstance().getViewRoot().getLocale();
              LocaleContext lc = new DefLocaleContext(locale);
              return lc;
          }


//Incase of Multi select, we get the code value ,which may not be informative. So need to display the //description. E.g. DepartmentId LOV will display 10,20,30 . So in order to display the description we need to bind the department LOV to the page.


    public ArrayList<Object> getCodeDescriptions(ArrayList<Object> codes) {
     // Add event code here...
        ArrayList<Object> DescriptionList = new ArrayList<Object>();

        DCBindingContainer bc = (DCBindingContainer)BindingContext.getCurrent().getCurrentBindingsEntry();
        DCIteratorBinding dcIteratorBindings = bc.findIteratorBinding("EmpLOV1Iterator");
        ViewObjectImpl lov = (ViewObjectImpl)dcIteratorBindings.getViewObject();
     
        ViewCriteria vc =  lov.createViewCriteria();
        ViewCriteriaRow vcr =  vc.createViewCriteriaRow();
        ViewCriteriaItem vci =vcr.ensureCriteriaItem("EmployeeId");
        vci.setOperator(JboCompOper.OPER_IN);
   
     
        for (int i = 0; i < codes.size(); i++) {
            vci.setValue(i, codes.get(i));
        }
     

        vc.addRow(vcr);
        lov.applyViewCriteria(vc);
        System.out.println(lov.getQuery());
        System.out.println(Arrays.toString(lov.getNamedWhereClauseParams().getAttributeValues()));
        lov.executeQuery();
     
        RowSetIterator iterator = lov.createRowSetIterator(null);
           
        iterator.reset();

               while (iterator.hasNext()) {
                 Row row = iterator.next();
//                  fieldValuePair.put((String)row.getAttribute("EmployeeId"), row.getAttribute("FirstName"));
                  DescriptionList.add((String)row.getAttribute("FirstName"));
             
              }

        iterator.closeRowSetIterator();
        return DescriptionList;
    }

MDS : Save Result Layout issue in adf

Probably everyone is aware of MDS(Meta data service) feature provide by ADF which is kind of a black box to developers. Most of the things are out of box and configuration driven , hardly developers need to code to get along with personalization . While the MDS feature works fine in most of the cases but there is a glitch which i encountered recently. 

This post only focuses on the issue with "Save Result Layout" feature provided in ADF query component when MDS is enabled and does not cover MDS and Security configurations which are essentials for runtime personalizationof ADF pages


ADF query component with MDS works great for saving the search results with search criteria, but it fails to persist the results layout.

Steps to reproduce:
1) Enter a search criteria and click on search
2) Hide a column(sps Dept Name) or Move a column on the results table from one position to another
3) Select the Save Results Layout checkbox while saving the search results with a name sps. "Dept Search : DeptName hidden".
4) Switch back to any other saved searches and you will see the changes which you made in step 2 is applied to all saved searches.

The above statement(Save Results Layout) is little confusing as the layout save happens on all the saved searches and not specific to any saved searches selected by user.

With much of a trying i could not hack any configurations or code anything to make it work but after spending much of time, played around with "UI Shell template" and to my surprise it worked.

So, finally i modified the UI Shell Template with just a center facet and used it as a jar in my consuming application. My consuming application has a page which inherits this template and in the page we can drag our taskflows or any query component directly to test MDS personalization of saved searches with layout.

Also make sure persistent change manager in adf-config file is changed to MDSDocumentChangeManager as below :
<persistent-change-manager-class>oracle.adf.view.rich.change.MDSDocumentChangeManager</persistent-change-manager-class>

Handle Multibyte characters(UTF-8) in Excel Upload - Plsql export to excel

I came across a PL/sql piece of code which exports data to Excel by generating HTML, but the export to Excel was not supporting multibyte charaters like Chinese, Portuguese ,Turkish, Polish, Scandinavian characters. When it encounters any mutilbyte character it use to put some junk characters in the Excel report.
So here i will point out how to generate multibyte or UTF-8 characters in excel report.

 Ideally, the notation to generate HTML from pl/sql code is done by "htp.p" statements show as below:

sample code:
htp.p('<table BORDER="1" CELLSPACING="0" CELLPADDING="0" width=50%>
            <tr><td><b>From:</b></td><td>ABC Financing</td></tr>
            <tr><td><b>To:</b></td><td>Cash Apps</td></tr>
            <tr><td><b>Date:</b></td><td align="left">'||trunc(sysdate)||'</td></tr>
            </table><br>');
    htp.p('<table  width=80% CELLSPACING="0" CELLPADDING="4" border=1>


You can use the Oracle supplied custom package to generate Excel file by calling
OWA_UTIL.MIME_HEADER('application/vnd.ms-excel');
Subsequently you can print whatever data you want your exported excel to hold.

Here is the catch!!! It exports data to excel report but fails to export multibyte characters. So to support this we need to add few lines of code which will form the HTML header.

Below procedure adds the header to HTML generated :

c_utf_charset         VARCHAR2(200) := '
  <META http-equiv=Content-Type content="text/html; charset=UTF-8">';

PROCEDURE headerExcel(in_title    VARCHAR2 DEFAULT c_title)
IS
BEGIN
htp.p('
<HTML>
<HEAD>
  <TITLE>' || in_title || '</TITLE>' || c_utf_charset );
END headXL;

Call this procedure just after :
OWA_UTIL.MIME_HEADER('application/vnd.ms-excel');
headerExcel('Sample Text')

Resetting ADF table sorting

ADF table sort behaves weird in some cases, like once you do a sort on any column and if you revisit the same screen, the table retains the sorting column in memory for all subsequent querries. If at all we forcefully re-execute the VO then also it retains the sorting.

So to remove sorting execute this piece of code :

    public void resetSort()

        {

            

            SortCriteria[] sc = new SortCriteria[0];

            DCBindingContainer iteratorbinding = this.getBindingContainer();

            if(iteratorbinding != null){

            DCIteratorBinding iter =

                iteratorbinding.findIteratorBinding("SampleVO1Iterator");

            

            iter.applySortCriteria(sc);

            

            }

        }

By doing this you do not need to re-execute the VO also.

Adding Multiple LOV's to a single ViewObject attribute

There is a requirement like i need to show different LOV(different LOV i mean LOV deriving data from different sources) defined  on one attribute based on the value selected from another attribute.

Requirement :

 Implementation:

I have a VO called SearchPanelVO in which i have 2 attributes called CriteriaAttr and CriteriaVal.
On CriteriaAttr  attribute i have defined a drop down from a static list as shown below :

So based on the value selected from this static list i need to invoke different LOVs defined on CriteriaVal attribute which means if i select "AIT #" i should show AitNameLOV, if i select "App Manager #" i should show AppManagerContactLOV and similarly AppMgmtContactLOV  and so on.

Open the SearchPanelVO , and in order to switch LOVs I defined new transient attribute LovSwitcher.(shown in snapshot below) The value of this attribute is a groovy expression which when evaluated should give proper LOV's name. Then i defined 5 LOVs on CriteriaVal attribute as shown in snapshot below.

 The groovy expression on the value property of the new transient attribute is like :

On the page add partialtrigger on the CriterVal attribute so that it can refresh based on the value selected in CriterAttr drop down.

Next you need to add a small piece of code which will clear the value selected from any LOV on the CriterVal attribute on changing value in CriteriaAttr attribute. Implement a ValueChangeListener on CriteriaAttr attribute which will clear the value from CriteriaVal attribute.

Done !!!

Import data from a CSV file into View Object

There is a use case where in you need to import data from a CSV file into a View Object, Here i will explain a use case where in i will be importing the attendance for a particular student.
The CSV file will be in a format as below(first line will be header info and subsequent lines will have attendance data)
SSN,Name,MonHrs,TueHrs,WedHrs,ThuHrs,FriHrs,SatHrs,SunHrs
1234-12-123,Sanjeeb,8,8,8,8,8,8,8 
4567-56-987,XYZ,7,8,5,8,8,7,8

In my page i  have dropped a upload component i.e. <af:inputFile/> which will allow me to select a file from my system and import the data into View object. To achieve the same i have implemented a valueChangeListener method in a managed bean for this component . The code below are self explanatory as i have included comments to understand better.


    public void fileUploaded(ValueChangeEvent valueChangeEvent) {
        //getting the file instance from the event.
        UploadedFile file = (UploadedFile)valueChangeEvent.getNewValue();
         try {
          //calling a private method to parse the CSV file and import the data into table component
           parseFile(file.getInputStream());
          //Refresh the table component programmatically, importTab is the binding of the table component
           AdfFacesContext.getCurrentInstance().addPartialTarget(importTab);
         } catch (IOException e) {
           // TODO : Handle your exception

         }
    }



    private 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)impTab.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("Ssn", theToken);
                        case 2:
                            rw.setAttribute("Firstname", theToken);
                        case 3:
                                rw.setAttribute("Monhrs", theToken);
                        case 4:
                                rw.setAttribute("Tuehrs", theToken);
                        case 5:
                                rw.setAttribute("Wedhrs", theToken);
                        case 6:
                                rw.setAttribute("Thrhrs", theToken);
                        case 7:
                                rw.setAttribute("Frihrs", theToken);
                        case 8:
                                rw.setAttribute("Sathrs", theToken);
                        case 9:
                                rw.setAttribute("Sunhrs", theToken);
                     
                        }
                    }
                }
                //reset token number
                tokenNumber = 0;
            }
        } catch (IOException e) {
            // TODO add more
            FacesContext fctx = FacesContext.getCurrentInstance();
            fctx.addMessage(impTab.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()));
        }
    }

Hope it helps !!!

Enable FTP for Mac OS X Lion

I was just wondering where is the FTP option for Mac OS X Lion. It used to be in System Preferences -> Sharing but with OS X Lion, it looks like the FTP option is removed but it is not!

To enable FTP, you need to run the following in the terminal
sudo -s launchctl load -w /System/Library/LaunchDaemons/ftp.plist

To disable,
sudo -s launchctl unload -w /System/Library/LaunchDaemons/ftp.plist