poi读Excel2003兼容2007总结

 

API:http://poi.apache.org/apidocs/index.html

1、

/**
 * ClassName:ExcelReader.java
 * Author: wenbin.ji
 * CreateTime: Jan 28, 2011 11:16:29 AM
 * Description:Excel数据读取工具类，POI实现，兼容Excel2003，及Excel2007
 **/
package com.company.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.ss.usermodel.FormulaEvaluator;
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;

public class ExcelReader {
  Workbook wb = null;
  List<String[]> dataList = new ArrayList<String[]>(100);
  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();
    }
    
  }  
  
  /**
   * 取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);// 没有这句的话，碰到Number报错：Cannot get a text value from a numeric cell
                   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;
    
  }
 }

参考：http://blog.csdn.net/jack0511/article/details/6179593

 

2、

package poi;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
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;
 
public class ReadExcel001 {
	public static void main(String[] args) {
		readXml("D:/test.xlsx");
		System.out.println("-------------");
		readXml("d:/test2.xls");
 	}
	public static void readXml(String fileName){
		boolean isE2007 = false;	//判断是否是excel2007格式
		if(fileName.endsWith("xlsx"))
			isE2007 = true;
		try {
			InputStream input = new FileInputStream(fileName);	//建立输入流
			Workbook wb  = null;
			//根据文件格式(2003或者2007)来初始化
			if(isE2007)
				wb = new XSSFWorkbook(input);
			else
				wb = new HSSFWorkbook(input);
			Sheet sheet = wb.getSheetAt(0);		//获得第一个表单
			Iterator<Row> rows = sheet.rowIterator();	//获得第一个表单的迭代器
			while (rows.hasNext()) {
				Row row = rows.next();	//获得行数据
				System.out.println("Row #" + row.getRowNum());	//获得行号从0开始
				Iterator<Cell> cells = row.cellIterator();	//获得第一行的迭代器
				while (cells.hasNext()) {
					Cell cell = cells.next();
					System.out.println("Cell #" + cell.getColumnIndex());
					switch (cell.getCellType()) {	//根据cell中的类型来输出数据
					case HSSFCell.CELL_TYPE_NUMERIC:
						System.out.println(cell.getNumericCellValue());
						break;
					case HSSFCell.CELL_TYPE_STRING:
						System.out.println(cell.getStringCellValue());
						break;
					case HSSFCell.CELL_TYPE_BOOLEAN:
						System.out.println(cell.getBooleanCellValue());
						break;
					case HSSFCell.CELL_TYPE_FORMULA:
						System.out.println(cell.getCellFormula());
						break;
					default:
						System.out.println("unsuported sell type");
					break;
					}
				}
			}
		} catch (IOException ex) {
			ex.printStackTrace();
		}
	}
}

参考：http://blog.csdn.net/shuwei003/article/details/67416493

 

3、

代码如下


public class Test {
 
 public static void main(String args[]){
  try {
   String  excelFilePath = "F://会员导入模板.xls";
           //模板.xlsx
 
   Workbook wb = null;
        int version=(excelFilePath.endsWith(".xls")?2003:2007);
        if(version==2003){
            try {
                InputStream stream=new FileInputStream(new File(excelFilePath));
                wb=new HSSFWorkbook(stream);
            } catch (FileNotFoundException e1) {
                e1.printStackTrace();
            } catch (IOException e1) {
                e1.printStackTrace();
            }
        }else if(version==2007){
            try {
             InputStream stream=new FileInputStream(new File(excelFilePath));
                wb=new XSSFWorkbook(stream);
            } catch (IOException e1) {
                e1.printStackTrace();
            }
        }
        
   String bs =""; 
        Sheet  sheet = wb.getSheetAt(0);
   Row row = null;
   int i=0;
   for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) {
    row = rit.next();
    i++;
    if(i>2){
     int j=0;
     bs+="(";
     for (Iterator<Cell> cit = row.cellIterator(); cit.hasNext();) {
      Cell cell = cit.next();
      if(j>0){
       System.out.println(cell.getStringCellValue());
      }
      j++;
     }
     bs+=")";
    }
    
   }


   System.out.println(bs);
  } catch (Exception e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  
 }
}

参考：http://tiantianshagn.blog.163.com/blog/static/1676323272011101193551732/

4、

public class ReadExcel {

     /**
     * 对外提供读取excel 的方法
     * */
public static List<List<Object>> readExcel(File file) throws IOException{
   String fileName = file.getName();
   String extension = fileName.lastIndexOf(".")==-1?"":fileName.substring(fileName.lastIndexOf(".")+1);
   if("xls".equals(extension)){
    return read2003Excel(file);
   }else if("xlsx".equals(extension)){
    return read2007Excel(file);
   }else{
    throw new IOException("不支持的文件类型");
   }
}


/**
* 读取 office 2003 excel
* @throws IOException 
* @throws FileNotFoundException */
private static List<List<Object>> read2003Excel(File file) throws IOException{
   List<List<Object>> list = new LinkedList<List<Object>>();
   HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
   HSSFSheet sheet = hwb.getSheetAt(0);
   Object value = null;
   HSSFRow row = null;
   HSSFCell cell = null;
  
   for(int i = sheet.getFirstRowNum();i<= sheet.getPhysicalNumberOfRows();i++){
    row = sheet.getRow(i);
    if (row == null) {
     continue;
    }
    List<Object> linked = new LinkedList<Object>();
    for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
     cell = row.getCell(j);
     if (cell == null) {
      continue;
     }
     DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符
     SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
     DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
     switch (cell.getCellType()) {
     case XSSFCell.CELL_TYPE_STRING:
      System.out.println(i+"行"+j+" 列 is String type");
      value = cell.getStringCellValue();
      break;
     case XSSFCell.CELL_TYPE_NUMERIC:
      System.out.println(i+"行"+j+" 列 is Number type ; DateFormt:"+cell.getCellStyle().getDataFormatString());
      if("@".equals(cell.getCellStyle().getDataFormatString())){
         value = df.format(cell.getNumericCellValue());
      } else if("General".equals(cell.getCellStyle().getDataFormatString())){
         value = nf.format(cell.getNumericCellValue());
      }else{
        value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
      }
      break;
     case XSSFCell.CELL_TYPE_BOOLEAN:
      System.out.println(i+"行"+j+" 列 is Boolean type");
      value = cell.getBooleanCellValue();
      break;
     case XSSFCell.CELL_TYPE_BLANK:
      System.out.println(i+"行"+j+" 列 is Blank type");
      value = "";
      break;
     default:
      System.out.println(i+"行"+j+" 列 is default type");
      value = cell.toString();
     }
     if (value == null || "".equals(value)) {
      continue;
     }
     linked.add(value);
    
   }
    list.add(linked);
   }
  
   return list;
}



/**
* 读取Office 2007 excel
* */

private static List<List<Object>> read2007Excel(File file) throws IOException {

   List<List<Object>> list = new LinkedList<List<Object>>();
   // 构造 XSSFWorkbook 对象，strPath 传入文件路径
   XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
   // 读取第一章表格内容
   XSSFSheet sheet = xwb.getSheetAt(0);
   Object value = null;
   XSSFRow row = null;
   XSSFCell cell = null;
   for (int i = sheet.getFirstRowNum(); i <= sheet
     .getPhysicalNumberOfRows(); i++) {
    row = sheet.getRow(i);
    if (row == null) {
     continue;
    }
    List<Object> linked = new LinkedList<Object>();
    for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
     cell = row.getCell(j);
     if (cell == null) {
      continue;
     }
     DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符
     SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
     DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字

     switch (cell.getCellType()) {
     case XSSFCell.CELL_TYPE_STRING:
      System.out.println(i+"行"+j+" 列 is String type");
      value = cell.getStringCellValue();
      break;
     case XSSFCell.CELL_TYPE_NUMERIC:
      System.out.println(i+"行"+j+" 列 is Number type ; DateFormt:"+cell.getCellStyle().getDataFormatString());
      if("@".equals(cell.getCellStyle().getDataFormatString())){
        value = df.format(cell.getNumericCellValue());
      } else if("General".equals(cell.getCellStyle().getDataFormatString())){
        value = nf.format(cell.getNumericCellValue());
      }else{
       value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
      }
      break;
     case XSSFCell.CELL_TYPE_BOOLEAN:
      System.out.println(i+"行"+j+" 列 is Boolean type");
      value = cell.getBooleanCellValue();
      break;
     case XSSFCell.CELL_TYPE_BLANK:
      System.out.println(i+"行"+j+" 列 is Blank type");
      value = "";
      break;
     default:
      System.out.println(i+"行"+j+" 列 is default type");
      value = cell.toString();
     }
     if (value == null || "".equals(value)) {
      continue;
     }
     linked.add(value);
    }
    list.add(linked);
   }
   return list;
}

}

说明：该类中共封装了三个方法，对外提供的读取excel文件的方法，两个私有的分别读取excel2003和excel2007的方法。外部使用，只需调用readExcel 方法，传入一个File 参数，程序根据文件扩展名来判断选取那个方法来读取Excel文件。
 

参考http://hi.baidu.com/%D2%BB%C6%F0%D7%DF%BA%F3/blog/item/81d938d06f16f281a1ec9c47.html