将大数据写入 Excel 工作表时出现 Java 堆空间错误、OutofMemory 异常

Question

我在将大量数据从数据库写入 Excel 工作表时遇到 Java 堆空间错误。我不想使用 JVM -XMX 选项来增加内存。

Following are the details:

1) I am using org.apache.poi.hssf api for excel sheet writing.

2) JDK version 1.5

3) Tomcat 6.0

我编写的代码对于大约 23000 条记录运行良好，但对于超过 23000 条记录则失败。

代码如下:

ArrayList l_objAllTBMList= new ArrayList();
    l_objAllTBMList = (ArrayList) m_objFreqCvrgDAO.fetchAllTBMUsers(p_strUserTerritoryId);
    ArrayList l_objDocList = new ArrayList();
    m_objTotalDocDtlsInDVL= new HashMap();
    Object l_objTBMRecord[] = null;
    Object l_objVstdDocRecord[] = null;
    int l_intDocLstSize=0;
    VisitedDoctorsVO l_objVisitedDoctorsVO=null;
    int l_tbmListSize=l_objAllTBMList.size();
    System.out.println(" getMissedDocDtlsList_NSM ");

        for(int i=0; i<l_tbmListSize;i++)
        {
            l_objTBMRecord = (Object[]) l_objAllTBMList.get(i);

            l_objDocList = (ArrayList) m_objGenerateVisitdDocsReportDAO.fetchAllDocDtlsInDVL_NSM((String) l_objTBMRecord[1], p_divCode, (String) l_objTBMRecord[2], p_startDt, p_endDt, p_planType, p_LMSValue, p_CycleId, p_finYrId);
            l_intDocLstSize=l_objDocList.size();
            try {
                    l_objVOFactoryForDoctors = new VOFactory(l_intDocLstSize, VisitedDoctorsVO.class); 

/* Factory class written to create and maintain limited no of Value Objects (VOs)*/

                } catch (ClassNotFoundException ex) {
                    m_objLogger.debug("DEBUG:getMissedDocDtlsList_NSM :Exception:"+ex);
                } catch (InstantiationException ex) {
                    m_objLogger.debug("DEBUG:getMissedDocDtlsList_NSM :Exception:"+ex);
                } catch (IllegalAccessException ex) {
                    m_objLogger.debug("DEBUG:getMissedDocDtlsList_NSM :Exception:"+ex);
                }


                for(int j=0; j<l_intDocLstSize;j++)
                {
                    l_objVstdDocRecord = (Object[]) l_objDocList.get(j);
                    l_objVisitedDoctorsVO = (VisitedDoctorsVO) l_objVOFactoryForDoctors.getVo();
                    if (((String) l_objVstdDocRecord[6]).equalsIgnoreCase("-"))
                    {
                        if (String.valueOf(l_objVstdDocRecord[2]) != "null")
                        {
                            l_objVisitedDoctorsVO.setPotential_score(String.valueOf(l_objVstdDocRecord[2]));
                            l_objVisitedDoctorsVO.setEmpcode((String) l_objTBMRecord[1]);
                            l_objVisitedDoctorsVO.setEmpname((String) l_objTBMRecord[0]);
                            l_objVisitedDoctorsVO.setDoctorid((String) l_objVstdDocRecord[1]);
                            l_objVisitedDoctorsVO.setDr_name((String) l_objVstdDocRecord[4] + " " + (String) l_objVstdDocRecord[5]);
                            l_objVisitedDoctorsVO.setDoctor_potential((String) l_objVstdDocRecord[3]);
                            l_objVisitedDoctorsVO.setSpeciality((String) l_objVstdDocRecord[7]);
                            l_objVisitedDoctorsVO.setActualpractice((String) l_objVstdDocRecord[8]);

                            l_objVisitedDoctorsVO.setLastmet("-");
                            l_objVisitedDoctorsVO.setPreviousmet("-");
                            m_objTotalDocDtlsInDVL.put((String) l_objVstdDocRecord[1], l_objVisitedDoctorsVO);
                        }

                    }

                }// End of While
               writeExcelSheet(); // Pasting this method at the end

            // Clean up code
            l_objVOFactoryForDoctors.resetFactory(); 
            m_objTotalDocDtlsInDVL.clear();// Clear the used map
            l_objDocList=null;
            l_objTBMRecord=null;
            l_objVstdDocRecord=null;

        }// End of While
        l_objAllTBMList=null;
        m_objTotalDocDtlsInDVL=null;

-------------------------------------------------------------------
private void writeExcelSheet() throws IOException
 {
        HSSFRow l_objRow = null;
        HSSFCell l_objCell = null;
        VisitedDoctorsVO l_objVisitedDoctorsVO = null;
        Iterator l_itrDocMap = m_objTotalDocDtlsInDVL.keySet().iterator();
        while (l_itrDocMap.hasNext())
        {
            Object key = l_itrDocMap.next();
            l_objVisitedDoctorsVO = (VisitedDoctorsVO) m_objTotalDocDtlsInDVL.get(key);
            l_objRow = m_objSheet.createRow(m_iRowCount++);

            l_objCell = l_objRow.createCell(0);
            l_objCell.setCellStyle(m_objCellStyle4);
            l_objCell.setCellValue(String.valueOf(l_intSrNo++));

            l_objCell = l_objRow.createCell(1);
            l_objCell.setCellStyle(m_objCellStyle4);
            l_objCell.setCellValue(l_objVisitedDoctorsVO.getEmpname() + " (" + l_objVisitedDoctorsVO.getEmpcode() + ")"); // TBM Name

            l_objCell = l_objRow.createCell(2);
            l_objCell.setCellStyle(m_objCellStyle4);
            l_objCell.setCellValue(l_objVisitedDoctorsVO.getDr_name());// Doc Name

            l_objCell = l_objRow.createCell(3);
            l_objCell.setCellStyle(m_objCellStyle4);
            l_objCell.setCellValue(l_objVisitedDoctorsVO.getPotential_score());// Freq potential score

            l_objCell = l_objRow.createCell(4);
            l_objCell.setCellStyle(m_objCellStyle4);
            l_objCell.setCellValue(l_objVisitedDoctorsVO.getDoctor_potential());// Freq potential score

            l_objCell = l_objRow.createCell(5);
            l_objCell.setCellStyle(m_objCellStyle4);
            l_objCell.setCellValue(l_objVisitedDoctorsVO.getSpeciality());//CP_GP_SPL

            l_objCell = l_objRow.createCell(6);
            l_objCell.setCellStyle(m_objCellStyle4);
            l_objCell.setCellValue(l_objVisitedDoctorsVO.getActualpractice());// Actual practise

            l_objCell = l_objRow.createCell(7);
            l_objCell.setCellStyle(m_objCellStyle4);
            l_objCell.setCellValue(l_objVisitedDoctorsVO.getPreviousmet());// Lastmet

            l_objCell = l_objRow.createCell(8);
            l_objCell.setCellStyle(m_objCellStyle4);
            l_objCell.setCellValue(l_objVisitedDoctorsVO.getLastmet());// Previousmet

        }
        // Write OutPut Stream
        try {
                out = new FileOutputStream(m_objFile);
                outBf = new BufferedOutputStream(out);
                m_objWorkBook.write(outBf);
            } catch (Exception ioe) {
            ioe.printStackTrace();
            System.out.println(" Exception in chunk write");
        } finally {
            if (outBf != null) {
                outBf.flush();
                outBf.close();
                out.close();

                l_objRow=null;
                l_objCell=null;
            }

        }


    }

Answer 1

您不需要在开始写入 Excel 之前将完整列表填充到内存中，而是需要修改代码以使得每个对象在从数据库读取时都写入到文件中。看看这个question了解另一种方法。