POI读取Excel(兼容Excel2003、Excel2007)

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);  
                          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;  
             
         }  
        } 
   

/** 
 * 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);  
                   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;  
      
  }  
 }