I'm stumped on this one, please help me oh wise stack exchangers...
I have a function that uses xlrd to read in an .xls file which is a file that my company puts out every few months. The file is always in the same format, just with updated data. I haven't had issues reading in the .xls files in the past but the newest release .xls file is not being read in and is producing this error: *** formula/tFunc unknown FuncID:186
Things I've tried:
I compared the new .xls file with the old to see if I could spot any differences. None that I could find.
I deleted all of the macros that were contained in the file (older versions also had macros)
Updated xlrd to version 0.9.3 but get the same error
These files are originally .xlsm files. I open them and save them as .xls files so that xlrd can read them in. This worked just fine on previous releases of the file. After upgrading to xlrd 0.9.3 which supposedly supports .xlsx, I tried saving the .xlsm file as.xlsx and tried to read it in but got an error with a blank error message
Useful Info:
My guess is that there is some sort of formula in the new file that xlrd doesn't know how to read. Does anybody know what FuncID: 186 is?
Edit: Still no clue on where to go with this. Anybody out there run into this? I tried searching up FuncID 186 to see if it's an excel function but to no avail...
In our case, the general cause turned out to be a "MySQL Excel COM add-in". See BUG: XLS files saved by Excel 2013 on Windows cause AssertionError #154 in the python-excel xlrd repo on github.
This add-in was putting a number of global formula (macros?) in the spreadsheets. By packaging up our own xlrd .egg with DEBUG = 1 set everywhere, we were able to see:
name: u'LOCAL_DATE_SEPARATOR'
name: u'LOCAL_DAY_FORMAT'
name: u'LOCAL_HOUR_FORMAT'
name: u'LOCAL_MINUTE_FORMAT'
name: u'LOCAL_MONTH_FORMAT'
name: u'LOCAL_MYSQL_DATE_FORMAT'
name: u'LOCAL_SECOND_FORMAT'
name: u'LOCAL_TIME_SEPARATOR'
name: u'LOCAL_YEAR_FORMAT'
...as the workbook was parsed, which wasn't present when others saved the workbook or when the add-in was disabled.
OpenOffice's "Documentation of the Microsoft Excel File Format" document (pdf) doesn't list a function ID 186, but there seem to be various pieces of evidence that this is some sort of named-function indirection or call.
It seems like Yoon Lee's workaround answer here -- adding 186: ('HACKED', 1, 1, 0x02, 1, 'V', 'V'), to formula.py -- will likely have the side effect of just ignoring all of these entries: it seems to be putting a "HACKED()" formula with a void arg and return type (I may be misinterpreting this slightly) in its place. As long as no attempt is made to evaluate that formula, this might work, although without understanding 186 a bit better, it's hard to tell if this might cause parsing to become desynced in some cases.
Will attempt to return and update this answer if we manage a better understanding of the issue or a safer workaround.