POI解析1997-2003、2007版本excel

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

maven引入：

sheet存储模型

import java.util.HashMap;
import java.util.Map;

/**
 * excel sheet 模型抽象
 * Created by xieyang@e6yun.com 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 xieyang@e6yun.com 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 xieyang@e6yun.com on 2016年11月2日.
 *
 */
public class ExcelCollection {

    /**
     * excel 描述
     */
    private String excelDesc;

    /**
     * 多个sheet集合
     */
    private  List<SheetCollection> sheetCollections=new ArrayList<SheetCollection>();

    /**
     * 添加一个sheet数据集合
     * Created by xieyang@e6yun.com 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 xieyang@e6yun.com on 2016年11月2日.
 *
 */
public class ExcelUtils {

    private static Logger logger=Logger.getLogger(ExcelUtils.class);

    /**
     * 解析excel
     * Created by xieyang@e6yun.com 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 xieyang@e6yun.com 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;
   }

}

常量