java中poi的shiftRows_java - 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);

}

}

}

但是， apache poi版本大于3.17 ，还4.1.0 ，有一个bug 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);

}

}

}