On Sun, 20 Sep 2015, Javen O'Neal wrote:
Right now XSSFCell.setCellFormula always runs the formula through the formula parser, which throws the FormulaParseException above.
That's our only way of sanity checking the formula before we write it, to try to catch people making mistakes, and to keep things consistent between HSSF and XSSF. (HSSF has to sanity check as part of turning it into the Ptgs to write into the file)
QUESTION: Do any solutions to this problem currently exist in POI? If not, what's the preferred fix for this? I'm thinking of something along the lines of setting workbook formula evaluation mode from automatic to manual mode. If manual mode is set, setCellFormula will not call parseFormula.
It'll have to be a XSSF-specific fix, as HSSF needs to do the parsing in order to get the ptgs for the file.
We've had a few requests over the years (in bugs, on the list, and on stackoverflow) for a way to copy a formula to a new cell "excel-like" with the non-$ references updated for the new location.
I wonder if the fix would be to add this missing functionality in, then have an option on that to skip / unchange unknown function names? (It might actually be easied to do the copy in the Ptg space, rather than Formula text space, as with the ptgs you could check for non-absolute references and update just them. That'd mean you'd need a ways to flag to skip these functions / leave them unchanged or something)
Nick --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
