欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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));
	}
	

}

 

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

 

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

Excel文件模板导出,记录分页追加
            
    
    
        ExcelApache.netSVN化工

 

  • Excel文件模板导出,记录分页追加
            
    
    
        ExcelApache.netSVN化工
  • 大小: 5.7 KB
  • Excel文件模板导出,记录分页追加
            
    
    
        ExcelApache.netSVN化工
  • 大小: 5.3 KB