7 ms·

Use Python in Excel without add-ins

251 points by gauriage 12y ago

Arkanosis 12y ago
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 12y ago
  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 12y ago
    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.
  - richiverse 12y ago
    xlsx: https://xlsxwriter.readthedocs.org/ https://xlsxwriter.readthedocs.org/ powerpoint: http://python-pptx.readthedocs.org/en/latest/ http://python-pptx.readthedocs.org/en/latest/ word: https://pypi.python.org/pypi/docx/0.2.4 https://pypi.python.org/pypi/docx/0.2.4
  - theophrastus 12y ago
    It is a bit of a mess; documentation often amounting to a few example calls. However, I've written a fairly well received data analysis package for a government department wherein the input and output had to be in excel spreadsheets (-sigh-), using only xlrd and xlwt, alongside numpy and scipy. It's a bit Rubegoldbergian (because.. excel), but I owe a lot to the authors of xlrd and xlwt (they've even supported named regions). So i'll be curious to see how xlwings implements the numerous methods available from xlrd/xlwt.
gegtik 12y ago
I am guessing DataNitro isn't happy about this
- gauriage 12y ago
  Yes, it's DataNitro but free and with Mac compatibility ...
  - bsg75 12y ago
    Mac Excel compatibility is a big deal for us. Is this the only thing if its kind not rooted in Windows Excel?
- will_lam 12y ago
  Ha.
- karamazov 12y ago
  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 12y ago
    Have you considered Mac interop?
pge 12y ago
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.
- friendzis 12y ago
  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.
- mattfrommars 12y ago
  Might be irrevelant but anyone know best source to learn VB macros?
  - shamney 12y ago
    excel 2007 vba programmers reference
  - nissimk 12y ago
    If you are trying to automate some actions in the UI, use the record macro functionality and then edit the code. You can usually remove or combine a lot of the code it recorded. If you want to use dictionaries/hashtable there is a reference you can add called scripting.dictionary. When you search google for how to do stuff in VBA, most of the best answers are in Ozgrid and MrExcel sites, and of course StackOverflow.
  - bmccormack 12y ago
    My favorite reference is the VBA Developer's Handbook by Getz & Gilbert from 2001. You can probably learn everything you need from Googling for solutions to problems, but that book was great when I was committed to working with Excel and Access for a while. It covers a lot of things you're not going to think to search about.
  - agumonkey 12y ago
hawkw 12y ago
Not sure if I see an actual use case for this since we live in a world that has Pandas, but I'm impressed.
- blumkvist 12y ago
  We live in a world where Excel has several tens of millions more users than Pandas.
- pyxll 12y ago
  with http://www.pyxll.com http://www.pyxll.com you can have functions that take and return pandas dataframes and series, see https://github.com/pyxll/pyxll-examples/blob/master/customtypes/pandastypes.py https://github.com/pyxll/pyxll-examples/blob/master/customty...
- new_test 12y ago
  Would you rather edit a messy dataset in pandas or in Excel? What about automatically generating reports? Going to give managers your ipython printout?
emodendroket 12y ago
So could you do UDFs with this?
- fzumstein 12y ago
  No but have a look at https://github.com/ericremoreynolds/excelpython https://github.com/ericremoreynolds/excelpython for UDFs.
  - emodendroket 12y ago
    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 12y ago
      Excel-DNA is a nice open source project that lets you write XLLs in .NET http://exceldna.codeplex.com http://exceldna.codeplex.com
  - pyxll 12y ago
    also http://www.pyxll.com http://www.pyxll.com if you want a solution that embeds python and doesn't use a COM server
darkxanthos 12y ago
Kind of a serious difference... This is using Excel in Python. Python is not running inside Excel with this.
- dangoor 12y ago
  True, but you can trigger Python code from Excel using this so it seems reasonable enough.
gauriage 12y ago
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 12y ago
  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 12y ago
    Python is very readable; those users can usually understand it if they try.
jpetersonmn 12y ago
So do you need excel to be installed to use this? Currently I use xlwt and read the files with google docs.
- fzumstein 12y ago
  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.
_deh 12y ago
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.
panzi 12y ago
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 12y ago
  You can see this limitation in the video, when generating 100^2 random numbers takes a while.
alexchamberlain 12y ago
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 12y ago
  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 12y ago
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 12y ago
  Or simply comment out that line: https://github.com/ZoomerAnalytics/xlwings/issues/72 https://github.com/ZoomerAnalytics/xlwings/issues/72
baldfat 12y ago
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/combining-f-and-excel-using-excel-dna-some-links.aspx 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.
ggrothendieck 12y ago
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 12y ago
  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.
elb0w 12y ago
But couldn't you do this with win32com.client already?
- jlarocco 12y ago
  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.
rattray 12y ago
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/
yangyang 12y ago
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 12y ago
  Except for the Windows only factor. Being able to code for all versions of Excel is useful.
rpcyc 12y ago
I wonder how this compares to datanitro
kelvin0 12y ago
I was looking for the 'secret' sauce used to get python being able to interact with Excel: http://showmedo.com/videotutorials/video?name=2190010&fromSeriesID=219 http://showmedo.com/videotutorials/video?name=2190010&fromSe... 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 12y ago
  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/xlwings/xlwings.bas https://github.com/ZoomerAnalytics/xlwings/blob/master/xlwin... Surprisingly simple actually.
  - kelvin0 12y ago
    I was expecting something a bit more complicated, but simplicity usually is the best way to go...
- deleted 12y ago
  [deleted]
bubbleRefuge 12y ago
How does the automation of Excel work on the Mac since there is no COM on mac? Is there some Applescript alternative ?
- deleted 12y ago
  [deleted]
- zedpm 12y ago
  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/Conceptual/ScriptingBridgeConcepts/Introduction/Introduction.html https://developer.apple.com/library/mac/documentation/Cocoa/...
s-phi-nl 12y ago
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 12y ago
  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/
- ehremo 12y ago
  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). [1] https://github.com/ericremoreynolds/excelpython https://github.com/ericremoreynolds/excelpython
zwieback 12y ago
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.
fortknots 12y ago
Very interesting.
leonth 12y ago
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.
detroitcoder 12y ago
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-objects-within-excel/ http://detroitcoder.me/2013/11/26/video-create-python-object...

251 points by gauriage 12y ago

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 12y ago
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 12y ago
  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.
- richiverse 12y ago
  xlsx: https://xlsxwriter.readthedocs.org/ https://xlsxwriter.readthedocs.org/ powerpoint: http://python-pptx.readthedocs.org/en/latest/ http://python-pptx.readthedocs.org/en/latest/ word: https://pypi.python.org/pypi/docx/0.2.4 https://pypi.python.org/pypi/docx/0.2.4
- theophrastus 12y ago
  It is a bit of a mess; documentation often amounting to a few example calls. However, I've written a fairly well received data analysis package for a government department wherein the input and output had to be in excel spreadsheets (-sigh-), using only xlrd and xlwt, alongside numpy and scipy. It's a bit Rubegoldbergian (because.. excel), but I owe a lot to the authors of xlrd and xlwt (they've even supported named regions). So i'll be curious to see how xlwings implements the numerous methods available from xlrd/xlwt.

gegtik 12y ago

I am guessing DataNitro isn't happy about this

gauriage 12y ago
Yes, it's DataNitro but free and with Mac compatibility ...
- bsg75 12y ago
  Mac Excel compatibility is a big deal for us. Is this the only thing if its kind not rooted in Windows Excel?
will_lam 12y ago
Ha.
karamazov 12y ago
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 12y ago
  Have you considered Mac interop?

pge 12y ago

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.

friendzis 12y ago
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.
mattfrommars 12y ago
Might be irrevelant but anyone know best source to learn VB macros?
- shamney 12y ago
  excel 2007 vba programmers reference
- nissimk 12y ago
  If you are trying to automate some actions in the UI, use the record macro functionality and then edit the code. You can usually remove or combine a lot of the code it recorded. If you want to use dictionaries/hashtable there is a reference you can add called scripting.dictionary. When you search google for how to do stuff in VBA, most of the best answers are in Ozgrid and MrExcel sites, and of course StackOverflow.
- bmccormack 12y ago
  My favorite reference is the VBA Developer's Handbook by Getz & Gilbert from 2001. You can probably learn everything you need from Googling for solutions to problems, but that book was great when I was committed to working with Excel and Access for a while. It covers a lot of things you're not going to think to search about.
- agumonkey 12y ago

hawkw 12y ago

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

blumkvist 12y ago
We live in a world where Excel has several tens of millions more users than Pandas.
pyxll 12y ago
with http://www.pyxll.com http://www.pyxll.com you can have functions that take and return pandas dataframes and series, see https://github.com/pyxll/pyxll-examples/blob/master/customtypes/pandastypes.py https://github.com/pyxll/pyxll-examples/blob/master/customty...
new_test 12y ago
Would you rather edit a messy dataset in pandas or in Excel? What about automatically generating reports? Going to give managers your ipython printout?

emodendroket 12y ago

So could you do UDFs with this?

fzumstein 12y ago
No but have a look at https://github.com/ericremoreynolds/excelpython https://github.com/ericremoreynolds/excelpython for UDFs.
- emodendroket 12y ago
  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 12y ago
    Excel-DNA is a nice open source project that lets you write XLLs in .NET http://exceldna.codeplex.com http://exceldna.codeplex.com
- pyxll 12y ago
  also http://www.pyxll.com http://www.pyxll.com if you want a solution that embeds python and doesn't use a COM server

darkxanthos 12y ago

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

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

gauriage 12y ago

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 12y ago
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 12y ago
  Python is very readable; those users can usually understand it if they try.

jpetersonmn 12y ago

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

fzumstein 12y ago
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.

_deh 12y ago

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.

panzi 12y ago

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 12y ago
You can see this limitation in the video, when generating 100^2 random numbers takes a while.

alexchamberlain 12y ago

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 12y ago
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 12y ago

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 12y ago
Or simply comment out that line: https://github.com/ZoomerAnalytics/xlwings/issues/72 https://github.com/ZoomerAnalytics/xlwings/issues/72

baldfat 12y ago

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/combining-f-and-excel-using-excel-dna-some-links.aspx 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.

ggrothendieck 12y ago

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 12y ago
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.

elb0w 12y ago

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

jlarocco 12y ago
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.

rattray 12y ago

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/

yangyang 12y ago

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 12y ago
Except for the Windows only factor. Being able to code for all versions of Excel is useful.

rpcyc 12y ago

I wonder how this compares to datanitro

kelvin0 12y ago

I was looking for the 'secret' sauce used to get python being able to interact with Excel: http://showmedo.com/videotutorials/video?name=2190010&fromSeriesID=219 http://showmedo.com/videotutorials/video?name=2190010&fromSe... 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 12y ago
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/xlwings/xlwings.bas https://github.com/ZoomerAnalytics/xlwings/blob/master/xlwin... Surprisingly simple actually.
- kelvin0 12y ago
  I was expecting something a bit more complicated, but simplicity usually is the best way to go...
deleted 12y ago
[deleted]

bubbleRefuge 12y ago

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

deleted 12y ago
[deleted]
zedpm 12y ago
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/Conceptual/ScriptingBridgeConcepts/Introduction/Introduction.html https://developer.apple.com/library/mac/documentation/Cocoa/...

s-phi-nl 12y ago

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 12y ago
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/
ehremo 12y ago
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). [1] https://github.com/ericremoreynolds/excelpython https://github.com/ericremoreynolds/excelpython

zwieback 12y ago

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.

fortknots 12y ago

Very interesting.

leonth 12y ago

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.

detroitcoder 12y ago

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-objects-within-excel/ http://detroitcoder.me/2013/11/26/video-create-python-object...