无法删除 Excel 工作表的第一行 [英] Impossible to delete first row of Excel sheet

问题描述

我想完全删除 Excel 工作表的第一行.但是，每次我启动我的代码时，它都会完全擦除我的工作表，每一行都变成空白.

I want to delete completly the first row of my Excel sheet. However, everytime I launch my code, it erased totaly my sheet and every row becomes blank.

预先感谢您的帮助.

我知道这个问题已经被处理过很多次了，但是所提出的解决方案都没有适用于我的情况.肯定有问题.

I know this question has already been processed many times, but none of the solution proposed worked on my case. There is something wrong for sure.

String exportPath = "C:\\Users\\User\\Downloads\\export.xlsx";

FileInputStream inputStream = new FileInputStream(new File(exportPath));
Workbook export = WorkbookFactory.create(inputStream);
Sheet exportSheet = export.getSheetAt(0);

int lastNum = exportSheet.getLastRowNum();
exportSheet.removeRow(exportSheet.getRow(0));
exportSheet.shiftRows(1, lastNum, -1);

inputStream.close();
FileOutputStream outputStream = new FileOutputStream(exportPath);
export.write(outputStream);
export.close();
outputStream.close();

我需要删除工作表的第一行.

I need the first row of my sheet to be deleted.

推荐答案

在 apache poi 4.0.1 中，shiftRows 不调整单元格的引用.如果第 1 行向上移动，则单元格中的引用仍然是 r="A2", r="B2", ... 但它们必须调整到新行:r="A1", r="B1", ...

In apache poi 4.0.1, the shiftRows does not adjusting references of the cells. If row 1 is shifted up, then reference in the cells remain r="A2", r="B2", ... But they must be adjusted to the new row though: r="A1", r="B1", ...

此错误仅出现在 XSSF(Office Open XML，*.xlsx)中.二进制HSSF(BIFF，*.xls)没有这个问题.

This bug appears in XSSF (Office Open XML, *.xlsx) only. The binary HSSF (BIFF, *.xls) does not have this problem.

示例:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;

class ExcelDeleteShiftRows {

 public static void main(String[] args) throws Exception {

  String filePath = "SAMPLE.xlsx";

  FileInputStream inputStream = new FileInputStream(filePath);
  Workbook workbook = WorkbookFactory.create(inputStream);
  Sheet sheet = workbook.getSheetAt(0);

  int lastNum = sheet.getLastRowNum();
  Row row0 = sheet.getRow(0);
  if (row0 != null) sheet.removeRow(row0);

  sheet.shiftRows(1, lastNum, -1); 
  // After that the sheet is corrupted. The shiftRows does not adjusting references of the cells.
  // If row 1 is shifted up, then reference in the cells remain r="A2", r="B2", ...
  // But they must be adjusted to the new row though: r="A1", r="B1", ...

  // This corrects this. But of course it is unperformant.
  if (sheet instanceof XSSFSheet) {
   for (Row row : sheet) {
    long rRef = ((XSSFRow)row).getCTRow().getR();
    for (Cell cell : row) {
     String cRef = ((XSSFCell)cell).getCTCell().getR();
     ((XSSFCell)cell).getCTCell().setR(cRef.replaceAll("[0-9]", "") + rRef);
    }
   }
  }

  FileOutputStream outputStream = new FileOutputStream(filePath);
  workbook.write(outputStream);
  outputStream.close();
  workbook.close();

 }
}

请向 apache poi 提交错误，以便 apache poi 开发团队直接更正此问题.这个完整的例子加上一个简短的 SAMPLE.xlsx 足够短，可以作为一个例子来说明问题.

Please file a bug to apache poi to get this corrected directly by apache poi developer team. This complete example, together with a short SAMPLE.xlsx is short enough to be placed as an example to show the problem.

这篇关于无法删除 Excel 工作表的第一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！