Skip to Main Content
Forums

Java Development Tools

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Export to Excel using Apache POI too slow:

Mohit KumarAug 10 2017 — edited Sep 1 2017

Hi,

I am using Apache POI to export to excel however it's taking too long to write few rows.

Last time I tried to export 6500 rows and got the exception stuckthreadmaxtime.

So i increased the max time from 600 to 1200 but I still fill if there will be more data in table I will again face the same issue.

Hence I am putting my code here and will need you guys to analyze it whether something is wrong with the code.

Requirement is like whatever you see on the table on Page only those columns needs to be exported.

 <af:commandButton text="Export" id="cb5" immediate="true">
                        <af:fileDownloadActionListener contentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                                                       filename="ABIResultsPOI.xlsx"
                                                       method="#{abiManagedBean.test}"/>
 </af:commandButton>

public void test(FacesContext facesContext, OutputStream outputStream) {
    try {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("ABI Sheet");
        DCIteratorBinding dcIter =
            ADFUtils.findIterator("TblAssyStartsAbiVO1Iterator");
        ViewObject vo = dcIter.getViewObject();
        RichColumn col = null;
        List<UIComponent> colNames = getAssyTable().getChildren();
        XSSFRow excelRow = null;
        long count = 0L;
        int i = 0;
        System.out.println(new Timestamp(System.currentTimeMillis()));
        CellStyle cellStyle = workbook.createCellStyle();
        CreationHelper createHelper =
            workbook.getCreationHelper();
        cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy"));
        
        CellStyle cellStyle1 = workbook.createCellStyle();
        CreationHelper createHelper1 =
            workbook.getCreationHelper();
        cellStyle1.setDataFormat(createHelper1.createDataFormat().getFormat("dd/MM/yyyy HH:mm:ss"));
        vo.reset();
        while (vo.hasNext()) {
            Row row = vo.next();
            System.out.println(count);
            //Print Header
            if (i == 0) {
                excelRow = sheet.createRow(i);
                int j = 0;
                for (int k = 0; k < colNames.size(); k++) {
                    col = (RichColumn)colNames.get(k);
                    if (col.isVisible() && col.isRendered()) {
                        XSSFCell cellA1 = excelRow.createCell(j);
                        cellA1.setCellValue(col.getHeaderText());
                        j++;
                    }
                }
            }
            //Print Data
            ++i;
            int j = 0;
            excelRow = sheet.createRow(i);
            XSSFCell cell = null;
            if (((RichColumn)colNames.get(0)).isVisible()) {
                cell = excelRow.createCell(j);
                Number abiId = (Number)row.getAttribute("AbiId");
                double dubAbiId = abiId.doubleValue();
                cell.setCellValue(dubAbiId);
                j++;
            }
            if (((RichColumn)colNames.get(1)).isVisible()) {
                cell = excelRow.createCell(j);
                if (row.getAttribute("MaplStartDate") != null) {
                    oracle.jbo.domain.Date time =
                        (oracle.jbo.domain.Date)row.getAttribute("MaplStartDate");
                    cell.setCellValue(time.getValue());
                    cell.setCellStyle(cellStyle);
                    j++;
                } else
                    j++;
            }
            if (((RichColumn)colNames.get(2)).isVisible()) {
                cell = excelRow.createCell(j);
                if (row.getAttribute("MaplOutDate") != null) {
                    Timestamp time =
                        (Timestamp)row.getAttribute("MaplOutDate");
                    cell.setCellValue(time);
                    cell.setCellStyle(cellStyle1);
                    j++;
                } else
                    j++;
            }
            if (((RichColumn)colNames.get(3)).isVisible()) {
                cell = excelRow.createCell(j);
                if (row.getAttribute("AssemblyItem") != null) {
                    cell.setCellValue(row.getAttribute("AssemblyItem").toString());
                    j++;
                } else
                    j++;
            }
            if (((RichColumn)colNames.get(4)).isVisible()) {
                cell = excelRow.createCell(j);
                if (row.getAttribute("MaplAssySite") != null) {
                    cell.setCellValue(row.getAttribute("MaplAssySite").toString());
                    j++;
                } else
                    j++;
            }
            if (((RichColumn)colNames.get(5)).isVisible()) {
                cell = excelRow.createCell(j);
                if (row.getAttribute("OrigMaplStartQty") != null) {
                    Number abiId =
                        (Number)row.getAttribute("OrigMaplStartQty");
                    double dubAbiId = abiId.doubleValue();
                    cell.setCellValue(dubAbiId);
                    j++;
                } else
                    j++;
            }
            if (((RichColumn)colNames.get(6)).isVisible()) {
                cell = excelRow.createCell(j);
                if (row.getAttribute("MaplStartQty") != null) {
                    Number abiId =
                        (Number)row.getAttribute("MaplStartQty");
                    double dubAbiId = abiId.doubleValue();
                    cell.setCellValue(dubAbiId);
                    j++;
                } else
                    j++;
            }
            if (((RichColumn)colNames.get(7)).isVisible()) {
                cell = excelRow.createCell(j);
                if (row.getAttribute("BacklogDie") != null) {
                    Number abiId = (Number)row.getAttribute("BacklogDie");
                    double dubAbiId = abiId.doubleValue();
                    cell.setCellValue(dubAbiId);
                    j++;
                } else
                    j++;
            }
            if (((RichColumn)colNames.get(8)).isVisible()) {
                cell = excelRow.createCell(j);
                if (row.getAttribute("BacklogUpstream") != null) {
                    Number abiId =
                        (Number)row.getAttribute("BacklogUpstream");
                    double dubAbiId = abiId.doubleValue();
                    cell.setCellValue(dubAbiId);
                    j++;
                } else
                    j++;
            }
            if (((RichColumn)colNames.get(9)).isVisible()) {
                cell = excelRow.createCell(j);
                if (row.getAttribute("NettedForecastDie") != null) {
                    Number abiId =
                        (Number)row.getAttribute("NettedForecastDie");
                    double dubAbiId = abiId.doubleValue();
                    cell.setCellValue(dubAbiId);
                    j++;
                } else
                    j++;
            }
            if (((RichColumn)colNames.get(10)).isVisible()) {
                cell = excelRow.createCell(j);
                if (row.getAttribute("NettedForecastUpstream") != null) {
                    Number abiId =
                        (Number)row.getAttribute("NettedForecastUpstream");
                    double dubAbiId = abiId.doubleValue();
                    cell.setCellValue(dubAbiId);
                    j++;
                } else
                    j++;
            }
            if (((RichColumn)colNames.get(11)).isVisible()) {
                cell = excelRow.createCell(j);
                if (row.getAttribute("WaferQty") != null) {
                    Number abiId = (Number)row.getAttribute("WaferQty");
                    double dubAbiId = abiId.doubleValue();
                    cell.setCellValue(dubAbiId);
                    j++;
                } else
                    j++;
            }
            if (((RichColumn)colNames.get(12)).isVisible()) {
                cell = excelRow.createCell(j);
                if (row.getAttribute("Gdpw") != null) {
                    Number abiId = (Number)row.getAttribute("Gdpw");
                    double dubAbiId = abiId.doubleValue();
                    cell.setCellValue(dubAbiId);
                    j++;
                } else
                    j++;
            }
            if (((RichColumn)colNames.get(13)).isVisible()) {
                cell = excelRow.createCell(j);
                if (row.getAttribute("MaplTestSite") != null) {
                    cell.setCellValue(row.getAttribute("MaplTestSite").toString());
                    j++;
                } else
                    j++;
            }
            if (((RichColumn)colNames.get(14)).isVisible()) {
                cell = excelRow.createCell(j);
                if (row.getAttribute("ReleaseToSubcon") != null) {
                    cell.setCellValue(row.getAttribute("ReleaseToSubcon").toString());
                    j++;
                } else
                    j++;
            }
            if (((RichColumn)colNames.get(15)).isVisible()) {
                cell = excelRow.createCell(j);
                if (row.getAttribute("SpecialInstructions") != null) {
                    cell.setCellValue(row.getAttribute("SpecialInstructions").toString());
                    j++;
                } else
                    j++;
            }
            if (((RichColumn)colNames.get(16)).isVisible()) {
                cell = excelRow.createCell(j);
                if (row.getAttribute("InternalComments") != null) {
                    cell.setCellValue(row.getAttribute("InternalComments").toString());
                    j++;
                } else
                    j++;
            }
            if (((RichColumn)colNames.get(17)).isVisible()) {
                cell = excelRow.createCell(j);
                if (row.getAttribute("ChangeCategory") != null) {
                    cell.setCellValue(row.getAttribute("ChangeCategory").toString());
                    j++;
                } else
                    j++;
            }
            if (((RichColumn)colNames.get(18)).isVisible()) {
                cell = excelRow.createCell(j);
                if (row.getAttribute("ChangeCancelFlag") != null) {
                    cell.setCellValue(row.getAttribute("ChangeCancelFlag").toString());
                    j++;
                } else
                    j++;
            }
            if (((RichColumn)colNames.get(19)).isVisible()) {
                cell = excelRow.createCell(j);
                if (row.getAttribute("LastUpdateDate") != null) {
                    Timestamp time =
                        (Timestamp)row.getAttribute("LastUpdateDate");
                    cell.setCellValue(time);
                    cell.setCellStyle(cellStyle1);
                    j++;
                } else
                    j++;
            }
            if (((RichColumn)colNames.get(20)).isVisible()) {
                cell = excelRow.createCell(j);
                if (row.getAttribute("AbiStatusDesc") != null) {
                    cell.setCellValue(row.getAttribute("AbiStatusDesc").toString());
                    j++;
                } else
                    j++;
            }
            if (((RichColumn)colNames.get(21)).isVisible()) {
                cell = excelRow.createCell(j);
                if (row.getAttribute("UserName") != null) {
                    cell.setCellValue(row.getAttribute("UserName").toString());
                    j++;
                } else
                    j++;
            }
            count++;
        }
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
        System.out.println(new Timestamp(System.currentTimeMillis()));
    } catch (Exception e) {
        e.printStackTrace();
    }
}
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 29 2017
Added on Aug 10 2017
#application-development-in-java, #jdeveloper-and-adf
10 comments
2,864 views