1

Can Python recognize formula from Excel cell and ignore processing the cell which has formulas?

user3065099
  • 21
  • 2
  • Seeing as how formulas always start with an equals sign, that shouldn't be too hard. – SBI Dec 04 '13 at 09:23
  • Visually, array formulas start with { so take care if you adopt SBI's approach. – Bathsheba Dec 04 '13 at 09:27
  • Are you using a library such as Apache POI to read the excel file into Python's memory? This library will tell you the data type of a cell (numeric, string, equation, error) – IceArdor Dec 04 '13 at 09:35

2 Answers2

1

Yes, but instead of reinventing the wheel. I would use one of these libraries. They seem to provide what you need and a Tutorial is also available.

CML
  • 353
  • 7
  • 18
1

According to the answer provided by the author of xlrd in Jan 2011, xlrd does not currently provide access to Excel formulas. As I'm currently trying to do this, I'm inclined to believe that this is still the case - I'm working with version 0.9.2 which according to github is the latest. I've just noticed there is xlrd1 on PyPI, but as the two limitations listed are

  • There is no support for files in the Microsoft Excel 2007/2010 format
  • One cannot extract formulas from the input file

this offers no joy either.

Although my search is hardly exhaustive (I'm in a hurry), I'm of the opinion that the only sure way of accessing formulas in Python is to access the underlying COM object via Mark Hammond's pywin32. Fairly obviously you will need Excel installed, so this will limit the availability of this solution away from Windows. I'm currently using the Python Excels website for a bit of inspiration. I'm afraid that I don't have any reliable or coherent code as yet - my answer is posted mainly to warn that xlrd is sadly not yet the answer to grabbing Excel formulas via Python.

=== Tue 18.Mar.2014 BTW as I am moderately new to stackoverflow and currently lack the ability to comment or recommend, I would add that this answer was added specifically in light of the inadequacy of the previous answer to the question "can Python recognize a formula from an Excel cell?" which xlrd for all its merits does not. The main reason I posted an incompletely researched answer was to warn other users of a false positive which, for all its merits, xlrd is in this instance.

I'm currently engaged in many tasks, one of which involves this question. If I find an approach which "does" rather than one which "might" answer this question, other than the approach I have given, I will amend my answer.

Community
  • 1
  • 1
Hamish
  • 193
  • 2
  • 6
  • You may wish to check out [OpenPyXL](http://pythonhosted.org/openpyxl/) if dealing with Excel 2007 onwards. I'm not 100% sure but that might offer formula support. – Jon Clements Mar 11 '14 at 10:07