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.
In LO you cannot define format strings that automagically scale the number, e.g. "d = "0"mm"%%% to use meters for further calculations yet display in mm.
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/) 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).
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.
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).
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.
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.
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,
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.
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.
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 ...
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"?
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?
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.
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.
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...
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?
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.
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).
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.
It doesn't really make much sense to compare xlwings with PyXLL, as they perform a slightly different function and actually could be used together.
PyXLL is more comparable to ExcelPython[1], which is open source does a similar thing (enables you to write UDFs in Python). ExcelPython hosts the Python process outside the Excel process but it
a) does not restart the Python process with every UDF therefore has good performance
b) does not require any registration of COM servers - therefore doesn't require admin rights
c) does not require an add-in (there is an add-in, but it just facilitates development, it's not actually required to use the resulting workbook).
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.
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.
Would you rather edit a messy dataset in pandas or in Excel? What about automatically generating reports? Going to give managers your ipython printout?
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.
pge|11 years ago
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.
pyxll|11 years ago
mattfrommars|11 years ago
friendzis|11 years ago
Arkanosis|11 years ago
adamc|11 years ago
It's a bit of a mess.
darkxanthos|11 years ago
dangoor|11 years ago
gegtik|11 years ago
karamazov|11 years ago
(I'm one of the DataNitro founders.)
gauriage|11 years ago
will_lam|11 years ago
rattray|11 years ago
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/
alexchamberlain|11 years ago
maxerickson|11 years ago
squeaky-clean|11 years ago
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,
fzumstein|11 years ago
ggrothendieck|11 years ago
bmccormack|11 years ago
gauriage|11 years ago
0003|11 years ago
panzi|11 years ago
JetSpiegel|11 years ago
_deh|11 years ago
baldfat|11 years ago
http://blogs.msdn.com/b/fsharpteam/archive/2013/07/16/combin...
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|11 years ago
kelvin0|11 years ago
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|11 years ago
https://github.com/ZoomerAnalytics/xlwings
The magic you're looking for is here:
https://github.com/ZoomerAnalytics/xlwings/blob/master/xlwin...
Surprisingly simple actually.
unknown|11 years ago
[deleted]
leonth|11 years ago
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|11 years ago
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/
pyxll|11 years ago
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/
ehremo|11 years ago
PyXLL is more comparable to ExcelPython[1], which is open source does a similar thing (enables you to write UDFs in Python). ExcelPython hosts the Python process outside the Excel process but it
a) does not restart the Python process with every UDF therefore has good performance
b) does not require any registration of COM servers - therefore doesn't require admin rights
c) does not require an add-in (there is an add-in, but it just facilitates development, it's not actually required to use the resulting workbook).
[1] https://github.com/ericremoreynolds/excelpython
jpetersonmn|11 years ago
fzumstein|11 years ago
zwieback|11 years ago
bubbleRefuge|11 years ago
zedpm|11 years ago
[1] https://developer.apple.com/library/mac/documentation/Cocoa/...
unknown|11 years ago
[deleted]
hawkw|11 years ago
new_test|11 years ago
pyxll|11 years ago
blumkvist|11 years ago
rpcyc|11 years ago
elb0w|11 years ago
jlarocco|11 years ago
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|11 years ago
In particular it's in-process and does UDFs.
Admittedly it isn't open-source but it is free for non-commercial use.
bsg75|11 years ago
emodendroket|11 years ago
fzumstein|11 years ago
fortknots|11 years ago