如何使用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

文档中说，如果要向左移动（覆盖左侧的列），应该使用负数。

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();
    }
}

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)

PIC2

PIC3

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);
  }
 }
}