Bug 69147 - TEXT Function Regression
Summary: TEXT Function Regression
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: unspecified
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on: 67475
  Show dependency tree
Reported: 2024-06-21 19:34 UTC by aarbor989
Modified: 2024-06-22 16:30 UTC (History)
1 user (show)


Note You need to log in before you can comment on or make changes to this bug.
Description aarbor989 2024-06-21 19:34:48 UTC
Hello, I think https://bz.apache.org/bugzilla/show_bug.cgi?id=67475 has added a regression for the TEXT function.

In 5.2.4 and previous, TEXT("02/28/2024", "MMM") returns "Feb". However, in 5.2.5, TEXT("02/28/2024", "MMM") returns "02/28/2024".

Here is a reproducer

>  @Test
>  void testTextFunction() throws Exception {
>   try (HSSFWorkbook workbook = new HSSFWorkbook()) {
>      Row row = workbook.createSheet().createRow(0);
>      Cell formula = row.createCell(0);
>      formula.setCellFormula("TEXT(\"02/28/2022\", \"MMM\")");
>      FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
>      DataFormatter formatter = new DataFormatter(Locale.getDefault());
>      evaluator.clearAllCachedResultValues();
>      String result = formatter.formatCellValue(formula, evaluator);
>      assertEquals(result, "Feb");
>    }
>  }
Comment 1 PJ Fanning 2024-06-21 20:08:30 UTC
I added r1918499 which shows that if the cell has the date in number format, that the test seems to work. Dates are usually formatted as numbers in the underlying xlsx files - although this may have changed in more recent Excel releases.
I have observed that if the cell has the date in string format, then the result is as the OP described.
I'm not sure what changes should be made. The code in the POI function implementations is not complete and changes tend to break other use cases.
Microsoft do not fully document what the functions are supposed to do and many functions have many differnt types of possible inputs.
Comment 2 PJ Fanning 2024-06-21 20:29:21 UTC
I added r1918501 - may fix this issue - but I would not be confident that POI has good coverage for the TEXT function. As things stand, POI support for Excel functions is far from complete and bug ridden.

In most cases, you don't need to get POI to evaluate functions anyway. POI gives you access to the cached result that Excel saved for each cell. This is far safer than making POI evaluate the values.
Comment 3 aarbor989 2024-06-21 22:16:13 UTC
Agreed on using cached values when possible, however in the use case this was discovered in we are creating the formulas and evaluating them in memory so there's no cached value to depend on.

During testing I did also see that when a number / date value is passed in it does seem to work correctly

> Cell date = row.createCell(0);
> date.setCellValue(LocalDate.of(2022, 2, 28));
> Cell formula = row.createCell(1);
> formula.setCellFormula("TEXT(A1, \"MMM\")");

However, 5.2.4 handling is preferable IMO because as you mentioned it matches the behavior in Excel