I'm working on an application that converts Excel documents to plain text files. For this I'm using Apache POI 3.7. I managed to iterate over all the sheets within a workbook as well as the rows and the cells. Extracting plain text, numeric values, boolean values, formula values, etc is going well also.
The troubles starts when working with formatting. I want to keep the same cell formatting to be use in the converted plain text files or as close as possible. Is this possible with the formatters provided by Apache POI, or should I write some formatters myself? As an example when I try to format the number 1283.3 with the (built-in) pattern #,##0.00;[Red]#,##0.00 using the CellNumberFormatter I'm getting an exception (String index out of range: 1). But when I use it to format the number 3 with the (custom) pattern "000"# it works as expected. Both values (1283.3 and 3) are considered numeric values (cell.getCellType() == Cell.CELL_TYPE_NUMERIC). I need some advice on how to use the formatters, and how I can create a plain text representation with the data that is, regarding the formatting, as close as possible as when opened within MS Excel.
