Excel文件模板导出，记录分页追加

ahzzhen2

  JXLS是一个简单易用的用于生成和读入Excel的工具。因本人对其接触使用不是很久，所以这里不再陈述，有兴趣的朋友可以取其源代码进行研究，其SVN地址:https://jxls.svn.sourceforge.net/svnroot/jxls



因在项目中需生成具有较大数据量的Excel报表，所以一次读入再模板化将降低系统的效率。能够以分页追加的方式来处理将显得尤为必要。基于此需求，于是写了一个比较简单的处理程序。

主要思路如下：
 
       1. 构造一个ExcelBuilder
       2. 提供一个Excel处理接口来具体处理Excel文件
       2. 提供一个putValues,和addValue方法用于设置值（putServices及addService设置回调）
       3. 提供一个parseWorkbook方法，用于追加至最终文件尾
       4. 调用create方法，持久化最终文件

下面看具体代码：
 
1.ExcelBuilder类
package net.sf.jxls;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.util.Date;import java.util.HashMap;import java.util.Iterator;import java.util.Map;import java.util.Set;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.apache.poi.ss.util.CellRangeAddress;/** * Excel文件追加(基于POI3.6) *  * @author zz(email:zhangzhen@foreveross.com) * @date 2011-4-19 *  */public class ExcelBuilder {private File templateFile;// 模板文件private File tempFile;// 临时文件private File resultFile;// 最终文件private HSSFWorkbook workbook;// 工作簿private int[] loopStartRows;//模板各sheet循环开始位置private boolean deleteTemp = true;//删除临时文件private Map<String,Object> beans,services;private WorkbookProcessor processor;//private HSSFWorkbook templateWorkbook;/** *  * @param templateFilePath模板路径 * @param resultFilePath生成文件路径 * @param loopStartRows模板各sheet循环开始位置 */public ExcelBuilder(String templateFilePath, String resultFilePath,int[] loopStartRows) {this.templateFile = new File(templateFilePath);this.resultFile = new File(resultFilePath);this.loopStartRows = loopStartRows;initBuilder();}/** *  * @param templateFile模板文件 * @param resultFile生成文件 * @param loopStartRows模板各sheet循环开始位置 */public ExcelBuilder(File templateFile, File resultFile,int[] loopStartRows) {this.templateFile = templateFile;this.resultFile = resultFile;this.loopStartRows = loopStartRows;initBuilder();}/** *  * @param templateFilePath模板文件路径 * @param resultFilePath生成文件路径 * @param tempFilePath临时文件路径 * @param loopStartRows模板各sheet循环开始位置 * @param deleteTemp是否删除临时文件 */public ExcelBuilder(String templateFilePath, String resultFilePath, String tempFilePath,int[] loopStartRows,boolean deleteTemp) {this(templateFilePath, resultFilePath,loopStartRows);this.deleteTemp = deleteTemp;this.tempFile = new File(tempFilePath);initBuilder();}/** * 初始化 */private void initBuilder() {this.templateWorkbook = this.openWorkbook(this.templateFile);String filePath = this.resultFile.getPath();if (this.tempFile == null && filePath.indexOf("\\") > -1){String tempPath = filePath.substring(0,        filePath.lastIndexOf("\\") + 1) + new Date().getTime() + "_temp.xls";this.tempFile = new File(tempPath);}}/** * 添加值 * @param key 关键字 * @param val值 */public void addValue(String key,Object val){if(this.beans==null)this.beans = new HashMap<String,Object>();this.beans.put(key, val);}/** * 添加服务 * @param key 关键字 * @param service服务 */public void addService(String key,Object service){if(this.services == null)this.services = new HashMap<String,Object>();this.services.put(key, service);}/** * 添加值Map集合 * @param vals值集合 */public void putValues(Map<String,Object> vals){this.beans = vals;}/** * 添加服务Map集合 * @param services服务集合 */public void putServices(Map<String,Object> services){Set<String> keys = services.keySet();for (Iterator<String> it = keys.iterator(); it.hasNext();) {String key =  it.next();this.addService(key,services.get(key));        }}/** * 模板处理 * @param config 配置项(可传入null) * @throws IOException */public void parseWorkbook(Map<Object,Object> config) throws IOException {if(this.processor == null)throw new RuntimeException("ExcelBuilder should be supplied an instance of WorkbookProcessor!");if(this.workbook == null){this.processor.processWorkbook(templateWorkbook,this.resultFile,this.beans,this.services,config);this.workbook = this.openWorkbook(this.resultFile);return ;}this.processor.processWorkbook(templateWorkbook,this.tempFile,this.beans,this.services,config);HSSFWorkbook tempWorkbook = this.openWorkbook(this.tempFile);int totalSheet = tempWorkbook.getNumberOfSheets();for (int i = 0; i < totalSheet; i++) {HSSFSheet srcSheet = tempWorkbook.getSheetAt(i);int from = srcSheet.getFirstRowNum();if(loopStartRows!=null&&loopStartRows.length>i)from = loopStartRows;mergeSheet(srcSheet, this.workbook.getSheetAt(i),tempWorkbook,this.workbook,from);}}/** * 打开一个工作簿 * @param file工作簿文件 * @return */private HSSFWorkbook openWorkbook(File file){InputStream in = null;HSSFWorkbook wb = null;try {in = new FileInputStream(file);wb = new HSSFWorkbook(in);} catch (Exception e) {throw new RuntimeException("File" + file.getPath() + " not found:" + e.getMessage());} finally {try {in.close();} catch (Exception e) {}}return wb;}/** * 创建Excel * @return * @throws IOException */public File create() throws IOException {FileOutputStream out = new FileOutputStream(this.resultFile);this.workbook.write(out);out.close();if(this.deleteTemp){if (this.tempFile.exists())this.tempFile.delete();}return this.resultFile;}/** * 合并工作表 * @param srcSheet 源工作表 * @param targetSheet目的工作表 * @param from源开始位置 */public static void mergeSheet(HSSFSheet srcSheet,HSSFSheet targetSheet, HSSFWorkbook srcWorkbook,HSSFWorkbook targetWorkbook, int from) {mergeSheet(srcSheet, targetSheet,srcWorkbook,targetWorkbook, from,        srcSheet.getLastRowNum() - from + 1);}/** * 合并工作表 * @param srcSheet源工作表 * @param targetSheet目的工作表 * @param from源开始位置 * @param count数目 */public static void mergeSheet(HSSFSheet srcSheet, HSSFSheet targetSheet,HSSFWorkbook srcWorkbook,HSSFWorkbook targetWorkbook,  int from, int count) {if (srcSheet.getLastRowNum() < from && srcSheet.getLastRowNum() - from < count)throw new IllegalArgumentException(        "请检查参数!row from " + from + ",count" + count);int targetRowStart = targetSheet.getLastRowNum();if (targetRowStart != 0)targetRowStart += 1;for (int rownum = from; rownum < from + count; rownum++) {HSSFRow fromRow = srcSheet.getRow(rownum);if (null == fromRow)return;HSSFRow targetFromRow = targetSheet.getRow(rownum);//为复制样式HSSFRow toRow = targetSheet.createRow(targetRowStart + rownum - from);toRow.setHeight(fromRow.getHeight());toRow.setHeightInPoints(fromRow.getHeightInPoints());for (int i = fromRow.getFirstCellNum(); i <fromRow.getLastCellNum() && i >= 0; i++) {HSSFCell fromCell = getCell(fromRow, i);HSSFCell toCell = getCell(toRow, i);HSSFCell targetFromCell = getCell(targetFromRow,i);toCell.setCellStyle(targetFromCell.getCellStyle());toCell.setCellType(fromCell.getCellType());switch (fromCell.getCellType()) {case HSSFCell.CELL_TYPE_BOOLEAN:toCell.setCellValue(fromCell.getBooleanCellValue());break;case HSSFCell.CELL_TYPE_FORMULA:toCell.setCellFormula(fromCell.getCellFormula());break;case HSSFCell.CELL_TYPE_NUMERIC:toCell.setCellValue(fromCell.getNumericCellValue());break;case HSSFCell.CELL_TYPE_STRING:toCell.setCellValue(fromCell.getRichStringCellValue());break;default:}}}// 合并单元格for (int j = 0; j < srcSheet.getNumMergedRegions(); j++) {CellRangeAddress region = srcSheet.getMergedRegion(j);if (region.getFirstRow() >= from && region.getLastRow() <= from + count) {int firstRow = region.getFirstRow() + targetRowStart-from;int lastRow = region.getLastRow() + targetRowStart-from;CellRangeAddress r = new CellRangeAddress(firstRow,lastRow,region.getFirstColumn(),region.getLastColumn());targetSheet.addMergedRegion(r);}}}/** *  * @param row 行数 * @param column列数 * @return */private static HSSFCell getCell(HSSFRow row, int column) {HSSFCell cell = row.getCell(column);if (cell == null) {cell = row.createCell(column);}return cell;}// resultFilepublic File getResultFile() {return this.resultFile;}//templateFilepublic File getTemplateFile() {    return this.templateFile;    }//tempFilepublic File getTempFile() {    return this.tempFile;    }//processorpublic WorkbookProcessor getProcessor() {    return this.processor;    }public void setProcessor(WorkbookProcessor processor) {    this.processor = processor;    }} 2.WorkbookProcessor接口：
package net.sf.jxls;import java.io.File;import java.io.IOException;import java.util.Map;import org.apache.poi.hssf.usermodel.HSSFWorkbook;public interface WorkbookProcessor {void processWorkbook(HSSFWorkbook templateWorkbook,File destFile,Map<String,Object> beans,Map<String,Object> services,Map<Object,Object> config) throws IOException;} 
以jxls处理Excel为例（当然可以用其它可以以Excel模板为模板，以提供的数据为参数持久化工作表【曾以ExcelUtils做过实现，可是当升级至poi3.7时，ExcelUtils存在问题】），实现WorkbookProcessor接口
 
 
package net.sf.jxls;import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import java.util.Map;import net.sf.jxls.transformer.XLSTransformer;import org.apache.poi.hssf.usermodel.HSSFWorkbook;public class XlsExcelBuilderProcessor implements WorkbookProcessor {private XLSTransformer transformer;public XlsExcelBuilderProcessor(XLSTransformer transformer){this.transformer = transformer;}public void processWorkbook(HSSFWorkbook templateWorkbook, File destFile, Map<String, Object> beans, Map<String, Object> services, Map<Object, Object> config)            throws IOException {XLSTransformer former = this.transformer;if(config!=null&&config.get(XLSTransformer.class)!=null){former =(XLSTransformer)config.get(XLSTransformer.class);}former.transformWorkbook(templateWorkbook, beans);FileOutputStream out = new FileOutputStream(destFile);templateWorkbook.write(out);out.flush();out.close();    }} 
如上我们的工作就算结束：编写测试
package net.sf.jxls;import java.io.IOException;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import net.sf.jxls.exception.ParsePropertyException;import net.sf.jxls.transformer.XLSTransformer;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;public class ExcelBuilderTest {public static void main(String[] args) throws ParsePropertyException, InvalidFormatException, IOException  {int pageSize = 5000,totalPage=4;testPage(pageSize,totalPage);        }public static void testPage(int pageSize,int totalPage) throws ParsePropertyException, InvalidFormatException, IOException{long start = System.currentTimeMillis();String templateFileName ="E:\\demo.xls";String destFileName="E:\\dest.xls";ExcelBuilder builder = new ExcelBuilder(templateFileName,destFileName,new int[]{2,2});builder.setProcessor(new XlsExcelBuilderProcessor(new XLSTransformer()));for(int i=1;i<=totalPage;i++){List<ArticleEntity> list = new ArrayList<ArticleEntity>();for(int j=1;j<=pageSize;j++){ArticleEntity t = new ArticleEntity();t.setTitle("title"+j*i);list.add(t);}Map<String,Object> beans = new HashMap<String,Object>();        beans.put("list", list);        beans.put("content", "ddd");        builder.putValues(beans);                builder.parseWorkbook(null);}                builder.create();        System.out.println("cost "+(System.currentTimeMillis()-start));}} 
经验证，在处理数据量较少的情况下，与不分页时耗相当。在处理大数据的情况下有明显优势。
 
生成文件的大致效果为图: