POI兼容2003,2007

1. /** 
2.  * ClassName:ExcelReader.java 
3.  * Author: wenbin.ji 
4.  * CreateTime: Jan 28, 2011 11:16:29 AM 
5.  * Description:Excel数据读取工具类，POI实现，兼容Excel2003，及Excel2007 
6.  **/  
7. package com.company.utils;  
8.   
9. import java.io.FileInputStream;  
10. import java.io.FileNotFoundException;  
11. import java.io.IOException;  
12. import java.io.InputStream;  
13. import java.util.ArrayList;  
14. import java.util.List;  
15. import org.apache.poi.ss.usermodel.FormulaEvaluator;  
16. import org.apache.poi.openxml4j.exceptions.InvalidFormatException;  
17. import org.apache.poi.ss.usermodel.Cell;  
18. import org.apache.poi.ss.usermodel.DateUtil;  
19. import org.apache.poi.ss.usermodel.Row;  
20. import org.apache.poi.ss.usermodel.Sheet;  
21. import org.apache.poi.ss.usermodel.Workbook;  
22. import org.apache.poi.ss.usermodel.WorkbookFactory;  
23.   
24. public class ExcelReader {  
25.   Workbook wb = null;  
26.   List<String[]> dataList = new ArrayList<String[]>(100);  
27.   public ExcelReader(String path){  
28.     try {  
29.       InputStream inp = new FileInputStream(path);  
30.       wb = WorkbookFactory.create(inp);        
31.     } catch (FileNotFoundException e) {  
32.       e.printStackTrace();  
33.     } catch (InvalidFormatException e) {  
34.       e.printStackTrace();  
35.     } catch (IOException e) {  
36.       e.printStackTrace();  
37.     }  
38.       
39.   }    
40.     
41.   /** 
42.    * 取Excel所有数据，包含header 
43.    * @return  List<String[]> 
44.    */  
45.  public List<String[]> getAllData(int sheetIndex){  
46.     int columnNum = 0;  
47.     Sheet sheet = wb.getSheetAt(sheetIndex);  
48.     if(sheet.getRow(0)!=null){  
49.         columnNum = sheet.getRow(0).getLastCellNum()-sheet.getRow(0).getFirstCellNum();  
50.     }  
51.     if(columnNum>0){  
52.       for(Row row:sheet){   
53.           String[] singleRow = new String[columnNum];  
54.           int n = 0;  
55.           for(int i=0;i<columnNum;i++){  
56.              Cell cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK);  
57.              switch(cell.getCellType()){  
58.                case Cell.CELL_TYPE_BLANK:  
59.                  singleRow[n] = "";  
60.                  break;  
61.                case Cell.CELL_TYPE_BOOLEAN:  
62.                  singleRow[n] = Boolean.toString(cell.getBooleanCellValue());  
63.                  break;  
64.                 //数值  
65.                case Cell.CELL_TYPE_NUMERIC:                 
66.                  if(DateUtil.isCellDateFormatted(cell)){  
67.                    singleRow[n] = String.valueOf(cell.getDateCellValue());  
68.                  }else{   
69.                    cell.setCellType(Cell.CELL_TYPE_STRING);  
70.                    String temp = cell.getStringCellValue();  
71.                    //判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串  
72.                    if(temp.indexOf(".")>-1){  
73.                      singleRow[n] = String.valueOf(new Double(temp)).trim();  
74.                    }else{  
75.                      singleRow[n] = temp.trim();  
76.                    }  
77.                  }  
78.                  break;  
79.                case Cell.CELL_TYPE_STRING:  
80.                  singleRow[n] = cell.getStringCellValue().trim();  
81.                  break;  
82.                case Cell.CELL_TYPE_ERROR:  
83.                  singleRow[n] = "";  
84.                  break;    
85.                case Cell.CELL_TYPE_FORMULA:  
86.                  cell.setCellType(Cell.CELL_TYPE_STRING);  
87.                  singleRow[n] = cell.getStringCellValue();  
88.                  if(singleRow[n]!=null){  
89.                    singleRow[n] = singleRow[n].replaceAll("#N/A","").trim();  
90.                  }  
91.                  break;    
92.                default:  
93.                  singleRow[n] = "";  
94.                  break;  
95.              }  
96.              n++;  
97.           }   
98.           if("".equals(singleRow[0])){continue;}//如果第一行为空，跳过  
99.           dataList.add(singleRow);  
100.       }  
101.     }  
102.     return dataList;  
103.   }    
104.   /** 
105.    * 返回Excel最大行index值，实际行数要加1 
106.    * @return 
107.    */  
108.   public int getRowNum(int sheetIndex){  
109.     Sheet sheet = wb.getSheetAt(sheetIndex);  
110.     return sheet.getLastRowNum();  
111.   }  
112.     
113.   /** 
114.    * 返回数据的列数 
115.    * @return  
116.    */  
117.   public int getColumnNum(int sheetIndex){  
118.     Sheet sheet = wb.getSheetAt(sheetIndex);  
119.     Row row = sheet.getRow(0);  
120.     if(row!=null&&row.getLastCellNum()>0){  
121.        return row.getLastCellNum();  
122.     }  
123.     return 0;  
124.   }  
125.     
126.   /** 
127.    * 获取某一行数据 
128.    * @param rowIndex 计数从0开始，rowIndex为0代表header行 
129.    * @return 
130.    */  
131.     public String[] getRowData(int sheetIndex,int rowIndex){  
132.       String[] dataArray = null;  
133.       if(rowIndex>this.getColumnNum(sheetIndex)){  
134.         return dataArray;  
135.       }else{  
136.         dataArray = new String[this.getColumnNum(sheetIndex)];  
137.         return this.dataList.get(rowIndex);  
138.       }  
139.         
140.     }  
141.     
142.   /** 
143.    * 获取某一列数据 
144.    * @param colIndex 
145.    * @return 
146.    */  
147.   public String[] getColumnData(int sheetIndex,int colIndex){  
148.     String[] dataArray = null;  
149.     if(colIndex>this.getColumnNum(sheetIndex)){  
150.       return dataArray;  
151.     }else{     
152.       if(this.dataList!=null&&this.dataList.size()>0){  
153.         dataArray = new String[this.getRowNum(sheetIndex)+1];  
154.         int index = 0;  
155.         for(String[] rowData:dataList){  
156.           if(rowData!=null){  
157.              dataArray[index] = rowData[colIndex];  
158.              index++;  
159.           }  
160.         }  
161.       }  
162.     }  
163.     return dataArray;  
164.       
165.   }  
166.  }