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

 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 stub
13     }
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 }

  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<NameEntity> listAll = new ArrayList<NameEntity>();
 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<NameEntity> 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<NameEntity> listAll = new ArrayList<NameEntity>();//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<NameEntity> 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<NameEntity> listAll = new ArrayList<NameEntity>();
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<NameEntity> 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 filePath
125      * @param sheetName
126      * @return
127      * @throws IOException
128      */
129     public List<NameEntity> 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<NameEntity> 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.8
147      * @param listAll
148      * @return
149      */
150     public List<NameEntity> groupByList(List<NameEntity> listAll){
151         List<NameEntity> result = new ArrayList<NameEntity>();
152         
153 //            Map<String,List<NameEntity>> map = listAll.stream().collect(Collectors.groupingBy(e->((NameEntity) e).getName()));
154             Map<String,List<NameEntity>> map = listAll.stream().collect(Collectors.groupingBy(e->((NameEntity) e).getNum()));
155             //下面对map进行迭代
156             Set<String> 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 list
178      * @throws IOException
179      */
180     public void just4Result(List<NameEntity> 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*256
187                 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 listAll
243      * @param sheet
244      * @return
245      */
246     public List<NameEntity> just4List(List<NameEntity> 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 sheet
290      * @return
291      */
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 }

 1 package com.sxd.test.unusualName;
 2 
 3 import java.io.File;
 4 
 5 import org.junit.Test;
 6 
 7 /**
 8  * 一键删除   异常文件以及 源文件
 9  * @author Administrator
10  *
11  */
12 public class DeleteUnusualFile {
13 
14     @Test
15     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){//若此路径是文件夹 且 下面的子文件不为null
27             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){//若此路径是文件夹 且 下面的子文件不为null
42             for (int i = 0; i < fileArr.length; i++) {
43                 if(fileArr[i].isFile()){
44                     fileArr[i].delete();
45                 }
46             }
47         }
48     }
49 }