Apache POI shiftRows损坏文件并删除内容

Apache POI shiftRows损坏文件并删除内容

您

shiftRows

尝试将第5行（索引4）和第6行（索引5）之间的行向下移动一行。但是第7、8、9和10行呢？如果需要获取新的空行5，则需要在第5行和最后一行之间向下移动一行。

使用

apache poi

版本，

3.17

这很简单：

import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.usermodel.*;import java.io.FileInputStream;import java.io.FileOutputStream;class ExcelReadShiftRowsAndWrite { public static void main(String[] args) throws Exception {  //String fileIn= "TestIn.xls";  //String fileOut= "TestOut.xls";  String fileIn= "TestIn.xlsx";  String fileOut= "TestOut.xlsx";  try (Workbook workbook = WorkbookFactory.create(new FileInputStream(fileIn));       FileOutputStream out = new FileOutputStream(fileOut)) {   Sheet sheet = workbook.getSheet("Sheet1");   sheet.shiftRows(4, sheet.getLastRowNum(), 1); //shifts rows between row 5 (index 4) and last row one row down   workbook.write(out);  }  }}

但是

apachepoi

大于的版本在使用中

3.17

也

4.1.0

有错误。在那里，移位后，单元格中的引用保持旧状态，而不是被调整为新的行。例如，参考文献，…保持向下移位，而不是得到调整后，…

shiftRows``XSSF``A5``A6``A6``A7

因此，必须更正此错误：

import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.usermodel.*;import java.io.FileInputStream;import java.io.FileOutputStream;class ExcelReadShiftRowsAndWrite { public static void main(String[] args) throws Exception {  //String fileIn= "TestIn.xls";  //String fileOut= "TestOut.xls";  String fileIn= "TestIn.xlsx";  String fileOut= "TestOut.xlsx";  try (Workbook workbook = WorkbookFactory.create(new FileInputStream(fileIn));       FileOutputStream out = new FileOutputStream(fileOut)) {   Sheet sheet = workbook.getSheet("Sheet1");   sheet.shiftRows(4, sheet.getLastRowNum(), 1); //shifts rows between row 5 (index 4) and last row one row down   if (sheet instanceof XSSFSheet) {      XSSFSheet xSSFSheet = (XSSFSheet)sheet;    // correcting bug that shiftRows does not adjusting references of the cells    // if row 3 is shifted down, then reference in the cells remain r="A3", r="B3", ...    // they must be adjusted to the new row thoug: r="A4", r="B4", ...    // apache poi 3.17 has done this properly but had have other bugs in shiftRows.    for (int r = xSSFSheet.getFirstRowNum(); r < sheet.getLastRowNum() + 1; r++) {     XSSFRow row = xSSFSheet.getRow(r);      if (row != null) {      long rRef = row.getCTRow().getR();      for (Cell cell : row) {       String cRef = ((XSSFCell)cell).getCTCell().getR();       ((XSSFCell)cell).getCTCell().setR(cRef.replaceAll("[0-9]", "") + rRef);      }     }    }    // end correcting bug   }   workbook.write(out);  }  }}