Hello Fellas,
I am using Oracle 12.2.1 Jdev,
I am exporting data by using "File Download Action Listener" Component with below code. but it's taking too much time when I have more than 1000 lines of record..
My query is How can I do it quickly for multiple rows.
**** Moderating action (Timo): edited the code into a code block for better readabillity ****
public void excelExport(FacesContext facesContext, OutputStream outputStream) {
try {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet worksheet = workbook.createSheet("Renewal Worksheet");
DCBindingContainer bindings = (DCBindingContainer) BindingContext.getCurrent().getCurrentBindingsEntry();
DCIteratorBinding dcIteratorBindings = bindings.findIteratorBinding("RenewalCrmDueListingHdrView1Iterator");
HSSFRow excelrow = null;
// Get all the rows of a iterator
// oracle.jbo.Row[] rows = dcIteratorBindings.getAllRowsInRange();
oracle.jbo.Row[] rows = dcIteratorBindings.getAllRowsInRange();
int LineNumber = 0;
ViewObject dueListVO = dcIteratorBindings.getViewObject();
RowSetIterator customIter = dueListVO.createRowSetIterator("CustomIterator");
customIter.reset();
while (customIter.hasNext()) {
// for (oracle.jbo.Row row : rows) {
oracle.jbo.Row row = customIter.next();
//print header on first row in excel
if (LineNumber == 0) {
// Create a Font for styling header cells
Font headerFont = workbook.createFont();
headerFont.setBold(true);
// Create a CellStyle with the font
CellStyle headerCellStyle = workbook.createCellStyle();
headerCellStyle.setFont(headerFont);
excelrow = (HSSFRow) worksheet.createRow((short) LineNumber);
short ColumnNumber = 0;
for (String colName : row.getAttributeNames()) {
if (("CcDueListingIdPk".equalsIgnoreCase(colName)) ||
("LastUpdatedBy".equalsIgnoreCase(colName)) || ("UserIdFk".equalsIgnoreCase(colName)) ||
("LastUpdatedDate".equalsIgnoreCase(colName)) || ("EnterDate".equalsIgnoreCase(colName))) {
} else {
HSSFCell cellA1 = excelrow.createCell((short) ColumnNumber);
cellA1.setCellValue(colName.toUpperCase());
cellA1.setCellStyle(headerCellStyle);
ColumnNumber++;
}
}
}
++LineNumber;
short j = 0;
excelrow = worksheet.createRow((short) LineNumber);
for (String colName : row.getAttributeNames()) {
HSSFCell cell = excelrow.createCell(j);
Object CellValue = null;
try {
CellValue = row.getAttribute(colName);
} catch (NullPointerException e) {
CellValue = null;
}
if (("CcDueListingIdPk".equalsIgnoreCase(colName)) || ("LastUpdatedBy".equalsIgnoreCase(colName)) ||
("UserIdFk".equalsIgnoreCase(colName)) || ("LastUpdatedDate".equalsIgnoreCase(colName)) ||
("EnterDate".equalsIgnoreCase(colName))) {
} else {
if (CellValue instanceof String) {
// if (("CommencementDate".equalsIgnoreCase(colName)) ||
// ("DueSince".equalsIgnoreCase(colName)) || ("ReceiptDate".equalsIgnoreCase(colName)) ||
// ("EnterDate".equalsIgnoreCase(colName))) {
//
// SimpleDateFormat formatter = new SimpleDateFormat("dd-MMM-yy");
// java.util.Date date = formatter.parse((String) CellValue);
//
// cell.setCellValue(formatter.format(date));
//
// }
// else {
cell.setCellValue((String) CellValue);
// }
} else if (CellValue instanceof oracle.jbo.domain.Date) {
// if (("CommencementDate".equalsIgnoreCase(colName)) ||
// ("DueSince".equalsIgnoreCase(colName)) || ("ReceiptDate".equalsIgnoreCase(colName)) ||
// ("EnterDate".equalsIgnoreCase(colName))) {
// SimpleDateFormat formatter = new SimpleDateFormat("dd-MMM-yy");
// java.util.Date date = formatter.parse((String) CellValue);
// cell.setCellValue((Date)formatter.format(date));
cell.setCellValue((String) CellValue.toString());
// }
} else if (CellValue instanceof oracle.jbo.domain.Number) {
cell.setCellValue(((oracle.jbo.domain.Number) CellValue).doubleValue());
} else {
try {
cell.setCellValue(CellValue.toString());
} catch (Exception e) {
cell.setCellValue("");
}
}
j++;
}
}
// Resize all columns to fit the content size
for (int l = 0; l < row.getAttributeCount(); l++) {
worksheet.autoSizeColumn(l);
}
// worksheet.createFreezePane(0, 1, 0, 1);
}
customIter.closeRowSetIterator();
workbook.write(outputStream);
outputStream.flush();
} catch (Exception e) {
e.printStackTrace();
}
}
Urgent response would be appreciable.