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()));
+  }
+}

Reply via email to