Bug 66039 - Versions greater than 5.1 damages structured references while setCellFormula
Summary: Versions greater than 5.1 damages structured references while setCellFormula
Status: NEW
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
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2022-04-28 05:56 UTC by Axel Richter
Modified: 2022-04-29 13:07 UTC (History)
0 users



Attachments

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  
   sheet.createRow(0).createCell(0).setCellValue("Field1");
   sheet.getRow(0).createCell(1).setCellValue("Field2");
   sheet.createRow(1).createCell(0).setCellValue(123);
   sheet.getRow(1).createCell(1);
  
   //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);
   dataTable.setName(tableName);
   dataTable.setDisplayName(tableName);
   
   //set table column formula
   dataTable.getCTTable().getTableColumns().getTableColumnList().get(1).addNewCalculatedColumnFormula().setStringValue(
      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]]");
   
   workbook.write(fileout);
  }

 }
}

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