You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2022/04/28 05:56:35 UTC
[Bug 66039] New: Versions greater than 5.1 damages structured references while setCellFormula
https://bz.apache.org/bugzilla/show_bug.cgi?id=66039
Bug ID: 66039
Summary: Versions greater than 5.1 damages structured
references while setCellFormula
Product: POI
Version: 5.2.0-FINAL
Hardware: PC
Status: NEW
Severity: normal
Priority: P2
Component: XSSF
Assignee: dev@poi.apache.org
Reporter: axel.richter.privat@web.de
Target Milestone: ---
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.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 66039] Versions greater than 5.1 damages structured references while setCellFormula
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=66039
--- Comment #3 from PJ Fanning <fa...@yahoo.com> ---
also added r1900383 - a javadoc change
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 66039] Versions greater than 5.1 damages structured references while setCellFormula
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=66039
--- Comment #2 from PJ Fanning <fa...@yahoo.com> ---
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.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 66039] Versions greater than 5.1 damages structured references while setCellFormula
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=66039
PJ Fanning <fa...@yahoo.com> changed:
What |Removed |Added
----------------------------------------------------------------------------
Resolution|--- |FIXED
Status|NEW |RESOLVED
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 66039] Versions greater than 5.1 damages structured references while setCellFormula
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=66039
--- Comment #1 from PJ Fanning <fa...@yahoo.com> ---
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.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 66039] Versions greater than 5.1 damages structured references while setCellFormula
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=66039
Dominik Stadler <do...@gmx.at> changed:
What |Removed |Added
----------------------------------------------------------------------------
Severity|normal |regression
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
[Bug 66039] Versions greater than 5.1 damages structured references while setCellFormula
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=66039
Dominik Stadler <do...@gmx.at> changed:
What |Removed |Added
----------------------------------------------------------------------------
OS| |All
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org