【POI xls Java map】使用POI处理xls 抽取出异常信息 --java1.8Group by ---map迭代 -- 设置单元格高度...-白红宇

代码处理逻辑：

代码流程：

1.首先需要创建一个实体用来存储相关信息

1 package com.sxd.test.unusualName; 2  3 public class NameEntity { 4      5     private String name;//姓名 6     private String num;//编号 7     private String rsNum;//RS号 8     private String disease;//疾病 9     private String rsInfo;//rs值10     11     public NameEntity() {12         // TODO Auto-generated constructor stub13     }14 15     public NameEntity(String name, String num, String rsNum, String disease,16             String rsInfo) {17         super();18         this.name = name;19         this.num = num;20         this.rsNum = rsNum;21         this.disease = disease;22         this.rsInfo = rsInfo;23     }24 25     public String getName() {26         return name;27     }28 29     public void setName(String name) {30         this.name = name;31     }32 33     public String getNum() {34         return num;35     }36 37     public void setNum(String num) {38         this.num = num;39     }40 41     public String getRsNum() {42         return rsNum;43     }44 45     public void setRsNum(String rsNum) {46         this.rsNum = rsNum;47     }48 49     public String getDisease() {50         return disease;51     }52 53     public void setDisease(String disease) {54         this.disease = disease;55     }56 57     public String getRsInfo() {58         return rsInfo;59     }60 61     public void setRsInfo(String rsInfo) {62         this.rsInfo = rsInfo;63     }64     65 }

View Code

2.具体的处理方法

1 package com.sxd.test.unusualName;  2   3 import java.io.File;  4 import java.io.FileInputStream;  5 import java.io.FileOutputStream;  6 import java.io.IOException;  7 import java.util.ArrayList;  8 import java.util.List;  9 import java.util.Map; 10 import java.util.Set; 11 import java.util.stream.Collectors; 12  13 import org.apache.poi.hssf.usermodel.HSSFCellStyle; 14 import org.apache.poi.hssf.usermodel.HSSFFont; 15 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 16 import org.apache.poi.hssf.util.HSSFColor; 17 import org.apache.poi.ss.usermodel.Cell; 18 import org.apache.poi.ss.usermodel.Row; 19 import org.apache.poi.ss.usermodel.Sheet; 20 import org.junit.Test; 21  22  23 public class Xls4Name { 24      25     /** 26      * 主方法--处理多份xls文件中 不同sheet[疾病]下  姓名/位点一致||订单编号一致，但是位点值不同的信息 27      * @throws IOException 28      */ 29     @Test 30     public void mainTest() throws IOException{ 31          32         test1(); 33         test2(); 34         test3(); 35          36          37     } 38     /** 39      * 处理 肝癌  冠心病 脑梗  位点信息为rs1801133 40      * @throws IOException 41      */ 42     public void test1() throws IOException{ 43         File file = new File("D:/20161110-时代基因大批量检测结果/"); 44         File [] allFile = file.listFiles(); 45         if(allFile.length > 0){ 46             List
      
        listAll = new ArrayList
       
        (); 47             String outPath = null; 48             for (int i = 0; i < allFile.length; i++) { 49                 String fileName = allFile[i].getName(); 50                 String filePath = "D:/20161110-时代基因大批量检测结果/"+fileName; 51                 String [] sheetName = {"肝癌","冠心病","脑梗（缺血性脑卒中）"}; 52                 String RsNum = "rs1801133"; 53                 outPath = "d:/异常/"+"异常1---"+fileName.substring(0,fileName.lastIndexOf("."))+".xls"; 54                 //获取原始数据 55                 listAll = just4ListArr(filePath,RsNum,sheetName); 56             } 57             //对原始数据分组 58             List
        
          list = groupByList(listAll); 59             //将处理好的数据存入异常文件 60             if(list.size() > 0){ 61                 just4Result(list,outPath); 62             } 63         } 64     } 65     /** 66      * 处理 胃癌  食管癌  位点信息  rs2274223 67      * @throws IOException 68      */ 69     public void test2() throws IOException{ 70         File file = new File("D:/20161110-时代基因大批量检测结果/"); 71         File [] allFile = file.listFiles(); 72         if(allFile.length > 0){ 73             List
         
           listAll = new ArrayList
          
           ();//listAll放在循环内部 可以单独处理多个文件 放在循环外面可以将多个文件统一处理 74             String outPath = null; 75             for (int i = 0; i < allFile.length; i++) { 76                 String fileName = allFile[i].getName(); 77                 String filePath = "D:/20161110-时代基因大批量检测结果/"+fileName; 78                 String [] sheetName = {"胃癌","食管癌"}; 79                 String RsNum = "rs2274223"; 80                 outPath = "d:/异常/"+"异常2---"+fileName.substring(0,fileName.lastIndexOf("."))+".xls"; 81                 //获取原始数据 82                 listAll = just4ListArr(filePath,RsNum,sheetName); 83                  84             } 85             //对原始数据分组 86             List
           
             list = groupByList(listAll); 87 //将处理好的数据存入异常文件 88 if(list.size() > 0){ 89 just4Result(list,outPath); 90 } 91 } 92 } 93 /** 94 * 高血压 脑梗 位点信息rs699 95 * @throws IOException 96 */ 97 public void test3() throws IOException{ 98 File file = new File("D:/20161110-时代基因大批量检测结果/"); 99 File [] allFile = file.listFiles();100 if(allFile.length > 0){101 List
            
              listAll = new ArrayList
             
              ();102 String outPath = null;103 for (int i = 0; i < allFile.length; i++) {104 String fileName = allFile[i].getName();105 String filePath = "D:/20161110-时代基因大批量检测结果/"+fileName;106 String [] sheetName = {"高血压","脑梗（缺血性脑卒中）"};107 String RsNum = "rs699";108 outPath = "d:/异常/"+"异常3---"+fileName.substring(0,fileName.lastIndexOf("."))+".xls";109 110 //获取原始数据111 listAll = just4ListArr(filePath,RsNum,sheetName);112 }113 //对原始数据分组114 List
              
                list = groupByList(listAll);115 //将处理好的数据存入异常文件116 if(list.size() > 0){117 just4Result(list,outPath);118 }119 }120 }121 122 /**123 * 根据传入的 文件路径以及sheet名称 分别创建sheet，并传入just4List(listAll,sheet)进行处理124 * @param filePath125 * @param sheetName126 * @return127 * @throws IOException128 */129 public List
               
                 just4ListArr(String filePath,String RsNum,String ...sheetName) throws IOException{130 FileInputStream in = new FileInputStream(new File(filePath));131 HSSFWorkbook work = new HSSFWorkbook(in);132 133 List
                
                  listAll = new ArrayList<>();134 for (String sName : sheetName) {135 Sheet sheet = work.getSheet(sName);//根据sheet名称 获取几种疾病的信息136 listAll = just4List(listAll,sheet,RsNum);137 }138 139 work.close();140 in.close();141 return listAll;142 143 }144 145 /**146 * 对xls中抽取出来的原始数据进行分组处理 按照用户名name称分组/或者按照订单号num分组 java1.8147 * @param listAll148 * @return149 */150 public List
                 
                   groupByList(List
                  
                    listAll){151 List
                   
                     result = new ArrayList
                    
                     ();152 153 // Map
                     
                      
                       > map = listAll.stream().collect(Collectors.groupingBy(e->((NameEntity) e).getName()));154 Map
                       
                        
                         > map = listAll.stream().collect(Collectors.groupingBy(e->((NameEntity) e).getNum()));155 //下面对map进行迭代156 Set
                         
                           set = map.keySet();157 for (String string : set) {158 int listSize = map.get(string).size();159 if(listSize > 1){160 String rs = map.get(string).get(0).getRsInfo();//获取第一个的rs号161 for (int i = 1; i < listSize; i++) { //循环判断 若出现rs号不一致 即抽取出同组的多条信息 保存162 if(!map.get(string).get(i).getRsInfo().equals(rs)){163 result.addAll(map.get(string));164 break;165 }166 }167 }168 }169 170 171 return result;172 }173 174 175 /**176 * 根据最终传入的list 177 * @param list178 * @throws IOException179 */180 public void just4Result(List
                          
                            list,String outPath) throws IOException{181 //输出文档182 FileOutputStream out = new FileOutputStream(new File(outPath));183 HSSFWorkbook workOut = new HSSFWorkbook();184 //设置sheet名称185 Sheet sheet = workOut.createSheet("异常名称");186 //设置第2列的宽度为 100*256187 sheet.setColumnWidth(1, 50 * 256);188 sheet.setColumnWidth(2, 15 * 256);189 sheet.setColumnWidth(3, 30 * 256);190 //创建首行191 Row row1 = sheet.createRow(0);192 //首行 行高193 row1.setHeight((short)500);194 //首行 列名数组195 String []rowName = {"姓名","编号（采样号）","RS号","疾病","RS值"};196 //设置首行样式197 HSSFCellStyle cellStyle = workOut.createCellStyle(); 198 //创建字体199 HSSFFont font = workOut.createFont();200 //设置加粗201 font.setBold(true);202 //设置字体颜色203 font.setColor(HSSFColor.AQUA.index);204 //设置字体大小205 font.setFontHeightInPoints((short)14);206 cellStyle.setFont(font);207 for (int i = 0; i < 5; i++) {208 Cell cell = row1.createCell(i);209 cell.setCellValue(rowName[i]);210 cell.setCellStyle(cellStyle);211 }212 213 //对应列放入对应数据214 for (int i = 0; i < list.size(); i++) {215 Row row2 = sheet.createRow(i+1);216 NameEntity nameEntity = list.get(i);217 for (int j = 0; j < 5; j++) {218 Cell cell = row2.createCell(j);219 switch (j) {220 case 0: cell.setCellValue(nameEntity.getName()); break;221 case 1: cell.setCellValue(nameEntity.getNum()); break;222 case 2: cell.setCellValue(nameEntity.getRsNum()); break;223 case 3: cell.setCellValue(nameEntity.getDisease()); break;224 case 4: cell.setCellValue(nameEntity.getRsInfo()); break;225 226 default: cell.setCellValue("数据异常");227 break;228 }229 230 }231 }232 233 workOut.write(out);234 out.close();235 workOut.close();236 }237 238 239 240 /**241 * 从原始xls文件中抽取出来最原始的数据 存放如listAll中242 * @param listAll243 * @param sheet244 * @return245 */246 public List
                           
                             just4List(List
                            
                              listAll,Sheet sheet,String RsNum){247 String diseaseName = sheet.getSheetName();248 249 Row row1 = sheet.getRow(0);250 Cell cell = row1.getCell(0);251 int maxRowNum = just4MaxRowNum(sheet);//先计算出 最大行数252 int maxCellNum = row1.getLastCellNum();//最大列数253 row1 = sheet.getRow(2); //固定 rsNum放在第三行 故 获取第三行254 int rsNum = 0;255 //判断对应的rsNum在第几列 记录列号256 cell = row1.getCell(4);257 String value = cell.getRichStringCellValue().toString();258 if(RsNum.equals(value)){259 rsNum = 4;260 }else {261 rsNum = 5;262 }263 264 //循环从5开始 因为原始文件中的前5行非数据265 for (int i = 5; i < maxRowNum ; i++) { //然后纵向提取 获取所有rs信息266 NameEntity nameEntity = new NameEntity();//实例化对象267 row1 = sheet.getRow(i);268 String rs = row1.getCell(rsNum).getStringCellValue();//获取rs 由于rs号不确定在第几列 故进行判断269 String name = row1.getCell(3).getStringCellValue();//获取name name列确定列号为3 即在第四行 也可以自行判断270 String num = null;271 if(row1.getCell(2).getCellType() == Cell.CELL_TYPE_NUMERIC){272 num = String.valueOf(row1.getCell(2).getNumericCellValue());//获取num 编号 条形码273 }else{274 num = String.valueOf(row1.getCell(2).getStringCellValue());275 }276 nameEntity.setNum(num);277 nameEntity.setName(name);278 nameEntity.setRsNum(RsNum);279 nameEntity.setDisease(diseaseName);280 nameEntity.setRsInfo(rs);281 listAll.add(nameEntity);282 }283 284 return listAll;285 }286 287 /**288 * 获取最大行数 由于人为原因 xls中某个单元格中内容虽然已经删除 但是单元格的对象依旧创建，因此需要自己获取有效行数289 * @param sheet290 * @return291 */292 public int just4MaxRowNum(Sheet sheet){293 int maxRowNum = sheet.getLastRowNum();//获取最大行号 但不是有效行号294 295 for (int i = 5; i < maxRowNum; i++) {296 Row row = sheet.getRow(i);297 Cell cell = row.getCell(3);298 if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK){ //判断cell单元格为null或者单元格类型为blank就表示此单元格没有数据 那这一行的上一行就是有效行数299 maxRowNum = i-1;300 break;301 }302 } 303 return maxRowNum;304 }305 }

View Code

3.在处理完成业务之后可以调用方法将文件夹中的使用过的文件删除

1 package com.sxd.test.unusualName; 2  3 import java.io.File; 4  5 import org.junit.Test; 6  7 /** 8  * 一键删除   异常文件以及 源文件 9  * @author Administrator10  *11  */12 public class DeleteUnusualFile {13 14     @Test15     public void deleteFile(){16         deleteSourceFile();17         deleteUnusualFile();18     }19     20     /**21      *删除源文件 22      */23     public void deleteSourceFile(){24         File file = new File("D:/20161110-时代基因大批量检测结果");25         File[] fileArr = file.listFiles();26         if(file.isDirectory() && fileArr.length > 0){
    //若此路径是文件夹 且 下面的子文件不为null27             for (int i = 0; i < fileArr.length; i++) {28                 if(fileArr[i].isFile()){29                     fileArr[i].delete();30                 }31             }32         }33         34     }35     /**36      * 删除异常文件37      */38     public void deleteUnusualFile(){39         File file = new File("D:/异常");40         File[] fileArr = file.listFiles();41         if(file.isDirectory() && fileArr.length > 0){
    //若此路径是文件夹 且 下面的子文件不为null42             for (int i = 0; i < fileArr.length; i++) {43                 if(fileArr[i].isFile()){44                     fileArr[i].delete();45                 }46             }47         }48     }49 }

View Code