POI实现一个通用的Excel读取模板

    POI是Apache基金会的提供的java实现的一套用于读取Excel、Word、PPT等文档的库，在实际项目中可能 很多地方都会用到Excel的读取，比如Excel的导入，我们不可能每个地方都单独实现一套Excel的读取方法，这时候就需要封装一个通用的类，只要有读取Excel的地方就调用该方法就可以了，下面我们就来实现它。

    首先，为了提高可扩展性，也为了方便扩展到譬如Word、PPT这类文档，我们需要抽象出一个Template类，通过工厂方法来构建不同的Template。

    然后，实现每个不同的Template，在实现过程中，我们还可以设置Listener，这样只要实现了该监听器，就能得到当前读取到的值，方便扩展。

    OK，下面请看具体的代码：

   

import java.io.InputStream;
import java.io.Serializable;
import java.util.List;

import cn.sunsharp.poi.bean.Filter;

/**
 * The template of POI.
 * All template must inherit this class.
 * @author lynn
 *
 */
public abstract class Template {

	protected List<Filter> filters;
	protected boolean ignore = false;
	
	public void setFilters(List<Filter> filters) {
		this.filters = filters;
	}
	
	public List<Filter> getFilters() {
		return filters;
	}
	
	public boolean isIgnore() {
		return ignore;
	}

	public void setIgnore(boolean ignore) {
		this.ignore = ignore;
	}

	/**
	 * parse the excel、ppt、word etc.
	 * it is a abstract method.
	 * you can implement it.
	 * @param inputStream
	 * @return
	 * @throws Exception
	 */
	public abstract List<Serializable> parse(InputStream inputStream)throws Exception;
	
	public abstract void parse(InputStream inputStream,TemplateListener listener)throws Exception;
}

import java.io.InputStream;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import cn.sunsharp.poi.bean.Excel;
import cn.sunsharp.poi.bean.ExcelFilter;
import cn.sunsharp.poi.bean.Filter;

class ExcelTemplate extends Template {

	@Override
	public List<Serializable> parse(InputStream inputStream) throws Exception {
		OPCPackage pkg = null;
		List<Serializable> list = new ArrayList<Serializable>();
		try{
			pkg = OPCPackage.open(inputStream);
			XSSFWorkbook workbook = new XSSFWorkbook(pkg);
			int sheetNum = workbook.getNumberOfSheets();
			XSSFSheet sheet = null;
			XSSFRow row = null;
			XSSFCell cell = null;
			Excel excel = null;
			for (int i = 0; i < sheetNum; i++) {
				sheet = workbook.getSheetAt(i);
				if(null == sheet){
					continue;
				}
				for (int j = 0,rowNum = sheet.getLastRowNum(); j <= rowNum; j++) {
					row = sheet.getRow(j);
					if(null == row){
						continue;
					}
					for (int k = 0,cellNum = row.getLastCellNum(); k <= cellNum; k++) {
						cell = row.getCell(k);
						if(null == cell){
							continue;
						}
						excel = new Excel();
						excel.setSheetName(sheet.getSheetName());
						excel.setSheetIndex(i);
						excel.setCell(k);
						excel.setRow(j);
						excel.setValue(cell.toString());
						list.add(excel);
					}
				}
			}
		}finally{
			if(null != pkg){
				pkg.close();
			}
		}
		return filterData(list);
	}

	@Override
	public void parse(InputStream inputStream,
			TemplateListener listener) throws Exception {
		if(null == listener){
			throw new NullPointerException("listener must not be null.");
		}
		List<Serializable> data = parse(inputStream);
		int size = 0;
		if(null != data && (size = data.size()) > 0){
			Excel excel = null;
			for (int i = 0; i < size; i++) {
				excel = (Excel)data.get(i);
				listener.callback(excel);
			}
		}
	}
	
	/**
	 * ignore为true，则不读取filter指定的数据，为false则读取filter指定的数据
	 * filter为空，则ignore参数失效，即读取所有数据
	 * filter参数中,sheetIndex为空，则读取所有sheet，row为空，则读取所有row,cell为空，则读取所有cell
	 */
	private List<Serializable> filterData(List<Serializable> list){
		List<Serializable> data = new ArrayList<Serializable>();
		int size = 0;
		if(null != list && (size = list.size()) > 0){
			Excel excel = null;
			if(null != filters && filters.size() > 0){
				for (int i = 0; i < size; i++) {
					excel = (Excel)list.get(i);
					for (Filter item : filters) {
						ExcelFilter filter = (ExcelFilter)item;
						Integer sheetIndex = filter.getSheetIndex();
						Integer row = filter.getRow();
						Integer cell = filter.getCell();
						if(ignore){
							if(null != sheetIndex && sheetIndex.intValue() == excel.getSheetIndex()){
								continue;
							}
							if(null != row && row.intValue() == excel.getRow()){
								continue;
							}
							if(null != cell && cell.intValue() == excel.getCell()){
								continue;
							}
							data.add(excel);
						}else{
							//如果三个都有值
							if(null != sheetIndex && null != row && null != cell){
								if(sheetIndex.intValue() == excel.getSheetIndex() && row.intValue() == excel.getRow() && cell.intValue() == excel.getCell()){
									data.add(excel);
								}
							}
							//如果sheetIndex没有值，row和cell有值
							if(null == sheetIndex && null != row && null != cell){
								if(row.intValue() == excel.getRow() && cell.intValue() == excel.getCell()){
									data.add(excel);
								}
							}
							//如果row没有值，sheetIndex和cell有值
							if(null == row && null != sheetIndex && null != cell){
								if(sheetIndex.intValue() == excel.getSheetIndex() && cell.intValue() == excel.getCell()){
									data.add(excel);
								}
							}
							//如果cell没有值，sheetIndex和row有值
							if(null == cell && null != sheetIndex && null != row){
								if(sheetIndex.intValue() == excel.getSheetIndex() && row.intValue() == excel.getRow()){
									data.add(excel);
								}
							}
							//如果只有sheetIndex有值
							if(null == row && null == cell && null != sheetIndex){
								if(sheetIndex.intValue() == excel.getSheetIndex()){
									data.add(excel);
								}
							}
							//如果只有row有值
							if(null == cell && null == sheetIndex && null != row){
								if(row.intValue() == excel.getRow()){
									data.add(excel);
								}
							}
							//如果只有cell有值
							if(null == sheetIndex && null == row && null != cell){
								if(cell.intValue() == excel.getCell()){
									data.add(excel);
								}
							}
						}
					}
				}
			}else{
				data.addAll(list);
			}
		}
		return data;
	}

}

import java.io.Serializable;

/**
 * template listener
 * you can implement this interface to push to your custom class.
 * @author lynn
 *
 */
public interface TemplateListener {

	public void callback(Serializable entity);
	
}

import cn.sunsharp.poi.enumeration.POI;

public class TemplateFactory {

	/**
	 * via the factory to create template
	 * @param poi
	 * @return
	 */
	public static Template create(POI poi){
		switch(poi){
		case Excel:
			return new ExcelTemplate();
		case PPT:
			break;
		case Word:
			break;
		case PDF:
			break;
		}
		return null;
	}
}

public enum POI {

	Excel,PPT,Word,PDF
}

import java.io.Serializable;

/**
 * the excel bean.
 * @author administrator
 *
 */
public class Excel implements Serializable {

	private static final long serialVersionUID = -7606795591653370811L;

	private String sheetName;
	
	private int sheetIndex;
	
	private int row;
	
	private int cell;
	
	private String value;

	public String getSheetName() {
		return sheetName;
	}

	public void setSheetName(String sheetName) {
		this.sheetName = sheetName;
	}

	public int getSheetIndex() {
		return sheetIndex;
	}

	public void setSheetIndex(int sheetIndex) {
		this.sheetIndex = sheetIndex;
	}

	public int getRow() {
		return row;
	}

	public void setRow(int row) {
		this.row = row;
	}

	public int getCell() {
		return cell;
	}

	public void setCell(int cell) {
		this.cell = cell;
	}
	
	public String getValue() {
		return value;
	}
	
	public void setValue(String value) {
		this.value = value;
	}

	@Override
	public String toString() {
		return "Excel [sheetName=" + sheetName + ", sheetIndex=" + sheetIndex
				+ ", row=" + row + ", cell=" + cell + ", value=" + value + "]";
	}

}

public class Filter {

}

public class ExcelFilter extends Filter {

	private Integer sheetIndex;
	
	private Integer row;
	
	private Integer cell;
	
	public static Builder options(){
		return new Builder();
	}
	
	public static class Builder{
		
		private Integer sheetIndex;
		
		private Integer row;
		
		private Integer cell;
		
		public Builder setSheetIndex(Integer sheetIndex){
			this.sheetIndex = sheetIndex;
			return this;
		}
		
		public Builder setRow(Integer row){
			this.row = row;
			return this;
		}
		
		public Builder setCell(Integer cell){
			this.cell = cell;
			return this;
		}
		
		public ExcelFilter build(){
			return new ExcelFilter(this);
		}
	}
	
	private ExcelFilter(Builder builder){
		this.sheetIndex = builder.sheetIndex;
		this.row = builder.row;
		this.cell = builder.cell;
	}
	
	public Integer getSheetIndex() {
		return sheetIndex;
	}
	
	public Integer getRow() {
		return row;
	}
	
	public Integer getCell() {
		return cell;
	}

	@Override
	public String toString() {
		return "ExcelFilter [sheetIndex=" + sheetIndex + ", row=" + row
				+ ", cell=" + cell + "]";
	}
	
}

通过以上代码，我们发现，在Template有Filter和ignore两个属性，Filter是过滤器，ignore如果为true，则不读取filter指定的数据，ignore为false，则只读取filter指定的数据，

这样就完成了比较优雅的可扩展的Excel读取，我们如果要读取PPT，就构建一个PPTTemplate来实现Template抽象类即可。下面请看测试类：

import java.io.FileInputStream;
import java.io.InputStream;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;

import cn.sunsharp.poi.bean.Excel;
import cn.sunsharp.poi.bean.ExcelFilter;
import cn.sunsharp.poi.bean.Filter;
import cn.sunsharp.poi.enumeration.POI;
import cn.sunsharp.poi.template.Template;
import cn.sunsharp.poi.template.TemplateFactory;
import cn.sunsharp.poi.template.TemplateListener;

public class Test implements TemplateListener{

	public static void main(String[] args) throws Exception{
		new Test().testExcel();
	}
	
	private List<String> headers = new ArrayList<String>();
	
	private void testExcel()throws Exception{
		String path = "C:\\Users\\administrator.YCSPC017\\Desktop\\农产品行业体系新旧编码对比.xlsx";
		Template template = TemplateFactory.create(POI.Excel);
		List<Filter> filters = new ArrayList<Filter>();
		ExcelFilter filter = ExcelFilter.options()
				.setRow(0)
				.setSheetIndex(2)
				.build();
		filters.add(filter);
//		filter = ExcelFilter.options()
//				.setRow(1)
//				.setSheetIndex(1)
//				.setCell(1)
//				.build();
//		filters.add(filter);
		template.setFilters(filters);
		template.setIgnore(false);
		InputStream inputStream = new FileInputStream(path);
		template.parse(inputStream,this);
		System.out.println(headers);
	}

	@Override
	public void callback(Serializable entity){
		Excel excel = (Excel)entity;
		headers.add(excel.getValue());
	}
	
}

import java.io.Serializable;

public class Category implements Serializable{
	
	private static final long serialVersionUID = 2348166129263260641L;
	private String name;
	
	private String id;
	
	public String getId() {
		return id;
	}
	
	public void setId(String id) {
		this.id = id;
	}
	
	public String getName() {
		return name;
	}
	
	public void setName(String name) {
		this.name = name;
	}

	@Override
	public String toString() {
		return "Category [name=" + name + ", id=" + id + "]";
	}

}

如果有问题，可以给我留言哦！