Use Python in Excel without add-ins • Hacker News Board

Re: Use Python in Excel without add-ins

#31

Post by baldfat » Fri, Sep 26, 2014, 3:35 PM UTC

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...

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.

Re: Use Python in Excel without add-ins

#32

Post by blumkvist » Fri, Sep 26, 2014, 3:48 PM UTC

hawkw wrote: post #8

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

We live in a world where Excel has several tens of millions more users than Pandas.

Re: Use Python in Excel without add-ins

#33

Post by ggrothendieck » Fri, Sep 26, 2014, 4:01 PM UTC

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.

Re: Use Python in Excel without add-ins

#34

Post by elb0w » Fri, Sep 26, 2014, 4:01 PM UTC

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

Re: Use Python in Excel without add-ins

#35

Post by theophrastus » Fri, Sep 26, 2014, 4:02 PM UTC

adamc wrote: post #4

Arkanosis wrote: post #2

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. It's a bit of a mess.

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.

Re: Use Python in Excel without add-ins

#36

Post by bsg75 » Fri, Sep 26, 2014, 4:02 PM UTC

karamazov wrote: post #26

gegtik wrote: post #3

I am guessing DataNitro isn't happy about this

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.)

Have you considered Mac interop?

Re: Use Python in Excel without add-ins

#37

Post by _deh » Fri, Sep 26, 2014, 4:09 PM UTC

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.

Re: Use Python in Excel without add-ins

#38

Post by rattray » Fri, Sep 26, 2014, 4:12 PM UTC

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/

Re: Use Python in Excel without add-ins

#39

Post by yangyang » Fri, Sep 26, 2014, 4:12 PM UTC

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.

Re: Use Python in Excel without add-ins

#40

Post by pyxll » Fri, Sep 26, 2014, 4:19 PM UTC

pge wrote: post #6

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.

LibreOffice has python support http://api.libreoffice.org/examples/examples.html