Apache POI shiftRows 损坏文件并删除内容 [英] Apache POI shiftRows corrupts file and deletes content

查看:40
本文介绍了Apache POI shiftRows 损坏文件并删除内容的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我想在表格中填充一个模板 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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆