【POI】修改已存在的xls，新添一列后，再保存本文件

使用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<String>  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<String>  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集合中的什么位置，返回位置，如果没有，返回-1
115                     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 cell
143      * @return
144      */
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      * @return
165      * @throws InvalidFormatException
166      * @throws IOException
167      */
168     public List<String> getList() throws InvalidFormatException, IOException{
169         File file = new File("D:/基因数据2/时代基因175精简版探针20170629定稿.xlsx");
170         XSSFWorkbook workbook = new XSSFWorkbook(file);
171         List<String>  mutationIds = new ArrayList<String>();
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 sheet
192      * @return
193      */
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 }

View Code