Ignore custom / unknown functions

[kehh]

I'm trying to diff two spreadsheets that were created with google spreadsheets. In them I use a number of google docs specific functions such as REGEXMATCH which fails the diff with Diff failed: Name 'REGEXMATCH' is completely unknown in the current workbook

• it would be great to specify a list of functions to ignore or maybe just a flag to ignore unknown Names.

[sdementen]

+1

I have some excel functions implemented in VBA that are called in some cells and I get the same failure message.

[na-ka-na]

Sorry somehow this slipped my notice. If you're still facing this - can you attach an example spreadsheet?

[mattpalermo]

I am having the same problem with a VBA user defined function used in one of my workbooks. Although I cannot reproduce it with another workbook.

Is it possible to print out more information about which cell the error is related to? So I can track down the offending formula.

[na-ka-na]

Hmm ya I can probably add cell to the failure message, good suggestion. Meanwhile can you try running it with --debug flag? It might give some hint.

[mattpalermo]

I got this message:

org.apache.poi.ss.formula.FormulaParseException: Name 'Tax' is completely unknown in the current workbook
        at org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:927)
        at org.apache.poi.ss.formula.FormulaParser.parseNonRange(FormulaParser.java:536)
        at org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:504)
        at org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:237)
        at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1123)
        at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1083)
        at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1070)
        at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1430)
        at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1530)
        at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1514)
        at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1471)
        at org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1451)
        at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1572)
        at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:145)
        at org.apache.poi.xssf.usermodel.XSSFCell.convertSharedFormula(XSSFCell.java:420)
        at org.apache.poi.xssf.usermodel.XSSFCell.getCellFormula(XSSFCell.java:392)
        at com.ka.spreadsheet.diff.CellExcel.getValue(SpreadSheetExcel.java:173)
        at com.ka.spreadsheet.diff.CellPos.getCellValue(CellPos.java:39)
        at com.ka.spreadsheet.diff.SpreadSheetDiffer.doDiff(SpreadSheetDiffer.java:61)
        at com.ka.spreadsheet.diff.SpreadSheetDiffer.doDiff(SpreadSheetDiffer.java:25)
        at com.ka.spreadsheet.diff.SpreadSheetDiffer.main(SpreadSheetDiffer.java:17)

[na-ka-na]

I just published a release https://github.com/na-ka-na/ExcelCompare/releases/download/0.6.1/ExcelCompare-0.6.1.zip which would print the cell information. Can you try that?

[mattpalermo]

Thanks. I tried it out and I have found that the only way to fix that problem is to goto the cell, place my cursor in the formula bar and press enter (I think effectively entering the formula again). I cannot fix the cells by doing a recalculation by pressing Ctrl + Alt + F9. It seems that the cells with the UDF in the formulas have found them selves in a strange state (possibly Excel bug? I know, presumptuous, but I have come across many bugs in Excel).

I still cannot seem to replicate the problem with another workbook :/ I thought it may be my use of VBA-IDE-Code-Export, but I tried various actions with that and cannot reproduce it. I tried to search to see if others have had the same problem but didn't find any solutions.

Not a problem, I think I'll pass on using ExcelCompare for this project. I may use it for others where I can start using it from the beginning so I know when this problem starts.

A possible solution for this problem might be to read the cell's formula instead of evaluating the cell's value. That is probably a better implementation anyway since you are then reading the actual authoritative content of the cell rather than a consequence of the content. (I am assuming the cell's formula is being evaluated based on the stack trace).

[na-ka-na]

Actually it is just reading (parsing) the formula, not evaluating it. I don't evaluate formulas. It is failing to just parse the formula.

On Jan 29, 2017 10:14 PM, "Matthew Palermo" notifications@github.com wrote:

Thanks. I tried it out and I have found that the only way to fix that problem is to goto the cell, place my cursor in the formula bar and press enter (I think effectively entering the formula again). I cannot fix the cells by doing a recalculation by pressing Ctrl + Alt + F9. It seems that the cells with the UDF in the formulas have found them selves in a strange state (possibly Excel bug? I know, presumptuous, but I have come across many bugs in Excel).

I still cannot seem to replicate the problem with another workbook :/ I thought it may be my use of VBA-IDE-Code-Export https://github.com/spences10/VBA-IDE-Code-Export, but I tried various actions with that and cannot reproduce it. I tried to search to see if others have had the same problem but didn't find any solutions.

Not a problem, I think I'll pass on using ExcelCompare for this project. I may use it for others where I can start using it from the beginning so I know when this problem starts.

A possible solution for this problem might be to read the cell's formula instead of evaluating the cell's value. That is probably a better implementation anyway since you are then reading the actual authoritative content of the cell rather than a consequence of the content. (I am assuming the cell's formula is being evaluated based on the stack trace).

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/na-ka-na/ExcelCompare/issues/26#issuecomment-275987282, or mute the thread https://github.com/notifications/unsubscribe-auth/AAQBhBlKecwQSmwoNfonbU_sel2x1ZTxks5rXX_MgaJpZM4Fp68M .

[mattpalermo]

Ah, my mistake. Judging by the description here https://poi.apache.org/apidocs/org/apache/poi/ss/formula/FormulaParseException.html, I'll have to ask Apache POI about this issue if I want to figure it out.

[mattpalermo]

It would be interesting to see if openpyxl or Open XML SDK has the same issue.