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:[email protected])
* @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));
}
}
经验证,在处理数据量较少的情况下,与不分页时耗相当。在处理大数据的情况下有明显优势。
生成文件的大致效果为图: