This is an automated email from the ASF dual-hosted git repository.
bamaer pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/hop.git
The following commit(s) were added to refs/heads/main by this push:
new 41d8bc014a Excel input repeats per-cell work that only depends on the
cell style or the field, fixes #9468 (#9470)
41d8bc014a is described below
commit 41d8bc014a4a1ad664025a6ffdd95104da3d6526
Author: Hans Van Akelyen <[email protected]>
AuthorDate: Sat Nov 14 13:27:23 2026 +0100
Excel input repeats per-cell work that only depends on the cell style or
the field, fixes #9468 (#9470)
---
.../transforms/0053-excel-input-xls-types.hpl | 337 +++++++++++++++++++++
.../0053-excel-input-xlsx-streaming-basic.hpl | 181 +++++++++++
.../0053-excel-input-xlsx-streaming-types.hpl | 337 +++++++++++++++++++++
.../transforms/0053-excel-input-xlsx-types.hpl | 337 +++++++++++++++++++++
.../datasets/golden-excel-input-types.csv | 5 +
integration-tests/transforms/files/excel/types.xls | Bin 0 -> 5632 bytes
.../transforms/files/excel/types.xlsx | Bin 0 -> 4006 bytes
.../transforms/main-0053-excel-input.hwf | 12 +
.../metadata/dataset/golden-excel-input-types.json | 144 +++++++++
.../unit-test/0053-excel-input-xls-types UNIT.json | 108 +++++++
...0053-excel-input-xlsx-streaming-basic UNIT.json | 43 +++
...0053-excel-input-xlsx-streaming-types UNIT.json | 108 +++++++
.../0053-excel-input-xlsx-types UNIT.json | 108 +++++++
.../pipeline/transforms/excelinput/ExcelInput.java | 43 ++-
.../transforms/excelinput/ExcelInputData.java | 6 +
.../transforms/excelinput/poi/PoiCell.java | 51 +++-
.../transforms/excelinput/poi/PoiSheet.java | 9 +-
.../excelinput/staxpoi/StaxPoiSheet.java | 25 +-
.../excelinput/ExcelInputCellTypesTest.java | 311 +++++++++++++++++++
.../transforms/excelinput/poi/PoiCellTest.java | 175 +++++++++++
20 files changed, 2328 insertions(+), 12 deletions(-)
diff --git a/integration-tests/transforms/0053-excel-input-xls-types.hpl
b/integration-tests/transforms/0053-excel-input-xls-types.hpl
new file mode 100644
index 0000000000..d3aa57642e
--- /dev/null
+++ b/integration-tests/transforms/0053-excel-input-xls-types.hpl
@@ -0,0 +1,337 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+-->
+<pipeline>
+ <info>
+ <name>0053-excel-input-xls-types</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description/>
+ <extended_description/>
+ <pipeline_version/>
+ <pipeline_type>Normal</pipeline_type>
+ <parameters>
+ </parameters>
+ <capture_transform_performance>N</capture_transform_performance>
+
<transform_performance_capturing_delay>1000</transform_performance_capturing_delay>
+
<transform_performance_capturing_size_limit>100</transform_performance_capturing_size_limit>
+ <created_user>-</created_user>
+ <created_date>2022/11/25 14:27:53.495</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2022/11/25 14:27:53.495</modified_date>
+ </info>
+ <notepads>
+ </notepads>
+ <order>
+ <hop>
+ <from>types.xls</from>
+ <to>Verify</to>
+ <enabled>Y</enabled>
+ </hop>
+ </order>
+ <transform>
+ <name>Verify</name>
+ <type>Dummy</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ <GUI>
+ <xloc>304</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>types.xls</name>
+ <type>ExcelInput</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <header>Y</header>
+ <noempty>Y</noempty>
+ <stoponempty>N</stoponempty>
+ <filefield/>
+ <sheetfield/>
+ <sheetrownumfield/>
+ <rownumfield/>
+ <sheetfield/>
+ <filefield/>
+ <limit>0</limit>
+ <encoding/>
+ <add_to_result_filenames>Y</add_to_result_filenames>
+ <accept_filenames>N</accept_filenames>
+ <accept_field/>
+ <accept_transform_name/>
+ <file>
+ <name>${PROJECT_HOME}/files/excel/types.xls</name>
+ <filemask/>
+ <exclude_filemask/>
+ <file_required>N</file_required>
+ <include_subfolders>N</include_subfolders>
+ </file>
+ <fields>
+ <field>
+ <name>int</name>
+ <type>Integer</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>num</name>
+ <type>Number</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>numText</name>
+ <type>String</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format>#,##0.00</format>
+ <currency/>
+ <decimal>.</decimal>
+ <group>,</group>
+ </field>
+ <field>
+ <name>date</name>
+ <type>Date</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>date2</name>
+ <type>Date</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>dateTime</name>
+ <type>Date</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>dateText</name>
+ <type>String</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format>yyyy/MM/dd</format>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>numDate</name>
+ <type>Number</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>text</name>
+ <type>String</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>both</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>textInt</name>
+ <type>Integer</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format>#</format>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>bool</name>
+ <type>Boolean</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>boolText</name>
+ <type>String</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>fNum</name>
+ <type>Number</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>fText</name>
+ <type>String</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>fDate</name>
+ <type>Date</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>fBool</name>
+ <type>Boolean</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>mixed</name>
+ <type>String</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal>.</decimal>
+ <group>,</group>
+ </field>
+ </fields>
+ <sheets>
+ <sheet>
+ <name>data</name>
+ <startrow>0</startrow>
+ <startcol>0</startcol>
+ </sheet>
+ </sheets>
+ <strict_types>N</strict_types>
+ <error_ignored>N</error_ignored>
+ <error_line_skipped>N</error_line_skipped>
+ <bad_line_files_destination_directory/>
+ <bad_line_files_extension>warning</bad_line_files_extension>
+ <error_line_files_destination_directory/>
+ <error_line_files_extension>error</error_line_files_extension>
+ <line_number_files_destination_directory/>
+ <line_number_files_extension>line</line_number_files_extension>
+ <shortFileFieldName/>
+ <pathFieldName/>
+ <hiddenFieldName/>
+ <lastModificationTimeFieldName/>
+ <uriNameFieldName/>
+ <rootUriNameFieldName/>
+ <extensionFieldName/>
+ <sizeFieldName/>
+ <spreadsheet_type>POI</spreadsheet_type>
+ <attributes/>
+ <GUI>
+ <xloc>112</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform_error_handling>
+ </transform_error_handling>
+ <attributes/>
+</pipeline>
diff --git
a/integration-tests/transforms/0053-excel-input-xlsx-streaming-basic.hpl
b/integration-tests/transforms/0053-excel-input-xlsx-streaming-basic.hpl
new file mode 100644
index 0000000000..96751b901a
--- /dev/null
+++ b/integration-tests/transforms/0053-excel-input-xlsx-streaming-basic.hpl
@@ -0,0 +1,181 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+-->
+<pipeline>
+ <info>
+ <name>0053-excel-input-xlsx-streaming-basic</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description/>
+ <extended_description/>
+ <pipeline_version/>
+ <pipeline_type>Normal</pipeline_type>
+ <parameters>
+ </parameters>
+ <capture_transform_performance>N</capture_transform_performance>
+
<transform_performance_capturing_delay>1000</transform_performance_capturing_delay>
+
<transform_performance_capturing_size_limit>100</transform_performance_capturing_size_limit>
+ <created_user>-</created_user>
+ <created_date>2022/11/25 14:27:53.495</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2022/11/25 14:27:53.495</modified_date>
+ </info>
+ <notepads>
+ </notepads>
+ <order>
+ <hop>
+ <from>basic.xlsx</from>
+ <to>Verify</to>
+ <enabled>Y</enabled>
+ </hop>
+ </order>
+ <transform>
+ <name>Verify</name>
+ <type>Dummy</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ <GUI>
+ <xloc>304</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>basic.xlsx</name>
+ <type>ExcelInput</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <header>Y</header>
+ <noempty>Y</noempty>
+ <stoponempty>N</stoponempty>
+ <filefield/>
+ <sheetfield/>
+ <sheetrownumfield/>
+ <rownumfield/>
+ <sheetfield/>
+ <filefield/>
+ <limit>0</limit>
+ <encoding/>
+ <add_to_result_filenames>Y</add_to_result_filenames>
+ <accept_filenames>N</accept_filenames>
+ <accept_field/>
+ <accept_transform_name/>
+ <file>
+ <name>${PROJECT_HOME}/files/excel/basic.xlsx</name>
+ <filemask/>
+ <exclude_filemask/>
+ <file_required>N</file_required>
+ <include_subfolders>N</include_subfolders>
+ </file>
+ <fields>
+ <field>
+ <name>col1</name>
+ <type>Integer</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>col2</name>
+ <type>Date</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format>yyy/MM/dd</format>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>col3</name>
+ <type>String</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>col4</name>
+ <type>Number</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format>0.00</format>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ </fields>
+ <sheets>
+ <sheet>
+ <name>Sheet1</name>
+ <startrow>0</startrow>
+ <startcol>0</startcol>
+ </sheet>
+ </sheets>
+ <strict_types>N</strict_types>
+ <error_ignored>N</error_ignored>
+ <error_line_skipped>N</error_line_skipped>
+ <bad_line_files_destination_directory/>
+ <bad_line_files_extension>warning</bad_line_files_extension>
+ <error_line_files_destination_directory/>
+ <error_line_files_extension>error</error_line_files_extension>
+ <line_number_files_destination_directory/>
+ <line_number_files_extension>line</line_number_files_extension>
+ <shortFileFieldName/>
+ <pathFieldName/>
+ <hiddenFieldName/>
+ <lastModificationTimeFieldName/>
+ <uriNameFieldName/>
+ <rootUriNameFieldName/>
+ <extensionFieldName/>
+ <sizeFieldName/>
+ <spreadsheet_type>SAX_POI</spreadsheet_type>
+ <attributes/>
+ <GUI>
+ <xloc>112</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform_error_handling>
+ </transform_error_handling>
+ <attributes/>
+</pipeline>
diff --git
a/integration-tests/transforms/0053-excel-input-xlsx-streaming-types.hpl
b/integration-tests/transforms/0053-excel-input-xlsx-streaming-types.hpl
new file mode 100644
index 0000000000..7b56f8c4fe
--- /dev/null
+++ b/integration-tests/transforms/0053-excel-input-xlsx-streaming-types.hpl
@@ -0,0 +1,337 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+-->
+<pipeline>
+ <info>
+ <name>0053-excel-input-xlsx-streaming-types</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description/>
+ <extended_description/>
+ <pipeline_version/>
+ <pipeline_type>Normal</pipeline_type>
+ <parameters>
+ </parameters>
+ <capture_transform_performance>N</capture_transform_performance>
+
<transform_performance_capturing_delay>1000</transform_performance_capturing_delay>
+
<transform_performance_capturing_size_limit>100</transform_performance_capturing_size_limit>
+ <created_user>-</created_user>
+ <created_date>2022/11/25 14:27:53.495</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2022/11/25 14:27:53.495</modified_date>
+ </info>
+ <notepads>
+ </notepads>
+ <order>
+ <hop>
+ <from>types.xlsx</from>
+ <to>Verify</to>
+ <enabled>Y</enabled>
+ </hop>
+ </order>
+ <transform>
+ <name>Verify</name>
+ <type>Dummy</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ <GUI>
+ <xloc>304</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>types.xlsx</name>
+ <type>ExcelInput</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <header>Y</header>
+ <noempty>Y</noempty>
+ <stoponempty>N</stoponempty>
+ <filefield/>
+ <sheetfield/>
+ <sheetrownumfield/>
+ <rownumfield/>
+ <sheetfield/>
+ <filefield/>
+ <limit>0</limit>
+ <encoding/>
+ <add_to_result_filenames>Y</add_to_result_filenames>
+ <accept_filenames>N</accept_filenames>
+ <accept_field/>
+ <accept_transform_name/>
+ <file>
+ <name>${PROJECT_HOME}/files/excel/types.xlsx</name>
+ <filemask/>
+ <exclude_filemask/>
+ <file_required>N</file_required>
+ <include_subfolders>N</include_subfolders>
+ </file>
+ <fields>
+ <field>
+ <name>int</name>
+ <type>Integer</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>num</name>
+ <type>Number</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>numText</name>
+ <type>String</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format>#,##0.00</format>
+ <currency/>
+ <decimal>.</decimal>
+ <group>,</group>
+ </field>
+ <field>
+ <name>date</name>
+ <type>Date</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>date2</name>
+ <type>Date</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>dateTime</name>
+ <type>Date</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>dateText</name>
+ <type>String</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format>yyyy/MM/dd</format>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>numDate</name>
+ <type>Number</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>text</name>
+ <type>String</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>both</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>textInt</name>
+ <type>Integer</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format>#</format>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>bool</name>
+ <type>Boolean</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>boolText</name>
+ <type>String</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>fNum</name>
+ <type>Number</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>fText</name>
+ <type>String</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>fDate</name>
+ <type>Date</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>fBool</name>
+ <type>Boolean</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>mixed</name>
+ <type>String</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal>.</decimal>
+ <group>,</group>
+ </field>
+ </fields>
+ <sheets>
+ <sheet>
+ <name>data</name>
+ <startrow>0</startrow>
+ <startcol>0</startcol>
+ </sheet>
+ </sheets>
+ <strict_types>N</strict_types>
+ <error_ignored>N</error_ignored>
+ <error_line_skipped>N</error_line_skipped>
+ <bad_line_files_destination_directory/>
+ <bad_line_files_extension>warning</bad_line_files_extension>
+ <error_line_files_destination_directory/>
+ <error_line_files_extension>error</error_line_files_extension>
+ <line_number_files_destination_directory/>
+ <line_number_files_extension>line</line_number_files_extension>
+ <shortFileFieldName/>
+ <pathFieldName/>
+ <hiddenFieldName/>
+ <lastModificationTimeFieldName/>
+ <uriNameFieldName/>
+ <rootUriNameFieldName/>
+ <extensionFieldName/>
+ <sizeFieldName/>
+ <spreadsheet_type>SAX_POI</spreadsheet_type>
+ <attributes/>
+ <GUI>
+ <xloc>112</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform_error_handling>
+ </transform_error_handling>
+ <attributes/>
+</pipeline>
diff --git a/integration-tests/transforms/0053-excel-input-xlsx-types.hpl
b/integration-tests/transforms/0053-excel-input-xlsx-types.hpl
new file mode 100644
index 0000000000..a0d2683fe9
--- /dev/null
+++ b/integration-tests/transforms/0053-excel-input-xlsx-types.hpl
@@ -0,0 +1,337 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+
+-->
+<pipeline>
+ <info>
+ <name>0053-excel-input-xlsx-types</name>
+ <name_sync_with_filename>Y</name_sync_with_filename>
+ <description/>
+ <extended_description/>
+ <pipeline_version/>
+ <pipeline_type>Normal</pipeline_type>
+ <parameters>
+ </parameters>
+ <capture_transform_performance>N</capture_transform_performance>
+
<transform_performance_capturing_delay>1000</transform_performance_capturing_delay>
+
<transform_performance_capturing_size_limit>100</transform_performance_capturing_size_limit>
+ <created_user>-</created_user>
+ <created_date>2022/11/25 14:27:53.495</created_date>
+ <modified_user>-</modified_user>
+ <modified_date>2022/11/25 14:27:53.495</modified_date>
+ </info>
+ <notepads>
+ </notepads>
+ <order>
+ <hop>
+ <from>types.xlsx</from>
+ <to>Verify</to>
+ <enabled>Y</enabled>
+ </hop>
+ </order>
+ <transform>
+ <name>Verify</name>
+ <type>Dummy</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <attributes/>
+ <GUI>
+ <xloc>304</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform>
+ <name>types.xlsx</name>
+ <type>ExcelInput</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <header>Y</header>
+ <noempty>Y</noempty>
+ <stoponempty>N</stoponempty>
+ <filefield/>
+ <sheetfield/>
+ <sheetrownumfield/>
+ <rownumfield/>
+ <sheetfield/>
+ <filefield/>
+ <limit>0</limit>
+ <encoding/>
+ <add_to_result_filenames>Y</add_to_result_filenames>
+ <accept_filenames>N</accept_filenames>
+ <accept_field/>
+ <accept_transform_name/>
+ <file>
+ <name>${PROJECT_HOME}/files/excel/types.xlsx</name>
+ <filemask/>
+ <exclude_filemask/>
+ <file_required>N</file_required>
+ <include_subfolders>N</include_subfolders>
+ </file>
+ <fields>
+ <field>
+ <name>int</name>
+ <type>Integer</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>num</name>
+ <type>Number</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>numText</name>
+ <type>String</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format>#,##0.00</format>
+ <currency/>
+ <decimal>.</decimal>
+ <group>,</group>
+ </field>
+ <field>
+ <name>date</name>
+ <type>Date</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>date2</name>
+ <type>Date</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>dateTime</name>
+ <type>Date</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>dateText</name>
+ <type>String</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format>yyyy/MM/dd</format>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>numDate</name>
+ <type>Number</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>text</name>
+ <type>String</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>both</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>textInt</name>
+ <type>Integer</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format>#</format>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>bool</name>
+ <type>Boolean</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>boolText</name>
+ <type>String</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>fNum</name>
+ <type>Number</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>fText</name>
+ <type>String</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>fDate</name>
+ <type>Date</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>fBool</name>
+ <type>Boolean</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal/>
+ <group/>
+ </field>
+ <field>
+ <name>mixed</name>
+ <type>String</type>
+ <length>-1</length>
+ <precision>-1</precision>
+ <trim_type>none</trim_type>
+ <repeat>N</repeat>
+ <format/>
+ <currency/>
+ <decimal>.</decimal>
+ <group>,</group>
+ </field>
+ </fields>
+ <sheets>
+ <sheet>
+ <name>data</name>
+ <startrow>0</startrow>
+ <startcol>0</startcol>
+ </sheet>
+ </sheets>
+ <strict_types>N</strict_types>
+ <error_ignored>N</error_ignored>
+ <error_line_skipped>N</error_line_skipped>
+ <bad_line_files_destination_directory/>
+ <bad_line_files_extension>warning</bad_line_files_extension>
+ <error_line_files_destination_directory/>
+ <error_line_files_extension>error</error_line_files_extension>
+ <line_number_files_destination_directory/>
+ <line_number_files_extension>line</line_number_files_extension>
+ <shortFileFieldName/>
+ <pathFieldName/>
+ <hiddenFieldName/>
+ <lastModificationTimeFieldName/>
+ <uriNameFieldName/>
+ <rootUriNameFieldName/>
+ <extensionFieldName/>
+ <sizeFieldName/>
+ <spreadsheet_type>POI</spreadsheet_type>
+ <attributes/>
+ <GUI>
+ <xloc>112</xloc>
+ <yloc>80</yloc>
+ </GUI>
+ </transform>
+ <transform_error_handling>
+ </transform_error_handling>
+ <attributes/>
+</pipeline>
diff --git a/integration-tests/transforms/datasets/golden-excel-input-types.csv
b/integration-tests/transforms/datasets/golden-excel-input-types.csv
new file mode 100644
index 0000000000..5ca378cf44
--- /dev/null
+++ b/integration-tests/transforms/datasets/golden-excel-input-types.csv
@@ -0,0 +1,5 @@
+int,num,numText,date,date2,dateTime,dateText,numDate,text,textInt,bool,boolText,fNum,fText,fDate,fBool,mixed
+1,1.50,"1,000.50",2024/03/11,2023/11/01,2024/07/01
13:45,2022/01/21,86400000,text 1,41,N,Y,2.00,f1,2024/03/12,N,7.25
+2,2.50,"2,000.50",2024/03/12,2023/11/02,2024/07/02
13:45,2022/01/22,172800000,text 2,42,Y,N,4.00,f2,2024/03/13,Y,2021/05/06
00:00:00.000
+3,,,,,,,,,,,,,,,,only
+4,4.50,"4,000.50",2024/03/14,2023/11/04,2024/07/04
13:45,2022/01/24,345600000,text 4,44,Y,N,8.00,f4,2024/03/15,Y,Y
diff --git a/integration-tests/transforms/files/excel/types.xls
b/integration-tests/transforms/files/excel/types.xls
new file mode 100644
index 0000000000..73ae037429
Binary files /dev/null and b/integration-tests/transforms/files/excel/types.xls
differ
diff --git a/integration-tests/transforms/files/excel/types.xlsx
b/integration-tests/transforms/files/excel/types.xlsx
new file mode 100644
index 0000000000..8d82862e3e
Binary files /dev/null and
b/integration-tests/transforms/files/excel/types.xlsx differ
diff --git a/integration-tests/transforms/main-0053-excel-input.hwf
b/integration-tests/transforms/main-0053-excel-input.hwf
index dec993cf95..9eb491daeb 100644
--- a/integration-tests/transforms/main-0053-excel-input.hwf
+++ b/integration-tests/transforms/main-0053-excel-input.hwf
@@ -63,6 +63,18 @@ limitations under the License.
<test_name>
<name>0053-excel-input-ods-basic UNIT</name>
</test_name>
+ <test_name>
+ <name>0053-excel-input-xlsx-streaming-basic UNIT</name>
+ </test_name>
+ <test_name>
+ <name>0053-excel-input-xls-types UNIT</name>
+ </test_name>
+ <test_name>
+ <name>0053-excel-input-xlsx-types UNIT</name>
+ </test_name>
+ <test_name>
+ <name>0053-excel-input-xlsx-streaming-types UNIT</name>
+ </test_name>
</test_names>
<parallel>N</parallel>
<xloc>304</xloc>
diff --git
a/integration-tests/transforms/metadata/dataset/golden-excel-input-types.json
b/integration-tests/transforms/metadata/dataset/golden-excel-input-types.json
new file mode 100644
index 0000000000..e10d1540fd
--- /dev/null
+++
b/integration-tests/transforms/metadata/dataset/golden-excel-input-types.json
@@ -0,0 +1,144 @@
+{
+ "base_filename": "golden-excel-input-types.csv",
+ "name": "golden-excel-input-types",
+ "description": "",
+ "dataset_fields": [
+ {
+ "field_comment": "",
+ "field_length": -1,
+ "field_type": 5,
+ "field_precision": -1,
+ "field_name": "int",
+ "field_format": "0"
+ },
+ {
+ "field_comment": "",
+ "field_length": -1,
+ "field_type": 1,
+ "field_precision": -1,
+ "field_name": "num",
+ "field_format": "0.00"
+ },
+ {
+ "field_comment": "",
+ "field_length": -1,
+ "field_type": 2,
+ "field_precision": -1,
+ "field_name": "numText",
+ "field_format": ""
+ },
+ {
+ "field_comment": "",
+ "field_length": -1,
+ "field_type": 3,
+ "field_precision": -1,
+ "field_name": "date",
+ "field_format": "yyyy/MM/dd"
+ },
+ {
+ "field_comment": "",
+ "field_length": -1,
+ "field_type": 3,
+ "field_precision": -1,
+ "field_name": "date2",
+ "field_format": "yyyy/MM/dd"
+ },
+ {
+ "field_comment": "",
+ "field_length": -1,
+ "field_type": 3,
+ "field_precision": -1,
+ "field_name": "dateTime",
+ "field_format": "yyyy/MM/dd HH:mm"
+ },
+ {
+ "field_comment": "",
+ "field_length": -1,
+ "field_type": 2,
+ "field_precision": -1,
+ "field_name": "dateText",
+ "field_format": ""
+ },
+ {
+ "field_comment": "",
+ "field_length": -1,
+ "field_type": 1,
+ "field_precision": -1,
+ "field_name": "numDate",
+ "field_format": "0"
+ },
+ {
+ "field_comment": "",
+ "field_length": -1,
+ "field_type": 2,
+ "field_precision": -1,
+ "field_name": "text",
+ "field_format": ""
+ },
+ {
+ "field_comment": "",
+ "field_length": -1,
+ "field_type": 5,
+ "field_precision": -1,
+ "field_name": "textInt",
+ "field_format": "0"
+ },
+ {
+ "field_comment": "",
+ "field_length": -1,
+ "field_type": 4,
+ "field_precision": -1,
+ "field_name": "bool",
+ "field_format": ""
+ },
+ {
+ "field_comment": "",
+ "field_length": -1,
+ "field_type": 2,
+ "field_precision": -1,
+ "field_name": "boolText",
+ "field_format": ""
+ },
+ {
+ "field_comment": "",
+ "field_length": -1,
+ "field_type": 1,
+ "field_precision": -1,
+ "field_name": "fNum",
+ "field_format": "0.00"
+ },
+ {
+ "field_comment": "",
+ "field_length": -1,
+ "field_type": 2,
+ "field_precision": -1,
+ "field_name": "fText",
+ "field_format": ""
+ },
+ {
+ "field_comment": "",
+ "field_length": -1,
+ "field_type": 3,
+ "field_precision": -1,
+ "field_name": "fDate",
+ "field_format": "yyyy/MM/dd"
+ },
+ {
+ "field_comment": "",
+ "field_length": -1,
+ "field_type": 4,
+ "field_precision": -1,
+ "field_name": "fBool",
+ "field_format": ""
+ },
+ {
+ "field_comment": "",
+ "field_length": -1,
+ "field_type": 2,
+ "field_precision": -1,
+ "field_name": "mixed",
+ "field_format": ""
+ }
+ ],
+ "folder_name": ""
+}
\ No newline at end of file
diff --git
a/integration-tests/transforms/metadata/unit-test/0053-excel-input-xls-types
UNIT.json
b/integration-tests/transforms/metadata/unit-test/0053-excel-input-xls-types
UNIT.json
new file mode 100644
index 0000000000..7a7acbc2bb
--- /dev/null
+++
b/integration-tests/transforms/metadata/unit-test/0053-excel-input-xls-types
UNIT.json
@@ -0,0 +1,108 @@
+{
+ "variableValues": [],
+ "database_replacements": [],
+ "autoOpening": true,
+ "basePath": "",
+ "golden_data_sets": [
+ {
+ "field_mappings": [
+ {
+ "transform_field": "int",
+ "data_set_field": "int"
+ },
+ {
+ "transform_field": "num",
+ "data_set_field": "num"
+ },
+ {
+ "transform_field": "numText",
+ "data_set_field": "numText"
+ },
+ {
+ "transform_field": "date",
+ "data_set_field": "date"
+ },
+ {
+ "transform_field": "date2",
+ "data_set_field": "date2"
+ },
+ {
+ "transform_field": "dateTime",
+ "data_set_field": "dateTime"
+ },
+ {
+ "transform_field": "dateText",
+ "data_set_field": "dateText"
+ },
+ {
+ "transform_field": "numDate",
+ "data_set_field": "numDate"
+ },
+ {
+ "transform_field": "text",
+ "data_set_field": "text"
+ },
+ {
+ "transform_field": "textInt",
+ "data_set_field": "textInt"
+ },
+ {
+ "transform_field": "bool",
+ "data_set_field": "bool"
+ },
+ {
+ "transform_field": "boolText",
+ "data_set_field": "boolText"
+ },
+ {
+ "transform_field": "fNum",
+ "data_set_field": "fNum"
+ },
+ {
+ "transform_field": "fText",
+ "data_set_field": "fText"
+ },
+ {
+ "transform_field": "fDate",
+ "data_set_field": "fDate"
+ },
+ {
+ "transform_field": "fBool",
+ "data_set_field": "fBool"
+ },
+ {
+ "transform_field": "mixed",
+ "data_set_field": "mixed"
+ }
+ ],
+ "field_order": [
+ "int",
+ "num",
+ "numText",
+ "date",
+ "date2",
+ "dateTime",
+ "dateText",
+ "numDate",
+ "text",
+ "textInt",
+ "bool",
+ "boolText",
+ "fNum",
+ "fText",
+ "fDate",
+ "fBool",
+ "mixed"
+ ],
+ "data_set_name": "golden-excel-input-types",
+ "transform_name": "Verify"
+ }
+ ],
+ "input_data_sets": [],
+ "name": "0053-excel-input-xls-types UNIT",
+ "description": "",
+ "persist_filename": "",
+ "trans_test_tweaks": [],
+ "pipeline_filename": "./0053-excel-input-xls-types.hpl",
+ "test_type": "UNIT_TEST"
+}
\ No newline at end of file
diff --git
a/integration-tests/transforms/metadata/unit-test/0053-excel-input-xlsx-streaming-basic
UNIT.json
b/integration-tests/transforms/metadata/unit-test/0053-excel-input-xlsx-streaming-basic
UNIT.json
new file mode 100644
index 0000000000..aa150bb645
--- /dev/null
+++
b/integration-tests/transforms/metadata/unit-test/0053-excel-input-xlsx-streaming-basic
UNIT.json
@@ -0,0 +1,43 @@
+{
+ "variableValues": [],
+ "database_replacements": [],
+ "autoOpening": true,
+ "basePath": "",
+ "golden_data_sets": [
+ {
+ "field_mappings": [
+ {
+ "transform_field": "col1",
+ "data_set_field": "col1"
+ },
+ {
+ "transform_field": "col2",
+ "data_set_field": "col2"
+ },
+ {
+ "transform_field": "col3",
+ "data_set_field": "col3"
+ },
+ {
+ "transform_field": "col4",
+ "data_set_field": "col4"
+ }
+ ],
+ "field_order": [
+ "col1",
+ "col2",
+ "col3",
+ "col4"
+ ],
+ "data_set_name": "golden-excel-input-basic-xls",
+ "transform_name": "Verify"
+ }
+ ],
+ "input_data_sets": [],
+ "name": "0053-excel-input-xlsx-streaming-basic UNIT",
+ "description": "",
+ "persist_filename": "",
+ "trans_test_tweaks": [],
+ "pipeline_filename": "./0053-excel-input-xlsx-streaming-basic.hpl",
+ "test_type": "UNIT_TEST"
+}
\ No newline at end of file
diff --git
a/integration-tests/transforms/metadata/unit-test/0053-excel-input-xlsx-streaming-types
UNIT.json
b/integration-tests/transforms/metadata/unit-test/0053-excel-input-xlsx-streaming-types
UNIT.json
new file mode 100644
index 0000000000..7a429de9f4
--- /dev/null
+++
b/integration-tests/transforms/metadata/unit-test/0053-excel-input-xlsx-streaming-types
UNIT.json
@@ -0,0 +1,108 @@
+{
+ "variableValues": [],
+ "database_replacements": [],
+ "autoOpening": true,
+ "basePath": "",
+ "golden_data_sets": [
+ {
+ "field_mappings": [
+ {
+ "transform_field": "int",
+ "data_set_field": "int"
+ },
+ {
+ "transform_field": "num",
+ "data_set_field": "num"
+ },
+ {
+ "transform_field": "numText",
+ "data_set_field": "numText"
+ },
+ {
+ "transform_field": "date",
+ "data_set_field": "date"
+ },
+ {
+ "transform_field": "date2",
+ "data_set_field": "date2"
+ },
+ {
+ "transform_field": "dateTime",
+ "data_set_field": "dateTime"
+ },
+ {
+ "transform_field": "dateText",
+ "data_set_field": "dateText"
+ },
+ {
+ "transform_field": "numDate",
+ "data_set_field": "numDate"
+ },
+ {
+ "transform_field": "text",
+ "data_set_field": "text"
+ },
+ {
+ "transform_field": "textInt",
+ "data_set_field": "textInt"
+ },
+ {
+ "transform_field": "bool",
+ "data_set_field": "bool"
+ },
+ {
+ "transform_field": "boolText",
+ "data_set_field": "boolText"
+ },
+ {
+ "transform_field": "fNum",
+ "data_set_field": "fNum"
+ },
+ {
+ "transform_field": "fText",
+ "data_set_field": "fText"
+ },
+ {
+ "transform_field": "fDate",
+ "data_set_field": "fDate"
+ },
+ {
+ "transform_field": "fBool",
+ "data_set_field": "fBool"
+ },
+ {
+ "transform_field": "mixed",
+ "data_set_field": "mixed"
+ }
+ ],
+ "field_order": [
+ "int",
+ "num",
+ "numText",
+ "date",
+ "date2",
+ "dateTime",
+ "dateText",
+ "numDate",
+ "text",
+ "textInt",
+ "bool",
+ "boolText",
+ "fNum",
+ "fText",
+ "fDate",
+ "fBool",
+ "mixed"
+ ],
+ "data_set_name": "golden-excel-input-types",
+ "transform_name": "Verify"
+ }
+ ],
+ "input_data_sets": [],
+ "name": "0053-excel-input-xlsx-streaming-types UNIT",
+ "description": "",
+ "persist_filename": "",
+ "trans_test_tweaks": [],
+ "pipeline_filename": "./0053-excel-input-xlsx-streaming-types.hpl",
+ "test_type": "UNIT_TEST"
+}
\ No newline at end of file
diff --git
a/integration-tests/transforms/metadata/unit-test/0053-excel-input-xlsx-types
UNIT.json
b/integration-tests/transforms/metadata/unit-test/0053-excel-input-xlsx-types
UNIT.json
new file mode 100644
index 0000000000..7afade3064
--- /dev/null
+++
b/integration-tests/transforms/metadata/unit-test/0053-excel-input-xlsx-types
UNIT.json
@@ -0,0 +1,108 @@
+{
+ "variableValues": [],
+ "database_replacements": [],
+ "autoOpening": true,
+ "basePath": "",
+ "golden_data_sets": [
+ {
+ "field_mappings": [
+ {
+ "transform_field": "int",
+ "data_set_field": "int"
+ },
+ {
+ "transform_field": "num",
+ "data_set_field": "num"
+ },
+ {
+ "transform_field": "numText",
+ "data_set_field": "numText"
+ },
+ {
+ "transform_field": "date",
+ "data_set_field": "date"
+ },
+ {
+ "transform_field": "date2",
+ "data_set_field": "date2"
+ },
+ {
+ "transform_field": "dateTime",
+ "data_set_field": "dateTime"
+ },
+ {
+ "transform_field": "dateText",
+ "data_set_field": "dateText"
+ },
+ {
+ "transform_field": "numDate",
+ "data_set_field": "numDate"
+ },
+ {
+ "transform_field": "text",
+ "data_set_field": "text"
+ },
+ {
+ "transform_field": "textInt",
+ "data_set_field": "textInt"
+ },
+ {
+ "transform_field": "bool",
+ "data_set_field": "bool"
+ },
+ {
+ "transform_field": "boolText",
+ "data_set_field": "boolText"
+ },
+ {
+ "transform_field": "fNum",
+ "data_set_field": "fNum"
+ },
+ {
+ "transform_field": "fText",
+ "data_set_field": "fText"
+ },
+ {
+ "transform_field": "fDate",
+ "data_set_field": "fDate"
+ },
+ {
+ "transform_field": "fBool",
+ "data_set_field": "fBool"
+ },
+ {
+ "transform_field": "mixed",
+ "data_set_field": "mixed"
+ }
+ ],
+ "field_order": [
+ "int",
+ "num",
+ "numText",
+ "date",
+ "date2",
+ "dateTime",
+ "dateText",
+ "numDate",
+ "text",
+ "textInt",
+ "bool",
+ "boolText",
+ "fNum",
+ "fText",
+ "fDate",
+ "fBool",
+ "mixed"
+ ],
+ "data_set_name": "golden-excel-input-types",
+ "transform_name": "Verify"
+ }
+ ],
+ "input_data_sets": [],
+ "name": "0053-excel-input-xlsx-types UNIT",
+ "description": "",
+ "persist_filename": "",
+ "trans_test_tweaks": [],
+ "pipeline_filename": "./0053-excel-input-xlsx-types.hpl",
+ "test_type": "UNIT_TEST"
+}
\ No newline at end of file
diff --git
a/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelinput/ExcelInput.java
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelinput/ExcelInput.java
index 13685aeebe..55fc8017e7 100644
---
a/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelinput/ExcelInput.java
+++
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelinput/ExcelInput.java
@@ -18,6 +18,7 @@
package org.apache.hop.pipeline.transforms.excelinput;
import java.util.ArrayList;
+import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.TimeZone;
@@ -191,18 +192,14 @@ public class ExcelInput extends
BaseTransform<ExcelInputMeta, ExcelInputData> {
if (sourceMeta != null
&& sourceMeta.getType() != targetMeta.getType()
&& row[rowColumn] != null) {
- IValueMeta sourceMetaCopy = sourceMeta.clone();
- sourceMetaCopy.setConversionMask(field.getFormat());
- sourceMetaCopy.setGroupingSymbol(field.getGroupSymbol());
- sourceMetaCopy.setDecimalSymbol(field.getDecimalSymbol());
- sourceMetaCopy.setCurrencySymbol(field.getCurrencySymbol());
+ IValueMeta sourceMetaCopy = getSourceMeta(rowColumn, sourceMeta,
field);
switch (targetMeta.getType()) {
// Use case: we find a numeric value: convert it using the
supplied format to the
// desired data type...
//
case IValueMeta.TYPE_NUMBER, IValueMeta.TYPE_INTEGER:
- if (field.getHopType() == IValueMeta.TYPE_DATE) {
+ if (getFieldType(rowColumn, field) == IValueMeta.TYPE_DATE) {
// number to string conversion (20070522.00 --> "20070522")
//
IValueMeta valueMetaNumber = new ValueMetaNumber("num");
@@ -318,6 +315,40 @@ public class ExcelInput extends
BaseTransform<ExcelInputMeta, ExcelInputData> {
return row;
}
+ /**
+ * The metadata to convert a cell of another type to the type of the field:
the type of the cell
+ * with the format and the symbols of the field. It is the same for every
row, so it is made once
+ * per field and cell type.
+ */
+ private IValueMeta getSourceMeta(int column, IValueMeta sourceMeta,
ExcelInputField field) {
+ if (data.sourceMetas == null) {
+ data.sourceMetas = new
IValueMeta[meta.getFields().size()][IValueMeta.TYPE_BOOLEAN + 1];
+ }
+ IValueMeta[] fieldMetas = data.sourceMetas[column];
+ IValueMeta fieldMeta = fieldMetas[sourceMeta.getType()];
+ if (fieldMeta == null) {
+ fieldMeta = sourceMeta.clone();
+ fieldMeta.setConversionMask(field.getFormat());
+ fieldMeta.setGroupingSymbol(field.getGroupSymbol());
+ fieldMeta.setDecimalSymbol(field.getDecimalSymbol());
+ fieldMeta.setCurrencySymbol(field.getCurrencySymbol());
+ fieldMetas[sourceMeta.getType()] = fieldMeta;
+ }
+ return fieldMeta;
+ }
+
+ /** The Hop type of a field is looked up by its name: once per field. */
+ private int getFieldType(int column, ExcelInputField field) {
+ if (data.fieldTypes == null) {
+ data.fieldTypes = new int[meta.getFields().size()];
+ Arrays.fill(data.fieldTypes, -1);
+ }
+ if (data.fieldTypes[column] < 0) {
+ data.fieldTypes[column] = field.getHopType();
+ }
+ return data.fieldTypes[column];
+ }
+
private void checkType(IKCell cell, IValueMeta v) throws HopException {
if (!meta.isStrictTypes()) {
return;
diff --git
a/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelinput/ExcelInputData.java
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelinput/ExcelInputData.java
index 0bc980ab9a..a74a9ef14d 100644
---
a/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelinput/ExcelInputData.java
+++
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelinput/ExcelInputData.java
@@ -83,6 +83,12 @@ public class ExcelInputData extends BaseTransformData
implements ITransformData
public IRowMeta conversionRowMeta;
+ /** Per field and cell type: the metadata to convert such a cell to the type
of the field. */
+ IValueMeta[][] sourceMetas;
+
+ /** Per field: its Hop type, -1 when not looked up yet. */
+ int[] fieldTypes;
+
public String[] sheetNames;
public int[] startColumn;
public int[] startRow;
diff --git
a/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelinput/poi/PoiCell.java
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelinput/poi/PoiCell.java
index 21e8744b95..98fae3117a 100644
---
a/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelinput/poi/PoiCell.java
+++
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelinput/poi/PoiCell.java
@@ -18,11 +18,13 @@
package org.apache.hop.pipeline.transforms.excelinput.poi;
import java.sql.Date;
+import java.util.Map;
import java.util.TimeZone;
import org.apache.hop.core.exception.HopRuntimeException;
import org.apache.hop.core.spreadsheet.IKCell;
import org.apache.hop.core.spreadsheet.KCellType;
import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
@@ -30,19 +32,64 @@ public class PoiCell implements IKCell {
private Cell cell;
+ /** Per cell style index: does its number format show a date? Shared by the
cells of a sheet. */
+ private final Map<Short, Boolean> dateStyles;
+
+ private KCellType type;
+ private boolean typeKnown;
+
public PoiCell(Cell cell) {
+ this(cell, null);
+ }
+
+ /**
+ * @param cell the cell to read
+ * @param dateStyles what is known about the cell styles of the workbook,
filled in while reading
+ */
+ public PoiCell(Cell cell, Map<Short, Boolean> dateStyles) {
this.cell = cell;
+ this.dateStyles = dateStyles;
}
+ /** The type is asked for more than once per cell, so it is only worked out
the first time. */
@Override
public KCellType getType() {
+ if (!typeKnown) {
+ type = findType();
+ typeKnown = true;
+ }
+ return type;
+ }
+
+ /**
+ * The same answer as {@link DateUtil#isCellDateFormatted(Cell)}: the value
has to be a valid date
+ * and the number format of the cell style has to be a date format. Only the
second part is the
+ * same for every cell with that style, so only that part is remembered.
+ */
+ private boolean isDateFormatted() {
+ if (dateStyles == null) {
+ return DateUtil.isCellDateFormatted(cell);
+ }
+ if (!DateUtil.isValidExcelDate(cell.getNumericCellValue())) {
+ return false;
+ }
+ CellStyle style = cell.getCellStyle();
+ if (style == null) {
+ return false;
+ }
+ return dateStyles.computeIfAbsent(
+ style.getIndex(),
+ index -> DateUtil.isADateFormat(style.getDataFormat(),
style.getDataFormatString()));
+ }
+
+ private KCellType findType() {
switch (cell.getCellType()) {
case BOOLEAN:
return KCellType.BOOLEAN;
case NUMERIC:
- if (DateUtil.isCellDateFormatted(cell)) {
+ if (isDateFormatted()) {
return KCellType.DATE;
}
return KCellType.NUMBER;
@@ -62,7 +109,7 @@ public class PoiCell implements IKCell {
case STRING:
return KCellType.STRING_FORMULA;
case NUMERIC:
- if (DateUtil.isCellDateFormatted(cell)) {
+ if (isDateFormatted()) {
return KCellType.DATE_FORMULA;
} else {
return KCellType.NUMBER_FORMULA;
diff --git
a/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelinput/poi/PoiSheet.java
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelinput/poi/PoiSheet.java
index ed8ff1f370..32b929aa8e 100644
---
a/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelinput/poi/PoiSheet.java
+++
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelinput/poi/PoiSheet.java
@@ -17,6 +17,8 @@
package org.apache.hop.pipeline.transforms.excelinput.poi;
+import java.util.HashMap;
+import java.util.Map;
import org.apache.hop.core.spreadsheet.IKCell;
import org.apache.hop.core.spreadsheet.IKSheet;
import org.apache.poi.ss.usermodel.Cell;
@@ -26,6 +28,9 @@ import org.apache.poi.ss.usermodel.Sheet;
public class PoiSheet implements IKSheet {
private Sheet sheet;
+ /** Per cell style index: does its number format show a date? */
+ private final Map<Short, Boolean> dateStyles = new HashMap<>();
+
public PoiSheet(Sheet sheet) {
this.sheet = sheet;
}
@@ -54,7 +59,7 @@ public class PoiSheet implements IKSheet {
for (int i = 0; i < cols; i++) {
Cell cell = row.getCell(i);
if (cell != null) {
- xlsCells[i] = new PoiCell(cell);
+ xlsCells[i] = new PoiCell(cell, dateStyles);
}
}
return xlsCells;
@@ -75,6 +80,6 @@ public class PoiSheet implements IKSheet {
if (cell == null) {
return null;
}
- return new PoiCell(cell);
+ return new PoiCell(cell, dateStyles);
}
}
diff --git
a/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelinput/staxpoi/StaxPoiSheet.java
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelinput/staxpoi/StaxPoiSheet.java
index 995bda5fa3..47f2e61d7a 100644
---
a/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelinput/staxpoi/StaxPoiSheet.java
+++
b/plugins/transforms/excel/src/main/java/org/apache/hop/pipeline/transforms/excelinput/staxpoi/StaxPoiSheet.java
@@ -21,7 +21,9 @@ import com.google.common.annotations.VisibleForTesting;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
+import java.util.HashMap;
import java.util.List;
+import java.util.Map;
import java.util.TimeZone;
import javax.xml.stream.XMLInputFactory;
import javax.xml.stream.XMLStreamConstants;
@@ -49,6 +51,9 @@ public class StaxPoiSheet implements IKSheet {
// set to UTC for coherence with PoiSheet
private static final TimeZone DATE_TZ = TimeZone.getTimeZone("UTC");
+ /** Per cell style of the sheet: does its number format show a date? */
+ private final Map<String, Boolean> dateStyles = new HashMap<>();
+
private final String sheetName;
private final String sheetId;
@@ -136,7 +141,7 @@ public class StaxPoiSheet implements IKSheet {
while (sheetReader.hasNext()) {
event = sheetReader.next();
if (event == XMLStreamConstants.CHARACTERS) {
- content = new
XSSFRichTextString(sheetReader.getText()).toString();
+ content = inlineText(sheetReader.getText());
break;
}
if (event == XMLStreamConstants.END_ELEMENT
@@ -255,7 +260,7 @@ public class StaxPoiSheet implements IKSheet {
while (sheetReader.hasNext()) {
event = sheetReader.next();
if (event == XMLStreamConstants.CHARACTERS) {
- content = new
XSSFRichTextString(sheetReader.getText()).toString();
+ content = inlineText(sheetReader.getText());
break;
}
if (event == XMLStreamConstants.END_ELEMENT
@@ -343,8 +348,24 @@ public class StaxPoiSheet implements IKSheet {
};
}
+ /**
+ * The text of an inline string. Only a text with an escaped character
("_x000D_") has to be
+ * decoded.
+ */
+ private static String inlineText(String text) {
+ return text.contains("_x") ? new XSSFRichTextString(text).toString() :
text;
+ }
+
@VisibleForTesting
protected boolean isDateCell(String cellStyle) {
+ if (cellStyle == null) {
+ return false;
+ }
+ // the answer only depends on the style, and a sheet has few styles
+ return dateStyles.computeIfAbsent(cellStyle, this::hasDateFormat);
+ }
+
+ private boolean hasDateFormat(String cellStyle) {
if (cellStyle != null) {
int styleIdx = Integer.parseInt(cellStyle);
CTXf cellXf = styles.getCellXfAt(styleIdx);
diff --git
a/plugins/transforms/excel/src/test/java/org/apache/hop/pipeline/transforms/excelinput/ExcelInputCellTypesTest.java
b/plugins/transforms/excel/src/test/java/org/apache/hop/pipeline/transforms/excelinput/ExcelInputCellTypesTest.java
new file mode 100644
index 0000000000..3e3588b54e
--- /dev/null
+++
b/plugins/transforms/excel/src/test/java/org/apache/hop/pipeline/transforms/excelinput/ExcelInputCellTypesTest.java
@@ -0,0 +1,311 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.pipeline.transforms.excelinput;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertNull;
+
+import java.io.OutputStream;
+import java.nio.file.Files;
+import java.nio.file.Path;
+import java.text.SimpleDateFormat;
+import java.util.Date;
+import org.apache.hop.core.row.IValueMeta;
+import org.apache.hop.junit.rules.RestoreHopEngineEnvironmentExtension;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.CellStyle;
+import org.apache.poi.ss.usermodel.Font;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.xssf.streaming.SXSSFWorkbook;
+import org.apache.poi.xssf.usermodel.XSSFWorkbook;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.extension.RegisterExtension;
+import org.junit.jupiter.api.io.TempDir;
+
+/**
+ * Reads workbooks that mix cell types, date styles and formulas, and checks
the values and the
+ * conversions to the field types, for every way the transform reads an Excel
file.
+ */
+class ExcelInputCellTypesTest extends BaseExcelParsingTest {
+ @RegisterExtension
+ static RestoreHopEngineEnvironmentExtension env = new
RestoreHopEngineEnvironmentExtension();
+
+ @TempDir Path folder;
+
+ private static final int DATA_ROWS = 4;
+
+ @Test
+ void xlsWithPoi() throws Exception {
+ readAndCheck(write(new HSSFWorkbook(), "types.xls"), SpreadSheetType.POI);
+ }
+
+ @Test
+ void xlsxWithPoi() throws Exception {
+ readAndCheck(write(new XSSFWorkbook(), "types.xlsx"), SpreadSheetType.POI);
+ }
+
+ @Test
+ void xlsxWithStreaming() throws Exception {
+ readAndCheck(write(new XSSFWorkbook(), "types.xlsx"),
SpreadSheetType.SAX_POI);
+ }
+
+ /** A workbook written by the streaming writer keeps its strings inline in
the sheet. */
+ @Test
+ void inlineStringsWithStreaming() throws Exception {
+ checkInlineStrings(SpreadSheetType.SAX_POI);
+ }
+
+ @Test
+ void inlineStringsWithPoi() throws Exception {
+ checkInlineStrings(SpreadSheetType.POI);
+ }
+
+ private void checkInlineStrings(SpreadSheetType type) throws Exception {
+ Path file = folder.resolve("inline.xlsx");
+ try (SXSSFWorkbook workbook = new SXSSFWorkbook()) {
+ Sheet sheet = workbook.createSheet("data");
+ String[] values = {"text", "plain", " padded ", "under_xscore",
"a_x0041_b", "x_", "€ é"};
+ for (int r = 0; r < values.length; r++) {
+ sheet.createRow(r).createCell(0).setCellValue(values[r]);
+ sheet.getRow(r).createCell(1).setCellValue(r);
+ }
+ try (OutputStream out = Files.newOutputStream(file)) {
+ workbook.write(out);
+ }
+ }
+
+ meta.setSpreadSheetType(type);
+ meta.setStartsWithHeader(true);
+ addSheet();
+ setFields(field("text", "String", null), field("nr", "Integer", null));
+ open(file);
+ process();
+
+ // "_x0041_" is how the file format writes the character with code 0x41
+ check(
+ new Object[][] {
+ {"plain", 1L},
+ {" padded ", 2L},
+ {"under_xscore", 3L},
+ {"aAb", 4L},
+ {"x_", 5L},
+ {"€ é", 6L}
+ });
+ }
+
+ private Path write(Workbook workbook, String name) throws Exception {
+ Path file = folder.resolve(name);
+ try (workbook) {
+ short isoDate = workbook.createDataFormat().getFormat("yyyy-mm-dd");
+ CellStyle date1 = workbook.createCellStyle();
+ date1.setDataFormat(isoDate);
+ // a second style with the same number format
+ CellStyle date2 = workbook.createCellStyle();
+ date2.setDataFormat(isoDate);
+ Font bold = workbook.createFont();
+ bold.setBold(true);
+ date2.setFont(bold);
+ CellStyle dateTime = workbook.createCellStyle();
+ dateTime.setDataFormat(workbook.createDataFormat().getFormat("dd/mm/yyyy
hh:mm"));
+ CellStyle decimals = workbook.createCellStyle();
+ decimals.setDataFormat(workbook.createDataFormat().getFormat("0.00"));
+
+ Sheet sheet = workbook.createSheet("data");
+ Row header = sheet.createRow(0);
+ String[] names = {
+ "int",
+ "num",
+ "numText",
+ "date",
+ "date2",
+ "dateTime",
+ "dateText",
+ "numDate",
+ "text",
+ "textInt",
+ "bool",
+ "boolText",
+ "fNum",
+ "fText",
+ "fDate",
+ "fBool",
+ "mixed"
+ };
+ for (int i = 0; i < names.length; i++) {
+ header.createCell(i).setCellValue(names[i]);
+ }
+
+ for (int r = 1; r <= DATA_ROWS; r++) {
+ Row row = sheet.createRow(r);
+ if (r == 3) {
+ // a row with only the first and the last cell
+ row.createCell(0).setCellValue(r);
+ row.createCell(16).setCellValue("only");
+ continue;
+ }
+ row.createCell(0).setCellValue(r);
+ Cell num = row.createCell(1);
+ num.setCellValue(r + 0.5);
+ num.setCellStyle(decimals);
+ row.createCell(2).setCellValue(1000 * r + 0.5);
+ Cell d1 = row.createCell(3);
+ d1.setCellValue(date("2024-03-1" + r + " 00:00"));
+ d1.setCellStyle(date1);
+ Cell d2 = row.createCell(4);
+ d2.setCellValue(date("2023-11-0" + r + " 00:00"));
+ d2.setCellStyle(date2);
+ Cell d3 = row.createCell(5);
+ d3.setCellValue(date("2024-07-0" + r + " 13:45"));
+ d3.setCellStyle(dateTime);
+ Cell d4 = row.createCell(6);
+ d4.setCellValue(date("2022-01-2" + r + " 00:00"));
+ d4.setCellStyle(r == 1 ? date1 : date2);
+ row.createCell(7).setCellValue(86_400_000d * r);
+ row.createCell(8).setCellValue(" text " + r + " ");
+ row.createCell(9).setCellValue("4" + r);
+ row.createCell(10).setCellValue(r % 2 == 0);
+ row.createCell(11).setCellValue(r % 2 == 1);
+ row.createCell(12).setCellFormula("A" + (r + 1) + "*2");
+ row.createCell(13).setCellFormula("\"f\"&A" + (r + 1));
+ Cell fDate = row.createCell(14);
+ fDate.setCellFormula("D" + (r + 1) + "+1");
+ fDate.setCellStyle(date1);
+ row.createCell(15).setCellFormula("A" + (r + 1) + ">1");
+ // one column, a different cell type in every row
+ Cell mixed = row.createCell(16);
+ switch (r) {
+ case 1 -> mixed.setCellValue(7.25);
+ case 2 -> {
+ mixed.setCellValue(date("2021-05-06 00:00"));
+ mixed.setCellStyle(date2);
+ }
+ default -> mixed.setCellValue(true);
+ }
+ }
+ workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
+ try (OutputStream out = Files.newOutputStream(file)) {
+ workbook.write(out);
+ }
+ }
+ return file;
+ }
+
+ private void readAndCheck(Path file, SpreadSheetType type) throws Exception {
+ meta.setSpreadSheetType(type);
+ meta.setStartsWithHeader(true);
+ addSheet();
+
+ ExcelInputField numText = field("numText", "String", "#,##0.00");
+ numText.setDecimalSymbol(".");
+ numText.setGroupSymbol(",");
+ ExcelInputField text = field("text", "String", null);
+ text.setTrimType(IValueMeta.TrimType.BOTH);
+ ExcelInputField mixed = field("mixed", "String", null);
+
+ setFields(
+ field("int", "Integer", null),
+ field("num", "Number", null),
+ numText,
+ field("date", "Date", null),
+ field("date2", "Date", null),
+ field("dateTime", "Date", null),
+ field("dateText", "String", "yyyy/MM/dd"),
+ field("numDate", "Date", null),
+ text,
+ field("textInt", "Integer", "#"),
+ field("bool", "Boolean", null),
+ field("boolText", "String", null),
+ field("fNum", "Number", null),
+ field("fText", "String", null),
+ field("fDate", "Date", null),
+ field("fBool", "Boolean", null),
+ mixed);
+ open(file);
+ process();
+
+ checkErrors();
+ assertEquals(DATA_ROWS, rows.size());
+ for (int r = 1; r <= DATA_ROWS; r++) {
+ Object[] row = rows.get(r - 1);
+ String at = type + " row " + r + " ";
+ assertEquals((long) r, row[0], at + "int");
+ if (r == 3) {
+ for (int i = 1; i < 16; i++) {
+ assertNull(row[i], at + "column " + i);
+ }
+ assertEquals("only", row[16], at + "mixed");
+ continue;
+ }
+ assertEquals(r + 0.5, row[1], at + "num");
+ assertEquals(r + ",000.50", row[2], at + "numText");
+ assertEquals(date("2024-03-1" + r + " 00:00"), row[3], at + "date");
+ assertEquals(date("2023-11-0" + r + " 00:00"), row[4], at + "date2");
+ assertEquals(date("2024-07-0" + r + " 13:45"), row[5], at + "dateTime");
+ assertEquals("2022/01/2" + r, row[6], at + "dateText");
+ assertEquals(new Date(86_400_000L * r), row[7], at + "numDate");
+ assertEquals("text " + r, row[8], at + "text");
+ assertEquals(40L + r, row[9], at + "textInt");
+ assertEquals(r % 2 == 0, row[10], at + "bool");
+ assertEquals(r % 2 == 1 ? "Y" : "N", row[11], at + "boolText");
+ assertEquals(r * 2d, row[12], at + "fNum");
+ assertEquals("f" + r, row[13], at + "fText");
+ assertEquals(date("2024-03-1" + (r + 1) + " 00:00"), row[14], at +
"fDate");
+ assertEquals(r > 1, row[15], at + "fBool");
+ }
+ assertEquals("7.25", rows.get(0)[16], type + " mixed number");
+ assertEquals("2021/05/06 00:00:00.000", rows.get(1)[16], type + " mixed
date");
+ assertEquals("Y", rows.get(3)[16], type + " mixed boolean");
+ }
+
+ private void addSheet() {
+ ExcelInputMeta.EISheet sheet = new ExcelInputMeta.EISheet();
+ sheet.setName("data");
+ sheet.setStartRow(0);
+ sheet.setStartColumn(0);
+ meta.getSheets().add(sheet);
+ }
+
+ private void open(Path file) throws Exception {
+ ExcelInputMeta.EIFile input = new ExcelInputMeta.EIFile();
+ input.setName(file.toString());
+ input.setMask("");
+ input.setExcludeMask("");
+ input.setRequired("Y");
+ input.setIncludeSubFolders("N");
+ meta.getFiles().add(input);
+
+ transform =
+ new ExcelInput(transformMeta, meta, new ExcelInputData(), 1,
pipelineMeta, pipeline);
+ transform.init();
+ transform.addRowListener(rowListener);
+ }
+
+ private static ExcelInputField field(String name, String type, String
format) {
+ ExcelInputField field = new ExcelInputField(name, -1, -1);
+ field.setType(type);
+ field.setFormat(format);
+ return field;
+ }
+
+ private static Date date(String text) throws Exception {
+ return new SimpleDateFormat("yyyy-MM-dd HH:mm").parse(text);
+ }
+}
diff --git
a/plugins/transforms/excel/src/test/java/org/apache/hop/pipeline/transforms/excelinput/poi/PoiCellTest.java
b/plugins/transforms/excel/src/test/java/org/apache/hop/pipeline/transforms/excelinput/poi/PoiCellTest.java
new file mode 100644
index 0000000000..14cffd541e
--- /dev/null
+++
b/plugins/transforms/excel/src/test/java/org/apache/hop/pipeline/transforms/excelinput/poi/PoiCellTest.java
@@ -0,0 +1,175 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.pipeline.transforms.excelinput.poi;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertNull;
+
+import java.util.Date;
+import java.util.TimeZone;
+import java.util.stream.Stream;
+import org.apache.hop.core.spreadsheet.IKCell;
+import org.apache.hop.core.spreadsheet.KCellType;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.CellStyle;
+import org.apache.poi.ss.usermodel.DateUtil;
+import org.apache.poi.ss.usermodel.Font;
+import org.apache.poi.ss.usermodel.FormulaError;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.xssf.usermodel.XSSFWorkbook;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.MethodSource;
+
+/** The cell type and value that the POI reader gives for every kind of cell.
*/
+class PoiCellTest {
+
+ static Stream<Workbook> workbooks() {
+ return Stream.of(new HSSFWorkbook(), new XSSFWorkbook());
+ }
+
+ @ParameterizedTest
+ @MethodSource("workbooks")
+ void plainCells(Workbook workbook) throws Exception {
+ try (workbook) {
+ Row row = workbook.createSheet("s").createRow(4);
+ row.createCell(0).setCellValue(true);
+ row.createCell(1).setCellValue(12.5);
+ row.createCell(2).setCellValue("text");
+ row.createCell(3).setBlank();
+ row.createCell(4).setCellErrorValue(FormulaError.DIV0.getCode());
+
+ check(row.getCell(0), KCellType.BOOLEAN, true, "true");
+ check(row.getCell(1), KCellType.NUMBER, 12.5, "12.5");
+ check(row.getCell(2), KCellType.LABEL, "text", "text");
+ check(row.getCell(3), KCellType.EMPTY, null, null);
+ check(row.getCell(4), KCellType.EMPTY, null, null);
+ assertEquals(4, new PoiCell(row.getCell(0)).getRow());
+ }
+ }
+
+ /** A number is a date when its style has a date format, and only then. */
+ @ParameterizedTest
+ @MethodSource("workbooks")
+ void datesFollowTheCellStyle(Workbook workbook) throws Exception {
+ try (workbook) {
+ CellStyle date = workbook.createCellStyle();
+ date.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd"));
+ // same number format, another style
+ CellStyle boldDate = workbook.createCellStyle();
+ boldDate.setDataFormat(date.getDataFormat());
+ Font bold = workbook.createFont();
+ bold.setBold(true);
+ boldDate.setFont(bold);
+ // a built-in date format
+ CellStyle builtIn = workbook.createCellStyle();
+ builtIn.setDataFormat((short) 14);
+ CellStyle decimals = workbook.createCellStyle();
+ decimals.setDataFormat(workbook.createDataFormat().getFormat("0.00"));
+ CellStyle boldOnly = workbook.createCellStyle();
+ boldOnly.setFont(bold);
+
+ Sheet sheet = workbook.createSheet("s");
+ double serial = 45000.25;
+ CellStyle[] styles = {date, decimals, boldDate, null, builtIn, boldOnly,
date, decimals};
+ boolean[] isDate = {true, false, true, false, true, false, true, false};
+ // two passes: the answer for a style must not depend on the cells read
before it
+ for (int pass = 0; pass < 2; pass++) {
+ for (int i = 0; i < styles.length; i++) {
+ Cell cell = sheet.createRow(pass * 10 + i).createCell(0);
+ cell.setCellValue(serial + i);
+ if (styles[i] != null) {
+ cell.setCellStyle(styles[i]);
+ }
+ if (isDate[i]) {
+ check(cell, KCellType.DATE, shifted(DateUtil.getJavaDate(serial +
i)), null);
+ } else {
+ check(cell, KCellType.NUMBER, serial + i, Double.toString(serial +
i));
+ }
+ }
+ }
+
+ // a negative number is not a date, whatever the style says
+ Cell negative = sheet.createRow(30).createCell(0);
+ negative.setCellValue(-3.5);
+ negative.setCellStyle(date);
+ check(negative, KCellType.NUMBER, -3.5, "-3.5");
+ // and the style is still a date style for the next cell
+ Cell next = sheet.createRow(31).createCell(0);
+ next.setCellValue(serial);
+ next.setCellStyle(date);
+ check(next, KCellType.DATE, shifted(DateUtil.getJavaDate(serial)), null);
+ }
+ }
+
+ @ParameterizedTest
+ @MethodSource("workbooks")
+ void formulaCells(Workbook workbook) throws Exception {
+ try (workbook) {
+ CellStyle date = workbook.createCellStyle();
+ date.setDataFormat(workbook.createDataFormat().getFormat("dd/mm/yyyy"));
+
+ Row row = workbook.createSheet("s").createRow(0);
+ row.createCell(0).setCellValue(45000);
+ row.createCell(1).setCellFormula("A1*2");
+ row.createCell(2).setCellFormula("\"a\"&\"b\"");
+ row.createCell(3).setCellFormula("A1>1");
+ Cell dateFormula = row.createCell(4);
+ dateFormula.setCellFormula("A1+1");
+ dateFormula.setCellStyle(date);
+ row.createCell(5).setCellFormula("1/0");
+ row.createCell(6).setCellFormula("IF(A1>1,\"\",\"x\")");
+ workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
+
+ check(row.getCell(1), KCellType.NUMBER_FORMULA, 90000d, "90000.0");
+ check(row.getCell(2), KCellType.STRING_FORMULA, "ab", "ab");
+ check(row.getCell(3), KCellType.BOOLEAN_FORMULA, true, "true");
+ check(row.getCell(4), KCellType.DATE_FORMULA,
shifted(DateUtil.getJavaDate(45001)), null);
+ check(row.getCell(5), KCellType.EMPTY, null, null);
+ check(row.getCell(6), KCellType.STRING_FORMULA, "", "");
+ }
+ }
+
+ /** The type and the value are the same however often they are asked for. */
+ private static void check(Cell cell, KCellType type, Object value, String
contents) {
+ IKCell poiCell =
+ new PoiSheet(cell.getSheet()).getCell(cell.getColumnIndex(),
cell.getRowIndex());
+ for (int i = 0; i < 2; i++) {
+ assertEquals(type, poiCell.getType());
+ if (value == null) {
+ assertNull(poiCell.getValue());
+ assertNull(poiCell.getContents());
+ } else {
+ assertEquals(value, poiCell.getValue());
+ // the reader gives a date as a java.sql.Date: its text is the day only
+ assertEquals(
+ contents == null ? new java.sql.Date(((Date)
value).getTime()).toString() : contents,
+ poiCell.getContents());
+ }
+ }
+ IKCell[] row = new PoiSheet(cell.getSheet()).getRow(cell.getRowIndex());
+ assertEquals(type, row[cell.getColumnIndex()].getType());
+ }
+
+ /** The reader gives a date with the offset of the time zone added to it. */
+ private static Date shifted(Date date) {
+ return new Date(date.getTime() +
TimeZone.getDefault().getOffset(date.getTime()));
+ }
+}