22

Does anyone know of a way of accessing MS Excel from Python? Specifically I am looking to create new sheets and fill them with data, including formulae.

Preferably I would like to do this on Linux if possible, but can do it from in a VM if there is no other way.

Jim Jeffries
  • 9,841
  • 15
  • 62
  • 103
  • 2
    A general comment is that Office file formats themselves are very complicated, but most of the Office tools will read in much simpler formats. Have you considered writing a `.csv` instead? – Katriel Jul 14 '11 at 19:01

6 Answers6

29

xlwt and xlrd can read and write Excel files, without using Excel itself:

http://www.python-excel.org/

Thomas K
  • 39,200
  • 7
  • 84
  • 86
  • 1
    Unfortunately they do not support formulae – Jim Jeffries Jul 14 '11 at 18:38
  • 1
    @jamesj: You can read/write formulae. You can't evaluate them. See e.g. https://secure.simplistix.co.uk/svn/xlwt/trunk/xlwt/examples/formulas.py – Thomas K Jul 14 '11 at 18:41
  • 1
    Thank you, I misunderstood. I thought you could only write them. – Jim Jeffries Jul 14 '11 at 18:43
  • 1
    I think you can access them too. xlrd is more complex than xlwt. – Thomas K Jul 14 '11 at 18:50
  • Yes after looking at this example and trying it out myself with some more non-trivial examples you can. Thanks! – Jim Jeffries Jul 14 '11 at 18:50
  • Interesting, since the maintainter of xlrd states explicitly that formula reading should not be expected anytime soon: http://stackoverflow.com/questions/4690423/get-formula-from-excel-cell-with-python-xlrd/6518681#6518681 So what has happened? – dgorissen Oct 19 '11 at 16:28
  • @dgorissen: As far as I know, you still can only get the values of formulas via `xlrd`, and even then, only when the file has been saved with Excel (thus the formulas have been evaluated by Excel). – John Y Aug 27 '13 at 14:40
  • `xlutils.copy` will lose data when copying, such as hyperlink. I think the canonical way to manipulate Excel for now is python -> COM -> Excel. But this implies you have to stay on Windows. – smwikipedia Jan 04 '16 at 06:58
6

Long time after the original question, but last answer pushed it top of feed again. Others might benefit from my experience using python and excel.

I am using excel and python quite bit. Instead of using the xlrd, xlwt modules directly, I normally use pandas. I think pandas uses these modules as imports, but i find it much easier using the pandas provided framework to create and read the spreadsheets. Pandas's Dataframe structure is very "spreadsheet-like" and makes life a lot easier in my opinion.

The other option that I use (not in direct answer to your problem) is DataNitro. It allows you to use python directly within excel. Different use case, but you would use it where you would normally have to write VBA code in Excel.

Joop
  • 7,840
  • 9
  • 43
  • 58
4

there is Python library to read/write Excel 2007 xlsx/xlsm files http://pythonhosted.org/openpyxl/

Chals
  • 141
  • 1
  • 3
4

I wrote python class that allows working with Excel via COM interface in Windows http://sourceforge.net/projects/excelcomforpython/

The class uses win32com to interact with Excel. You can use class directly or use it as example. A lot of options implemented like array formulas, conditional formatting, charts etc.

Alex
  • 2,009
  • 6
  • 24
  • 27
3

It's surely possible through the Excel object model via COM: just use win32com modules for Python. Can't remember more but I once controlled the Media Player through COM from Python. It was piece of cake.

Vladimir Sinenko
  • 4,629
  • 1
  • 27
  • 39
  • 1
    I was hoping to do it on linux, which wouldn't give me access to win32com – Jim Jeffries Jul 14 '11 at 18:46
  • 1
    Yes, I understand. But when it comes to the Microsoft proprietary formats (now open, but practically useless) it's better leave the document generation and processing to the original code. You can always find an earlier version of Excel to run under Wine... – Vladimir Sinenko Jul 14 '11 at 18:55
0

Its actually very simple. You can actually run anything from any program. Just see a way to reach command prompt from that program. In case of Excel, create a user defined function by pressing Alt+F11 and paste the following code.

Function call_cmd()
Shell "CMD /C Notepad", vbNormalFocus
End Function

Now press ctrl+s and go back to Excel, select a cell and run the function =call_cmd(). Here I ran Notepad. In the same way, you can see where python.exe is installed and run it. If you want to pass any inputs to python, then save the cells as file in local directory as csv file and read them in python using os.system().

Manideep Karthik
  • 99
  • 9