| Summary: | Versions greater than 5.1 damages structured references while setCellFormula | ||
|---|---|---|---|
| Product: | POI | Reporter: | Axel Richter <axel.richter.privat> |
| Component: | XSSF | Assignee: | POI Developers List <dev> |
| Status: | RESOLVED FIXED | ||
| Severity: | regression | ||
| Priority: | P2 | ||
| Version: | 5.2.0-FINAL | ||
| Target Milestone: | --- | ||
| Hardware: | PC | ||
| OS: | All | ||
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.
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. |
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.