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 {@Overridepublic 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);}@Overridepublic 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;}@Overridepublic 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;}@Overridepublic 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);}@Overridepublic 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;}@Overridepublic String toString() {return "Category [name=" + name + ", id=" + id + "]";}}

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