[
https://issues.apache.org/jira/browse/DRILL-8071?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17454585#comment-17454585
]
PJ Fanning commented on DRILL-8071:
-----------------------------------
[~cgivre] I spotted what I think is another issue in the excel code
> 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
> 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.
>
> 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)
