使用POI版本：

①

②

 

③ ④

 

 

 

 

1 package com.poi.dealXlsx;  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  10  11 import org.apache.poi.openxml4j.exceptions.InvalidFormatException; 12 import org.apache.poi.ss.usermodel.Cell; 13 import org.apache.poi.ss.usermodel.Row; 14 import org.apache.poi.ss.usermodel.Sheet; 15 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 16 import org.junit.Test; 17  18 public class DealXlsx { 19      20     /** 21      * ①有一组xlsx文件，每个xlsx中有一个工作簿。 22      * ②另外有一个xlsx中的某一个标准列 23      * ③从②中将标准列插入到①中的每一个文件的工作薄中 24      * ④对比①中第46列的数据和插入的这一列，看看每一行出现在新插入这一列的哪一行 25      * @throws IOException 26      * @throws InvalidFormatException 27      */ 28     @Test 29     public void dealXlsxS() throws IOException, InvalidFormatException{ 30         insertOneLine(); 31         readAllFile(); 32     } 33      34     /** 35      * 将包含的点插入到每一个xlsx中 36      * @throws InvalidFormatException 37      * @throws IOException 38      */ 39     public void insertOneLine() throws InvalidFormatException, IOException{ 40          41         List
      
         mutationIds = getList(); 42         File file1 = new File("D:/基因数据测试"); 43         File [] files = file1.listFiles(); 44  45         for (int i = 0; i < files.length; i++) { 46             FileInputStream fileInputStream = new FileInputStream(files[i]); 47              48             XSSFWorkbook workbook1 = new XSSFWorkbook(fileInputStream); 49             Sheet sheet1 = workbook1.getSheetAt(0); 50              51             if(sheet1 != null){ 52                 Row row = null; 53                 int lastRowNum = sheet1.getLastRowNum(); 54                 for (int i1 = 0; i1 < 4241; i1++) { 55                     if(i1 > lastRowNum ){ 56                         row = sheet1.createRow(i1); 57                     }else{ 58                         row = sheet1.getRow(i1); 59                     } 60                     if(row != null){ 61                         Cell cell = row.createCell(64); 62                         cell.setCellValue(mutationIds.get(i1)); 63                     } 64                 } 65             } 66             System.out.println("插入第"+(i+1)+"个文件"); 67             FileOutputStream outPutStream = new FileOutputStream(files[i]); 68             workbook1.write(outPutStream); 69             outPutStream.close(); 70             workbook1.close(); 71         } 72     } 73      74      75     /** 76      * 执行对比操作 77      * @throws IOException 78      * @throws InvalidFormatException  79      */ 80     public void readAllFile() throws IOException, InvalidFormatException{ 81         File file = new File("D:/基因数据测试"); 82         File [] files = file.listFiles(); 83         //获取插入的标准列 84         List
       
          mutationIds = getList(); 85         for (File file2 : files) { 86             FileInputStream fileInputStream = new FileInputStream(file2); 87              88             XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream); 89             Sheet sheet = workbook.getSheetAt(0); 90             if(sheet != null){ 91                 Row row = null; 92                 //循环每一行 93                 int maxRowNum = just4MaxRowNum(sheet); 94                 for (int i = 0; i < maxRowNum; i++) { 95                     row = sheet.getRow(i); 96                     //如果是第一行 跳过 97                     if(row.getRowNum() == 0){ 98                         Cell cell = row.createCell(65); 99                         cell.setCellValue("目标行数");100                         continue;101                     }102                     //获取到46列的对比列 单元格中的数据103                     Cell cell = row.getCell(46);104                     String cellValue = getCellValue(cell);105                     //对数据进行截取106                     cellValue = cellValue.substring(cellValue.indexOf("=")+1, cellValue.indexOf(";"));107                     //将数据拆分或者放入数组中108                     String [] cVs = null;109                     if(cellValue.contains(",")){110                         cVs = cellValue.split(",");111                     }else{112                         cVs = new String[]{cellValue};113                     }114                     //对比List集合中的什么位置，返回位置，如果没有，返回-1115                     int thisNum = -1;116                     for (int i1 = 0; i1 < cVs.length; i1++) {117                         thisNum =  mutationIds.indexOf(cVs[i1]);118                         if(thisNum > -1){119                             break;120                         }121                     }122                     123                     //如果存在值，将位置值写入最后一列124                     if(thisNum > -1){125                         cell = row.createCell(65);126                         cell.setCellValue(String.valueOf(thisNum+1));127                     }128                     129                 }130                 131             }132             FileOutputStream outPutStream = new FileOutputStream(file2);133             workbook.write(outPutStream);134             outPutStream.close();135             workbook.close();136             137         }138     }139     140     /**141      * 获取当前单元格内容142      * @param cell143      * @return144      */145     public String getCellValue(Cell cell){146         String cellVaule = null;147         switch (cell.getCellType()) {148         case 0: cellVaule = String.valueOf(cell.getNumericCellValue());break;149         case 1: cellVaule = cell.getStringCellValue();break;150         case 2: cellVaule = cell.getStringCellValue();break;151         case 3: cellVaule = null;break;152         case 4: cellVaule = String.valueOf(cell.getBooleanCellValue());break;153         case 5: cellVaule = String.valueOf(cell.getErrorCellValue());break;154 155         default:cellVaule = null; break;156         }157         158         return cellVaule.trim();159     }160     161     162     /**163      * 获取到包含的点 的数据 用于插入每一个xlsx中164      * @return165      * @throws InvalidFormatException166      * @throws IOException167      */168     public List
        
          getList() throws InvalidFormatException, IOException{169         File file = new File("D:/基因数据2/时代基因175精简版探针20170629定稿.xlsx");170         XSSFWorkbook workbook = new XSSFWorkbook(file);171         List
         
            mutationIds = new ArrayList
          
           ();172         Sheet sheet = workbook.getSheet("包含的点");173         if(sheet != null){174             Row row = null;175             for (int i = 0; i < 4241; i++) {176                 row = sheet.getRow(i);177                 if(row != null){178                     mutationIds.add(getCellValue(row.getCell(4)));179                 }180             }181             182             System.out.println("包含的点总共有："+mutationIds.size());183         }184         185         return mutationIds;186     }187     188     189     /**190      * 获取最大行数  由于人为原因  xls中某个单元格中内容虽然已经删除 但是单元格的对象依旧创建，因此需要自己获取有效行数191      * @param sheet192      * @return193      */194     public int just4MaxRowNum(Sheet sheet){195         int maxRowNum = sheet.getLastRowNum();//获取最大行号  但不是有效行号196         197         for (int i = 5; i < maxRowNum; i++) {198             Row row = sheet.getRow(i);199             Cell cell = row.getCell(46);200             if(cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK){
    //判断cell单元格为null或者单元格类型为blank就表示此单元格没有数据  那这一行的上一行就是有效行数201                 maxRowNum = i-1;202                 break;203             }204         }    205         return maxRowNum;206     }207     208 }