java 导出Excel 大数据量（一）

 

分析导出实现代码，XLSX支持：

Java代码

/**

     * 生成<span style="white-space: normal; background-color: rgb(255, 255, 255);">XLSX</span>,2007版本的excel,每个sheet无6.5W的行数限制，但是到了一定数量，可能内存溢出，

     * 次方法适合在预计10W以下的数据导出时使用,本机测试，14W可以导出。列数量在8列左右

     *

     * @param fileOut

     *            输出流

     * @param sheetMap

     *            要设置的数据信息

     * @throws SQLException

     */ 

    public static void createXSLXByResultSet(OutputStream fileOut, WriteXLSBean... beans) 

            throws SQLException { 

        try { 

            //重点 Workbook 

            Workbook wb = new XSSFWorkbook(); 

            for (int i = 0, len = beans.length; i < len; i++) { 

                WriteXLSBean xlsBean = beans[i]; 

                Sheet sheet = wb.createSheet(xlsBean.getSheetName()); 

                ResultSet rs = xlsBean.getRs(); 

                ResultSetMetaData rsmd = rs.getMetaData(); 

                TypeHandlerRegistry tr = BeanContext.tr; 

                Map<String, String> th = xlsBean.getTh(); 

                int index = 0; 

                while (rs.next()) { 

                    long t1 = System.currentTimeMillis(); 

                    org.apache.poi.ss.usermodel.Row row = sheet 

                            .createRow(index); 

                    for (int j = 0, numberOfColumns = rsmd.getColumnCount(); j < numberOfColumns; j++) { 

                        String key = rsmd.getColumnLabel(j + 1).toLowerCase(); 

                        if (th.containsKey(key)) { 

                            TypeHandler<?> type = tr.getTypeHandler(JdbcType 

                                    .forCode(rsmd.getColumnType(j + 1))); 

                            Object obj = type.getResult(rs, key); 

                            row.createCell(j).setCellValue(obj == null ? "" 

                                    : obj.toString()); 

                        } 

                    } 

                    System.out.println(index + " :"                             + (System.currentTimeMillis() - t1)); 

                    index++; 

                } 

            } 

            //重点 Workbook 

            wb.write(fileOut); 

        } catch (IOException e) { 

            e.printStackTrace(); 

            throw new ServiceRunTimeException("生产xls文档错误", e); 

        } finally { 

 

        } 

    } 

<span style="font-size:18px;">/**
	 * 生成<span style="white-space: normal; background-color: rgb(255, 255, 255);">XLSX</span>,2007版本的excel,每个sheet无6.5W的行数限制，但是到了一定数量，可能内存溢出，
	 * 次方法适合在预计10W以下的数据导出时使用,本机测试，14W可以导出。列数量在8列左右
	 * 
	 * @param fileOut
	 *            输出流
	 * @param sheetMap
	 *            要设置的数据信息
	 * @throws SQLException
	 */
	public static void createXSLXByResultSet(OutputStream fileOut, WriteXLSBean... beans)
			throws SQLException {
		try {
			//重点 Workbook
			Workbook wb = new XSSFWorkbook();
			for (int i = 0, len = beans.length; i < len; i++) {
				WriteXLSBean xlsBean = beans[i];
				Sheet sheet = wb.createSheet(xlsBean.getSheetName());
				ResultSet rs = xlsBean.getRs();
				ResultSetMetaData rsmd = rs.getMetaData();
				TypeHandlerRegistry tr = BeanContext.tr;
				Map<String, String> th = xlsBean.getTh();
				int index = 0;
				while (rs.next()) {
					long t1 = System.currentTimeMillis();
					org.apache.poi.ss.usermodel.Row row = sheet
							.createRow(index);
					for (int j = 0, numberOfColumns = rsmd.getColumnCount(); j < numberOfColumns; j++) {
						String key = rsmd.getColumnLabel(j + 1).toLowerCase();
						if (th.containsKey(key)) {
							TypeHandler<?> type = tr.getTypeHandler(JdbcType
									.forCode(rsmd.getColumnType(j + 1)));
							Object obj = type.getResult(rs, key);
							row.createCell(j).setCellValue(obj == null ? ""
									: obj.toString());
						}
					}
					System.out.println(index + " :"
							+ (System.currentTimeMillis() - t1));
					index++;
				}
			}
			//重点 Workbook
			wb.write(fileOut);
		} catch (IOException e) {
			e.printStackTrace();
			throw new ServiceRunTimeException("生产xls文档错误", e);
		} finally {

		}
	}</span>

在上面 标注了重点的两处，分别是：

1.构建一个Excel对象

2.将该对象写入一个OutPutStream

而在构建过程中，没有地方写入OutPutSteam ,也就是说必须在内存中构建整个 Excel，才能进行写出操作，在大数据量情况下，这样将导致所有数据加载到内存中，而不能输出，导致最后 内存溢出。

根据运行环境不用，可能内存溢出的 情况不同

根据情况，如果数据量达到10W以上，建议使用

1、多个Excel，每个Excel一个Sheet,因为所有Sheet都是Workbook的组成部分。如果不分多个Excel,即使分Sheet也没用，

2、每个Excel中列数适中，比如： 5W行每个Excel档，实现分多次导出和分页查询原理一样

3、对多个Excel导出到一个临时目录，并通过程序压缩，然后提供给客户下载

4、用另外一种方式，见 2 http://lyjilu.iteye.com/blog/2083106

2003版通过数据库结果存到List中，然后进行生产：Table 就是List Row 是Map

Java代码

/**

     * 生产xls,2003版本的excel,每个sheet有6.5W的行数限制

     *

     * @param fileOut

     *            输出流，未关闭

     * @param sheetMap

     *            要导出的数据信息

     */ 

    public static void createXSLByMap(OutputStream fileOut, Map<String, Table<Row<String, Object>>> sheetMap){ 

        try { 

            HSSFWorkbook wb = new HSSFWorkbook(); 

            Set<String> keys = sheetMap.keySet(); 

            for (Iterator<String> iterator = keys.iterator(); iterator 

                    .hasNext();) { 

                String SheetKey = iterator.next(); 

                Sheet sheet = wb.createSheet(SheetKey); 

                List<Row<String, Object>> sheetRows = sheetMap.get(SheetKey); 

                for (int i = 0, len = sheetRows.size(); i < len; i++) { 

                    Map<String, Object> cellMap = sheetRows.get(i); 

                    Set<String> cellSet = cellMap.keySet(); 

                    org.apache.poi.ss.usermodel.Row row = sheet.createRow(i); 

                    int j = 0; 

                    for (Iterator<String> iterCell = cellSet.iterator(); iterCell 

                            .hasNext(); j++) { 

                        String cellKey = iterCell.next(); 

                        Object obj = cellMap.get(cellKey); 

                        row.createCell(j).setCellValue(obj == null ? "" 

                                : obj.toString()); 

                    } 

                } 

            } 

            wb.write(fileOut); 

        } catch (IOException e) { 

            e.printStackTrace(); 

            throw new ServiceRunTimeException("生产xls文档错误", e); 

        } finally { 

        } 

    } 

<span style="font-size:18px;">/**
	 * 生产xls,2003版本的excel,每个sheet有6.5W的行数限制
	 * 
	 * @param fileOut
	 *            输出流，未关闭
	 * @param sheetMap
	 *            要导出的数据信息
	 */
	public static void createXSLByMap(OutputStream fileOut, Map<String, Table<Row<String, Object>>> sheetMap) {
		try {
			HSSFWorkbook wb = new HSSFWorkbook();
			Set<String> keys = sheetMap.keySet();
			for (Iterator<String> iterator = keys.iterator(); iterator
					.hasNext();) {
				String SheetKey = iterator.next();
				Sheet sheet = wb.createSheet(SheetKey);
				List<Row<String, Object>> sheetRows = sheetMap.get(SheetKey);
				for (int i = 0, len = sheetRows.size(); i < len; i++) {
					Map<String, Object> cellMap = sheetRows.get(i);
					Set<String> cellSet = cellMap.keySet();
					org.apache.poi.ss.usermodel.Row row = sheet.createRow(i);
					int j = 0;
					for (Iterator<String> iterCell = cellSet.iterator(); iterCell
							.hasNext(); j++) {
						String cellKey = iterCell.next();
						Object obj = cellMap.get(cellKey);
						row.createCell(j).setCellValue(obj == null ? ""
								: obj.toString());
					}
				}
			}
			wb.write(fileOut);
		} catch (IOException e) {
			e.printStackTrace();
			throw new ServiceRunTimeException("生产xls文档错误", e);
		} finally {
		}
	}</span>

新版本 POI+office 2007版本excel可以导出几十万条而不内存溢出，详细见：

http://lyjilu.iteye.com/blog/2083106