Java--Excel操作 

 1 public static List<Info> readXml(String fileName, Map<String, Fuck> pcMap) throws Exception{  
 2         boolean isE2007 = false;    //判断是否是excel2007格式  
 3         if(fileName.endsWith("xlsx"))  
 4             isE2007 = true;  
 5         try {  
 6             InputStream input = new FileInputStream(fileName);  //建立输入流  
 7             Workbook wb  = null;  
 8             //根据文件格式(2003或者2007)来初始化  
 9             if(isE2007) {
10                 wb = new XSSFWorkbook(input);  
11             } else {
12                 wb = new HSSFWorkbook(input);  
13             }
14             Sheet sheet = wb.getSheetAt(0);     //获得第一个表单  
15             Iterator<Row> rows = sheet.rowIterator(); //获得第一个表单的迭代器  
16             
17             
18             //初始化列标
19             Map<String, Integer> map = new HashMap<String, Integer>();
20             if(rows.hasNext()) {
21                 Row row = rows.next();
22                 Iterator<Cell> cells = row.cellIterator();
23                 int i = 0;
24                 while(cells.hasNext()) {
25                     Cell cell = cells.next();
26                     if(map.containsKey(cell.toString())) {
27                         continue ;
28                     }
29                     map.put(cell.toString(), i++);
30                 }
31             }
32 //            for(Map.Entry<String, Integer> set : map.entrySet()) {
33 //                System.out.println(set.getKey() + " " + set.getValue());
34 //            }
35             
36             
37             //取数据
38             List<Info> dataList = new ArrayList<Info>();
39             while (rows.hasNext()) {  
40                 Row row = rows.next();  //获得行数据  
41 //                System.out.println("Row #" + row.getRowNum());  //获得行号从0开始  
42                 Iterator<Cell> cells = row.cellIterator();    //获得第一行的迭代器  
43 
44                 String msg = "";
45                 if(cells.hasNext()) {
46                     Cell cell = cells.next();
47                     cell.setCellType(Cell.CELL_TYPE_STRING);
48                     Info info = new Info();
49                     
50                     Cell org_noCell = row.getCell(map.get("美国编号"));
51                     org_noCell.setCellType(Cell.CELL_TYPE_STRING);
52                     info.setOrg_no(org_noCell.toString());
53                     
54                     Cell org_nameCell = row.getCell(map.get("餐厅名字"));
55                     org_nameCell.setCellType(Cell.CELL_TYPE_STRING);
56                     info.setOrg_name(org_nameCell.toString());
57                     
58                     Cell szcsCell = row.getCell(map.get("城市"));
59                     szcsCell.setCellType(Cell.CELL_TYPE_STRING);
60                     info.setSzcs(szcsCell.toString());
61                     
62                     Cell addrCell = row.getCell(map.get("地址"));
63                     addrCell.setCellType(Cell.CELL_TYPE_STRING);
64                     info.setAddr(addrCell.toString());
65                     
66                     Cell telCell = row.getCell(map.get("电话"));
67                     telCell.setCellType(Cell.CELL_TYPE_STRING);
68                     info.setTel(telCell.toString());
69                     
70                     Cell taxcodeCell = row.getCell(map.get("对应纳税号"));
71                     taxcodeCell.setCellType(Cell.CELL_TYPE_STRING);
72                     info.setTaxcode(taxcodeCell.toString());
73                     
74                     msg = org_noCell.toString() + org_nameCell + szcsCell + addrCell + telCell + taxcodeCell;
75                     if("".equals(msg.trim())) {
76                         continue ;
77                     }
78                     if(info != null) {
79                         if(pcMap.containsKey(taxcodeCell.toString())) {
80                             Fuck fuck = pcMap.get(taxcodeCell.toString());
81                             info.setPayee(info.getOrg_no());
82                             info.setChecker(fuck.getChecker());
83                         }
84                         dataList.add(info);
85                     }
86                 }
87             }
88             return dataList;
89         } catch (IOException ex) {  
90             ex.printStackTrace();  
91             throw new Exception("请参考模板并选择正确的文件！");
92         }
93     }

 

posted @ 2017-03-13 08:43  MicroCat  阅读(360)  评论(0)    收藏  举报