[
https://issues.apache.org/jira/browse/DRILL-8071?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Charles Givre updated DRILL-8071:
---------------------------------
Affects Version/s: 1.19.0
> format-excel should use POI DataFormatter
> -----------------------------------------
>
> Key: DRILL-8071
> URL: https://issues.apache.org/jira/browse/DRILL-8071
> Project: Apache Drill
> Issue Type: Improvement
> Components: Execution - Data Types
> Affects Versions: 1.19.0
> Reporter: PJ Fanning
> Priority: Major
>
> The existing ExcelBatchReader uses the raw data values from the cells. This
> raw data ignores formatting set on the cells. As an example, numbers and
> dates are stored as doubles. With the POI DataFormatter, you can get the cell
> style applied so that the data will appear as it does when you view the data
> in Excel itself.
> [https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/DataFormatter.html#formatCellValue-org.apache.poi.ss.usermodel.Cell-]
>
> A big number like 123456789.987654 could be stored as double that is more
> like 123456789.9876539999999 when represented in decimal format (because this
> might be the closest match that double can represent). The cell format could
> say that cell has 6 decimal places after the decimal point so the formatter
> would round the number back to the value that it displayed in Excel as.
> Even if you choose not to use the DataFormatter, you have unprotected calls
> to `cell.getNumericCellValue()` and that could easily throw an exception (if
> the data is not stored a number). Even `cell.getStringCellValue()` can throw
> an exception - for similar reasons.
>
> There is also custom code for handling the conversion of the raw numbers
> representing dates/timestamps but this also seems like a bad idea. The Cell
> class has getLocalDateTimeCellValue and this has the right logic for
> converting 1904 and 1900 based dates - yes, Excel uses 2 different formats.
> Code that processes excel files is a real pain to get right because the
> Microsoft storage format is really bad.
>
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
