POI使用:用poi接口不区分xls/xlsx格式解析Excel文档(41种日期格式解析方法,5种公式结果类型解析方法,3种常用数值类型精度控制办法)

来源:互联网 发布:通江集团 知乎 编辑:程序博客网 时间:2024/06/05 10:29

一、使用poi解析excel文档

注:全部采用poi接口进行解析,不需要区分xls、xlsx格式,不需要判断文档类型。
poi中的日期格式判断仅支持欧美日期习惯,对国内的日期格式并不支持判断,怎么办?所以通过日期格式判断是极其重要的手段,因为日期在excel中也是double类型的数值,所以靠类型判断是极不可靠的,但是有几种常用的日期格式(比如:yyyy-mm-dd,yy-mm-dd等)还是可以通过类型进行判断,因为它们的类型在excel中属于保留值,这点很重要,毕竟office文档想要正确显示出日期类型也是需要通过类型进行判断的。

1、打开excel文件

[java] view plain copy
 print?在CODE上查看代码片派生到我的代码片
  1. /** 
  2.     * 解析excel文档(支持xls、xlsx格式) 
  3.     * @param path - 文件路径 
  4.     * @param formula - 是否获取公式结果 
  5.     * @param sdf - 日期格式 
  6.     * @return  List - 结果表 
  7.     * @throws Exception - 打开文件失败 
  8.     */  
  9.    public List<Map> parse(String path, boolean formula, SimpleDateFormat sdf)  
  10.        throws Exception  
  11.    {  
  12.        path = this.getClass().getResource("/").getPath() + path;  
  13.        File file = new File(path);  
  14.        List<Map>list=null;  
  15.        if (file.isFile())  
  16.        {  
  17.            Workbook wb = createWorkbook(file);  
  18.            System.out.println("当前活动sheet" + wb.getActiveSheetIndex());  
  19.            System.out.println("当前几个文档"+wb.getNumberOfSheets());  
  20.            list=new ArrayList<Map>();  
  21.            int max=wb.getNumberOfSheets();  
  22.            for(int sheetNum=0;sheetNum<max;sheetNum++){  
  23.                list.add(getSheet(wb, sheetNum, formula, sdf));//解析sheet表  
  24.            }  
  25.        }  
  26.        else  
  27.        {  
  28.            throw new Exception("文件不存在");  
  29.        }  
  30.        return list;  
  31.    }  

2、解析sheet表

[java] view plain copy
 print?在CODE上查看代码片派生到我的代码片
  1. /** 
  2.      * 获取sheet表内容 
  3.      * @param wb - 文档 
  4.      * @param sheetNum - 打开那张sheet表 
  5.      * @param formula - 是否获得公式结果 
  6.      * @param sdf - 日期格式 
  7.      * @return Map - 结果表 
  8.      */  
  9.     public Map<Integer, Map<Integer, String>> getSheet(Workbook wb, int sheetNum, boolean formula, SimpleDateFormat sdf)  
  10.     {  
  11.         String sheetName = wb.getSheetName(sheetNum);  
  12.         System.out.println("打开了sheet表:" + sheetName);  
  13.         Sheet sheet = wb.getSheet(sheetName);  
  14.         Map<Integer, Map<Integer, String>> map=getRowAndCell(sheet, formula, sdf);//解析所有单元格  
  15.         return map;  
  16.   
  17.     }  

3、解析单元格内容

[java] view plain copy
 print?在CODE上查看代码片派生到我的代码片
  1. /** 
  2.    * 从sheet表中获取每行每列的值 
  3.    * @param sheet - sheet表 
  4.    * @param formula - 是否获取公式结果 
  5.    * @param sdf - 日期格式 
  6.    * @return Map - 结果表 
  7.    */  
  8.    public Map<Integer, Map<Integer, String>> getRowAndCell(Sheet sheet, boolean formula, SimpleDateFormat sdf)  
  9.    {  
  10.        Map<Integer,Map<Integer,String>>rowMap=null;  
  11.        int firstRowNum = sheet.getFirstRowNum();  
  12.        int lastRowNum = sheet.getLastRowNum();  
  13.         
  14.        if(lastRowNum>0){  
  15.        rowMap=new HashMap<Integer,Map<Integer,String>>();  
  16.          
  17.        // 遍历行  
  18.        for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++ )  
  19.        {  
  20.            Row row = sheet.getRow(rowNum);  
  21.            int firstCellNum = row.getFirstCellNum();  
  22.            int lastCellNum = row.getLastCellNum();  
  23.            Map<Integer,String> cellMap=new HashMap<Integer,String> ();  
  24.            // 遍历列  
  25.            for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++ )  
  26.            {  
  27.                Cell cell = row.getCell(cellNum);  
  28.                int type = cell.getCellType();  
  29.                String data=getValue(cell, formula, sdf);//根据单元格具体类型获得内容  
  30.                System.out.println("第" + rowNum + "行,第" + cellNum + "列,类型是" + type +",内容是:"+data);  
  31.                cellMap.put(cellNum, data);  
  32.            }  
  33.            rowMap.put(rowNum, cellMap);  
  34.        }  
  35.        }  
  36.        return rowMap;  
  37.    }  

4、判断单元格类型并获取内容

[java] view plain copy
 print?在CODE上查看代码片派生到我的代码片
  1. /** 
  2.     * 判断数值类型自动解析日期格式等其他特殊类型 
  3.     *  
  4.     * @param data - 存放数据 
  5.     * @param cell - 单元格 
  6.     * @param sdf - 日期格式 
  7.     * @return String - 结果 
  8.     */  
  9.    private String parseDate(Cell cell, SimpleDateFormat sdf)  
  10.    {  
  11.        System.out.println("是否是有效的日期格式:"+DateUtil.isCellDateFormatted(cell));  
  12.        //poi的日期判断仅适用于欧美日期格式,对中文日期不支持,另外增加两个方法判断中文格式日期  
  13.        if (DateUtil.isCellDateFormatted(cell)||isReserved(cell.getCellStyle().getDataFormat())||isDateFormat(cell.getCellStyle().getDataFormatString()))  
  14.        {  
  15.            return sdf.format(cell.getDateCellValue());  
  16.        }  
  17.        System.out.println("格式:"+cell.getCellStyle().getDataFormatString()+",类型"+cell.getCellStyle().getDataFormat());  
  18.        Double d=cell.getNumericCellValue();  
  19.        if(cell.getCellStyle().getDataFormat()==0)  
  20.        {  
  21.           DecimalFormat dfs = new DecimalFormat("0");  
  22.           return dfs.format(d);  
  23.        }  
  24.        return String.valueOf(d);  
  25.       
  26.    }  


[java] view plain copy
 print?在CODE上查看代码片派生到我的代码片
  1. /** 
  2.     * 获取单元格内容 
  3.     * @param cell - 单元格 
  4.     * @param sdf - 日期格式 
  5.     * @param formula - 是否得出公式结果 
  6.     * @return String - 单元格内容 
  7.     */  
  8.    private String getValue(Cell cell, boolean formula, SimpleDateFormat sdf)  
  9.    {  
  10.        String data = null;  
  11.        switch (cell.getCellType())  
  12.        {  
  13.            case Cell.CELL_TYPE_NUMERIC: // 数字  
  14.                data = parseDate(cell, sdf);  
  15.                break;  
  16.            case Cell.CELL_TYPE_STRING: // 字符串  
  17.   
  18.                data = cell.getStringCellValue();  
  19.                break;  
  20.            case Cell.CELL_TYPE_BOOLEAN: // Boolean  
  21.                data = String.valueOf(cell.getBooleanCellValue());  
  22.                break;  
  23.            case Cell.CELL_TYPE_FORMULA: // 公式  
  24.                // 解析公式  
  25.                data = parseFormula(cell, formula);  
  26.                break;  
  27.            case Cell.CELL_TYPE_BLANK: // 空格  
  28.                System.out.println("遇到一个空格");  
  29.                data = null;  
  30.                break;  
  31.            case Cell.CELL_TYPE_ERROR:// 错误  
  32.                System.out.println("遇到一个错误");  
  33.                data = null;  
  34.                break;  
  35.            default:  
  36.                data = null;  
  37.        }  
  38.        return data;  
  39.    }  

二、poi的6种基本类型

Cell.CELL_TYPE_NUMERIC: // 数值
 
Cell.CELL_TYPE_STRING: // 字符串

Cell.CELL_TYPE_BOOLEAN: // Boolean

Cell.CELL_TYPE_FORMULA: // 公式

Cell.CELL_TYPE_BLANK: // 空格

Cell.CELL_TYPE_ERROR:// 错误

三、41种日期格式解析方法

注意:看着好像有几个是重复的,但是它们的日期格式是不一样的(比如yyyy-m-d与yyyy-mm-dd同样都是显示:2015-12-13)

日期表(1-41):

1 2015-12-13
2 2015年12月
3 2015年12月15日
十二月十六日
二〇一五年十二月
二〇一五年十二月十八日
12月13日
2015-12-13 12:00 AM
2015-12-14 0:00
10 15-12-15
11 12-16
12 12-17-15
13 12-18-15
14 19-Dec
15 20-Dec-15
16 21-Dec-15
17 Dec-15
18 December-15
19 D
20 D-15
21 2015年12月26日
22 2015年12月
23 二〇一五年十二月二十七日
24 二〇一五年十二月
25 十二月二十九日
26 12月30日
27 星期四
28 
29 2016-1-2
30 2016-1-3 12:00 AM
31 2016-1-4 0:00
32 16-1-5
33 1-6
34 1-7-16
35 01-08-16
36 9-Jan
37 10-Jan-16
38 Jan-16
39 January-16
40 J
41      J-16


2、日期对应的类型(0-40对应上面日期表1-41)

序号=类型

0=14, 

1=27, 

2=31, 

3=176, 

4=177, 

5=178, 

6=28, 

7=179, 

8=22, 

9=180, 

10=181, 

11=30, 

12=182, 

13=16, 

14=15, 

15=183, 

16=17, 

17=184, 

18=185, 

19=186, 

20=187, 

21=188, 

22=189, 

23=190, 

24=191, 

25=192, 

26=193, 

27=194, 

28=195, 

29=196, 

30=197, 

31=198, 

32=199, 

33=200, 

34=201, 

35=202, 

36=203, 

37=204, 

38=205, 

39=206, 

40=207


3、对应的日期格式(0-40,同上):

0=m/d/yy, 

1=reserved-0x1b, 

2=reserved-0x1f, 

3=[DBNum1][$-804]m"月"d"日", 

4=[DBNum1][$-804]yyyy"年"m"月", 

5=[DBNum1][$-804]yyyy"年"m"月"d"日", 

6=reserved-0x1c, 

7=yyyy/m/d\ h:mm\ AM/PM, 

8=m/d/yy h:mm, 

9=yy/m/d, 

10=m/d, 

11=reserved-0x1e, 

12=mm/dd/yy, 

13=d-mmm, 

14=d-mmm-yy, 

15=dd/mmm/yy, 

16=mmm-yy, 

17=mmmm/yy, 

18=mmmmm, 

19=mmmmm/yy, 

20=yyyy"年"m"月"d"日";@, 

21=yyyy"年"m"月";@, 

22=[DBNum1][$-804]yyyy"年"m"月"d"日";@, 

23=[DBNum1][$-804]yyyy"年"m"月";@, 

24=[DBNum1][$-804]m"月"d"日";@, 

25=m"月"d"日";@, 

26=[$-804]aaaa;@, 

27=[$-804]aaa;@, 

28=yyyy/m/d;@, 

29=[$-409]yyyy/m/d\ h:mm\ AM/PM;@, 

30=yyyy/m/d\ h:mm;@, 

31=yy/m/d;@, 

32=m/d;@, 

33=m/d/yy;@, 

34=mm/dd/yy;@, 

35=[$-409]d/mmm;@, 

36=[$-409]d/mmm/yy;@, 

37=[$-409]mmm/yy;@, 

38=[$-409]mmmm/yy;@, 

39=[$-409]mmmmm;@, 

40=[$-409]mmmmm/yy;@


根据上述的格式进行单独判断就可以正确解析所有日期格式。下面是我的实现方式,可能效率不高,如果有其它高效的方法可以提出来,欢迎一起交流

4、解析数值类型中的日期:

[java] view plain copy
 print?在CODE上查看代码片派生到我的代码片
  1. /** 
  2.     * 判断数值类型自动解析日期格式等其他特殊类型 
  3.     *  
  4.     * @param data - 存放数据 
  5.     * @param cell - 单元格 
  6.     * @param sdf - 日期格式 
  7.     * @return String - 结果 
  8.     */  
  9.    private String parseDate(Cell cell, SimpleDateFormat sdf)  
  10.    {  
  11.        System.out.println("是否是有效的日期格式:"+DateUtil.isCellDateFormatted(cell));  
  12.        //poi的日期判断仅适用于欧美日期格式,对中文日期不支持,另外增加两个方法判断中文格式日期  
  13.        if (DateUtil.isCellDateFormatted(cell)||isReserved(cell.getCellStyle().getDataFormat())||isDateFormat(cell.getCellStyle().getDataFormatString()))  
  14.        {  
  15.            return sdf.format(cell.getDateCellValue());  
  16.        }  
  17.        System.out.println("格式:"+cell.getCellStyle().getDataFormatString()+",类型"+cell.getCellStyle().getDataFormat());  
  18.        Double d=cell.getNumericCellValue();  
  19.        if(cell.getCellStyle().getDataFormat()==0)  
  20.        {  
  21.           DecimalFormat dfs = new DecimalFormat("0");  
  22.           return dfs.format(d);  
  23.        }  
  24.        return String.valueOf(d);  
  25.       
  26.    }  


[java] view plain copy
 print?在CODE上查看代码片派生到我的代码片
  1. <span style="font-size:18px;"/** 
  2.      * 是否是日期格式保留字段 
  3.      * @return boolean<ul><li>true - 是保留字段</li><li>false - 不是</li></ul> 
  4.      */  
  5.     private boolean isReserved(short reserv)  
  6.     {  
  7.         if(reserv>=27&&reserv<=31)  
  8.         {  
  9.             return true;  
  10.         }  
  11.         return false;  
  12.     }  
  13.     /** 
  14.      * 判断是否是中文日期格式 
  15.      * @param isNotDate 
  16.      * @return boolean<ul><li>true - 是日期格式</li><li>false - 不是</li></ul> 
  17.      */  
  18.     private boolean isDateFormat(String isNotDate)  
  19.     {  
  20.         if(isNotDate.contains("年")||isNotDate.contains("月")||isNotDate.contains("日"))  
  21.         {  
  22.             return true;  
  23.         }  
  24.         else if(isNotDate.contains("aaa;")||isNotDate.contains("AM")||isNotDate.contains("PM"))  
  25.         {  
  26.             return true;  
  27.         }  
  28.     
  29.           
  30.         return false;  
  31.     }</span>  


四、5种公式类型及结果解析方法

1、公式只有一种,结果分为5种

除了基本类型中的空格不可能是结果,其他几种结果都可能是公式计算出来的结果

2、解析5种公式

[java] view plain copy
 print?在CODE上查看代码片派生到我的代码片
  1. <span style="font-size:18px;"/** 
  2.      * 解析公式 
  3.      *  
  4.      * @param data  - 存放数据 
  5.      * @param cell - 单元格 
  6.      * @param formula - 是否计算公式结果 
  7.      * @return String - 结果 
  8.      */  
  9.     private String parseFormula(Cell cell, boolean formula)  
  10.     {  
  11.         String data = null;  
  12.         if (formula)  
  13.         {  
  14.             switch (cell.getCachedFormulaResultType())  
  15.             {  
  16.                 case 0:  
  17.                     if (0 == cell.getCellStyle().getDataFormat())  
  18.                     {  
  19.                         DecimalFormat df = new DecimalFormat("0");  
  20.                         data = df.format(cell.getNumericCellValue());  
  21.                     }  
  22.                     else  
  23.                     {  
  24.                         data = String.valueOf(cell.getNumericCellValue());  
  25.                     }  
  26.                     break;  
  27.                 case 1:  
  28.                     data = String.valueOf(cell.getRichStringCellValue());  
  29.                     break;  
  30.                 case 4:  
  31.                     data = String.valueOf(cell.getBooleanCellValue());  
  32.                     break;  
  33.                 case 5:  
  34.                     data = String.valueOf(cell.getErrorCellValue());  
  35.                     break;  
  36.                 default:  
  37.                     data = cell.getCellFormula();  
  38.             }  
  39.         }  
  40.         else  
  41.         {  
  42.             data = cell.getCellFormula();  
  43.         }  
  44.         return data;  
  45.     }</span>  


五、3种数值类型(货币,浮点、整数)精度控制(正确解析整数型数值)

货币等同于浮点数
整数一般用于序号和手机号码,邮编等等整数型数值表示
1、很有意思的是整数型的数据,如果没有设置自定义格式,那么是这样的:
默认格式,类型值
123 类型是 0
18094.75 类型是 2
100.02119422386752 类型是176
99.95066018068103 类型是178

2、如果设置了特殊格式,比如货币类型或者自定义类型,都属于特殊类型:
格式,类型值
_($*#,##0.00_);_($*(#,##0.00);_($*"-"??_);_(@_),类型44
_(*#,##0.00_);_(*(#,##0.00);_(*"-"??_);_(@_),类型43
0.0000_ ,类型208
"¥"#,##0.000;"¥"\-#,##0.000,类型209

总结:可能有人已经看出来了,日期格式中有几种类型还是跟数值类型一样的,怎么办?所以通过日期格式判断是极其重要方法,因为日期在excel中也是double类型的数值,所以靠类型判断是极不可靠的,但是有几种常用的日期格式(比如:yyyy-mm-dd,yy-mm-dd等)还是可以通过类型进行判断,因为它们的类型在excel中属于保留值,并不会用在其他数值,所以这点事比较放心的。
0 0
原创粉丝点击