POI读取Excel(兼容Excel2003、Excel2007)

<pre code_snippet_id="248011" snippet_file_name="blog_20140320_1_5046819" name="code" class="java">/**
 * ClassName:ExcelReader.java
 * Author: zgz
 * Description:Excel数据读取工具类，POI实现，兼容Excel2003，及Excel2007
 **/
package com.xxx.utils;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi2.hssf.usermodel.HSSFSheet;
import org.apache.poi2.hssf.usermodel.HSSFWorkbook;

public class ExcelReader {
  public ExcelReader() {
		super();
	}

Workbook wb = null;
  List<String[]> dataList = new ArrayList<String[]>(100);
  List<CellRangeAddress> cralist = new ArrayList<CellRangeAddress>();	//合并的单元格区域集合
  CellRangeAddress cra = null;	//合并的单元格
  int rc,rr;
  
  public Workbook getWb() {
	return wb;
  }

	public void setWb(Workbook wb) {
		this.wb = wb;
	}


  public List<CellRangeAddress> getCralist() {
	return cralist;
}

public void setCralist(List<CellRangeAddress> cralist) {
	this.cralist = cralist;
}

  public int getRc() {
	return rc;
}

public void setRc(int rc) {
	this.rc = rc;
}

public int getRr() {
	return rr;
}

public void setRr(int rr) {
	this.rr = rr;
}

public CellRangeAddress getCra() {
	return cra;
  }

	public void setCra(CellRangeAddress cra) {
		this.cra = cra;
	}

public ExcelReader(String path){
    try {
      InputStream inp = new FileInputStream(path);
      wb = WorkbookFactory.create(inp);      
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (InvalidFormatException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    }
    
  }  
  
/**
 * inputstream的構造器
 * @param is
 */
public ExcelReader(InputStream is){
    try {
      wb = WorkbookFactory.create(is);      
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (InvalidFormatException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    }
    
  }  
  /**
   * 取Excel所有数据，包含header
   * @return  List<String[]>
   */
 public List<String[]> getAllData(int sheetIndex){
    int columnNum = 0;
    Sheet sheet = wb.getSheetAt(sheetIndex);
    if(sheet.getRow(0)!=null){
        columnNum = sheet.getRow(0).getLastCellNum()-sheet.getRow(0).getFirstCellNum();
    }
    if(columnNum>0){
      for(Row row:sheet){ 
          String[] singleRow = new String[columnNum];
          int n = 0;
          for(int i=0;i<columnNum;i++){
             Cell cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK);
             switch(cell.getCellType()){
               case Cell.CELL_TYPE_BLANK:
                 singleRow[n] = "";
                 break;
               case Cell.CELL_TYPE_BOOLEAN:
                 singleRow[n] = Boolean.toString(cell.getBooleanCellValue());
                 break;
                //数值
               case Cell.CELL_TYPE_NUMERIC:               
                 if(DateUtil.isCellDateFormatted(cell)){
                   singleRow[n] = String.valueOf(cell.getDateCellValue());
                 }else{ 
                   cell.setCellType(Cell.CELL_TYPE_STRING);
                   String temp = cell.getStringCellValue();
                   //判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串
                   if(temp.indexOf(".")>-1){
                     singleRow[n] = String.valueOf(new Double(temp)).trim();
                   }else{
                     singleRow[n] = temp.trim();
                   }
                 }
                 break;
               case Cell.CELL_TYPE_STRING:
                 singleRow[n] = cell.getStringCellValue().trim();
                 break;
               case Cell.CELL_TYPE_ERROR:
                 singleRow[n] = "";
                 break;  
               case Cell.CELL_TYPE_FORMULA:
                 cell.setCellType(Cell.CELL_TYPE_STRING);
                 singleRow[n] = cell.getStringCellValue();
                 if(singleRow[n]!=null){
                   singleRow[n] = singleRow[n].replaceAll("#N/A","").trim();
                 }
                 break;  
               default:
                 singleRow[n] = "";
                 break;
             }
             n++;
          } 
          if("".equals(singleRow[0])){continue;}//如果第一行为空，跳过
          dataList.add(singleRow);
      }
    }
    return dataList;
  }  
  /**
   * 返回Excel最大行index值，实际行数要加1
   * @return
   */
  public int getRowNum(int sheetIndex){
    Sheet sheet = wb.getSheetAt(sheetIndex);
    return sheet.getLastRowNum();
  }
  
  /**
   * 返回数据的列数
   * @return 
   */
  public int getColumnNum(int sheetIndex){
    Sheet sheet = wb.getSheetAt(sheetIndex);
    Row row = sheet.getRow(0);
    if(row!=null&&row.getLastCellNum()>0){
       return row.getLastCellNum();
    }
    return 0;
  }
  
  /**
   * 获取某一行数据
   * @param rowIndex 计数从0开始，rowIndex为0代表header行
   * @return
   */
    public String[] getRowData(int sheetIndex,int rowIndex){
      String[] dataArray = null;
      if(rowIndex>this.getColumnNum(sheetIndex)){
        return dataArray;
      }else{
        dataArray = new String[this.getColumnNum(sheetIndex)];
        return this.dataList.get(rowIndex);
      }
      
    }
  
  /**
   * 获取某一列数据
   * @param colIndex
   * @return
   */
  public String[] getColumnData(int sheetIndex,int colIndex){
    String[] dataArray = null;
    if(colIndex>this.getColumnNum(sheetIndex)){
      return dataArray;
    }else{   
      if(this.dataList!=null&&this.dataList.size()>0){
        dataArray = new String[this.getRowNum(sheetIndex)+1];
        int index = 0;
        for(String[] rowData:dataList){
          if(rowData!=null){
             dataArray[index] = rowData[colIndex];
             index++;
          }
        }
      }
    }
    return dataArray;
    
  }
  
  /*
   * 获取所有的合并单元格区域
   */
  public void getRegionlist(){
	  Sheet sheet = wb.getSheetAt(0);
	  for(int i = 0; i < sheet.getNumMergedRegions(); i++){
		  CellRangeAddress rg = sheet.getMergedRegion(i);
		  cralist.add(rg);
	  }
  }
  
  public String getRowColumnLength(int r, int c){
	  for(int i=0; i<cralist.size(); i++){
		  if(cralist.get(i).isInRange(r, c)){
//			  System.out.println(r+","+c+"是合并的单元格");
			  cra = cralist.get(i);
			  int temp1,temp2;
			  temp1 = cra.getLastRow() - cra.getFirstRow();
			  temp2 = cra.getLastColumn() - cra.getFirstColumn();
			  return temp1 + 1 +"," + temp2 + 1;
		  }
	  }
	  return null;
  }
  
  /*
   *判断某个单元格是否是合并单元格 
   */
  public boolean checkIsRange(int r, int c){
	  for(int i=0; i<cralist.size(); i++){
		  if(cralist.get(i).isInRange(r, c)){
//			  System.out.println(r+","+c+"是合并的单元格");
			  cra = cralist.get(i);
			  rc = cra.getFirstColumn();
			  rr = cra.getFirstRow();
			  return true;
		  }
	  }
//	  System.out.println(r+","+c+"不是合并的单元格");
	  return false;
  }
  
 }