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模版Xml

	public 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 boy
	private 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() + " 失败");
			}
		}
	}

	// 创建数据流
	@Deprecated
	public 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: spec
				if (urlPath.startsWith("file:"))
					urlPath = urlPath.substring(5);

				// Else it's in a JAR, grab the path to the jar
				if (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（压缩工具支持中文）