hansva opened a new issue, #9468:
URL: https://github.com/apache/hop/issues/9468

   ### Apache Hop version?
   
   2.20.0
   
   ### Java version?
   
   21
   
   ### Operating system
   
   Other
   
   ### What happened?
   
   The Excel input transform redoes several lookups for every cell, although 
their result only depends on the cell's style or on the field definition. This 
costs 25 to 45% of the run time when reading a workbook.
   
   1. **The cell type is worked out twice per cell (POI engine).**
      `ExcelInput.fillRow()` calls `cell.getType()`, and `PoiCell.getValue()` 
calls `getType()` again. For a numeric cell each call runs 
`DateUtil.isCellDateFormatted(cell)`, which reads the cell style and its format 
string and tests whether it is a date format.
   
   2. **The date check is repeated for every cell with the same style (both 
engines).**
      Whether a number format is a date format only depends on the style. 
`PoiCell` asks POI again for every cell, and `StaxPoiSheet.isDateCell()` looks 
up the style and the number format for every cell.
   
   3. **The conversion metadata is rebuilt for every converted cell.**
      When the type of a cell differs from the type of the field (for example a 
numeric cell read into an Integer field), `fillRow()` clones a value meta and 
sets the mask and the grouping, decimal and currency symbols, for every cell. 
The values are the same for every row.
   
   4. **The field type is resolved through the plugin registry per converted 
cell.**
      `ExcelInputField.getHopType()` calls 
`ValueMetaFactory.getIdForValueMeta(type)` each time.
   
   5. **The streaming engine wraps every inline string in an 
`XSSFRichTextString`.**
      `StaxPoiSheet` does this only to decode `_xHHHH_` escapes, which almost 
no value contains.
   
   ### Proposed change
   
   - `PoiCell` works out its type once and keeps it.
   - Cache "is a date format" per cell style, per sheet, in `PoiSheet` and 
`StaxPoiSheet`. The value part of POI's check (`DateUtil.isValidExcelDate`) is 
still done per cell, so the result is the same as 
`DateUtil.isCellDateFormatted`.
   - `ExcelInput` builds the conversion value meta once per field and source 
type, with the same mask and symbols as today, and resolves the field type once 
per field.
   - `StaxPoiSheet` uses the inline text directly when it does not contain 
`_x`, and keeps the current code when it does.
   
   ### Compatibility
   
   No change in behaviour is intended: the same cell types, the same masks and 
symbols, and the same values in the output rows. No metadata, dialog or 
documentation changes.
   
   ### Issue Priority
   
   Priority: 2
   
   ### Issue Component
   
   Component: Transforms


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]

Reply via email to