POI解析1997-2003、2007版本excel

本文就poi解析1997-2003/2007版本进行总结，走统一接口。

maven引入：

sheet存储模型

import java.util.HashMap;import java.util.Map;/** * excel sheet 模型抽象 * Created by [e-mail] on 2016年11月2日. * */public class SheetCollection {    /**     *sheet 名称      */    private String sheetName;    /**     * key :行号     * value: [key:名称;value:单元格的值]     */    private Map<String, Map<String, String>> sheetMapCollection=new HashMap<String, Map<String,String>>();    /**     * sheet 集合中新增一行     * Created by [e-mail] on 2016年11月2日.     * @param rowId 行号     * @param rowValues 行中单元格集合     */    public void insertRow(String rowId,Map<String, String> rowValues){        sheetMapCollection.put(rowId, rowValues);    }    public String getSheetName() {        return sheetName;    }    public void setSheetName(String sheetName) {        this.sheetName = sheetName;    }    public Map<String, Map<String, String>> getSheetMapCollection() {        return sheetMapCollection;    }    public void setSheetMapCollection(            Map<String, Map<String, String>> sheetMapCollection) {        this.sheetMapCollection = sheetMapCollection;    }    @Override    public String toString() {        return "SheetCollection [sheetName=" + sheetName                + ", sheetMapCollection=" + sheetMapCollection + "]";    }}

excel存储模型，多个sheet集合

import java.util.ArrayList;import java.util.List;/** * excel sheet集合 * Created by [e-mail] on 2016年11月2日. * */public class ExcelCollection {    /**     * excel 描述     */    private String excelDesc;    /**     * 多个sheet集合     */    private  List<SheetCollection> sheetCollections=new ArrayList<SheetCollection>();    /**     * 添加一个sheet数据集合     * Created by [e-mail] on 2016年11月2日.     * @param sheetCollection     */    public void insertSheet(SheetCollection sheetCollection){        sheetCollections.add(sheetCollection);    }    public String getExcelDesc() {        return excelDesc;    }    public void setExcelDesc(String excelDesc) {        this.excelDesc = excelDesc;    }    public List<SheetCollection> getSheetCollections() {        return sheetCollections;    }    public void setSheetCollections(List<SheetCollection> sheetCollections) {        this.sheetCollections = sheetCollections;    }    @Override    public String toString() {        return "ExcelCollection [excelDesc=" + excelDesc                + ", sheetCollections=" + sheetCollections + "]";    }}

excel 解析助手

import java.io.FileInputStream;import java.io.IOException;import java.util.HashMap;import java.util.Map;import org.apache.log4j.Logger;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import com.e6yun.routeOptimize.common.Constants;import com.e6yun.routeOptimize.utils.excel.model.ExcelCollection;import com.e6yun.routeOptimize.utils.excel.model.SheetCollection;/** * excel助手 * excel 03,07版本解析与写入 * Created by [e-mail] on 2016年11月2日. * */public class ExcelUtils {    private static Logger logger=Logger.getLogger(ExcelUtils.class);    /**     * 解析excel     * Created by [e-mail] on 2016年11月2日.     * @param fileName     * @return     */    public static ExcelCollection parseExcel(String fileName){        ExcelCollection excelCollection=null;        try {            //excel Poi顶层抽象            Workbook workbook=null;            if(fileName.endsWith(Constants.EXCEL_03_SURFIX)){                workbook=new HSSFWorkbook(new FileInputStream(fileName));            }else if(fileName.endsWith(Constants.EXCEL_07_SURFIX)){                workbook=new XSSFWorkbook(fileName);            }else{                return excelCollection;            }            //解析excel            excelCollection=getExcelFromWorkBook(workbook);            //关闭excel文件释放资源            workbook.close();        } catch (IOException e) {            logger.error(String.format("Parse Excel [%s] fail!,The Reason Is [%s]", fileName,e));        }        return excelCollection;    }    /**     * 从WorkBook中获取数据     * Created by [e-mail] on 2016年11月2日.     * @param workbook     * @return     */   private static ExcelCollection getExcelFromWorkBook(Workbook workbook){       ExcelCollection excelCollection=new ExcelCollection();       //获取sheet数量       int sheetNums=workbook.getNumberOfSheets();       //遍历sheet       for (int i=0;i<sheetNums;i++) {           Sheet sheet=workbook.getSheetAt(i);           SheetCollection sheetCollection=new SheetCollection();           //获取sheet名称           String sheetName=sheet.getSheetName();           sheetCollection.setSheetName(sheetName);           //sheet中总行数           int rowNums=sheet.getLastRowNum();           Map<String, String> tempTitleMap=new HashMap<String, String>();           for (int rowIndex = 0; rowIndex < rowNums; rowIndex++) {              Map<String, String> rowMap=new HashMap<String, String>();              Row row=sheet.getRow(rowIndex);              //获取每行的列数              short columnNums=row.getLastCellNum();              for (int columnIndex = 0; columnIndex < columnNums; columnIndex++) {                  Cell cell=row.getCell(columnIndex);                  if(cell!=null){                      cell.setCellType(HSSFCell.CELL_TYPE_STRING);                      if(rowIndex==0){                          //加入到临时表头中                          tempTitleMap.put(columnIndex+"", cell.getStringCellValue());                      }                      rowMap.put(tempTitleMap.get(columnIndex+""), cell.getStringCellValue());                  }              }              sheetCollection.insertRow(rowIndex+"", rowMap);           }           excelCollection.insertSheet(sheetCollection);       }       return excelCollection;   }}

常量