I would like to delete a column in Excel (XLSX) with the Apache POI 5.0.0. I want to use the shiftColumns method, so affected formulas get adjusted automatically.

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

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

The documentation says that, if you want to shift left (overriding columns on the left side), you should use a negative number.

I tried the following example on the following Excel file:

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 file I want to alter:

After executing the code, I get this error on line 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)

FYI, I use Java 11 and these libraries:

libraries

After execution, my file has 0KB.

I debugged into the Apache POI library while saving the workbook in the Excel file. This is where the problem starts. Maybe it helps you:

pic1

pic2

pic3

CodePudding user response：

Yes, here are still problems in Sheet.shiftColumns even in latest version Apache POI 5.1.0. The shiftColumns does not removing the cells properly when negative shifted. That's why the error while writing then.

If you explicitly remove the cells of the column you want over-shfting, then the error is gone. So we need to remove all cells from second column (index 1) before shifting third column (index 2) to left.

But there are additional problems too. Even the last version does not update the calculation chain while shifting formulas. This is the problem of this Q/A: shiftColumn method is not working when cell has formula.

Complete example:

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