Oracle Fusion Middleware Tips: 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;
}

Oracle Fusion Middleware Tips

Friday, August 19, 2016

Export to excel Search Result with search query parameter.

No comments:

Post a Comment