JAVA导出数据到excel中大数据量的解决方法——续

之前写了个大数据导入excel的方法，将大数据拆分成多个excel文件，再打包。有人提出能不能放在一个excel文件分成多个sheet。后来也写了实现，一直没贴出来。

首先接口还是那个接口

import java.io.OutputStream;import java.util.Collection;public interface ExportData {public void export(final Collection<String> titles, final OutputStream os, final String sql, final Object... sqlParams);}

只有具体的实现不同

import java.io.IOException;import java.io.OutputStream;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.util.ArrayList;import java.util.Collection;import java.util.List;import org.apache.commons.io.IOUtils;import org.springframework.dao.DataAccessException;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.PreparedStatementCreator;import org.springframework.jdbc.core.PreparedStatementCreatorFactory;import org.springframework.jdbc.core.PreparedStatementSetter;import org.springframework.jdbc.core.ResultSetExtractor;import org.springframework.jdbc.core.SqlParameterValue;import org.springframework.jdbc.support.JdbcUtils;import org.springframework.web.util.HtmlUtils;import com.avicinfo.common.base.util.StaticMethod;import com.avicinfo.v2.core.export.ExportData;import com.cnaec.common.Constant;/** * 将数据导出到excel，多于指定行数后 放在下一个sheet *  * @author lisen *  */public class ExportData2ExcelSheetImpl implements ExportData {/** * 每个文件的最大行数 超过请求按默认算 */public static final int MAXROWS = 50000;private int maxRow = MAXROWS;/** * 用于数据查询 */private JdbcTemplate jdbcTemplate;StringBuffer head = new StringBuffer("<?xml version=\"1.0\"?>").append("<?mso-application progid=\"Excel.Sheet\"?> ").append("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" ").append("  xmlns:o=\"urn:schemas-microsoft-com:office:office\" ").append("  xmlns:x=\"urn:schemas-microsoft-com:office:excel\" ").append("  xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" ").append("  xmlns:html=\"http://www.w3.org/TR/REC-html40\">");StringBuffer foot = new StringBuffer("</Workbook>");StringBuffer sheetHead = new StringBuffer("<Worksheet ss:Name=\"sheet{0}\">").append("<Table>");StringBuffer sheetFoot = new StringBuffer("</Table>").append("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">").append("<ProtectObjects>False</ProtectObjects>").append("<ProtectScenarios>False</ProtectScenarios>").append("</WorksheetOptions>").append("</Worksheet>");public ExportData2ExcelSheetImpl(JdbcTemplate jdbcTemplate) {this.jdbcTemplate = jdbcTemplate;}/** * 获取单个文件最大行数 *  * @param maxRow * @return */protected int getMaxRow() {return maxRow < MAXROWS ? maxRow : MAXROWS;}/** * 数据输出 *  * @param data * @param fos * @throws IOException */protected void writeToOutputStream(String data, OutputStream os) throws IOException {IOUtils.write(data, os, Constant.ENCODING);}/** * 文件头的写入 *  * @param fos */protected void writeHeaderToOutputStream(OutputStream os) throws IOException {writeToOutputStream(head.toString(), os);}/** * 文件结尾的写入 *  * @param fos */protected void writeFooterToOutputStream(OutputStream os) throws IOException {writeToOutputStream(foot.toString(), os);}/** * 文件头的写入 *  * @param fos */protected void writeSheetHeaderToOutputStream(OutputStream os, int count) throws IOException {String sh = sheetHead.toString();String head = java.text.MessageFormat.format(sh, count);writeToOutputStream(head, os);}/** * 文件结尾的写入 *  * @param fos */protected void writeSheetFooterToOutputStream(OutputStream os) throws IOException {writeToOutputStream(sheetFoot.toString(), os);}protected void writeTitleToOutputStream(Collection<String> titles, OutputStream os) throws IOException {if (titles != null && titles.size() > 0) {writeToOutputStream("<Row>", os);for (String title : titles) {writeToOutputStream("<Cell><Data ss:Type=\"String\">"+ (title == null ? "" : HtmlUtils.htmlEscape(title)) + "</Data></Cell>", os);}writeToOutputStream("</Row>", os);}}protected Object getColumnValue(ResultSet rs, int index) throws SQLException {return JdbcUtils.getResultSetValue(rs, index);}protected void writeOneRowToOutputStream(ResultSet rs, OutputStream os) throws SQLException, IOException {// 获取metaData;ResultSetMetaData rsmd = rs.getMetaData();int columnCount = rsmd.getColumnCount();writeToOutputStream("<Row>", os);for (int i = 1; i <= columnCount; i++) {Object obj = getColumnValue(rs, i);writeToOutputStream("<Cell><Data ss:Type=\"String\">"+ (obj == null ? "" : HtmlUtils.htmlEscape(obj.toString())) + "</Data></Cell>", os);}writeToOutputStream("</Row>", os);}public void export(final Collection<String> titles, final Collection<String> firstRow,final Collection<String> lastRow, final OutputStream os, String sql, Object... sqlParams) {// 每个文件最大行数final int max = getMaxRow();List<SqlParameterValue> spvList = new ArrayList<SqlParameterValue>(sqlParams.length);for (Object param : sqlParams) {SqlParameterValue spv = new SqlParameterValue(JdbcUtils.TYPE_UNKNOWN, param);spvList.add(spv);}PreparedStatementCreatorFactory factory = new PreparedStatementCreatorFactory(sql, spvList);factory.setResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE);PreparedStatementCreator psc = factory.newPreparedStatementCreator(sqlParams);PreparedStatementSetter pss = factory.newPreparedStatementSetter(sqlParams);jdbcTemplate.query(psc, pss, new ResultSetExtractor() {@Overridepublic Object extractData(ResultSet rs) throws SQLException, DataAccessException {try {writeHeaderToOutputStream(os);// 行数记录器int i = 0, j = 0;while (rs.next()) {if (i == 0) {// 写每个sheet头writeSheetHeaderToOutputStream(os, j);if (rs.isFirst() && !StaticMethod.isEmpty(firstRow)) {writeTitleToOutputStream(firstRow, os);}// 数据区标题栏writeTitleToOutputStream(titles, os);}// 写一行i++;writeOneRowToOutputStream(rs, os);if (rs.isLast() && !StaticMethod.isEmpty(lastRow)) {writeTitleToOutputStream(lastRow, os);}if (i == max) {i = 0;j++;// 写每个sheet尾writeSheetFooterToOutputStream(os);} else if (rs.isLast()) {writeSheetFooterToOutputStream(os);}}writeFooterToOutputStream(os);} catch (IOException e) {e.printStackTrace();}return null;}});}@Overridepublic void export(final Collection<String> titles, final OutputStream os, String sql, Object... sqlParams) {// 每个文件最大行数export(titles, null, null, os, sql, sqlParams);}public void setMaxRow(int maxRow) {this.maxRow = maxRow;}}

原理：

excel可以识别出xml格式的文件，之前是拼成html中的table，这次是按照excel的标准格式拼成xml，这个xml格式由excel另存为后可以获得。

excel的xml格式中，将不同的sheet以“<Worksheet ss:Name='sheet0'></Worksheet>”标识，sheet中的表为table，行为row，列为cell，单元格中的数据为Data，以此拼出xml后由excel打开。

我在本机测试导出123456行数据 用时不到7秒，感觉速度还行