[
https://issues.apache.org/jira/browse/NIFI-13922?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17891993#comment-17891993
]
Daniel Stieglitz edited comment on NIFI-13922 at 10/22/24 8:39 PM:
-------------------------------------------------------------------
It seems the issue is found in org.apache.poi.ss.util.CellUtil lines 105-120 as
we want the cachedFormulaResultType in order to obtain numeric cell value and
not the cell formula.
{code:java}
public static void copyCell(Cell srcCell, Cell destCell, CellCopyPolicy policy,
CellCopyContext context) {
if (policy.isCopyCellValue()) {
if (srcCell != null) {
CellType copyCellType = srcCell.getCellType();
if (copyCellType == CellType.FORMULA &&
!policy.isCopyCellFormula()) {
copyCellType = srcCell.getCachedFormulaResultType();
}
switch (copyCellType) {
case NUMERIC:
if (DateUtil.isCellDateFormatted(srcCell)) {
destCell.setCellValue(srcCell.getDateCellValue());
} else {
destCell.setCellValue(srcCell.getNumericCellValue());
}
break;
{code}
hence in SplitExcel on lines 113-114 instead of
{code:java}
private static final CellCopyPolicy CELL_COPY_POLICY = new
CellCopyPolicy.Builder()
.cellFormula(CellCopyPolicy.DEFAULT_COPY_CELL_FORMULA_POLICY)
{code}
we should configure the CellCopyPolicy.Builder with
{code:java}
private static final CellCopyPolicy CELL_COPY_POLICY = new
CellCopyPolicy.Builder()
.cellFormula(false)
{code}
was (Author: JIRAUSER294662):
It seems the issue is found in org.apache.poi.ss.util.CellUtil lines 105-120 as
we want the cachedFormulaResultType in order to obtain numeric cell value and
not the cell formula. Hence it seems
{code:java}
public static void copyCell(Cell srcCell, Cell destCell, CellCopyPolicy policy,
CellCopyContext context) {
if (policy.isCopyCellValue()) {
if (srcCell != null) {
CellType copyCellType = srcCell.getCellType();
if (copyCellType == CellType.FORMULA &&
!policy.isCopyCellFormula()) {
copyCellType = srcCell.getCachedFormulaResultType();
}
switch (copyCellType) {
case NUMERIC:
if (DateUtil.isCellDateFormatted(srcCell)) {
destCell.setCellValue(srcCell.getDateCellValue());
} else {
destCell.setCellValue(srcCell.getNumericCellValue());
}
break;
{code}
hence in SplitExcel on lines 113-114 instead of
{code:java}
private static final CellCopyPolicy CELL_COPY_POLICY = new
CellCopyPolicy.Builder()
.cellFormula(CellCopyPolicy.DEFAULT_COPY_CELL_FORMULA_POLICY)
{code}
we should configure the CellCopyPolicy.Builder with
{code:java}
private static final CellCopyPolicy CELL_COPY_POLICY = new
CellCopyPolicy.Builder()
.cellFormula(false)
{code}
> SplitExcel appears to drop column which has values from a calulated formula
> ----------------------------------------------------------------------------
>
> Key: NIFI-13922
> URL: https://issues.apache.org/jira/browse/NIFI-13922
> Project: Apache NiFi
> Issue Type: Bug
> Reporter: Daniel Stieglitz
> Assignee: Daniel Stieglitz
> Priority: Major
>
> When reading the SalesData.xlsx referred to in NIFI-13418 with a flow which
> consists of
> GetFile -> SplitExcel -> ConvertRecord where the ConvertRecord is configured
> with an ExcelReader and a CsvrecordSetWriter it appears the values for column
> 'Sale_amt' which is a formula (column F * column G) is completely dropped as
> seen in the sample below with column headers and the first row
> {code:java}
> OrderDate,Region,Manager,SalesMan,Item,Units,Unit_price,Sale_amt
> 43106,East,Martha,Alexander,Television,95,1198,
> {code}
> Although when reading one of the tabs with a flow which consists of GetFile
> -> ConvertRecord the column is not dropped e.g. of the column names and the
> first row
> {code:java}
> OrderDate,Region,Manager,SalesMan,Item,Units,Unit_price,Sale_amt
> 1515196800000,East,Martha,Alexander,Television,95,1198,113810.0
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
