Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 238 Big Data Appliance
- 1.9K Data Science
- 450.2K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 436 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
How to Export data in Excel (FAST)

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.
Answers
-
Timo Hahn Senior Principal Technical Consultant - Oracle ACE Director Member, Moderator Posts: 38,433 Red Diamond
User, I don't know, but it looks like you have copied some code without understanding it or howand for what purpost it is used.
Some parts of the code are commented out. Why?
If you don't need them remove them.
Put the code in a code block would help us to read the code.
From what I understand, you are creating an exel file. This will take some time, Have you tried an exportToExcel listener directly?
Check if this works faster then the code you copied.
If you need a real excel file, you should start analyse where the code is consuming the most time. -this part can then be optimized.
Back to the code: It reads teh rows multiple times using only one row later for pocessing.
Timo
-
Some parts are commented because logic changed by user,
I know what I have written and copied, and everyone do same and apply it by there own way
No I didnt try exportToExcel directly,
-
Timo Hahn Senior Principal Technical Consultant - Oracle ACE Director Member, Moderator Posts: 38,433 Red Diamond
User, I edited your original post to make the code readable. If you don't use some of the code, you should remove it. Otherwise it's hard to understand.
USE A CODE BLOCK FOR CODE, PLEASE!
Go other the code gain, and comment each block (at least) to describe what you try to do there.
Analyze where the time is spent. Optimizations don't make sense if you don't know where the time is spent.
Then it would help if you defined what you mean by 'fast.' I'm a fast runner, but without anything to compare 'fast' to, it doesn't mean anything.
Timo