POI 讀取Excel文檔中的數據——相容Excel2003和Excel2007

Apache POI是Apache軟體基金會的開放源碼函式庫，POI提供API給Java程式對Microsoft Office格式檔案讀和寫的功能。 HSSF －提供讀寫Microsoft Excel格式檔案的功能。 XSSF －提供讀寫Microsoft OOXML格式檔案的功能。 ...

　　Apache POI是Apache軟體基金會的開放源碼函式庫，POI提供API給Java程式對Microsoft Office格式檔案讀和寫的功能。

　　HSSF －提供讀寫Microsoft Excel格式檔案的功能。　　XSSF －提供讀寫Microsoft OOXML格式檔案的功能。　以下是項目工程結構圖：　　

　　使用POI解析EXCEL文件需要用到POI相關的jar包，這些jar包可以在apache官網上去下載：http://poi.apache.org/download.html；

這裡我使用的jar包版本為poi-3.14-beta1-20151223.jar

　相關代碼如下：

　　Excel文件解析介面 IExcelParse.java

 1 /*
 2  * IExcelParse.java
 3  * 
 4  * 2016-1-6 下午4:45:53
 5  * 
 6  * RecluseKapoor
 7  *  
 8  * Copyright © 2016, RecluseKapoor. All rights reserved.
 9  * 
10  */
11 package com.rk.pub.poi.excel;
12 
13 /**
14  * @Title: recluse-Excel文件解析介面
15  * 
16  * @Description:Excel文件解析介面，所有版本的Excel解析類都要實現該介面
17  * 
18  * @Company: 卡普工作室
19  * 
20  * @Website: http://www.cnblogs.com/reclusekapoor/
21  * 
22  * @author: RecluseKapoor
23  * 
24  * @CreateDate：2016-1-6 下午9:42:08
25  * 
26  * @version: 1.0
27  * 
28  * @lastModify:
29  * 
30  */
31 public interface IExcelParse {
32     public void loadExcel(String path) throws Exception;
33 
34     public String getSheetName(int sheetNo);
35 
36     public int getSheetCount() throws Exception;
37 
38     public int getRowCount(int sheetNo);
39 
40     public int getRealRowCount(int sheetNo);
41 
42     public String readExcelByRowAndCell(int sheetNo, int rowNo, int cellNo)
43             throws Exception;
44 
45     public String[] readExcelByRow(int sheetNo, int rowNo) throws Exception;
46 
47     public String[] readExcelByCell(int sheetNo, int cellNo) throws Exception;
48 
49     public void close();
50 }

  1 /*
  2  * ExcelParse2003.java
  3  * 
  4  * 2016-1-6 下午4:45:53
  5  * 
  6  * RecluseKapoor
  7  *  
  8  * Copyright © 2016, RecluseKapoor. All rights reserved.
  9  * 
 10  */
 11 package com.rk.pub.poi.excel;
 12 
 13 import java.io.FileInputStream;
 14 import java.io.FileNotFoundException;
 15 import java.io.IOException;
 16 import java.sql.Timestamp;
 17 import java.text.DecimalFormat;
 18 import java.util.Date;
 19 
 20 import org.apache.poi.hssf.usermodel.HSSFCell;
 21 import org.apache.poi.hssf.usermodel.HSSFDateUtil;
 22 import org.apache.poi.hssf.usermodel.HSSFRow;
 23 import org.apache.poi.hssf.usermodel.HSSFSheet;
 24 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 25 import org.apache.poi.poifs.filesystem.POIFSFileSystem;
 26 
 27 /**
 28  * @Title: recluse--2003版Excel文件解析工具
 29  * 
 30  * @Description: 解析2003版Excel文件具體實現類
 31  * 
 32  * @Company: 卡普工作室
 33  * 
 34  * @Website: http://www.cnblogs.com/reclusekapoor/
 35  * 
 36  * @author: RecluseKapoor
 37  * 
 38  * @CreateDate：2016-1-6 下午9:59:51
 39  * 
 40  * @version: 1.0
 41  * 
 42  * @lastModify:
 43  * 
 44  */
 45 public class ExcelParse2003 implements IExcelParse {
 46     // Excel工作區
 47     private HSSFWorkbook wb = null;
 48 
 49     /**
 50      * 載入excel文件，獲取excel工作區
 51      * 
 52      * @param filePathAndName
 53      * @throws FileNotFoundException
 54      * @throws IOException
 55      */
 56     @Override
 57     public void loadExcel(String filePathAndName) throws FileNotFoundException,
 58             IOException {
 59         FileInputStream fis = null;
 60         POIFSFileSystem fs = null;
 61         try {
 62             fis = new FileInputStream(filePathAndName);
 63             fs = new POIFSFileSystem(fis);
 64             wb = new HSSFWorkbook(fs);
 65         } catch (FileNotFoundException e) {
 66             e.printStackTrace();
 67             throw new FileNotFoundException("載入Excel文件失敗：" + e.getMessage());
 68         } catch (IOException e) {
 69             e.printStackTrace();
 70             throw new IOException("載入Excel文件失敗：" + e.getMessage());
 71         } finally {
 72             if (fis != null) {
 73                 fis.close();
 74                 fis = null;
 75             }
 76             if (fs != null) {
 77                 fs.close();
 78             }
 79         }
 80     }
 81 
 82     /**
 83      * 獲取sheet頁名稱
 84      * 
 85      * @param sheetNo
 86      * @return
 87      */
 88     public String getSheetName(int sheetNo) {
 89         return wb.getSheetName(sheetNo - 1);
 90     }
 91 
 92     /**
 93      * 獲取sheet頁數
 94      * 
 95      * @return int
 96      */
 97     public int getSheetCount() throws Exception {
 98         int sheetCount = wb.getNumberOfSheets();
 99         if (sheetCount == 0) {
100             throw new Exception("Excel中沒有SHEET頁");
101         }
102         return sheetCount;
103     }
104 
105     /**
106      * 獲取sheetNo頁行數
107      * 
108      * @param sheetNo
109      * @return
110      */
111     public int getRowCount(int sheetNo) {
112         int rowCount = 0;
113         HSSFSheet sheet = wb.getSheetAt(sheetNo - 1);
114         rowCount = sheet.getLastRowNum();
115         return rowCount;
116     }
117 
118     /**
119      * 獲取sheetNo頁行數(含有操作或者內容的真實行數)
120      * 
121      * @param sheetNo
122      * @return
123      */
124     public int getRealRowCount(int sheetNo) {
125         int rowCount = 0;
126         int rowNum = 0;
127         HSSFSheet sheet = wb.getSheetAt(sheetNo - 1);
128         rowCount = sheet.getLastRowNum();
129         if (rowCount == 0) {
130             return rowCount;
131         }
132         HSSFRow row = null;
133         HSSFCell cell = null;
134         rowNum = rowCount;
135         for (int i = 0; i < rowCount; i++) {
136             row = sheet.getRow(rowNum);
137             rowNum--;
138             if (row == null) {
139                 continue;
140             }
141             short firstCellNum = row.getFirstCellNum();
142             short lastCellNum = row.getLastCellNum();
143             for (int j = firstCellNum; j < lastCellNum; j++) {
144                 cell = row.getCell(j);
145                 if (cell == null) {
146                     continue;
147                 } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
148                     continue;
149                 } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
150                     String value = cell.getStringCellValue();
151                     if (value == null || value.equals("")) {
152                         continue;
153                     } else {
154                         value = value.trim();
155                         if (value.isEmpty() || value.equals("")
156                                 || value.length() == 0) {
157                             continue;
158                         }
159                     }
160                 }
161                 rowCount = rowNum + 1;
162                 return rowCount;
163             }
164         }
165         rowCount = rowNum;
166         return rowCount;
167     }
168 
169     /**
170      * 讀取第sheetNo個sheet頁中第rowNo行第cellNo列的數據
171      * 
172      * @param sheetNo
173      *            sheet頁編號
174      * @param rowNo
175      *            行號
176      * @param cellNo
177      *            列號
178      * @return 返回相應的excel單元格內容
179      * @throws Exception
180      */
181     public String readExcelByRowAndCell(int sheetNo, int rowNo, int cellNo)
182             throws Exception {
183         String rowCellData = "";
184         sheetNo = sheetNo - 1;
185         HSSFSheet sheet = wb.getSheetAt(sheetNo);
186         String sheetName = wb.getSheetName(sheetNo);
187         try {
188             HSSFRow row = sheet.getRow(rowNo - 1);
189             if (row == null) {
190                 return "NoData";
191             }
192             HSSFCell cell = row.getCell((cellNo - 1));
193             if (cell == null) {
194                 return "NoData";
195             }
196             int cellType = cell.getCellType();
197             if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {// 數值(包括excel中數值、貨幣、日期、時間、會計專用等單元格格式)
198                 //判斷數值是否為日期或時間；但是該判斷方法存在漏洞，只能識別一種日期格式。
199                 if (HSSFDateUtil.isCellDateFormatted(cell)) {//日期、時間
200                     double d = cell.getNumericCellValue();
201                     Date date = HSSFDateUtil.getJavaDate(d);
202                     Timestamp timestamp = new Timestamp(date.getTime());
203                     String temp = timestamp.toString();
204                     if (temp.endsWith("00:00:00.0")) {
205                         rowCellData = temp.substring(0,
206                                 temp.lastIndexOf("00:00:00.0"));
207                     } else if (temp.endsWith(".0")) {
208                         rowCellData = temp.substring(0, temp.lastIndexOf(".0"));
209                     } else {
210                         rowCellData = timestamp.toString();
211                     }
212                 } else {//數值、貨幣、會計專用、百分比、分數、科學記數 單元格式
213                     rowCellData = new DecimalFormat("0.########").format(cell
214                             .getNumericCellValue());
215                 }
216             } else if (cellType == HSSFCell.CELL_TYPE_STRING) {// 字元串
217                 rowCellData = cell.getStringCellValue();
218             } else if (cellType == HSSFCell.CELL_TYPE_FORMULA) {// 公式
219                 double d = cell.getNumericCellValue();
220                 rowCellData = String.valueOf(d);
221             } else if (cellType == HSSFCell.CELL_TYPE_BLANK) {// 空值
222                 rowCellData = "";
223             } else if (cellType == HSSFCell.CELL_TYPE_BOOLEAN) {// boolean值
224                 rowCellData = "";
225             } else if (cellType == HSSFCell.CELL_TYPE_ERROR) {// 異常
226                 rowCellData = "";
227             } else {
228 
229             }
230         } catch (Exception e) {
231             e.printStackTrace();
232             throw new Exception(sheetName + "sheet頁中" + "第" + rowNo + "行" + "第"
233                     + cellNo + "列" + "數據不符合要求,請檢查sheet頁");
234         }
235         return rowCellData;
236     }
237 
238     /**
239      * 讀取第sheetNo個sheet頁中第rowNo行的數據
240      * 
241      * @param sheetNo
242      *            指定sheetNo頁
243      * @param rowNo
244      *            指定rowNo行
245      * @return 返回第rowNo行的數據
246      * @throws Exception
247      */
248     public String[] readExcelByRow(int sheetNo, int rowNo) throws Exception {
249         String[] rowData = null;
250         HSSFSheet sheet = wb.getSheetAt(sheetNo - 1);
251         HSSFRow row = sheet.getRow(rowNo - 1);
252         int cellCount = row.getLastCellNum();
253         rowData = new String[cellCount];
254         for (int k = 1; k <= cellCount; k++) {
255             rowData[k - 1] = readExcelByRowAndCell(sheetNo, rowNo, k);
256         }
257         return rowData;
258     }
259 
260     /**
261      * 讀取第sheetNo個sheet頁中第cellNo列的數據
262      * 
263      * @param sheetNo
264      *            指定sheetNo頁
265      * @param cellNo
266      *            指定cellNo列號
267      * @return 返回第cellNo列的數據
268      * @throws Exception
269      */
270     public String[] readExcelByCell(int sheetNo, int cellNo) throws Exception {
271         String[] cellData = null;
272         HSSFSheet sheet = wb.getSheetAt(sheetNo - 1);
273         int rowCount = sheet.getLastRowNum();
274         cellData = new String[rowCount + 1];
275         for (int i = 0; i <= rowCount; i++) {
276             cellData[i] = readExcelByRowAndCell(sheetNo - 1, i, cellNo - 1);
277         }
278         return cellData;
279     }
280 
281     /**
282      * 關閉excel工作區，釋放資源
283      * 
284      * @throws Exception
285      */
286     @Override
287     public void close() {
288         if (wb != null) {
289             try {
290                 wb.close();
291                 wb = null;
292             } catch (IOException e) {
293                 e.printStackTrace();
294             }
295         }
296     }
297 }

  1 /*
  2  * ExcelParse2007.java
  3  * 
  4  * 2016-1-6 下午4:45:53
  5  * 
  6  * RecluseKapoor
  7  *  
  8  * Copyright © 2016, RecluseKapoor. All rights reserved.
  9  * 
 10  */
 11 package com.rk.pub.poi.excel;
 12 
 13 import java.io.FileInputStream;
 14 import java.io.FileNotFoundException;
 15 import java.io.IOException;
 16 import java.sql.Timestamp;
 17 import java.text.DecimalFormat;
 18 import java.util.Date;
 19 
 20 import org.apache.poi.ss.usermodel.DateUtil;
 21 import org.apache.poi.xssf.usermodel.XSSFCell;
 22 import org.apache.poi.xssf.usermodel.XSSFRow;
 23 import org.apache.poi.xssf.usermodel.XSSFSheet;
 24 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 25 
 26 /**
 27  * @Title: recluse--2007版Excel文件解析工具
 28  * 
 29  * @Description: 解析2007版Excel文件具體實現類
 30  * 
 31  * @Company: 卡普工作室
 32  * 
 33  * @Website: http://www.cnblogs.com/reclusekapoor/
 34  * 
 35  * @author: RecluseKapoor
 36  * 
 37  * @CreateDate：2016-1-6 下午9:51:15
 38  * 
 39  * @version: 1.0
 40  * 
 41  * @lastModify:
 42  * 
 43  */
 44 public class ExcelParse2007 implements IExcelParse {
 45     // Excel工作區
 46     private XSSFWorkbook wb = null;
 47 
 48     /**
 49      * 載入excel文件，獲取excel工作區
 50      * 
 51      * @param filePathAndName
 52      * @throws FileNotFoundException
 53      * @throws IOException
 54      */
 55     public void loadExcel(String filePathAndName) throws FileNotFoundException,
 56             IOException {
 57         FileInputStream fis = null;
 58         try {
 59             fis = new FileInputStream(filePathAndName);
 60             wb = new XSSFWorkbook(fis);
 61         } catch (FileNotFoundException e) {
 62             e.printStackTrace();
 63             throw new FileNotFoundException("載入Excel文件失敗：" + e.getMessage());
 64         } catch (IOException e) {
 65             e.printStackTrace();
 66             throw new IOException("載入Excel文件失敗：" + e.getMessage());
 67         } finally {
 68             if (fis != null) {
 69                 fis.close();
 70                 fis = null;
 71             }
 72         }
 73     }
 74 
 75     /**
 76      * 獲取sheet頁名稱
 77      * 
 78      * @param sheetNo
 79      * @return
 80      */
 81     public String getSheetName(int sheetNo) {
 82         return wb.getSheetName(sheetNo - 1);
 83     }
 84 
 85     /**
 86      * 獲取sheet頁數
 87      * 
 88      * @return int
 89      */
 90     public int getSheetCount() throws Exception {
 91         int sheetCount = wb.getNumberOfSheets();
 92         if (sheetCount == 0) {
 93             throw new Exception("Excel中沒有SHEET頁");
 94         }
 95         return sheetCount;
 96     }
 97 
 98     /**
 99      * 獲取sheetNo頁行數
100      * 
101      * @param sheetNo
102      * @return
103      */
104     public int getRowCount(int sheetNo) {
105         int rowCount = 0;
106         XSSFSheet sheet = wb.getSheetAt(sheetNo - 1);
107         rowCount = sheet.getLastRowNum();
108         return rowCount;
109     }
110 
111     /**
112      * 獲取sheetNo頁行數(含有操作或者內容的真實行數)
113      * 
114      * @param sheetNo
115      * @return
116      */
117     public int getRealRowCount(int sheetNo) {
118         int rowCount = 0;
119         int rowNum = 0;
120         XSSFSheet sheet = wb.getSheetAt(sheetNo - 1);
121         rowCount = sheet.getLastRowNum();
122         if (rowCount == 0) {
123             return rowCount;
124         }
125         XSSFRow row = null;
126         XSSFCell cell = null;
127         rowNum = rowCount;
128         for (int i = 0; i < rowCount; i++) {
129             row = sheet.getRow(rowNum);
130             rowNum--;
131             if (row == null) {
132                 continue;
133             }
134             short firstCellNum = row.getFirstCellNum();
135             short lastCellNum = row.getLastCellNum();
136             for (int j = firstCellNum; j < lastCellNum; j++) {
137                 cell = row.getCell(j);
138                 if (cell == null) {