Apache POI shiftRows 损坏文件并删除内容 [英] Apache POI shiftRows corrupts file and deletes content
问题描述
我想在表格中填充一个模板 excel 文件.我想插入行并填充它们.我使用 java Apache POI 库来访问 excel 文件.首先,我创建了一个新文件,并用 1..10 个数字填充了从第 1 行到第 10 行的 A 列并保存了文件.然后我读取文件并尝试使用 sheet.shiftRows()
方法插入一个空行.我尝试了下面的代码,但输出文件在打开(读取)时有问题,第 5、6、7 行是空的,并且没有发生移动.
I want to fill table a template excel file. I want to insert rows and fill them. I used java Apache POI library to access excel files. At first, I created a new file and filled column A from row 1 to 10 with 1..10 numbers and saved the file. Then I read the file and tried to insert a single empty row with a sheet.shiftRows()
method. I tried below code but output file has a problem in the opening (reading) and rows 5,6,7 are empty and move has not occurred.
InputStream inputStream = new FileInputStream("TestIn-1.xlsx");
Workbook workbookIn = new XSSFWorkbook(inputStream);
Sheet sheetIn = workbookIn.getSheet("Sheet1");
sheetIn.shiftRows(4,5,1);
OutputStream outputStream = new FileOutputStream("TestOut.xlsx");
workbookIn.write(outputStream);
outputStream.close();
推荐答案
您的 shiftRows
尝试在第 5 行(索引 4)和第 6 行(索引 5)之间向下移动一行.但是第 7、8、9 和 10 行呢?如果需要获得一个新的空行 5,您需要在第 5 行和最后一行之间移动一行.
Your shiftRows
tries shifting rows between row 5 (index 4) and row 6 (index 5) one row down. But what about row 7, 8, 9 and 10? You needs shifting rows between row 5 and last row one row down if the need is getting a new empty row 5.
使用 apache poi
版本 3.17
这很简单:
Using apache poi
version 3.17
this is as simple as:
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
,在 shiftRows
中使用XSSF
.在那里,移动后,单元格中的引用保持旧而不是调整到新行.例如参考 A5
, A6
, ... 在下移后保留而不是调整为 A6
, A7
, ...
But apache poi
versions greater than 3.17
, also 4.1.0
, have a bug in shiftRows
using XSSF
. There, after shifting, the references in the cells remain old instead being adjusted to the new rows. For example the references A5
, A6
, ... remain after shifting down instead of getting adjusted to 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);
}
}
}
这篇关于Apache POI shiftRows 损坏文件并删除内容的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!