[joy]POI导出Excel简单扩展（一）

学习实践

 

目标：

1、简化POI导出Excel的调用，方便使用（只提供数据即可）。

2、具有一定的扩展性（整体默认样式、单元格样式）。

3、对一些设计模式的具体实践。

 

技术要求：

对POI进行扩展。

模仿JSONObject(JsonValueProcessor)、easyui的datagrid(formatter/styler)。

模板方法模式、桥接模式。

 

涉及的类：

ExportGenerator	核心类	导出具体实现类，子类可对其进行扩展
ExportConfig	核心类	用于保存导出相关配置
CellValueProcessor	核心接口	单元格处理器接口
YearReportExportGenerator	扩展类	对ExportGenerator的扩展，提供一些基础样式

 

 

 整个程序的执行过程：

 

具体代码：

/**
 * 该类只提供生成workbook、填充数据到workbook 
 * 子类可对支持的数据格式进行拓展 子类可在生成前后加逻辑进行拓展
 * 
 * 支持的数据格式： List<Map>、List<DynaBean>、List<List> 
 * 其他数据格式可自己扩展 Collention<?> + ?[]
 * (Collention各种+数组各种) SimpleExportGenerator --> 简单导出，支持常见的格式
 * ComplexExportGenerator(implements simple) --> 复杂导出，支持Collention<?>
 * 
 * 生成workbook执行顺序(子类可重写) 
 * 1、postProcessBeforeGenerate() 
 * 2、generate()
 * 3、postProcessAfterGenerate()
 */
public class ExportGenerator {
	private List<Map> dataList; //所要导出的数据

	private HSSFWorkbook workBook; //workBook实体类

	private ExportConfig exportConfig; //导出配置信息

	public ExportGenerator() {
		this(new ArrayList<Map>());
	};
	public ExportGenerator(List<Map> dataList) {
		this(dataList==null?new ArrayList<Map>():dataList, new ExportConfig());
	};
	public ExportGenerator(List<Map> dataList, ExportConfig exportConfig) {
		this.dataList = dataList==null?new ArrayList<Map>():dataList;
		this.exportConfig = exportConfig==null?new ExportConfig():exportConfig;
	};

	public ExportConfig getExportConfig() {
		return this.exportConfig;
	}
	public void setExportConfig(ExportConfig exportConfig) {
		this.exportConfig = exportConfig;
	}

	public List<Map> getDataList() {
		return this.dataList;
	};
	public void setDataList(List<Map> dataList) {
		this.dataList = dataList;
	};
	
	public HSSFWorkbook getWorkBook() {
		return workBook;
	}
	public void setWorkBook(HSSFWorkbook workBook) {
		this.workBook = workBook;
	}

	/**
	 * 获取 HSSFWorkbook 的源头 子类可以进行拓展
	 */
	protected HSSFWorkbook initWorkbook() {
		return new HSSFWorkbook();
	}

	/**
	 * 生成excel模板方法 主要确定方法执行顺序
	 */
	public void generateWorkBook() {
		workBook = initWorkbook();
		postProcessBeforeGenerate(dataList, workBook);
		generate(dataList, workBook);
		postProcessAfterGenerate(dataList, workBook);
	}

	/**
	 * 填充数据前调用该方法
	 */
	public void postProcessBeforeGenerate(List<Map> dataList,HSSFWorkbook workBook) {};

	/**
	 * 填充完数据后调用该方法
	 */
	public void postProcessAfterGenerate(List<Map> dataList,HSSFWorkbook workBook) {};

	/**
	 * 获取sheet
	 */
	protected HSSFSheet getOrCreateSheet(int index,HSSFWorkbook workBook){
		if(workBook.getNumberOfSheets()>0){
			return workBook.getSheetAt(0);
		}
		return workBook.createSheet();
	}
	
	/**
	 * 具体生成workBook方法
	 */
	protected void generate(List<Map> dataList, HSSFWorkbook workBook) {
		HSSFSheet sheet = getOrCreateSheet(0,workBook);
		int rowIndex = exportConfig.getRowStartIndex(), colIndex = exportConfig.getColStartIndex();
		for (Map m : dataList) {
			HSSFRow row = sheet.createRow(rowIndex);
			for (Object k : m.keySet()) {
				HSSFCell cell = row.createCell(colIndex);
				Object value = processCellValue(m.get(k), row, cell);
				setValue(cell, value);
				cell.setCellStyle(processCellStyle(value, row, cell));
				colIndex++;
			}
			colIndex = 0;
			rowIndex++;
		}
	}

	/**
	 * 对poi中的cell进行赋值
	 */
	protected void setValue(HSSFCell cell, Object value) {
		if (null == value) {
			cell.setCellValue((java.lang.String) value);//如果值为null的话，poi自己会处理
		} else if (value instanceof java.lang.String) {
			cell.setCellValue((java.lang.String) value);
		} else if (value instanceof java.lang.Double) {
			cell.setCellValue((double) value);
		} else if (value instanceof java.lang.Float) {
			cell.setCellValue((float) value);
		} else if (value instanceof java.lang.Long) {
			cell.setCellValue((long) value);
		} else if (value instanceof java.lang.Integer) {
			cell.setCellValue((int) value);
		} else if (value instanceof java.lang.Short) {
			cell.setCellValue((short) value);
		} else if (value instanceof java.lang.Boolean) {
			cell.setCellValue((boolean) value);
		} else if (value instanceof java.util.Date) {
			cell.setCellValue((java.util.Date) value);
		} else if (value instanceof java.util.Calendar) {
			cell.setCellValue((java.util.Calendar) value);
		} else if (value instanceof org.apache.poi.ss.usermodel.RichTextString) {
			cell.setCellValue((org.apache.poi.ss.usermodel.RichTextString) value);
		} else {
			cell.setCellValue(value.toString());// 无匹配类型按String处理
		}
	}

	/**
	 * 根据Processor获取值
	 */
	protected Object processCellValue(Object obj, HSSFRow row, HSSFCell cell) {
		CellValueProcessor cvp = exportConfig.getCellValueProcessor();
		if (null == cvp) {
			return obj;
		}
		return cvp.processCellValue(obj, row, cell);
	}

	/**
	 * 根据Processor获取值 返回空不影响已有样式
	 */
	protected CellStyle processCellStyle(Object value, HSSFRow row,HSSFCell cell) {
		CellValueProcessor cvp = exportConfig.getCellValueProcessor();
		if (null == cvp) {
			return null;
		}
		return cvp.processCellStyle(value, row, cell);
	}

	public HSSFWorkbook getWorkBookInstance() {
		return getWorkBookInstance(false);
	}

	/**
	 * 向外部提供获取 HSSFWorkbook 的方法 refresh 是否重新生成HSSFWorkbook
	 */
	public HSSFWorkbook getWorkBookInstance(boolean refresh) {
		if (refresh || null == workBook) {
			generateWorkBook();
		}
		return workBook;
	}
}

 

/**
 * 导出配置，方便传递各种参数
 */
public class ExportConfig {
	
	/**
	 * 填充数据的开始行坐标
	 */
	private int rowStartIndex=0;
	
	/**
	 * 填充数据的开始列坐标
	 */
	private int colStartIndex=0;
	
	/**
	 * 填充单元格时的值处理器
	 */
	CellValueProcessor cellValueProcessor;

	public CellValueProcessor getCellValueProcessor(){
		return cellValueProcessor;
	}
	public void setCellValueProcessor(CellValueProcessor cellValueProcessor){
		this.cellValueProcessor = cellValueProcessor;
	}

	public int getRowStartIndex() {
		return rowStartIndex;
	}
	public void setRowStartIndex(int rowStartIndex) {
		this.rowStartIndex = rowStartIndex;
	}

	public int getColStartIndex() {
		return colStartIndex;
	}
	public void setColStartIndex(int colStartIndex) {
		this.colStartIndex = colStartIndex;
	}
}

 

/**
 * 值产生器接口
 */
public interface CellValueProcessor {
	
	Object processCellValue(Object value,HSSFRow row,HSSFCell cell);
	
	CellStyle processCellStyle(Object value,HSSFRow row,HSSFCell cell);
}

 

简单扩展：

public class YearReportExportGenerator extends ExportGenerator{
	public YearReportExportGenerator(List<Map> dataList) {
		super(dataList);
	}
	public YearReportExportGenerator(List<Map> dataList, ExportConfig exportConfig) {
		super(dataList, exportConfig);
	}
	
	/**
	 * 添加表头数据
	 */
	@Override
	public void postProcessBeforeGenerate(List<Map> dataList,HSSFWorkbook workBook) {
		createHead();
	}
	@Override
	public void postProcessAfterGenerate(List<Map> dataList,HSSFWorkbook workBook) {
		createBottom();
		createBorder();
	}
	
	/**
	 * 生成一个简易的表头
	 */
	public void createHead(){
		HSSFSheet sheet = getWorkBook().createSheet();
		HSSFRow row = sheet.createRow(0);
		HSSFCell cell = row.createCell(0);
		row.setHeight((short)500);
		cell.setCellValue("2015年XXX全年报表");
		for(int i=1;i<13;i++){
			row.createCell(i);
		}
		sheet.addMergedRegion(new CellRangeAddress(0,0,0,12));
		HSSFCellStyle style = getWorkBook().createCellStyle();
		HSSFFont font = getWorkBook().createFont();
		font.setBold(true);
		font.setFontHeightInPoints((short)16);
		style.setFont(font);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cell.setCellStyle(style);
		
		HSSFRow row2 = sheet.createRow(1);
		HSSFCellStyle centerStyle = getWorkBook().createCellStyle();
		centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		for(int i=0;i<13;i++){
			HSSFCell c = row2.createCell(i);
			if(i>0){
				c.setCellValue(i+"月");
				c.setCellStyle(centerStyle);
			}
		}
		ExportConfig cfg = getExportConfig();
		cfg.setRowStartIndex(cfg.getRowStartIndex()+2);
	}
	
	/**
	 * 添加边框
	 */
	public void createBorder(){
		HSSFSheet sheet = getWorkBook().getSheetAt(0);
		int rows = sheet.getLastRowNum();
		HSSFCellStyle style;
		for(int i=0;i<rows+1;i++){
			HSSFRow row = sheet.getRow(i);
			Iterator<Cell> iter = row.cellIterator();
			while(iter.hasNext()){
				Cell c = iter.next();
				style = row.getSheet().getWorkbook().createCellStyle();
				style.cloneStyleFrom(c.getCellStyle());
				style.setBorderTop((short)1);
				style.setBorderBottom((short)1);
				style.setBorderLeft((short)1);
				style.setBorderRight((short)1);
				c.setCellStyle(style);
			}
		}
	}
	
	/**
	 * 创建底部信息
	 */
	public void createBottom(){
		HSSFSheet sheet = getWorkBook().getSheetAt(0);
		int rows = sheet.getLastRowNum();
		HSSFRow row = sheet.createRow(rows+1);
		for(int i=1;i<13;i++){
			row.createCell(i);
		}
		Cell c = row.createCell(0);
		c.setCellValue("报告人：多啦a芊  报告日期：2015-12-17");
		HSSFCellStyle style = row.getSheet().getWorkbook().createCellStyle();
		style.cloneStyleFrom(c.getCellStyle());
		style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		c.setCellStyle(style);
		sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(),row.getRowNum(),0,12));
	}
}

 

以下为调用端代码：

public class Client {
	public static void main(String[] args) throws IOException {
		test01();
		test02();
		test03();
	}
	
	/**
	 * 只导出数据Excel
	 */
	public static void test01() throws IOException{
		List<Map> l = getList();
		ExportGenerator generator = new ExportGenerator(l);
		HSSFWorkbook workBook = generator.getWorkBookInstance();
		FileOutputStream out = new FileOutputStream("D:\\excel1.xls");
		workBook.write(out);
	}
	
	/**
	 * 导出带表头、带边框的Excel
	 */
	public static void test02() throws IOException{
		List<Map> l = getList();
		ExportGenerator generator = new YearReportExportGenerator(l);
		HSSFWorkbook workBook = generator.getWorkBookInstance();
		FileOutputStream out = new FileOutputStream("D:\\excel2.xls");
		workBook.write(out);
	}
	
	/**
	 * 导出带表头、带边框和行样式的Excel
	 */
	public static void test03() throws IOException{
		List<Map> l = getList();
		CellValueProcessor cvp = new CellValueProcessor(){
			@Override
			public Object processCellValue(Object value, HSSFRow row,HSSFCell cell) {
				return value==null?"-":value;
			}
			@Override
			public CellStyle processCellStyle(Object value, HSSFRow row,HSSFCell cell) {
				HSSFCellStyle style = null;
				if(value instanceof Number){
					int v = (int)value;
					if(v<0){
						style = row.getSheet().getWorkbook().createCellStyle();
						style.cloneStyleFrom(cell.getCellStyle());
						style.setFillForegroundColor(HSSFColor.RED.index);
						style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
					}else if(v>100){
						style = row.getSheet().getWorkbook().createCellStyle();
						style.cloneStyleFrom(cell.getCellStyle());
						style.setFillForegroundColor(HSSFColor.BRIGHT_GREEN.index);
						style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
					}
				}else{
					style = row.getSheet().getWorkbook().createCellStyle();
					style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
				}
				return style;
			}
		};
		ExportConfig cfg = new ExportConfig();
		cfg.setCellValueProcessor(cvp);
		ExportGenerator generator = new YearReportExportGenerator(l,cfg);
		HSSFWorkbook workBook = generator.getWorkBookInstance();
		FileOutputStream out = new FileOutputStream("D:\\excel3.xls");
		workBook.write(out);
	}
	
	/**
	 * 获取假数据
	 */
	public static List<Map> getList(){
		List<Map> l = new ArrayList<Map>();
		Random r = new Random();
		Map m;
		for(int i=0;i<10;i++){
			m = new LinkedMap();
			m.put("name", "指标"+i);
			for(int j=0;j<12;j++){
				if(r.nextInt(20)<1){
					m.put(j, null);
				}else{
					m.put(j, r.nextInt(120)-10);	
				}
			}
			l.add(m);
		}
		return l;
	}
}

 

test01()生成结果：

 

test02()生成结果：

 

test03()生成结果：