通过POI统一读取Excel文件（兼容97-2003和2007+两种格式）

通过POI统一读取Excel文件（兼容97-2003和2007+两种格式）

• 博客分类： 
• J2EE
• 案例文档

poi excel 

1、引言 
由于系统需要提供给用户导入Excel文件数据的功能，但Excel文件有97-2003和2007+两种格式，且要求给用户有较大的灵活性。导入Excel文件的处理无非就是读取Excel文件的内容，然后根据一定的业务规则进行校验，校验正确后处理写入系统。对Excel文件的读取可通过JXL或POI两个Jar来完成，决定使用POI来开发，但POI对两种格式的处理又有所不同，那么如何通过POI灵活读取Excel文件的内容呢？ 

2、分析 
Excel文件的读取有以下读取情况 
（1）读取整个工作表中的所有内容 
 

（2）读取工作表中指定区域块的内容 
 

（3）读取工作表中指定行列的内容 
 

（4）读取工作表中指定单元格的内容 
 

情况（2）中，当区域块的内容为整个工作表的内容时，即为情况（1），也就是说情况（1）为情况（2）的特例。 
情况（3）中，当指定行列范围内容中的列范围连续时，即为情况（2），也就是说情况（2）为情况（3）的特例。 
情况（4）中，当指定单元格内容中的行范围连续时，即为情况（3），也就是说情况（3）为情况（4）的特例。 

从上述4种情况的分析可知，前3种情况均可视为情况（4）的特例，从而将工作表范围转化为指定单元格范围的处理。由于指定单元格范围可能存在上述的4种情况，因而提供灵活的并且能够覆盖这些情况的配置方式显得尤为关键。 

行列范围参数中均采用“,”作为不连续值的分割符，采用“-”作为两个连续值的连接符，这样简化了用户的参数配置，同时也保留了配置的灵活性，例如： 
（1）12-        表示查询范围为从第十二行(列)到EXCEL中有记录的最后一行(列)； 
（2）12-24      表示查询范围为从第十二行(列)到第二十四行(列)； 
（3）12-24，30  表示查询范围为从第十二行(列)到第二十四行(列)、第三十行(列)等； 

3、解决过程 
（1）POI处理 
对Excel的读取，主要涉及工作薄、工作薄、行数据、单元格等的处理，POI对97-2003和2007+两个版本的处理采用不同的类，如下图所示。 
 

其中： 
a）Workbook、Sheet、Row、Cell等为接口； 
b）HSSFWorkbook、HSSFSheet、HSSFRow、HSSFCell为97-2003版本对应的处理实现类； 
c）XSSFWorkbook、XSSFSheet、XSSFRow、XSSFCell为2007+版本对应的处理实现类； 

（2）针对POI接口统一Excel处理类PoiExcelHelper 

Java代码  

1. import java.util.ArrayList;  
2.   
3. import org.apache.poi.ss.usermodel.Cell;  
4. import org.apache.poi.ss.usermodel.Row;  
5. import org.apache.poi.ss.usermodel.Sheet;  
6.   
7. /** 
8.  * Excel统一POI处理类（针对2003以前和2007以后两种格式的兼容处理） 
9.  * @author  chengesheng 
10.  * @date    2012-5-3 下午03:10:23 
11.  * @note    PoiHelper 
12.  */  
13. public abstract class PoiExcelHelper {  
14.     public static final String SEPARATOR = ",";  
15.     public static final String CONNECTOR = "-";  
16.   
17.     /** 获取sheet列表，子类必须实现 */  
18.     public abstract ArrayList<String> getSheetList(String filePath);  
19.       
20.     /** 读取Excel文件数据 */  
21.     public ArrayList<ArrayList<String>> readExcel(String filePath, int sheetIndex) {  
22.         return readExcel(filePath, sheetIndex, "1-", "1-");  
23.     }  
24.       
25.     /** 读取Excel文件数据 */  
26.     public ArrayList<ArrayList<String>> readExcel(String filePath, int sheetIndex, String rows) {  
27.         return readExcel(filePath, sheetIndex, rows, "1-");  
28.     }  
29.       
30.     /** 读取Excel文件数据 */  
31.     public ArrayList<ArrayList<String>> readExcel(String filePath, int sheetIndex, String[] columns) {  
32.         return readExcel(filePath, sheetIndex, "1-", columns);  
33.     }  
34.       
35.     /** 读取Excel文件数据，子类必须实现 */  
36.     public abstract ArrayList<ArrayList<String>> readExcel(String filePath, int sheetIndex, String rows, String columns);  
37.   
38.     /** 读取Excel文件数据 */  
39.     public ArrayList<ArrayList<String>> readExcel(String filePath, int sheetIndex, String rows, String[] columns) {  
40.         int[] cols = getColumnNumber(columns);  
41.           
42.         return readExcel(filePath, sheetIndex, rows, cols);  
43.     }  
44.   
45.     /** 读取Excel文件数据，子类必须实现 */  
46.     public abstract ArrayList<ArrayList<String>> readExcel(String filePath, int sheetIndex, String rows, int[] cols);  
47.       
48.     /** 读取Excel文件内容 */  
49.     protected ArrayList<ArrayList<String>> readExcel(Sheet sheet, String rows, int[] cols) {  
50.         ArrayList<ArrayList<String>> dataList = new ArrayList<ArrayList<String>> ();  
51.         // 处理行信息，并逐行列块读取数据  
52.         String[] rowList = rows.split(SEPARATOR);  
53.         for (String rowStr : rowList) {  
54.             if (rowStr.contains(CONNECTOR)) {  
55.                 String[] rowArr = rowStr.trim().split(CONNECTOR);  
56.                 int start = Integer.parseInt(rowArr[0]) - 1;  
57.                 int end;  
58.                 if (rowArr.length == 1) {  
59.                     end = sheet.getLastRowNum();  
60.                 } else {  
61.                     end = Integer.parseInt(rowArr[1].trim()) - 1;  
62.                 }  
63.                 dataList.addAll(getRowsValue(sheet, start, end, cols));  
64.             } else {  
65.                 dataList.add(getRowValue(sheet, Integer.parseInt(rowStr) - 1, cols));  
66.             }  
67.         }  
68.         return dataList;  
69.     }  
70.   
71.     /** 获取连续行、列数据 */  
72.     protected ArrayList<ArrayList<String>> getRowsValue(Sheet sheet, int startRow, int endRow,  
73.             int startCol, int endCol) {  
74.         if (endRow < startRow || endCol < startCol) {  
75.             return null;  
76.         }  
77.           
78.         ArrayList<ArrayList<String>> data = new ArrayList<ArrayList<String>>();  
79.         for (int i = startRow; i <= endRow; i++) {  
80.             data.add(getRowValue(sheet, i, startCol, endCol));  
81.         }  
82.         return data;  
83.     }  
84.   
85.     /** 获取连续行、不连续列数据 */  
86.     private ArrayList<ArrayList<String>> getRowsValue(Sheet sheet, int startRow, int endRow, int[] cols) {  
87.         if (endRow < startRow) {  
88.             return null;  
89.         }  
90.           
91.         ArrayList<ArrayList<String>> data = new ArrayList<ArrayList<String>>();  
92.         for (int i = startRow; i <= endRow; i++) {  
93.             data.add(getRowValue(sheet, i, cols));  
94.         }  
95.         return data;  
96.     }  
97.       
98.     /** 获取行连续列数据 */  
99.     private ArrayList<String> getRowValue(Sheet sheet, int rowIndex, int startCol, int endCol) {  
100.         if(endCol < startCol) {  
101.             return null;  
102.         }  
103.           
104.         Row row = sheet.getRow(rowIndex);  
105.         ArrayList<String> rowData = new ArrayList<String>();  
106.         for (int i = startCol; i <= endCol; i++) {  
107.             rowData.add(getCellValue(row, i));  
108.         }  
109.         return rowData;  
110.     }  
111.       
112.     /** 获取行不连续列数据 */  
113.     private ArrayList<String> getRowValue(Sheet sheet, int rowIndex, int[] cols) {  
114.         Row row = sheet.getRow(rowIndex);  
115.         ArrayList<String> rowData = new ArrayList<String>();  
116.         for (int colIndex : cols) {  
117.             rowData.add(getCellValue(row, colIndex));  
118.         }  
119.         return rowData;  
120.     }  
121.       
122.     /** 
123.      * 获取单元格内容 
124.      *  
125.      * @param row 
126.      * @param column 
127.      *            a excel column string like 'A', 'C' or "AA". 
128.      * @return 
129.      */  
130.     protected String getCellValue(Row row, String column) {  
131.         return getCellValue(row,getColumnNumber(column));  
132.     }  
133.   
134.     /** 
135.      * 获取单元格内容 
136.      *  
137.      * @param row 
138.      * @param col 
139.      *            a excel column index from 0 to 65535 
140.      * @return 
141.      */  
142.     private String getCellValue(Row row, int col) {  
143.         if (row == null) {  
144.             return "";  
145.         }  
146.         Cell cell = row.getCell(col);  
147.         return getCellValue(cell);  
148.     }  
149.   
150.     /** 
151.      * 获取单元格内容 
152.      *  
153.      * @param cell 
154.      * @return 
155.      */  
156.     private String getCellValue(Cell cell) {  
157.         if (cell == null) {  
158.             return "";  
159.         }  
160.   
161.         String value = cell.toString().trim();  
162.         try {  
163.             // This step is used to prevent Integer string being output with  
164.             // '.0'.  
165.             Float.parseFloat(value);  
166.             value=value.replaceAll("\\.0$", "");  
167.             value=value.replaceAll("\\.0+$", "");  
168.             return value;  
169.         } catch (NumberFormatException ex) {  
170.             return value;  
171.         }  
172.     }  
173.   
174.     /** 
175.      * Change excel column letter to integer number 
176.      *  
177.      * @param columns 
178.      *            column letter of excel file, like A,B,AA,AB 
179.      * @return 
180.      */  
181.     private int[] getColumnNumber(String[] columns) {  
182.         int[] cols = new int[columns.length];  
183.         for(int i=0; i<columns.length; i++) {  
184.             cols[i] = getColumnNumber(columns[i]);  
185.         }  
186.         return cols;  
187.     }  
188.   
189.     /** 
190.      * Change excel column letter to integer number 
191.      *  
192.      * @param column 
193.      *            column letter of excel file, like A,B,AA,AB 
194.      * @return 
195.      */  
196.     private int getColumnNumber(String column) {  
197.         int length = column.length();  
198.         short result = 0;  
199.         for (int i = 0; i < length; i++) {  
200.             char letter = column.toUpperCase().charAt(i);  
201.             int value = letter - 'A' + 1;  
202.             result += value * Math.pow(26, length - i - 1);  
203.         }  
204.         return result - 1;  
205.     }  
206.   
207.     /** 
208.      * Change excel column string to integer number array 
209.      *  
210.      * @param sheet 
211.      *            excel sheet 
212.      * @param columns 
213.      *            column letter of excel file, like A,B,AA,AB 
214.      * @return 
215.      */  
216.     protected int[] getColumnNumber(Sheet sheet, String columns) {  
217.         // 拆分后的列为动态，采用List暂存  
218.         ArrayList<Integer> result = new ArrayList<Integer> ();  
219.         String[] colList = columns.split(SEPARATOR);  
220.         for(String colStr : colList){  
221.             if(colStr.contains(CONNECTOR)){  
222.                 String[] colArr = colStr.trim().split(CONNECTOR);  
223.                 int start = Integer.parseInt(colArr[0]) - 1;  
224.                 int end;  
225.                 if(colArr.length == 1){  
226.                     end = sheet.getRow(sheet.getFirstRowNum()).getLastCellNum() - 1;  
227.                 }else{  
228.                     end = Integer.parseInt(colArr[1].trim()) - 1;  
229.                 }  
230.                 for(int i=start; i<=end; i++) {  
231.                     result.add(i);  
232.                 }  
233.             }else{  
234.                 result.add(Integer.parseInt(colStr) - 1);  
235.             }  
236.         }  
237.           
238.         // 将List转换为数组  
239.         int len = result.size();  
240.         int[] cols = new int[len];   
241.         for(int i = 0; i<len; i++) {  
242.             cols[i] = result.get(i).intValue();  
243.         }  
244.   
245.         return cols;  
246.     }  
247. }  

（3）97-2003格式Excel文件处理类PoiExcel2k3Helper 

Java代码  

1. import java.io.FileInputStream;  
2. import java.util.ArrayList;  
3.   
4. import org.apache.poi.hssf.usermodel.HSSFSheet;  
5. import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
6.   
7. /** 
8.  * Excel 读取（97-2003格式） 
9.  * @author  chengesheng 
10.  * @date    2012-4-27 下午03:39:01 
11.  * @note    PoiExcel2k3Helper 
12.  */  
13. public class PoiExcel2k3Helper extends PoiExcelHelper {  
14.     /** 获取sheet列表 */  
15.     public ArrayList<String> getSheetList(String filePath) {  
16.         ArrayList<String> sheetList = new ArrayList<String>(0);  
17.         try {  
18.             HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(filePath));  
19.             int i = 0;  
20.             while (true) {  
21.                 try {  
22.                     String name = wb.getSheetName(i);  
23.                     sheetList.add(name);  
24.                     i++;  
25.                 } catch (Exception e) {  
26.                     break;  
27.                 }  
28.             }  
29.         } catch (Exception e) {  
30.             e.printStackTrace();  
31.         }  
32.         return sheetList;  
33.     }  
34.   
35.     /** 读取Excel文件内容 */  
36.     public ArrayList<ArrayList<String>> readExcel(String filePath, int sheetIndex, String rows, String columns) {  
37.         ArrayList<ArrayList<String>> dataList = new ArrayList<ArrayList<String>> ();  
38.         try {  
39.             HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(filePath));  
40.             HSSFSheet sheet = wb.getSheetAt(sheetIndex);  
41.               
42.             dataList = readExcel(sheet, rows, getColumnNumber(sheet, columns));  
43.         } catch (Exception e) {  
44.             e.printStackTrace();  
45.         }  
46.         return dataList;  
47.     }  
48.       
49.     /** 读取Excel文件内容 */  
50.     public ArrayList<ArrayList<String>> readExcel(String filePath, int sheetIndex, String rows, int[] cols) {  
51.         ArrayList<ArrayList<String>> dataList = new ArrayList<ArrayList<String>> ();  
52.         try {  
53.             HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(filePath));  
54.             HSSFSheet sheet = wb.getSheetAt(sheetIndex);  
55.               
56.             dataList = readExcel(sheet, rows, cols);  
57.         } catch (Exception e) {  
58.             e.printStackTrace();  
59.         }  
60.         return dataList;  
61.     }  
62. }  

（4）2007+新格式Excel文件处理类PoiExcel2k7Helper 

Java代码  

1. import java.io.FileInputStream;  
2. import java.util.ArrayList;  
3. import java.util.Iterator;  
4.   
5. import org.apache.poi.xssf.usermodel.XSSFSheet;  
6. import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
7.   
8. /** 
9.  * Excel 读取（2007+新格式） 
10.  * @author  chengesheng 
11.  * @date    2012-4-27 下午03:39:01 
12.  * @note    PoiExcel2k7Helper 
13.  */  
14. public class PoiExcel2k7Helper extends PoiExcelHelper {  
15.     /** 获取sheet列表 */  
16.     public ArrayList<String> getSheetList(String filePath) {  
17.         ArrayList<String> sheetList = new ArrayList<String>(0);  
18.         try {  
19.             XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(filePath));  
20.             Iterator<XSSFSheet> iterator = wb.iterator();  
21.             while (iterator.hasNext()) {  
22.                 sheetList.add(iterator.next().getSheetName());  
23.             }  
24.         } catch (Exception e) {  
25.             e.printStackTrace();  
26.         }  
27.         return sheetList;  
28.     }  
29.   
30.     /** 读取Excel文件内容 */  
31.     public ArrayList<ArrayList<String>> readExcel(String filePath, int sheetIndex, String rows, String columns) {  
32.         ArrayList<ArrayList<String>> dataList = new ArrayList<ArrayList<String>> ();  
33.         try {  
34.             XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(filePath));  
35.             XSSFSheet sheet = wb.getSheetAt(sheetIndex);  
36.               
37.             dataList = readExcel(sheet, rows, getColumnNumber(sheet, columns));  
38.         } catch (Exception e) {  
39.             e.printStackTrace();  
40.         }  
41.         return dataList;  
42.     }  
43.       
44.     /** 读取Excel文件内容 */  
45.     public ArrayList<ArrayList<String>> readExcel(String filePath, int sheetIndex, String rows, int[] cols) {  
46.         ArrayList<ArrayList<String>> dataList = new ArrayList<ArrayList<String>> ();  
47.         try {  
48.             XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(filePath));  
49.             XSSFSheet sheet = wb.getSheetAt(sheetIndex);  
50.               
51.             dataList = readExcel(sheet, rows, cols);  
52.         } catch (Exception e) {  
53.             e.printStackTrace();  
54.         }  
55.         return dataList;  
56.     }  
57. }  

（5）测试类PoiExcelTest 

Java代码  

1. import java.util.ArrayList;  
2.   
3. /** 
4.  * Excel统一POI处理测试类（针对2003以前和2007以后两种格式的兼容处理） 
5.  * @author  chengesheng 
6.  * @date    2012-5-3 下午03:10:23 
7.  * @note    PoiHelper 
8.  */  
9. public abstract class PoiExcelTest {  
10.     // *************************************************  
11.     // ================以下为测试代码====================  
12.     // *************************************************  
13.     public static void main(String[] args){  
14.         // 获取Excel文件的sheet列表  
15.         testGetSheetList("c:/test.xlsx");  
16.           
17.         // 获取Excel文件的第1个sheet的内容  
18.         testReadExcel("c:/test.xls", 0);  
19.           
20.         // 获取Excel文件的第2个sheet的第2、4-7行和第10行及以后的内容  
21.         testReadExcel("c:/test.xlsx", 1, "2,4-7,10-");  
22.           
23.         // 获取Excel文件的第3个sheet中a,b,g,h,i,j等列的所有内容  
24.         testReadExcel("c:/test.xls", 2, new String[] {"a","b","g","h","i","j"});  
25.           
26.         // 获取Excel文件的第4个sheet的第2、4-7行和第10行及以后，a,b,g,h,i,j等列的内容  
27.         testReadExcel("c:/test.xlsx", 3, "2,4-7,10-", new String[] {"a","b","g","h","i","j"});  
28.     }  
29.       
30.     // 测试获取sheet列表  
31.     private static void testGetSheetList(String filePath) {  
32.         PoiExcelHelper helper = getPoiExcelHelper(filePath);  
33.           
34.         // 获取Sheet列表  
35.         ArrayList<String> sheets = helper.getSheetList(filePath);  
36.           
37.         // 打印Excel的Sheet列表  
38.         printList(filePath, sheets);  
39.     }  
40.       
41.     // 测试Excel读取  
42.     private static void testReadExcel(String filePath, int sheetIndex) {  
43.         PoiExcelHelper helper = getPoiExcelHelper(filePath);  
44.           
45.         // 读取excel文件数据  
46.         ArrayList<ArrayList<String>> dataList = helper.readExcel(filePath, sheetIndex);  
47.           
48.         // 打印单元格数据  
49.         printBody(dataList);  
50.     }  
51.       
52.     // 测试Excel读取  
53.     private static void testReadExcel(String filePath, int sheetIndex, String rows) {  
54.         PoiExcelHelper helper = getPoiExcelHelper(filePath);  
55.           
56.         // 读取excel文件数据  
57.         ArrayList<ArrayList<String>> dataList = helper.readExcel(filePath, sheetIndex, rows);  
58.           
59.         // 打印单元格数据  
60.         printBody(dataList);  
61.     }  
62.       
63.     // 测试Excel读取  
64.     private static void testReadExcel(String filePath, int sheetIndex, String[] columns) {  
65.         PoiExcelHelper helper = getPoiExcelHelper(filePath);  
66.           
67.         // 读取excel文件数据  
68.         ArrayList<ArrayList<String>> dataList = helper.readExcel(filePath, sheetIndex, columns);  
69.           
70.         // 打印列标题  
71.         printHeader(columns);  
72.           
73.         // 打印单元格数据  
74.         printBody(dataList);  
75.     }  
76.       
77.     // 测试Excel读取  
78.     private static void testReadExcel(String filePath, int sheetIndex, String rows, String[] columns) {  
79.         PoiExcelHelper helper = getPoiExcelHelper(filePath);  
80.           
81.         // 读取excel文件数据  
82.         ArrayList<ArrayList<String>> dataList = helper.readExcel(filePath, sheetIndex, rows, columns);  
83.           
84.         // 打印列标题  
85.         printHeader(columns);  
86.           
87.         // 打印单元格数据  
88.         printBody(dataList);  
89.     }  
90.       
91.     // 获取Excel处理类  
92.     private static PoiExcelHelper getPoiExcelHelper(String filePath) {  
93.         PoiExcelHelper helper;  
94.         if(filePath.indexOf(".xlsx")!=-1) {  
95.             helper = new PoiExcel2k7Helper();  
96.         }else {  
97.             helper = new PoiExcel2k3Helper();  
98.         }  
99.         return helper;  
100.     }  
101.   
102.     // 打印Excel的Sheet列表  
103.     private static void printList(String filePath, ArrayList<String> sheets) {  
104.         System.out.println();  
105.         for(String sheet : sheets) {  
106.             System.out.println(filePath + " ==> " + sheet);  
107.         }  
108.     }  
109.   
110.     // 打印列标题  
111.     private static void printHeader(String[] columns) {  
112.         System.out.println();  
113.         for(String column : columns) {  
114.             System.out.print("\t\t" + column.toUpperCase());  
115.         }  
116.     }  
117.   
118.     // 打印单元格数据  
119.     private static void printBody(ArrayList<ArrayList<String>> dataList) {  
120.         int index = 0;  
121.         for(ArrayList<String> data : dataList) {  
122.             index ++;  
123.             System.out.println();  
124.             System.out.print(index);  
125.             for(String v : data) {  
126.                 System.out.print("\t\t" + v);  
127.             }  
128.         }  
129.     }  
130. }