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

  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:[e-mail])
 * @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));
	}
	

}

 

经验证，在处理数据量较少的情况下，与不分页时耗相当。在处理大数据的情况下有明显优势。

 

生成文件的大致效果为图:

 

评论

1 楼 jccmjl 2012-06-28  

朋友在吗？就是我现在做一个excel功能，遇见了分页的问题，能否在百忙中抽出时间来我请教下，可以 的话加我QQ452276647