
Use Python in Excel without add-ins - gauriage
http://xlwings.org/
======
pge
This is fantastic - I am going to start testing it now. I write a fair amount
of VB macros, and would much prefer to be able to use Python.

And, if they could create a plugin that allowed Libre Office to do the same,
they could solve one of the last remaining major interoperability issues with
LO and Excel: the inability to run VB macros in LO. That would be huge.

~~~
mattfrommars
Might be irrevelant but anyone know best source to learn VB macros?

~~~
agumonkey
When I had to spend a large amount of intimate time with VBA I used to go to

[http://www.cpearson.com/Excel/Topic.aspx](http://www.cpearson.com/Excel/Topic.aspx)

[http://www.ozgrid.com/VBA/](http://www.ozgrid.com/VBA/)

Old websites but in my case I dealt with Office <= 2003

~~~
medell
Great recommendations, these two sites are still the best today. Ozgrid is my
go to forum, the community there is top notch.

~~~
agumonkey
I'm still not sure there's another skywalker. I stopped [ab]using VBA in 2012
and unfortunately I can't recall the name yet.

------
Arkanosis
Not the same thing, but this made me think about it and readers might be
interested to know that the xlrd, xlwt and xlutils python modules
([http://www.python-excel.org/](http://www.python-excel.org/)) allow you to
manipulate Excel files in Python, without even having Excel installed (it's
pure Python, so it also works on Linux, for example).

~~~
adamc
Looks like xlrd may have been updated to read newer formats, but xlwt is still
limited and cannot write .xlsx format spreadsheets, for example. openpyxl
_can_ handle the newer formats, but only the newer formats.

It's a bit of a mess.

~~~
pmorici
Working with Excel spreadsheets in general is a mess. There really isn't a
single library that does everything well and if you want of be able to get all
the information like formatting, formulas, etc... the only really complete
solution is to use pywin32 and com which requires Excel and isn't particularly
fast or easy either.

------
darkxanthos
Kind of a serious difference... This is using Excel in Python. Python is not
running inside Excel with this.

~~~
dangoor
True, but you can trigger Python code from Excel using this so it seems
reasonable enough.

------
gegtik
I am guessing DataNitro isn't happy about this

~~~
karamazov
Not at all, it's great to see more products helping people get away from VBA.
xlwings is well-done and has some features we don't (e.g. Mac interop), and
likewise we have some features they don't (e.g. UDF's).

(I'm one of the DataNitro founders.)

~~~
bsg75
Have you considered Mac interop?

------
rattray
Awesome to see a FOSS solution for this.

Ben at DataNitro[1], a paid solution that's been around for a while, has been
awesome in my occasional correspondence with him as a user. Anyone looking for
xlwing's functionality with commercial support etc should give them a look.

[1] [https://datanitro.com/](https://datanitro.com/)

------
alexchamberlain
I know this doesn't support Linux and LibreOffice, but could it? Is there a
technical way that would allow Python to sit alongside LibreOffice?

~~~
maxerickson
The documentation doesn't seem very mature, but Python is supposedly supported
as a scripting/macro language, and it is also possible to interact with LO at
the API level.

------
squeaky-clean
Very cool, just this week I was looking for something exactly like this to
help automate reporting that absolutely has to be Excel. I wonder how well it
plays with PivotTables and how it handles lots of data. I tried all the other
python Excel libraries, and they either had memory errors with our data (It's
not even that big, but it's much larger than we should be using Excel for), or
were fine on memory, but would destroy PivotTables in the sheets they wrote.

For anyone running 64-bit Office and getting a compile error, to get it to
run, just add the PtrSafe keyword to the Declare line like this,

    
    
        Private Declare PtrSafe Function [etc...]

~~~
fzumstein
Or simply comment out that line:
[https://github.com/ZoomerAnalytics/xlwings/issues/72](https://github.com/ZoomerAnalytics/xlwings/issues/72)

------
ggrothendieck
To run a python function you do need to write one line of VBA code and you
also need a vba file that comes with the package so although its not very much
to add on the VBA side the title here seems a bit misleading.

~~~
bmccormack
From what I gather watching the video, you only need the xlwing.bas module and
the supporting VBA code if you plan to invoke python from within the Excel
file. If you're only manipulating an Excel file with Python externally, which
was covered in the beginning of the video, it doesn't appear you need the
module and VBA code.

------
gauriage
I use it since today, and I'm very impressed. I work for a financial startups,
with old fashioned non-tech guys who works only on excel. Xlwings sove me from
VBA programmation for my algorithms ...

~~~
0003
This may not be an issue where you work, but how do you get away from those
who "want to see the formulas" and "want to be able to modify the
spreadsheets"?

~~~
karamazov
Python is very readable; those users can usually understand it if they try.

------
panzi
So this talks via IPC to Excel? How does it perform when working with huge
data sets? Is the copying back and forth between the two processes a bigger
bottleneck than the Python interpreter itself?

~~~
JetSpiegel
You can see this limitation in the video, when generating 100^2 random numbers
takes a while.

------
_deh
Very nice indeed. My team do statistical work, usually delivered in Excel, and
we're going to be focused on automation / productivity quite heavily in the
next few months. I need to put this through its paces a bit more than putting
'Hello World' in A1 - but it raises the tantalising prospect of a bunch of R
and VBA work turning into a bunch of R and Python work.

------
baldfat
Well I did a quick look for F# and C# tools like this and found

[http://blogs.msdn.com/b/fsharpteam/archive/2013/07/16/combin...](http://blogs.msdn.com/b/fsharpteam/archive/2013/07/16/combining-
f-and-excel-using-excel-dna-some-links.aspx)

Interesting usage. Personally I try to use CSV and code in R. R has libraries
where I can pull information in BUT I am always afraid of issues with a non-
open file format.

In the Universe of everything is done in Word and I am hard pressed to get
people to even use Excel these approaches might make my job easier.

------
detroitcoder
This has been available for over a decade with the win32com client using COM.
You can easily register python classes so that you can call them from Excel
and easily call Excel from Python. There is nothing new in this video. I put a
simple tutorial on how to do some of this last year.
[http://detroitcoder.me/2013/11/26/video-create-python-
object...](http://detroitcoder.me/2013/11/26/video-create-python-objects-
within-excel/)

------
kelvin0
I was looking for the 'secret' sauce used to get python being able to interact
with Excel:
[http://showmedo.com/videotutorials/video?name=2190010&fromSe...](http://showmedo.com/videotutorials/video?name=2190010&fromSeriesID=219)

However, I still haven't looked how the reverse is done (by xlwings.bas in
Excel). Is it that XLWings also implments a COM server from which VBA can run
Python?

~~~
jonknee
The source is on GitHub...

[https://github.com/ZoomerAnalytics/xlwings](https://github.com/ZoomerAnalytics/xlwings)

The magic you're looking for is here:

[https://github.com/ZoomerAnalytics/xlwings/blob/master/xlwin...](https://github.com/ZoomerAnalytics/xlwings/blob/master/xlwings/xlwings.bas)

Surprisingly simple actually.

~~~
kelvin0
I was expecting something a bit more complicated, but simplicity usually is
the best way to go...

------
leonth
I recently compared various ways to automate Excel with python:
[http://leontius.net/2014/excel-data-analysis-
python/](http://leontius.net/2014/excel-data-analysis-python/)

Xlwings is something I did not know before writing the post, but it does look
great and I would love to try it as soon as possible. It basically has all the
advantages of Datanitro but it is free.

------
s-phi-nl
Can anyone compare this with PyXLL[1]? It does require add-ins, but xlwings
requires a VBA module. From the instructions, importing the two seem about
equally complicated.

I am more curious about technical differences than the difference in license
(I know that PyXLL is closed source, while XLWings is F/OSS, BSD-licensed).

[1] [https://www.pyxll.com/](https://www.pyxll.com/)

~~~
pyxll
PyXLL embeds python, and so runs python in-process (that is, in the same
process as Excel, without spawning a child process).

PyXLL fully supports UDFs (user defined functions) in a fast, efficient way.
Calling a pyxll function (UDF written in python) calls into the in-process
python interpreter and so there is no cost of starting up a new python process
or calling into an out of process COM server.

Excel and Python types are converted to and from each other in optimized C
code, depending on the argument and return types specified for the function
(UDF). The type conversion is user extensible and so can handle automatically
converting Excel ranges to and from pandas DataFrames (just one example).

The full Excel API is accessible from pyxll using the well known and
established win32com package.

PyXLL works with Python 2 and 3, 32 bit and 64 bit (all versions from python
2.3 and 3.4).

PyXLL supports newer features of recent Excel releases like multi-threading
and asynchronous functions, while maintaining compatibility with older
versions.

PyXLL can expose python functions as Excel macros and menus as well as UDFs.

If you want to script Excel from a process outside of excel (eg IPython
notebook) you can use win32com very easily without the need for any other
package. But, if you want to have a python interpreter running inside Excel to
be able to write performant UDFs as an alternative to writing VBA or C addins
as far as I'm aware PyXLL is still the only package capable of doing that[1].

PyXLL has commercial support and is partnered with a major python training and
consultancy company, Enthought[2].

[1]DataNitro claims to be able to do with, but according to their docs you are
limited to only being able to run one Excel instance at a time, which for most
real world users is too limiting in my experience.

[2][https://www.enthought.com/products/pyxll/](https://www.enthought.com/products/pyxll/)

------
jpetersonmn
So do you need excel to be installed to use this? Currently I use xlwt and
read the files with google docs.

~~~
fzumstein
Yes...you need Excel installed (on Windows or Mac) as this is a package for
interaction/programming with open files, not just reading/writing files.

------
zwieback
Very nice although the real pain of programming against Office APIs isn't the
language but the Office object models themselves, which seem to have been
invented in hell by multiple demons that weren't communicating with each
other.

------
bubbleRefuge
How does the automation of Excel work on the Mac since there is no COM on mac?
Is there some Applescript alternative ?

~~~
zedpm
Office apps on the Mac are automated using Scripting Bridge [1]. Applescript
is one way to drive a scriptable app, but Python and other languages can also
use the bridge. I built a substantial Word addin on the Mac in C# on Mono,
using the Scripting Bridge.

[1]
[https://developer.apple.com/library/mac/documentation/Cocoa/...](https://developer.apple.com/library/mac/documentation/Cocoa/Conceptual/ScriptingBridgeConcepts/Introduction/Introduction.html)

------
hawkw
Not sure if I see an actual use case for this since we live in a world that
has Pandas, but I'm impressed.

~~~
new_test
Would you rather edit a messy dataset in pandas or in Excel? What about
automatically generating reports? Going to give managers your ipython
printout?

------
rpcyc
I wonder how this compares to datanitro

------
elb0w
But couldn't you do this with win32com.client already?

~~~
jlarocco
Yes.

In the past, I've used Python to automate Excel, Outlook, and some non-MS
products like Catia.

Honestly, I don't see the advantage here. FOSS is nice, and OSX support is
nice, but if you're interacting with MS products, FOSS is not a concern
anyway, and it's probably running on Windows...

What would be really nice is if it supported multiple backends, so I could run
the same Python code with Excel, Apple's Numbers, Google spreadsheet or
Gnumeric, and have it work the same on all of them.

------
yangyang
[http://pyxll.com/](http://pyxll.com/) already does all of this, better

In particular it's in-process and does UDFs.

Admittedly it isn't open-source but it is free for non-commercial use.

~~~
bsg75
Except for the Windows only factor. Being able to code for all versions of
Excel is useful.

------
emodendroket
So could you do UDFs with this?

~~~
fzumstein
No but have a look at
[https://github.com/ericremoreynolds/excelpython](https://github.com/ericremoreynolds/excelpython)
for UDFs.

~~~
emodendroket
Cool. The problem with all these big "VBA replacements" (and don't get me
wrong, I've done both VBA and VSTO and the latter is more pleasant to work
with) is that they never seem to provide replacements for UDFs, which is
probably the #1 reason you'd want to use VBA in the first place.

~~~
bjoerns
Excel-DNA is a nice open source project that lets you write XLLs in .NET
[http://exceldna.codeplex.com](http://exceldna.codeplex.com)

------
fortknots
Very interesting.

