使用 poi导大量数据到excel 2007导致内存溢出

v

刚开始的时候，我执行到6万条记录就内存溢出了，我电脑内存完全被占用了。后来通过查资料，使用SXSSFWorkbook 这个类来写excel，并且在内存中记录满100条的时候就往硬盘上输出。这样我电脑内存一直有空余。代码出错在187行，workbook.write(os);这一段。我的数据量并没有达到Integer的最大值。我感觉是在写excel的过程中，有大量的对象被创建，而没有被gc及时清理，而导致内存不足。

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211

package com.hzqy.web.music.stat.poiutil;   import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.util.List;   import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFWorkbook;   import com.hzqy.web.music.stat.refutil.GetValueByRef;   /**  * poi实现excel的生成  *  * @author sd  *  */ public class POIUtil {     private static final Log LOG = LogFactory.getLog(POIUtil.class);       public static int start_index = 0;// 写入序号     public static int start_row = 0;// 从第几列开始写             public static final int RAM_MAX_SIZE = 100;//内存中记录缓存数量       public static final int BUFF_SIZE = 1024;//字节缓冲数量     //TODO 建立样式     public void getStyle(){               }           /**      * 初始化excel      *      * @param fileName      *            excel名称（XXX.xls或者XXX.xlsx）      * @param sheetIndex      *            第几个sheet      * @param title      *            报表标题      * @param colName      *            报表列明      * @param widthArr      *            报表宽度      */     public static void initExcel(String fileName, int sheetIndex, String title,             String[] colName, int[] widthArr) {         if (null == fileName || fileName.equals("")) {             LOG.error("请传入文件名 ");             return;         }         start_index = 0;// 设置为初始值。不然static的index会一直递增         start_row = 0;         int init = 0;         SXSSFWorkbook  workbook = null;         OutputStream os = null;         try {             workbook = new SXSSFWorkbook(RAM_MAX_SIZE);//内存中保留 10000 条数据，以免内存溢出，其余写入 硬盘              CellStyle style = workbook.createCellStyle();             style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中             style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平居中             Font font = workbook.createFont();             font.setFontHeightInPoints((short) 16);//字体大小             // 把字体应用到当前的样式             style.setFont(font);             LOG.debug("工作环境创建成功");             Sheet sheet = workbook.createSheet("sheet" + sheetIndex);// 获得要操作的单个sheet             Row hssrow = sheet.createRow(0);// 创建一行，作为标题             if (null != title && !title.equals("")) {                 sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,                         colName.length - 1));// 合并单元格                 Cell cell = hssrow.createCell(0);// 创建一个单元格                 cell.setCellStyle(style);// 设置样式                 cell.setCellValue(title);// 写入标题                 init++;             }             int col = 0;//  从第几列开始写 //设置单元格宽度             Row tr_row = sheet.createRow(init);             if(colName != null){                 for (col = 0; col < colName.length; col++) {                     sheet.setColumnWidth(col, widthArr[col] * 256);// 以256像素为一单位                     Cell cell = tr_row.createCell(col);// 创建一个单元格                     cell.setCellValue(colName[col]);// 写入标题                 }                 init++;             }             start_row = init;             os = new FileOutputStream(fileName,true);             workbook.write(os);             LOG.debug("基本excel建立成功");         } catch (FileNotFoundException e) {             LOG.error("找不到指定文件：" + fileName);             e.printStackTrace();         } catch (IOException e) {             LOG.debug("io 异常");             e.printStackTrace();         } finally {             if (null != os) {                 try {                     os.close();                 } catch (IOException e) {                     LOG.error("输出流关闭失败");                     e.printStackTrace();                 }             }         }     }       /**      * 将voList 写入 excel      *      * @param sheetIndex      *            第几个表单      * @param objList      *            要插入的数据      * @param fileName      *            操作的excel表      * @param fieldArr      *            bean 字段      */     public static <T> void writeObjListToExcel(int sheetIndex,final List<T> objList,             String fileName, String[] fieldArr) {         if (objList != null && objList.size() > 0) {             SXSSFWorkbook workbook = null;             FileInputStream in = null;             OutputStream os = null;             XSSFWorkbook xssbook = null;             try {                 /**                  * 读取原来写入的文件                  */                 in = new FileInputStream(fileName);                 xssbook = new XSSFWorkbook(in);                 workbook = new SXSSFWorkbook(xssbook,RAM_MAX_SIZE);// 通过已存在的excel获取workbook                 SXSSFSheet  sheet = (SXSSFSheet) workbook.getSheetAt(sheetIndex);// 获取指定的sheet                 /**                  * 写入数据                  */                 for (Object tmp : objList) {                     int col = 0;                     start_index++;                     Row hssrow = sheet.createRow(start_row);                     Cell cellindex = hssrow.createCell(0);// cellindex用来写序号                     cellindex.setCellValue(start_index);// 第一列用来写序号                     col++;                     /**                      * 通过反射取值，并且写入到excel中                      */                     for (int i = 0; i < fieldArr.length; i++) {                         String fieldName = fieldArr[i];                         Object value = GetValueByRef.getValueByRef(tmp,                                 fieldName);                         String str = null;                         if (value == null) {                             str = "";                         } else {                             str = String.valueOf(value);                         }                         Cell cellvalue = hssrow.createCell(col);// cellvalue用来写字段对应的值                         cellvalue.setCellValue(str);                         col++;                     }                     start_row++;                     if(start_row % RAM_MAX_SIZE ==0){//数据到达内存最缓存最大值，写入到zip中                         sheet.flushRows();//全部写入到硬盘                     }                 } //              sheet.flushRows();                 os = new FileOutputStream(fileName); //              System.out.println(os.);                 workbook.write(os);                 LOG.debug("报表写入成功,写入数据 "+start_row+"行");             } catch (IOException e) {                 LOG.debug("io 异常");             } finally {                 if (in != null) {                     try {                         in.close();                     } catch (IOException e) {                         LOG.debug(" 读取文件流异常");                         e.printStackTrace();                     }                 }                 if (os != null) {                     try {                         os.close();                     } catch (IOException e) {                         LOG.error("关闭文件流异常");                         e.printStackTrace();                     }                 }             }           }     }   }

poiutil用到的工具类：GetValueByRef

 

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

package com.hzqy.web.music.stat.refutil;   import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method;   import com.hzqy.web.commons.vo.AreaVo;   public class GetValueByRef {           /**      * 用反射获取 字段的值      * @param srcObj 作用对象      * @param fieldName 字段名称      * @return      */           public static Object getValueByRef(Object srcObj, String fieldName){         Object value = null;         Class objClass = srcObj.getClass();         fieldName =fieldName.replaceFirst(fieldName.substring(0, 1), fieldName.substring(0, 1).toUpperCase());         String getMethodName = "get"+fieldName;         try {             Method method = objClass.getMethod(getMethodName);//第一个参数为调用的方法名。第二个为方法的返回值:类型               value = method.invoke(srcObj);///第一个参数表示要调用的对象，后者为传给这个方法的参数           }  catch (IllegalAccessException e) {             e.printStackTrace();         } catch (IllegalArgumentException e) {             e.printStackTrace();         } catch (InvocationTargetException e) {             e.printStackTrace();         }catch (NoSuchMethodException e) {             e.printStackTrace();         } catch (SecurityException e) {             e.printStackTrace();         }         return value;     } }