java - 如何使用 shiftColumns 方法删除列

Question

我想使用 Apache POI 5.0.0 删除 Excel (XLSX) 中的一列。我想使用该shiftColumns方法，因此受影响的公式会自动调整。

void shiftColumns(int startColumn, int endColumn, int n)

https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Sheet.html

文档说，如果你想左移（覆盖左侧的列），你应该使用负数。

我在以下 Excel 文件上尝试了以下示例：

String path = "pathOfTheExcelFile";
File file = new File(path);
    
Workbook wb = null;

try (FileInputStream inputStream = new FileInputStream(file)) {

    wb = WorkbookFactory.create(inputStream); // read workbook

} catch (IOException e) {
    e.printStackTrace();
}
    
if(wb == null)
        return;
    
Sheet sheet = wb.getSheetAt(0); // read first sheet
    
// deleting / overriding 2nd column
sheet.shiftColumns(2, 5, -1); // shifting from 3rd to last column to the left
    
try (OutputStream fileOut = new FileOutputStream(path)) {
    wb.write(fileOut); // writing the result in the Excel file (ERROR)
}
catch (Exception e) {
    e.printStackTrace();
}
finally {
    try {
        if (wb != null)
            wb.close();
    }
    catch (IOException e) {
        e.printStackTrace();
    }
}

我要更改的 Excel 文件：

执行代码后，我在线收到此错误wb.write(fileOut);：

java.lang.IndexOutOfBoundsException
    at org.apache.xmlbeans.impl.store.Xobj.removeElement(Xobj.java:2099)
    at org.apache.xmlbeans.impl.store.Xobj.remove_element(Xobj.java:2130)
    at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTRowImpl.removeC(CTRowImpl.java:173)
    at org.apache.poi.xssf.usermodel.XSSFRow.fixupCTCells(XSSFRow.java:612)
    at org.apache.poi.xssf.usermodel.XSSFRow.onDocumentWrite(XSSFRow.java:582)
    at org.apache.poi.xssf.usermodel.XSSFSheet.write(XSSFSheet.java:3625)
    at org.apache.poi.xssf.usermodel.XSSFSheet.commit(XSSFSheet.java:3570)
    at org.apache.poi.ooxml.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:465)
    at org.apache.poi.ooxml.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:470)
    at org.apache.poi.ooxml.POIXMLDocument.write(POIXMLDocument.java:221)
    at test.App.main(App.java:38)

仅供参考，我使用 Java 11 和这些库：

图书馆

执行后，我的文件有 0KB。

我在将工作簿保存在 Excel 文件中时调试了 Apache POI 库。这就是问题开始的地方。也许它可以帮助你：

图1

图2

图3

标签： javaexcelapache-poi

Answer 1

是的，即使在最新版本的 Apache POI 5.1.0中， Sheet.shiftColumns中仍然存在问题。当shiftColumns负移时，不会正确移除单元格。这就是为什么在写作时出错的原因。

如果您明确删除要过度移动的列的单元格，那么错误就消失了。因此，在将第三列（索引 2）向左移动之前，我们需要从第二列（索引 1）中删除所有单元格。

但也有其他问题。即使是最后一个版本，在移动公式时也不会更新计算链。这是这个 Q/A 的问题：当单元格有公式时 shiftColumn 方法不起作用。

完整示例：

import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.model.CalculationChain;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import java.lang.reflect.Method;

public class ExcelShiftColums {

 private static void removeCalcChain(XSSFWorkbook workbook) throws Exception {
  CalculationChain calcchain = workbook.getCalculationChain();
  Method removeRelation = POIXMLDocumentPart.class.getDeclaredMethod("removeRelation", POIXMLDocumentPart.class); 
  removeRelation.setAccessible(true); 
  removeRelation.invoke(workbook, calcchain);
 }


 private static void removeColumn(Sheet sheet, int column) {
  for (Row row : sheet) {
   Cell cell = row.getCell(column);
   if (cell != null) {
    row.removeCell(cell);
   }
  }
 }
 
 private static int getLastFilledColumn(Sheet sheet) {
  int result = 0;
  for (Row row : sheet) {
   if (row.getLastCellNum() > result) result = row.getLastCellNum();
  }
  return result;     
 }

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

  String inFilePath = "./ExcelExampleIn.xlsx"; String outFilePath = "./ExcelExampleOut.xlsx";

  //String inFilePath = "./ExcelExampleIn.xls"; String outFilePath = "./ExcelExampleOut.xls";

  
  try (Workbook workbook = WorkbookFactory.create(new FileInputStream(inFilePath));
       FileOutputStream out = new FileOutputStream(outFilePath ) ) {

   Sheet sheet = workbook.getSheetAt(0);
   
   int lastFilledColumn = getLastFilledColumn(sheet);

   removeColumn(sheet, 1);
   sheet.shiftColumns(2, lastFilledColumn, -1);
   
   if (workbook instanceof XSSFWorkbook) removeCalcChain((XSSFWorkbook)workbook);
  
   workbook.write(out);
  }
 }
}