I have an xlsm
file which contains a VisualBasic function which is called from some cells in the workbook like this:
=myfunc(A1)
I know that if I modify Excel files with POI which contain macros and controls for them (like buttons) POI won't touch them and won't throw exceptions but when I try to modify a file which uses VB functions as formulas I get the following exception:
org.apache.poi.ss.formula.FormulaParseException: Name 'myfunc' is completely unknown in the current workbook
at org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:920)
at org.apache.poi.ss.formula.FormulaParser.parseNonRange(FormulaParser.java:558)
at org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:429)
at org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:268)
at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1119)
at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1079)
at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1066)
at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1426)
at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1526)
at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1510)
at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1467)
at org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1447)
at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1568)
at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:176)
at org.apache.poi.xssf.usermodel.XSSFCell.setFormula(XSSFCell.java:444)
at org.apache.poi.xssf.usermodel.XSSFCell.setCellFormula(XSSFCell.java:424)
at net.sf.jett.transform.SheetTransformer.replaceFormulas(SheetTransformer.java:336)
at net.sf.jett.transform.ExcelTransformer.replaceFormulas(ExcelTransformer.java:929)
at net.sf.jett.transform.ExcelTransformer.postTransformation(ExcelTransformer.java:720)
at net.sf.jett.transform.ExcelTransformer.transform(ExcelTransformer.java:411)
at net.sf.jett.transform.ExcelTransformer.transform(ExcelTransformer.java:384)
...
...
My idea is to configure POI to ignore (don't evaluate) these cells. Is this possible somehow?