JAVA导出大数据量Excel文件的解决方案

最近在工作遇到了导出大数据量(10万级)Excel文件的问题，在网上找了很多文章都感觉不是很好，总内存溢出或卡死掉，偶尔能成功但很不稳定。

通过实践总结一套可行的解决方案，速度大约1000/s;

不管是使用POI、JXL还是FastExcel一次直接导出20万条数据性能暂不提就内存就受不了，这也是导致导出数据失败的主要原因，故使用多次导出每次可以限定在10000条数据（经测试是性能和稳定性最好-在普通配置得PC机上）,然后将多个Excel文件压缩成一个ZIP文件提供前台下载。这需要提醒的是每次导出时都创建一个Excel文件而不是创建一个SHEET这样可以及时释放内存。

下面是具体实现

ExportExcelUtil

package com.quanyou.pwm.util;import java.io.BufferedInputStream;import java.io.ByteArrayInputStream;import java.io.ByteArrayOutputStream;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.net.URL;import java.net.URLDecoder;import java.util.ArrayList;import java.util.Enumeration;import java.util.HashMap;import java.util.Iterator;import java.util.List;import java.util.Map;import javax.servlet.http.HttpServletRequest;import org.apache.commons.beanutils.BeanUtils;import org.apache.log4j.Logger;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.dom4j.Document;import org.dom4j.Element;import org.dom4j.io.SAXReader;public class ExportExcelUtil {private static final Logger logger = org.apache.log4j.Logger.getLogger(ExportExcelUtil.class);private String fileName;// 导出excel名称private HttpServletRequest request;// 请求对象private int pageSize;// 分页大小private Object service;// 服务private Map<String, Object> params;// 参数private String method;// 调用服务方法名称private List<String> subFileNames;// 子文件集合private File zip;// 压缩文件private int start;// 开始条数private int fileIndex;// 子文件索引private List<Map<String, Object>> heads; // excel头部private String xmlHeadId; // 对应xml模版的配置private Map<String, Object> xml = ParseExport.getXml(); // 导出Excel模版Xmlpublic static ExportExcelUtil getInstance(String fileName,HttpServletRequest request, int pageSize, Object service,Map<String, Object> params, String method, String xmlHeadId) {ExportExcelUtil instance = new ExportExcelUtil(fileName, request,pageSize, service, params, method, xmlHeadId);return instance;}public ExportExcelUtil() {init();}public ExportExcelUtil(String fileName, HttpServletRequest request,int pageSize, Object service, Map<String, Object> params,String method, String xmlHeadId) {this.fileName = fileName;this.request = request;this.pageSize = pageSize;this.service = service;this.params = params;this.method = method;this.xmlHeadId = xmlHeadId;init();}/** * 导出Excel文件 */public String export() {FileOutputStream out = null;try {List<Object> list = null;do {Page page = new Page(start, pageSize);start += pageSize;list = getDatas(page);if (list != null && list.size() > 0) {String file = getExcelName(fileName, fileIndex++);subFileNames.add(file);out = new FileOutputStream(file);HSSFWorkbook workbook = createWorkbook(list);workbook.write(out);out.flush();try {out.close();} catch (Exception e) {}}} while (list != null && list.size() == pageSize);// 压缩文件compressFile();} catch (Exception e) {e.printStackTrace();}return zip.getName();}public HSSFWorkbook createWorkbook(List<Object> list) {HSSFWorkbook workbook = new HSSFWorkbook();try {HSSFSheet sheet = workbook.createSheet(fileName);// 创建第一行及excel的头部createExcelHead(sheet);for (int i = 0; i < list.size(); i++) {createExcelRow(list.get(i), sheet, i);}} catch (Exception e) {e.printStackTrace();}return workbook;}// 创建excel头部private void createExcelHead(HSSFSheet sheet) {HSSFRow contenRow = sheet.createRow(0);HSSFCell contentCell = null;if (heads != null) {for (int i = 0; i < heads.size(); i++) {contentCell = contenRow.createCell(i);contentCell.setCellValue(heads.get(i).get("head").toString());sheet.setColumnWidth(i,Integer.parseInt(heads.get(i).get("width").toString()));}}}// 创建excel boyprivate void createExcelRow(Object bean, HSSFSheet sheet, int index) {HSSFRow contenRow = null;contenRow = sheet.createRow(index + 1);HSSFCell contentCell = null;for (int i = 0; i < heads.size(); i++) {contentCell = contenRow.createCell(i);contentCell.setCellValue(processValue(bean,heads.get(i).get("fields").toString()));}}// 处理每一个表格的数据private String processValue(Object bean, String field) {String result = "";try {Map<String, Object> map = BeanUtils.describe(bean);if (!"".equals(field)) {String[] fs = field.split(",");// 每一列的数据可能由多个字段组成用","解析for (String f : fs)result += map.get(f) + " ";}} catch (Exception e) {e.printStackTrace();}return result.trim();}// 得到要导出的数据private List<Object> getDatas(Page page) {List<Object> result = new ArrayList<Object>();try {result = (List<Object>) service.getClass().getMethod(method, new Class[] { Map.class, Page.class }).invoke(service, new Object[] { params, page });} catch (Exception e) {e.printStackTrace();}return result;}// 压缩文件private void compressFile() {File[] srcfile = new File[subFileNames.size()];for (int i = 0; i < srcfile.length; i++) {srcfile[i] = new File(subFileNames.get(i));}FileZipUtil.ZipFiles(srcfile, zip);// 压缩完成所删除子文件for (String f : subFileNames) {File subFile = new File(f);if (subFile.exists()) {if (!subFile.delete())logger.error("清除子文件:" + subFile.getName() + " 失败");}}}// 创建数据流@Deprecatedpublic ByteArrayInputStream createStream() {ByteArrayInputStream inputStream = null;BufferedInputStream in = null;ByteArrayOutputStream out = null;try {in = new BufferedInputStream(new FileInputStream(zip));out = new ByteArrayOutputStream(1048576);byte[] temp = new byte[1048576];int size = 0;while ((size = in.read(temp)) != -1) {out.write(temp, 0, size);}byte[] b = out.toByteArray();inputStream = new ByteArrayInputStream(b);} catch (Exception e) {e.printStackTrace();} finally {try {in.close();} catch (Exception e) {}try {out.close();} catch (Exception e) {}}return inputStream;}private void init() {subFileNames = new ArrayList<String>();if (!"".equals(fileName))zip = new File(getZipName(fileName));start = 0;fileIndex = 1;loadHeads();}// 加载模版private void loadHeads() {if (xml != null && xml.get(xmlHeadId) != null) {this.heads = (List) xml.get(xmlHeadId);} else {logger.error("获取模版配置失败，将导致导出数据失败！");}}private String getZipName(String name) {return request.getSession().getServletContext().getRealPath("/")+ "/excels/" + System.currentTimeMillis() + ".zip";}private String getExcelName(String name, int index) {File root = new File(request.getSession().getServletContext().getRealPath("/")+ "/excels");if (!root.exists())root.mkdir();return request.getSession().getServletContext().getRealPath("/")+ "/excels/" + name + "_" + DateFormat.DateToyyyy_MM_dd() + "_"+ System.currentTimeMillis() + "(" + index + ").xls";}}/** * 解析导出Excel文件模版 *  * @author TangYang */final class ParseExport {private final static String defaultXml = "init.xml";private final static String defaultPackage = "excel";private static String initConfig = "";private static String configRoot = "";private static Map<String, Object> xml = new HashMap<String, Object>();private static List<String> configFile = new ArrayList<String>();private static List<String> includeFile = new ArrayList<String>();public static Map<String, Object> getXml() {return xml;}static {initConfigPath();if (!"".equals(initConfig)) {configFile.add(initConfig);begin();}}// 开始解析xml文件private static void begin() {includeFile.clear();if (configFile != null) {for (String c : configFile) {File file = new File(c);if (file.exists())readXml(file);}}if (includeFile.size() > 0) {configFile.clear();configFile.addAll(includeFile);begin();}}// 读取配置文件private static void readXml(File file) {FileInputStream in = null;try {SAXReader reader = new SAXReader();in = new FileInputStream(file);Document doc = reader.read(in);Element root = doc.getRootElement();parseInclude(root);parseExcel(root);} catch (Exception e1) {e1.printStackTrace();} finally {try {if (in != null)in.close();} catch (Exception e) {}}}// 解析include的文件private static void parseInclude(Element root) {for (Iterator it = root.elementIterator(); it.hasNext();) {Element e = (Element) it.next();if ("include".equals(e.getName())) {String resouce = e.attributeValue("resouce");if (resouce != null && !"".equals(resouce))includeFile.add(configRoot + "/" + resouce);}}}// 解析Excel模版private static void parseExcel(Element root) {for (Iterator it = root.elementIterator(); it.hasNext();) {Element e = (Element) it.next();String key = e.attributeValue("id");xml.put(key, parseLine(e));}}private static List parseLine(Element node) {List result = new ArrayList();for (Iterator it = node.elementIterator(); it.hasNext();) {Element e = (Element) it.next();result.add(parseAttribte(e));}return result;}private static Map<String, Object> parseAttribte(Element node) {Map<String, Object> result = new HashMap<String, Object>();for (Iterator it = node.elementIterator(); it.hasNext();) {Element e = (Element) it.next();String key = e.attributeValue("name");if (key != null && !"".equals(key)) {result.put(key, e.getTextTrim());}}return result;}private static void initConfigPath() {ClassLoader loader = Thread.currentThread().getContextClassLoader();Enumeration<URL> urls;try {urls = loader.getResources(defaultPackage.replace('.', '/'));while (urls.hasMoreElements()) {String urlPath = urls.nextElement().getFile();urlPath = URLDecoder.decode(urlPath, "UTF-8");// If it's a file in a directory, trim the stupid file: specif (urlPath.startsWith("file:"))urlPath = urlPath.substring(5);// Else it's in a JAR, grab the path to the jarif (urlPath.indexOf('!') > 0)urlPath = urlPath.substring(0, urlPath.indexOf('!'));findFile(urlPath);}} catch (Exception e) {e.printStackTrace();}}// 查找配置文件private static void findFile(String urlPath) {File file = new File(urlPath);if (file.exists()) {File[] files = file.listFiles();if (files != null && files.length > 0) {for (File f : files) {if (f.getName().equals(defaultXml)) {configRoot = urlPath;initConfig = urlPath + "/" + f.getName();}}}}}}

 

FileZipUtil压缩文件工具类

package com.quanyou.pwm.util;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import org.apache.tools.zip.ZipEntry;import org.apache.tools.zip.ZipOutputStream;//import java.util.zip.ZipEntry;//import java.util.zip.ZipOutputStream;/** *  * @author http://javaflex.iteye.com/ *  */public class FileZipUtil {/** *  * @param srcfile *            文件名数组 * @param zipfile *            压缩后文件 */public static void ZipFiles(java.io.File[] srcfile, java.io.File zipfile) {byte[] buf = new byte[1024];try {ZipOutputStream out = new ZipOutputStream(new FileOutputStream(zipfile));for (int i = 0; i < srcfile.length; i++) {FileInputStream in = new FileInputStream(srcfile[i]);out.putNextEntry(new ZipEntry(srcfile[i].getName()));int len;while ((len = in.read(buf)) > 0) {out.write(buf, 0, len);}out.closeEntry();in.close();}out.close();} catch (IOException e) {e.printStackTrace();}}}

涉及到xml配置文件仅供参考

init.xml

<?xml version="1.0" encoding="UTF-8"?><export><include resouce="wages-model.xml"></include></export>

wages-model.xml

<?xml version="1.0" encoding="UTF-8"?><export><excel id="wages"><line><property name="head">排工单号</property><property name="width">4000</property><property name="fields">jobOrderNo</property></line><line><property name="head">工厂名称</property><property name="width">5000</property><property name="fields">factoryCode,factoryName</property></line><line><property name="head">姓名</property><property name="width">4000</property><property name="fields">staffName</property></line><line><property name="head">身份证号码</property><property name="width">6000</property><property name="fields">idCard</property></line><line><property name="head">员工工号</property><property name="width">6000</property><property name="fields">staffCode</property></line><line><property name="head">订单号</property><property name="width">5000</property><property name="fields">aufnr</property></line><line><property name="head">包件编码</property><property name="width">4000</property><property name="fields">matnr</property></line><line><property name="head">包件名称</property><property name="width">8000</property><property name="fields">maktx</property></line><line><property name="head">板件编码</property><property name="width">4000</property><property name="fields">bmatnr</property></line><line><property name="head">板件名称</property><property name="width">4000</property><property name="fields">bmaktx</property></line><line><property name="head">子工序代码</property><property name="width">4000</property><property name="fields">stepCode</property></line><line><property name="head">子工序名称</property><property name="width">4000</property><property name="fields">stepName</property></line><line><property name="head">操作代码</property><property name="width">4000</property><property name="fields">oper</property></line><line><property name="head">操作名称</property><property name="width">4000</property><property name="fields">operName</property></line><line><property name="head">数量</property><property name="width">4000</property><property name="fields">num</property></line><line><property name="head">单价</property><property name="width">4000</property><property name="fields">price</property></line><line><property name="head">金额</property><property name="width">4000</property><property name="fields">amount</property></line></excel></export>

涉及jar包仅参考

poi-3.7-20101029.jar，poi-3.9-20121203.jar，poi-ooxml-3.9-20121203.jar，poi-ooxml-schemas-3.9-20121203.jar，poi-scratchpad-3.9-20121203.jar，ant.jar（压缩工具支持中文）