HSSFWorkbook和XSSFWorkbook的区别

HSSFWorkbook读取97-2003格式 ，XSSFWorkbook读取2007-2013格式 

1.   
2.     /** 
3.      * 读取97-2003格式 
4.      * @param filePath 文件路径 
5.      * @throws java.io.IOException 
6.      */  
7.     public static List<Map> readExcel2003(String filePath) throws IOException{  
8.         //返回结果集  
9.         List<Map> valueList=new ArrayList<Map>();  
10.         FileInputStream fis=null;  
11.         try {  
12.             fis=new FileInputStream(filePath);  
13.             HSSFWorkbook wookbook = new HSSFWorkbook(fis);  // 创建对Excel工作簿文件的引用  
14.             HSSFSheet sheet = wookbook.getSheetAt(0);   // 在Excel文档中，第一张工作表的缺省索引是0  
15.             int rows = sheet.getPhysicalNumberOfRows(); // 获取到Excel文件中的所有行数­  
16.             Map<Integer,String> keys=new HashMap<Integer, String>();  
17.             int cells=0;  
18.             // 遍历行­（第1行  表头） 准备Map里的key  
19.             HSSFRow firstRow = sheet.getRow(0);  
20.             if (firstRow != null) {  
21.                 // 获取到Excel文件中的所有的列  
22.                 cells = firstRow.getPhysicalNumberOfCells();  
23.                 // 遍历列  
24.                 for (int j = 0; j < cells; j++) {  
25.                     // 获取到列的值­  
26.                     try {  
27.                         HSSFCell cell = firstRow.getCell(j);  
28.                         String cellValue = getCellValue(cell);  
29.                         keys.put(j,cellValue);                        
30.                     } catch (Exception e) {  
31.                         e.printStackTrace();      
32.                     }  
33.                 }  
34.             }  
35.             // 遍历行­（从第二行开始）  
36.             for (int i = 1; i < rows; i++) {  
37.                 // 读取左上端单元格(从第二行开始)  
38.                 HSSFRow row = sheet.getRow(i);  
39.                 // 行不为空  
40.                 if (row != null) {  
41.                     //准备当前行 所储存值的map  
42.                     Map<String, Object> val=new HashMap<String, Object>();  
43.                       
44.                     boolean isValidRow = false;  
45.                       
46.                     // 遍历列  
47.                     for (int j = 0; j < cells; j++) {  
48.                         // 获取到列的值­  
49.                         try {  
50.                             HSSFCell cell = row.getCell(j);  
51.                             String cellValue = getCellValue(cell);  
52.                             val.put(keys.get(j),cellValue);   
53.                             if(!isValidRow && cellValue!=null && cellValue.trim().length()>0){  
54.                                 isValidRow = true;  
55.                             }  
56.                         } catch (Exception e) {  
57.                             e.printStackTrace();          
58.                         }  
59.                     }  
60.                     //第I行所有的列数据读取完毕，放入valuelist  
61.                     if(isValidRow){  
62.                         valueList.add(val);  
63.                     }  
64.                 }  
65.             }  
66.         } catch (IOException e) {  
67.             e.printStackTrace();  
68.         }finally {  
69.             fis.close();  
70.         }  
71.         return valueList;  
72.     } 

 

1. /** 
2.      * 读取2007-2013格式 
3.      * @param filePath 文件路径 
4.      * @return 
5.      * @throws java.io.IOException 
6.      */  
7.     public static List<Map> readExcel2007(String filePath) throws IOException{  
8.         List<Map> valueList=new ArrayList<Map>();  
9.         FileInputStream fis =null;  
10.         try {  
11.             fis =new FileInputStream(filePath);  
12.             XSSFWorkbook xwb = new XSSFWorkbook(fis);   // 构造 XSSFWorkbook 对象，strPath 传入文件路径  
13.             XSSFSheet sheet = xwb.getSheetAt(0);            // 读取第一章表格内容  
14.             // 定义 row、cell  
15.             XSSFRow row;  
16.             // 循环输出表格中的第一行内容   表头  
17.             Map<Integer, String> keys=new HashMap<Integer, String>();  
18.             row = sheet.getRow(0);  
19.             if(row !=null){  
20.                 //System.out.println("j = row.getFirstCellNum()::"+row.getFirstCellNum());  
21.                 //System.out.println("row.getPhysicalNumberOfCells()::"+row.getPhysicalNumberOfCells());  
22.                 for (int j = row.getFirstCellNum(); j <=row.getPhysicalNumberOfCells(); j++) {  
23.                     // 通过 row.getCell(j).toString() 获取单元格内容，  
24.                     if(row.getCell(j)!=null){  
25.                         if(!row.getCell(j).toString().isEmpty()){  
26.                             keys.put(j, row.getCell(j).toString());  
27.                         }  
28.                     }else{  
29.                         keys.put(j, "K-R1C"+j+"E");  
30.                     }  
31.                 }  
32.             }  
33.             // 循环输出表格中的从第二行开始内容  
34.             for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getPhysicalNumberOfRows(); i++) {  
35.                 row = sheet.getRow(i);  
36.                 if (row != null) {  
37.                     boolean isValidRow = false;  
38.                     Map<String, Object> val = new HashMap<String, Object>();  
39.                     for (int j = row.getFirstCellNum(); j <= row.getPhysicalNumberOfCells(); j++) {  
40.                         XSSFCell cell = row.getCell(j);  
41.                         if (cell != null) {  
42.                             String cellValue = null;  
43.                             if(cell.getCellType()==XSSFCell.CELL_TYPE_NUMERIC){  
44.                                 if(DateUtil.isCellDateFormatted(cell)){  
45.                                     cellValue = new DataFormatter().formatRawCellContents(cell.getNumericCellValue(), 0, "yyyy-MM-dd HH:mm:ss");  
46.                                 }  
47.                                 else{  
48.                                     cellValue = String.valueOf(cell.getNumericCellValue());  
49.                                 }  
50.                             }  
51.                             else{  
52.                                 cellValue = cell.toString();  
53.                             }  
54.                             if(cellValue!=null&&cellValue.trim().length()<=0){  
55.                                 cellValue=null;  
56.                             }  
57.                             val.put(keys.get(j), cellValue);  
58.                             if(!isValidRow && cellValue!= null && cellValue.trim().length()>0){  
59.                                 isValidRow = true;  
60.                             }  
61.                         }  
62.                     }  
63.   
64.                     // 第I行所有的列数据读取完毕，放入valuelist  
65.                     if (isValidRow) {  
66.                         valueList.add(val);  
67.                     }  
68.                 }  
69.             }  
70.         } catch (IOException e) {  
71.             e.printStackTrace();  
72.         }finally {  
73.             fis.close();  
74.         }  
75.   
76.         return valueList;  
77.     }  
78.       
80.     /** 
81.      * 文件操作 获取文件扩展名 
82.      *  
83.      * @Author: sunny 
84.      * @param filename 
85.      *            文件名称包含扩展名 
86.      * @return 
87.      */  
88.     public static String getExtensionName(String filename) {  
89.         if ((filename != null) && (filename.length() > 0)) {  
90.             int dot = filename.lastIndexOf('.');  
91.             if ((dot > -1) && (dot < (filename.length() - 1))) {  
92.                 return filename.substring(dot + 1);  
93.             }  
94.         }  
95.         return filename;  
96.     }  

转载于:https://www.cnblogs.com/silentmuh/p/5319964.html