This is an automated email from the ASF dual-hosted git repository.
MikeThomsen pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/nifi.git
The following commit(s) were added to refs/heads/main by this push:
new 269a0de57ba NIFI-16443 Added Ignore Cells Beyond Header property to
ExcelReader (#11783)
269a0de57ba is described below
commit 269a0de57ba23b0fd81780c1c561574143933780
Author: Vincenzo Lombardo <[email protected]>
AuthorDate: Thu Oct 15 20:30:45 2026 +0200
NIFI-16443 Added Ignore Cells Beyond Header property to ExcelReader (#11783)
* NIFI-16443 Added Ignore Cells Beyond Header property to ExcelReader
Co-Authored-By: Claude Opus 5.5 <[email protected]>
---
.../java/org/apache/nifi/excel/ExcelReader.java | 17 +++++-
.../excel/ExcelStartingRowSchemaInference.java | 68 +++++++++++++++++++---
.../additionalDetails.md | 5 ++
.../excel/TestExcelStartingRowSchemaInference.java | 46 ++++++++++++++-
4 files changed, 127 insertions(+), 9 deletions(-)
diff --git
a/nifi-extension-bundles/nifi-poi-bundle/nifi-poi-services/src/main/java/org/apache/nifi/excel/ExcelReader.java
b/nifi-extension-bundles/nifi-poi-bundle/nifi-poi-services/src/main/java/org/apache/nifi/excel/ExcelReader.java
index 81c2ae6342c..467e3ddaa77 100644
---
a/nifi-extension-bundles/nifi-poi-bundle/nifi-poi-services/src/main/java/org/apache/nifi/excel/ExcelReader.java
+++
b/nifi-extension-bundles/nifi-poi-bundle/nifi-poi-services/src/main/java/org/apache/nifi/excel/ExcelReader.java
@@ -108,6 +108,19 @@ public class ExcelReader extends SchemaRegistryService
implements RecordReaderFa
.addValidator(StandardValidators.NON_BLANK_VALIDATOR)
.build();
+ public static final PropertyDescriptor IGNORE_CELLS_BEYOND_HEADER = new
PropertyDescriptor
+ .Builder().name("Ignore Cells Beyond Header")
+ .description("""
+ Whether to ignore cells located beyond the last column of
the header row, instead of failing schema inference. \
+ Such cells are often blank but formatted. A warning is
logged when ignored cells contain a value. \
+ Only the rows evaluated by the '%s' property are checked.\
+ """.formatted(ROW_EVALUATION_STRATEGY.getName()))
+ .required(true)
+ .allowableValues("true", "false")
+ .defaultValue("false")
+ .dependsOn(SchemaAccessUtils.SCHEMA_ACCESS_STRATEGY,
ExcelStartingRowSchemaInference.USE_STARTING_ROW)
+ .build();
+
public static final PropertyDescriptor REQUIRED_SHEETS = new
PropertyDescriptor
.Builder().name("Required Sheets")
.description("Comma-separated list of Excel document sheet names
whose rows should be extracted from the excel document. If this property" +
@@ -165,6 +178,7 @@ public class ExcelReader extends SchemaRegistryService
implements RecordReaderFa
properties.add(PASSWORD);
properties.add(STARTING_ROW);
properties.add(ROW_EVALUATION_STRATEGY);
+ properties.add(IGNORE_CELLS_BEYOND_HEADER);
properties.add(REQUIRED_SHEETS);
properties.add(DateTimeUtils.DATE_FORMAT);
properties.add(DateTimeUtils.TIME_FORMAT);
@@ -181,8 +195,9 @@ public class ExcelReader extends SchemaRegistryService
implements RecordReaderFa
final int firstRow = context.getProperty(STARTING_ROW)
.evaluateAttributeExpressions()
.asInteger();
+ final boolean ignoreCellsBeyondHeader =
context.getProperty(IGNORE_CELLS_BEYOND_HEADER).asBoolean();
final SchemaInferenceEngine<Row> inference =
- new ExcelStartingRowSchemaInference(rowEvaluationStrategy,
firstRow, createTimeValueInference());
+ new ExcelStartingRowSchemaInference(rowEvaluationStrategy,
firstRow, createTimeValueInference(), ignoreCellsBeyondHeader, getLogger());
return createInferSchemaAccessStrategy(context, inference);
} else if
(SchemaInferenceUtil.INFER_SCHEMA.getValue().equals(allowableValue)) {
final SchemaInferenceEngine<Row> inference = new
ExcelSchemaInference(createTimeValueInference());
diff --git
a/nifi-extension-bundles/nifi-poi-bundle/nifi-poi-services/src/main/java/org/apache/nifi/excel/ExcelStartingRowSchemaInference.java
b/nifi-extension-bundles/nifi-poi-bundle/nifi-poi-services/src/main/java/org/apache/nifi/excel/ExcelStartingRowSchemaInference.java
index d951f81ef4e..2170696345e 100644
---
a/nifi-extension-bundles/nifi-poi-bundle/nifi-poi-services/src/main/java/org/apache/nifi/excel/ExcelStartingRowSchemaInference.java
+++
b/nifi-extension-bundles/nifi-poi-bundle/nifi-poi-services/src/main/java/org/apache/nifi/excel/ExcelStartingRowSchemaInference.java
@@ -17,6 +17,7 @@
package org.apache.nifi.excel;
import org.apache.nifi.components.AllowableValue;
+import org.apache.nifi.logging.ComponentLog;
import org.apache.nifi.schema.access.SchemaNotFoundException;
import org.apache.nifi.schema.inference.FieldTypeInference;
import org.apache.nifi.schema.inference.RecordSource;
@@ -26,8 +27,10 @@ import org.apache.nifi.serialization.SimpleRecordSchema;
import org.apache.nifi.serialization.record.RecordField;
import org.apache.nifi.serialization.record.RecordSchema;
import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.util.CellReference;
import java.io.IOException;
import java.util.ArrayList;
@@ -35,6 +38,7 @@ import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
+import java.util.OptionalInt;
import java.util.stream.Collectors;
import java.util.stream.IntStream;
@@ -50,17 +54,23 @@ public class ExcelStartingRowSchemaInference implements
SchemaInferenceEngine<Ro
private final int firstRow;
private final CellFieldTypeReader cellFieldTypeReader;
private final DataFormatter dataFormatter;
+ private final boolean ignoreCellsBeyondHeader;
+ private final ComponentLog logger;
- public ExcelStartingRowSchemaInference(RowEvaluationStrategy
rowEvaluationStrategy, int firstRow, TimeValueInference timeValueInference) {
+ public ExcelStartingRowSchemaInference(RowEvaluationStrategy
rowEvaluationStrategy, int firstRow, TimeValueInference timeValueInference,
+ boolean ignoreCellsBeyondHeader,
ComponentLog logger) {
this.rowEvaluationStrategy = rowEvaluationStrategy;
this.firstRow = firstRow;
this.cellFieldTypeReader = new
StandardCellFieldTypeReader(timeValueInference);
this.dataFormatter = new DataFormatter();
+ this.ignoreCellsBeyondHeader = ignoreCellsBeyondHeader;
+ this.logger = logger;
}
@Override
public RecordSchema inferSchema(RecordSource<Row> recordSource) throws
IOException {
final Map<String, FieldTypeInference> typeMap = new LinkedHashMap<>();
+ final Map<String, IgnoredCells> ignoredCellsBySheet = new
LinkedHashMap<>();
final int zeroBasedFirstRow = ExcelReader.getZeroBasedIndex(firstRow);
List<String> fieldNames = null;
int index = 0;
@@ -79,16 +89,22 @@ public class ExcelStartingRowSchemaInference implements
SchemaInferenceEngine<Ro
} else {
if (RowEvaluationStrategy.STANDARD == rowEvaluationStrategy) {
if (index <=
RowEvaluationStrategy.NUM_ROWS_TO_DETERMINE_TYPES) {
- inferSchema(row, fieldNames, typeMap);
+ inferSchema(row, fieldNames, typeMap,
ignoredCellsBySheet);
} else {
break;
}
} else {
- inferSchema(row, fieldNames, typeMap);
+ inferSchema(row, fieldNames, typeMap, ignoredCellsBySheet);
}
}
index++;
}
+
+ for (Map.Entry<String, IgnoredCells> entry :
ignoredCellsBySheet.entrySet()) {
+ final IgnoredCells ignoredCells = entry.getValue();
+ logger.warn("Ignored non-empty cells beyond the {} header columns
in {} row(s) of sheet [{}], first at row {} column {}",
+ fieldNames.size(), ignoredCells.rowCount(),
entry.getKey(), ignoredCells.firstRowNumber(), ignoredCells.firstColumnName());
+ }
return createSchema(typeMap);
}
@@ -131,15 +147,24 @@ public class ExcelStartingRowSchemaInference implements
SchemaInferenceEngine<Ro
return renamedDuplicateFieldNames;
}
- private void inferSchema(final Row row, final List<String> fieldNames,
final Map<String, FieldTypeInference> typeMap) throws IOException {
+ private void inferSchema(final Row row, final List<String> fieldNames,
final Map<String, FieldTypeInference> typeMap,
+ final Map<String, IgnoredCells>
ignoredCellsBySheet) throws IOException {
// NOTE: This allows rows to be blank when inferring the schema
if (ExcelUtils.hasCells(row)) {
+ final int cellCount;
if (row.getLastCellNum() > fieldNames.size()) {
- throw new IOException(new
SchemaNotFoundException(String.format("Row %s has %s cells, more than the
expected %s number of field names",
- row.getRowNum(), row.getLastCellNum(),
fieldNames.size())));
+ if (!ignoreCellsBeyondHeader) {
+ throw new IOException(new
SchemaNotFoundException(String.format("Row %s has %s cells, more than the
expected %s number of field names",
+ row.getRowNum(), row.getLastCellNum(),
fieldNames.size())));
+ }
+
+ cellCount = fieldNames.size();
+ trackIgnoredCells(row, fieldNames.size(), ignoredCellsBySheet);
+ } else {
+ cellCount = row.getLastCellNum();
}
- IntStream.range(0, row.getLastCellNum())
+ IntStream.range(0, cellCount)
.forEach(index -> {
final Cell cell = row.getCell(index);
final String fieldName = fieldNames.get(index);
@@ -148,6 +173,32 @@ public class ExcelStartingRowSchemaInference implements
SchemaInferenceEngine<Ro
}
}
+ private void trackIgnoredCells(final Row row, final int headerCellCount,
final Map<String, IgnoredCells> ignoredCellsBySheet) {
+ // NOTE: Cells without content, such as formatted blank cells, are
ignored silently
+ final OptionalInt firstColumnWithContent =
IntStream.range(headerCellCount, row.getLastCellNum())
+ .filter(index -> hasContent(row.getCell(index)))
+ .findFirst();
+
+ if (firstColumnWithContent.isPresent()) {
+ final IgnoredCells ignoredCells = new IgnoredCells(1,
row.getRowNum() + 1,
CellReference.convertNumToColString(firstColumnWithContent.getAsInt()));
+ ignoredCellsBySheet.merge(row.getSheet().getSheetName(),
ignoredCells,
+ (existing, added) -> new IgnoredCells(existing.rowCount()
+ 1, existing.firstRowNumber(), existing.firstColumnName()));
+ }
+ }
+
+ private static boolean hasContent(final Cell cell) {
+ if (cell == null) {
+ return false;
+ }
+
+ final CellType cellType = cell.getCellType() == CellType.FORMULA ?
cell.getCachedFormulaResultType() : cell.getCellType();
+ return switch (cellType) {
+ case STRING -> !cell.getStringCellValue().isBlank();
+ case BLANK, _NONE -> false;
+ default -> true;
+ };
+ }
+
private RecordSchema createSchema(final Map<String, FieldTypeInference>
inferences) throws IOException {
if (inferences.isEmpty()) {
throw new IOException(new SchemaNotFoundException("Failed to infer
schema from empty rows"));
@@ -158,4 +209,7 @@ public class ExcelStartingRowSchemaInference implements
SchemaInferenceEngine<Ro
.collect(Collectors.toList());
return new SimpleRecordSchema(recordFields);
}
+
+ private record IgnoredCells(int rowCount, int firstRowNumber, String
firstColumnName) {
+ }
}
diff --git
a/nifi-extension-bundles/nifi-poi-bundle/nifi-poi-services/src/main/resources/docs/org.apache.nifi.excel.ExcelReader/additionalDetails.md
b/nifi-extension-bundles/nifi-poi-bundle/nifi-poi-services/src/main/resources/docs/org.apache.nifi.excel.ExcelReader/additionalDetails.md
index c7c325046d7..eabe5056b2e 100644
---
a/nifi-extension-bundles/nifi-poi-bundle/nifi-poi-services/src/main/resources/docs/org.apache.nifi.excel.ExcelReader/additionalDetails.md
+++
b/nifi-extension-bundles/nifi-poi-bundle/nifi-poi-services/src/main/resources/docs/org.apache.nifi.excel.ExcelReader/additionalDetails.md
@@ -70,6 +70,11 @@ Schema" strategy, the Reader will determine the schema by
first parsing all data
fields that it has encountered and the type of each field. Once all data has
been parsed, a schema is formed that
encompasses all fields that have been encountered.
+When using the "Use Starting Row" strategy, a row having more cells than the
header row causes schema inference to
+fail. This often happens with spreadsheets edited by hand, where cells to the
right of the data are blank but formatted.
+Setting the "Ignore Cells Beyond Header" property to true ignores the cells
beyond the last header column instead. Blank
+cells are ignored silently, while a warning is logged for each sheet where the
ignored cells contain a value.
+
A common concern when inferring schemas is how to handle the condition of two
values that have different types. For
example, consider a FlowFile with the following two records:
diff --git
a/nifi-extension-bundles/nifi-poi-bundle/nifi-poi-services/src/test/java/org/apache/nifi/excel/TestExcelStartingRowSchemaInference.java
b/nifi-extension-bundles/nifi-poi-bundle/nifi-poi-services/src/test/java/org/apache/nifi/excel/TestExcelStartingRowSchemaInference.java
index 104a4171240..8d6bb8ac5a8 100644
---
a/nifi-extension-bundles/nifi-poi-bundle/nifi-poi-services/src/test/java/org/apache/nifi/excel/TestExcelStartingRowSchemaInference.java
+++
b/nifi-extension-bundles/nifi-poi-bundle/nifi-poi-services/src/test/java/org/apache/nifi/excel/TestExcelStartingRowSchemaInference.java
@@ -61,10 +61,16 @@ import static
org.junit.jupiter.api.Assertions.assertInstanceOf;
import static org.junit.jupiter.api.Assertions.assertNotNull;
import static org.junit.jupiter.api.Assertions.assertThrows;
import static org.junit.jupiter.api.Assertions.assertTrue;
+import static org.mockito.ArgumentMatchers.any;
+import static org.mockito.ArgumentMatchers.anyString;
+import static org.mockito.ArgumentMatchers.eq;
+import static org.mockito.Mockito.never;
+import static org.mockito.Mockito.verify;
@ExtendWith(MockitoExtension.class)
public class TestExcelStartingRowSchemaInference {
private static final TimeValueInference TIME_VALUE_INFERENCE = new
TimeValueInference("MM/dd/yyyy", "HH:mm:ss.SSS", "yyyy/MM/dd/ HH:mm");
+ private static final Object FORMATTED_BLANK = new Object();
@Mock
private ComponentLog logger;
@@ -133,6 +139,34 @@ public class TestExcelStartingRowSchemaInference {
}
}
+ @ParameterizedTest
+ @EnumSource(RowEvaluationStrategy.class)
+ void testIgnoreCellsBeyondHeaderWithoutContent(RowEvaluationStrategy
rowEvaluationStrategy) throws Exception {
+ final Object[][] singleSheet = {{"ID", "First", "Middle"}, {1,
"Manny", "M", FORMATTED_BLANK, " ", new Formula("\"\"")}, {2, "Moe", "M"}};
+ final ByteArrayOutputStream outputStream = createWorkbook(singleSheet);
+
+ try (final InputStream inputStream = new
ByteArrayInputStream(outputStream.toByteArray())) {
+ final InferSchemaAccessStrategy<?> inferSchemaAccessStrategy =
getInferSchemaAccessStrategy(rowEvaluationStrategy, true);
+ final RecordSchema schema =
inferSchemaAccessStrategy.getSchema(null, inputStream, null);
+ assertEquals(List.of("ID", "First", "Middle"),
schema.getFieldNames());
+ verify(logger, never()).warn(anyString(), any(Object[].class));
+ }
+ }
+
+ @ParameterizedTest
+ @EnumSource(RowEvaluationStrategy.class)
+ void testIgnoreCellsBeyondHeaderWithContent(RowEvaluationStrategy
rowEvaluationStrategy) throws Exception {
+ final Object[][] singleSheet = {{"ID", "First", "Middle"}, {1,
"Manny", "M"}, {2, "Moe", "M", FORMATTED_BLANK, "Extra"}, {3, "Jack", "J", 4}};
+ final ByteArrayOutputStream outputStream = createWorkbook(singleSheet);
+
+ try (final InputStream inputStream = new
ByteArrayInputStream(outputStream.toByteArray())) {
+ final InferSchemaAccessStrategy<?> inferSchemaAccessStrategy =
getInferSchemaAccessStrategy(rowEvaluationStrategy, true);
+ final RecordSchema schema =
inferSchemaAccessStrategy.getSchema(null, inputStream, null);
+ assertEquals(List.of("ID", "First", "Middle"),
schema.getFieldNames());
+ verify(logger).warn(anyString(), eq(3), eq(2), eq("Sheet 1"),
eq(3), eq("E"));
+ }
+ }
+
@Test
void testWhereTotalRowsLessThanConfiguredInferenceRows() throws Exception {
final Object[][] singleSheet = {{"ID", "First", "Middle"}, {1,
"Manny", "M"}, {2, "Moe", "M"}, {3, "Jack", "J"}};
@@ -315,9 +349,13 @@ public class TestExcelStartingRowSchemaInference {
}
private InferSchemaAccessStrategy<?>
getInferSchemaAccessStrategy(RowEvaluationStrategy rowEvaluationStrategy) {
+ return getInferSchemaAccessStrategy(rowEvaluationStrategy, false);
+ }
+
+ private InferSchemaAccessStrategy<?>
getInferSchemaAccessStrategy(RowEvaluationStrategy rowEvaluationStrategy,
boolean ignoreCellsBeyondHeader) {
return new InferSchemaAccessStrategy<>(
(variables, content) -> new ExcelRecordSource(content,
context, variables, logger),
- new ExcelStartingRowSchemaInference(rowEvaluationStrategy, 1,
TIME_VALUE_INFERENCE), logger);
+ new ExcelStartingRowSchemaInference(rowEvaluationStrategy, 1,
TIME_VALUE_INFERENCE, ignoreCellsBeyondHeader, logger), logger);
}
private static ByteArrayOutputStream createWorkbook(Object[][]...
sheetData) throws IOException {
@@ -346,15 +384,21 @@ public class TestExcelStartingRowSchemaInference {
cell.setCellValue(localDate);
cell.setCellStyle(dayMonthYearCellStyle);
}
+ case Formula formula ->
cell.setCellFormula(formula.expression());
+ case Object blank when blank == FORMATTED_BLANK ->
cell.setCellStyle(dayMonthYearCellStyle);
default -> throw new
IllegalStateException("Unexpected value: " + field);
}
}
}
sheetCount++;
}
+ creationHelper.createFormulaEvaluator().evaluateAll();
workbook.write(outputStream);
}
return outputStream;
}
+
+ private record Formula(String expression) {
+ }
}
