Excel文件模板导出,记录分页追加 ExcelApache.netSVN化工
程序员文章站
2022-06-06 15:07:43
...
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[i]; 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; } // resultFile public File getResultFile() { return this.resultFile; } //templateFile public File getTemplateFile() { return this.templateFile; } //tempFile public File getTempFile() { return this.tempFile; } //processor public 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)); } }
经验证,在处理数据量较少的情况下,与不分页时耗相当。在处理大数据的情况下有明显优势。
生成文件的大致效果为图: