Bug 66039 - Versions greater than 5.1 damages structured references while setCellFormula
Summary: Versions greater than 5.1 damages structured references while setCellFormula
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 5.2.0-FINAL
Hardware: PC All
: P2 regression (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2022-04-28 05:56 UTC by Axel Richter
Modified: 2022-08-15 10:40 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
Description Axel Richter 2022-04-28 05:56:35 UTC
Complete Example:

import java.io.FileOutputStream;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;

class StructuredReferenceFormulaBug {

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

  try (XSSFWorkbook workbook = new XSSFWorkbook();
       FileOutputStream fileout = new FileOutputStream("./Excel.xlsx") ) {

   XSSFSheet sheet = workbook.createSheet();
   //set sheet content  
   //create the table
   String tableName = "Table1";
   CellReference topLeft = new CellReference(sheet.getRow(0).getCell(0));
   CellReference bottomRight = new CellReference(sheet.getRow(1).getCell(1));
   AreaReference tableArea = workbook.getCreationHelper().createAreaReference(topLeft, bottomRight);
   XSSFTable dataTable = sheet.createTable(tableArea);
   //set table column formula
      tableName + "[[#This Row],[Field1]]");

   //set the formula in sheet
   XSSFCell formulaCell = sheet.getRow(1).getCell(1);
   formulaCell.setCellFormula(tableName + "[[#This Row],[Field1]]"); // this gets Sheet0!A2:A2 in versions greater than 5.1
   //following is not necessary up to apache poi 5.1.0, but later versions of apache poi uses formula parser which damages structured table formulas
   //formulaCell.getCTCell().getF().setStringValue(tableName + "[[#This Row],[Field1]]");


As commented in the code, formulaCell.setCellFormula(tableName + "[[#This Row],[Field1]]") sets Sheet0!A2:A2 as formula instead of the structured reference using versions greater than 5.1. For Versions up to 5.1 it works.
Comment 1 PJ Fanning 2022-04-29 07:21:43 UTC
I haven't validated this but the XSSFWorkbook has this method:

     * Whether a call to {@link XSSFCell#setCellFormula(String)} will validate the formula or not.
     * @param value true if the application will validate the formula is correct
     * @since 3.17
    public void setCellFormulaValidation(final boolean value) {
        this.cellFormulaValidation = value;

So in theory, the formula parser change logic will not kick if `setCellFormulaValidation(false)` is used.
Comment 2 PJ Fanning 2022-04-29 10:56:29 UTC
I added r1900375 - a test that shows that setCellFormulaValidation=false can help stopping the attempted reformatting of the formula.

I think in a lot of cases, users don't want POI to reformat their formulas - but it will be hard to revert the changes because some users may rely on them.

We may be able to change the code that kicks in when setCellFormulaValidation=true  so that it correctly interprets this type of formula.
Comment 3 PJ Fanning 2022-04-29 13:07:37 UTC
also added r1900383 - a javadoc change