shiftColumn method is not working when cell has formula [closed]

sheet.shiftColumns(int start, int end, int shift) is not working when the Excel sheet has formula. After this operation Excel is getting corrupted. It’s working perfectly fine when the cell has no formula in it.

Any idea on this?

Code is something like this :

Sheet sheet = workbook.getSheet(“sheetName”); sheet.shiftColumn(3,5,1); // this call corrupts the Excel sheet

The excel sheet im using to create the workbook has 5 columns in it and some cells have got formulae.

Answer

This issue only occurs using XSSF. If you open the *.xlsx after shifting columns and choose yes, so Excel tries repairing, you get told that formula records got removed from /xl/calcChain.xml-Part. So calcChain.xml was not updated after shifting.

What is a CalculationChain? See https://docs.microsoft.com/en-us/office/open-xml/working-with-the-calculation-chain:

The Calculation Chain part specifies the order in which cells in the workbook were last calculated. It only records information about cells containing formulas.

But after shifting cells having formulas, the calcChain.xml now contains cells not having formulas, explicitly the ones which were shifted to another place. So calcChain.xml needs to be updated. At least the cells which now not longer contain formulas needs to be removed.

Simplest workaround would be simply removing the whole calcChain.xml. This is possible since Excel uses calculation chain only for optimizing recalculation. It does not necessarily need the calculation chain but of course it fails the optimization process if there are wrong entries in calculation chain.

A method to remove all entries from calcChain.xml could look like this:

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

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

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

   sheet.shiftColumns(3, 4, 1);
  
   if (workbook instanceof XSSFWorkbook) removeCalcChain((XSSFWorkbook)workbook);

   workbook.write(out);
  }
 }
}

Why is this not an issue using shiftRows?

While new creating rows using XSSFSheet.createRow all the former cells in that row get removed using XSSFRow.removeCell. Using this XSSFWorkbook.onDeleteFormula gets called when former cell contains a formula. And this updates calculation chain too.

But XSSFRow.createCell does not removing the former cell but simply creates a new one in the place of the former one. So XSSFWorkbook.onDeleteFormula not gets called and calculation chain remains wrong.

That means one can simply make a corrupt *.xlsx having wrong calculation chain by doing:

...
XSSFRow row = sheet.getRow(2);
row.createCell(2);
...

when C3 was a cell containing a formula.

@Apache POI: Please patch XSSFRow.createCell so this also removes the former cells if there are such.

Leave a Reply

Your email address will not be published. Required fields are marked *