We have a program that does pattern replacement in a variety of files (including Excel) and are using Aspose. So for example we might have a cell that is PATTERN_TO_REPLACE and then we replace that with "6" or "6.0" or "1,000.00" (in the US) or "1.000,00" (in the UK).
(PATTERN_TO_REPLACE could also be in a formula).
The problem is that we have to use a Java matcher to find (and replace) the string, which means we are calling cell.setValue(String). This led Aspose to change the CellValueType to string, even when it was previously numeric.
Our initial solution was to look and see if we thought the string was a number, then cast it to a BigDecimal and pass it in as such, but that leads to a lot of work (particularly for localization of types of currencies...)
What I'd like to do is call cell.getStyle(), cell.getType(), save them, set the value, then return them, but there is no cell.setType() method. Is there an easy way to do what I want?
When you use cell.setValue(String), surely, it will set string type. I think you may try to use the overloads like, cell.putValue(String, true) instead. This way, if you are inserting numbers (as string), it will be automatically converted to numbers and set it to numeric type.
PS. I am working as Support developer/ Evangelist at Aspose.