POI导出百万级Excel

POI导出百万级Excel

SXSSFWorkbook对象

SXSSFWorkbook对象在生成Excel文件的过程中，会在磁盘中写入临时文件，有效得解决了生成大文件时对内存的开销。

org.apache.poi.util.TempFile类中内部类DefaultTempFileCreationStrategy的createTempFile方法指定了生成临时文件的目录。

PS：有兴趣的同学，可以在生成Excel过程中观察输出临时文件的过程，此目录是项目的相对路径。

package org.apache.poi.util;

public static class DefaultTempFileCreationStrategy implements TempFileCreationStrategy {
    private File dir;

    public DefaultTempFileCreationStrategy() {
        this((File)null);
    }

    public DefaultTempFileCreationStrategy(File dir) {
        this.dir = dir;
    }

    public File createTempFile(String prefix, String suffix) throws IOException {
        if (this.dir == null) {
            this.dir = new File(System.getProperty("java.io.tmpdir"), "poifiles");
            this.dir.mkdir();
            if (System.getProperty("poi.keep.tmp.files") == null) {
                this.dir.deleteOnExit();
            }
        }

        File newFile = File.createTempFile(prefix, suffix, this.dir);
        if (System.getProperty("poi.keep.tmp.files") == null) {
            newFile.deleteOnExit();
        }

        return newFile;
    }
}

注意点

• Excel本身的行数限制。Excel2003版最大行数是65536行，Excel2007开始的版本最大行数是1048576行。对超出的部分需要写到另一个Sheet页中，可以将原数据进行分页处理，导出至不同的Sheet中。
• 若需要创建多个Sheet页，可以开启多个线程同时向各个Sheet中同时写入。
• 每写完一个Sheet后，调用flushRows()方法，释放当前数据；全部文件写入完成后，调用SXSSFWorkbook的dispose()方法，清空临时文件。
• 若写入过程中临时文件过大，可以考虑将SXSSFWorkbook的compressTempFiles属性设置为true，对输出的临时文件时进行压缩处理。
• 尽可能避免复制原数据的情况，减少内存开销，即直接将数据库返回的数据传入导出API中。

环境

Java 1.7

Hibernate 3.3.2 (数据返回类型是Object[], 使用MyBatis时做适当调整)

代码部分

ExcelUtilCallback用于自定义组装数据并返回，避免再次创建数据对象。因为数据查询出之后可能需要进行调整，比如指定位置或者条件判断等。

/**
 * ExcelUtil过滤集合工具接口
 * @author Yichen Qiao
 */
public interface ExcelUtilCallback {

    Object[] dataFilter(List dataList, int rowIndex);

}

导出工具类ExcelUtil

package com.goldenstone.education.app.framework.utils.Excel;

import com.goldenstone.education.app.framework.utils.BrowserCheck;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.concurrent.*;

/**
 * Excel工具类
 * @author YYQ
 */
public class ExcelUtil {

	// 单sheet最大行数
	public static final int maxRowNum = 30000;
	public static final String defaultSheetName = "sheet";

	private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

	/**
	 * 导出Excel
	 * @param request
	 * @param response
	 * @param dataList
	 * @param titleColumns
	 * @param fileName
	 * @param callback
	 */
	public static void exportExcel(HttpServletRequest request, HttpServletResponse response, List dataList, String[] titleColumns, String fileName, ExcelUtilCallback callback) {
		SXSSFWorkbook workbook = new SXSSFWorkbook();
		workbook.setCompressTempFiles(true);
		// 数据总记录数
		int dataCountNum = dataList.size();
		// 计算总分页sheet数量
		int totalSheetNum = getTotalSheetNum(dataCountNum);
		for (int i = 1; i <= totalSheetNum; i++) {
			// 将数据写入sheet
			Sheet sheet = writeSheet(workbook, dataList, titleColumns, i, dataCountNum, callback);
			try {
				// 释放当前sheet数据
				((SXSSFSheet)sheet).flushRows();
			} catch (IOException e) {
				logger.error("SXSSFSheet flushRows error", e);
				throw new RuntimeException();
			}
		}
		dataList.clear();
		try {
			// 输出Excel文件并关闭文件流
			write(request, response, workbook, fileName);
		} catch (IOException e) {
			logger.error("write outputStream error", e);
			throw new RuntimeException();
		}
	}

	/**
	 * 导出Excel(多线程)
	 * @param request
	 * @param response
	 * @param dataList
	 * @param titleColumns
	 * @param fileName
	 * @param callback
	 */
	public static void exportExcelByExecutor(HttpServletRequest request, HttpServletResponse response, final List dataList, final String[] titleColumns, String fileName, final ExcelUtilCallback callback) {
		final SXSSFWorkbook workbook = new SXSSFWorkbook();
		// 设置缓存数据压缩
		//workbook.setCompressTempFiles(true);
		// 数据总记录数
		final int dataCountNum = dataList.size();
		// 计算总分页sheet数量
		int totalSheetNum = getTotalSheetNum(dataCountNum);
		final CountDownLatch latch = new CountDownLatch(totalSheetNum);
		ExecutorService executor = Executors.newCachedThreadPool();
		final Map<Integer, Sheet> sheetMap = new ConcurrentHashMap<>(totalSheetNum);
		for (int i = 1; i <= totalSheetNum; i++) {
			final Integer currentIndex = i;
			Sheet sheet = workbook.createSheet(ExcelUtil.defaultSheetName + currentIndex);
			sheet = workbook.getSheetAt(currentIndex - 1);
			sheetMap.put(currentIndex, sheet);
			FutureTask<Void> futureTask = new FutureTask<>(new Runnable() {
				@Override
				public void run() {
					final Sheet sheet = writeSheet(sheetMap, dataList, titleColumns, currentIndex, dataCountNum, callback);
					try {
						// 释放当前sheet数据
						((SXSSFSheet)sheet).flushRows();
					} catch (IOException e) {
						logger.error("SXSSFSheet flushRows error", e);
						throw new RuntimeException();
					}
					latch.countDown();
				}
			}, null);
			executor.execute(futureTask);
		}
		try {
			latch.await();
		} catch (InterruptedException e) {
			logger.error("shutDowanLatch interrupted error", e);
			throw new RuntimeException();
		}
		executor.shutdown();
		dataList.clear();
		try {
			// 输出Excel文件并关闭文件流
			write(request, response, workbook, fileName);
		} catch (IOException e) {
			logger.error("write outputStream error", e);
			throw new RuntimeException();
		}
	}


	/**
	 * 计算总Sheet数量
	 * @param dataSize
	 * @return
	 */
	private static int getTotalSheetNum(int dataSize) {
		int totalSheetSize = 1;
		if (dataSize % maxRowNum == 0 && dataSize > maxRowNum) {
			totalSheetSize = dataSize / maxRowNum;
		} else if (dataSize % maxRowNum != 0 && dataSize > maxRowNum) {
			totalSheetSize = (dataSize / maxRowNum) + 1;
		}
		return totalSheetSize;
	}

	/**
	 * 将数据写入sheet
	 * @param workbook
	 * @param dataList
	 * @param titleColumns
	 * @param sheetIndex
	 * @param dataCountNum
	 * @param callback
	 */
	private static Sheet writeSheet(final SXSSFWorkbook workbook, List dataList, String[] titleColumns, int sheetIndex, int dataCountNum, ExcelUtilCallback callback) {
		// 创建一个新sheet
		Sheet sheet = workbook.createSheet(ExcelUtil.defaultSheetName + sheetIndex);
		// 指向当前sheet
		sheet = workbook.getSheetAt(sheetIndex - 1);
		// 设置列名
		Row headerRow = sheet.createRow(0);
		for (int j = 0; j < titleColumns.length; j++) {
			Cell cell = headerRow.createCell(j);
			cell.setCellValue(titleColumns[j]);
		}
		// 输出表内容
		for (int z = (sheetIndex - 1) * maxRowNum; z < sheetIndex * maxRowNum && z < dataCountNum; z++) {
			if (callback != null) {
				// 根据写入规则对导出数据进行过滤
				writeCell(callback.dataFilter(dataList, z), sheetIndex, sheet, z);
			} else {
				writeCell((Object[])dataList.get(z), sheetIndex, sheet, z);
			}
		}
		return sheet;
	}

	/**
	 * 将数据写入sheet(多线程)
	 * @param sheetMap
	 * @param dataList
	 * @param titleColumns
	 * @param sheetIndex
	 * @param dataCountNum
	 * @param callback
	 * @return
	 */
	private static Sheet writeSheet(final Map<Integer, Sheet> sheetMap, final List dataList, String[] titleColumns, int sheetIndex, int dataCountNum, ExcelUtilCallback callback) {
		// 创建一个新sheet
		Sheet sheet = sheetMap.get(sheetIndex);
		// 设置列名
		Row headerRow = sheet.createRow(0);
		for (int j = 0; j < titleColumns.length; j++) {
			Cell cell = headerRow.createCell(j);
			cell.setCellValue(titleColumns[j]);
		}
		// 输出表内容
		for (int z = (sheetIndex - 1) * maxRowNum; z < sheetIndex * maxRowNum && z < dataCountNum; z++) {
			if (callback != null) {
				// 根据写入规则对导出数据进行过滤
				writeCell(callback.dataFilter(dataList, z), sheetIndex, sheet, z);
			} else {
				writeCell((Object[])dataList.get(z), sheetIndex, sheet, z);
			}
		}
		return sheet;
	}

	/**
	 * 将数据写入cell
	 * @param objArray
	 * @param sheetIndex
	 * @param sheet
	 * @param rowIndex
	 */
	private static void writeCell(Object[] objArray, int sheetIndex, Sheet sheet, int rowIndex) {
		Row row = sheet.createRow(rowIndex + 1 - ((sheetIndex - 1) * maxRowNum));
		for (int x = 0; x < objArray.length; x++) {
			Cell cell = row.createCell(x);
			String str = objArray[x] == null ? "" : objArray[x].toString();
			cell.setCellValue(str);
		}
		objArray = null;
	}

	/**
	 * 输出Excel文件
	 * @param request
	 * @param response
	 * @param workbook
	 * @param fileName
	 * @throws IOException
	 */
	private static void write(HttpServletRequest request, HttpServletResponse response, SXSSFWorkbook workbook, String fileName) throws IOException {
		ServletOutputStream outputStream = null;
		try {
			outputStream = response.getOutputStream();
			if (!"firefox".equals(BrowserCheck.check(request))) {
				response.setHeader("Content-Disposition", "attachment; filename=\""
						+ URLEncoder.encode(fileName, "UTF-8") + ".xlsx\"");
			} else {
				response.addHeader("Content-Disposition", "attachment;filename=\""+ new String(fileName.getBytes("UTF-8"),"ISO-8859-1")  + ".xlsx\"");
			}
			// 导出Excel文件
			workbook.write(outputStream);
			// 清除xml临时文件
			workbook.dispose();
		} catch (IOException e) {
			logger.error("outputStream write error", e);
			throw new RuntimeException();
		} finally {
			// 关闭Workbook对象
			if (null != workbook) {
				workbook.close();
			}
			// 关闭outputStream
			if (null != outputStream) {
				outputStream.flush();
				outputStream.close();
			}
		}
	}

}

调用示例

@Override
public void exportExcel(HttpServletRequest request, HttpServletResponse response, String flieName, String[] titleColumns, String condition) {
	List dataList = tempDao.exportExcel(condition);
	final String flag = condition;
	ExcelUtil.exportExcelByExecutor(request, response, dataList, titleColumns, flieName,
			new ExcelUtilCallback() {
				@Override
				public Object[] dataFilter(List dataList, int rowIndex) {
					Object[] o = (Object[])dataList.get(rowIndex);
					Object[] objArray = new Object[o.length];
					if ("conditionA".equals(flag)) {
						objArray[0] = o[0] == null ? "" : o[0].toString(); 
						objArray[1] = o[1] == null ? "" : o[1].toString(); 
						objArray[2] = o[2] == null ? "" : o[2].toString();
						objArray[3] = o[3] == null ? "" : o[3].toString();
						objArray[4] = o[4] == null ? "" : o[4].toString();
						objArray[5] = o[5] == null ? "" : o[5].toString();
						objArray[6] = o[6] == null ? "" : o[6].toString();
						objArray[7] = o[7] == null ? "" : o[7].toString();
						objArray[8] = o[8] == null ? "" : o[8].toString();

					} else {
						objArray[0] = o[0] == null ? "" : o[0].toString();
						objArray[1] = o[1] == null ? "" : o[1].toString();
						objArray[2] = o[2] == null ? "" : o[2].toString();
						objArray[3] = o[3] == null ? "" : o[3].toString();
						objArray[4] = o[4] == null ? "" : o[4].toString();
						objArray[5] = o[17] == null ? "" : o[17].toString();
						objArray[6] = o[18] == null ? "" : o[18].toString();
						objArray[7] = o[5] == null ? "" : o[5].toString();
						objArray[8] = o[6] == null ? "" : o[6].toString();
					}
					return objArray;
				}
			});
}