Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Ignore custom / unknown functions #26

Open
kehh opened this issue Aug 12, 2015 · 10 comments
Open

Ignore custom / unknown functions #26

kehh opened this issue Aug 12, 2015 · 10 comments

Comments

@kehh
Copy link

kehh commented Aug 12, 2015

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
Copy link

+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
Copy link
Owner

na-ka-na commented May 8, 2016

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

@mattpalermo
Copy link

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
Copy link
Owner

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
Copy link

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
Copy link
Owner

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
Copy link

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
Copy link
Owner

na-ka-na commented Jan 30, 2017 via email

@mattpalermo
Copy link

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
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@kehh @na-ka-na @sdementen @mattpalermo