POI解析Excel

来源：互联网发布：javascript声明数组编辑：程序博客网时间：2024/06/06 00:25
原创作品，允许转载，转载时请务必以超链接形式标明文章原始出处、作者信息和本声明。否则将追究法律责任。http://programmer.blog.51cto.com/2859493/1266937
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class FileUtil
{
    /**
     * 解析excel文件到VO [完美支持97-03-07-10]
     * @description: 每个sheet列数<=50个，多出部分自动舍弃
     * @time: 上午10:15:11 2013-8-1
     * @param url 文件的全路径
     * @return sheet集合,文件不存在或没有sheet返回null
     */
    public static List<List<RowVO>> excleTOVO(String url){
        String errorMsg="";
        try{
            url=URLDecoder.decode(url,"utf-8"); //防止服务器路径中包含空格等问题
        }catch (UnsupportedEncodingException e1){
            e1.printStackTrace();
        }
        String suffix = url.substring(url.lastIndexOf("."));  // 文件后辍.
        List<List<RowVO>> excelSheets=null;
        File file=new File(url);
        if(file.exists()){
            try{
                Workbook workBook=null;
                InputStream is =new FileInputStream(new File(url));
                try{
                    if(".xls".equals(suffix)){ //97-03
                        workBook= new HSSFWorkbook(is);
                    }else if(".xlsx".equals(suffix)){ //2007
                        workBook = new XSSFWorkbook(is);
                    }else{
                        System.out.println("不支持的文件类型！");
                        return null;
                    }
                }catch (Exception e){
                    System.out.println("解析xls文件出错！");
                    e.printStackTrace();
                }finally{
                    try{
                        is.close();
                    }catch (Exception e2){
                                                                     
                    }
                }
                int sheets=null!=workBook?workBook.getNumberOfSheets():0;
                if(sheets>0){
                    excelSheets=new ArrayList<List<RowVO>>();
                    for (int i = 0; i < sheets; i++){
                        Sheet sheet = workBook.getSheetAt(i); //读取第一个sheet
                        int rows = sheet.getPhysicalNumberOfRows(); // 获得行数
                        List<RowVO> sheetList=new ArrayList<RowVO>();
                        if(rows>1){ //第一行默认为标题
    //                      sheet.getMargin(HSSFSheet.TopMargin);
                            for (int j = 1; j < rows; j++){
                                Row row = sheet.getRow(j);
                                RowVO commonVO=new RowVO();
                                int cells = row.getLastCellNum();// 获得列数
                                if(cells>0){
                                    for (int k = 0; k < cells; k++){
                                        Cell cell=row.getCell(k);
                                        cell.setCellType(Cell.CELL_TYPE_STRING); //全部置成String类型的单元格
    //                                  String cellValue="";
    //                                  if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
    //                                      cellValue=cell.getNumericCellValue()+"";
    //                                  }else{
    //                                      cellValue=cell.getStringCellValue();
    //                                  }
                                        if(k<=50){
                                            PropertyUtils.setProperty(commonVO, "str"+k, cell.getStringCellValue());
                                        }else{
                                            System.out.println("第"+(i+1)+"个sheet,第"+(j+1)+"行数据列数超过了最大储存的个数50，将自动舍弃!");
                                            break;
                                        }
                                    }
                                }else{
                                    errorMsg="第"+(j+1)+"行数据没有列数为空!";
                                }
                                sheetList.add(commonVO);
                            }
                        }else{
                            errorMsg="第"+(i+1)+"个sheet中数据行数<=1";
                        }
                        excelSheets.add(sheetList);
                    }
                }else{
                    errorMsg="没有sheet!";
                }
            }catch (Exception ex){
                ex.printStackTrace();
            }
        }else{
            errorMsg="文件不存在！";
        }
        if(errorMsg.length()>0){
            System.out.println("错误消息："+errorMsg);
        }
        return excelSheets;
    }
                                                 
    public static void main(String[] args){
        String fileName="C:/Users/Administrator.WPFFPBG4GYKE5ZX/Desktop/测试4.xls";
        List<List<RowVO>> excelSheets=FileUtil.excleTOVO(fileName);
        if(null!=excelSheets){
            for (int i = 0; i < excelSheets.size(); i++){
                List<RowVO> sheet=excelSheets.get(i);
                for (int j = 0; j < sheet.size(); j++){
                    RowVO commonVO=sheet.get(j);
                    System.out.println(commonVO.getStr0()+"|"+commonVO.getStr11());
                }
            }
        }
    }
                                                 
}
0 0