Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

My thoughts on Python in Excel

xlwings.org

Post reply on HN
Print view View original

61–70 of 123 posts

Re: My thoughts on Python in Excel

#61

Post by tichiian »

airstrike wrote: post #8
wizzwizz4 wrote: post #7

> PY cells are evaluated from left to right and top to bottom. This includes the sheets, so the first sheet gets calculated before the second sheet, etc. Oh, no. This is MS Excel 4.0 Macro sheets (Ctrl+F11) all over again, except somehow with a worse execution order. > It prevents you from referencing a cell with a Python object directly, as you’re always running the risk that someday, someone switches that cell to V…

> Excel still lets you name cells, does it not? It does, but that too is a half assed feature so users struggle with it IRL

Excel implementers struggle with it.

Half the features of Excel cannot deal with named cells. Try for example to use named cells in Conditional Formatting. Doesn't work at all, ranges cannot be named cells or tables, and condition formulas can only reference named cells with extremely ugly contortions.

There are lots and lots of additional examples of similar problems.

Excel is a loose agglomeration of unrelated features hidden under a pretty GUI.

Re: My thoughts on Python in Excel

#62

Post by buovjaga »

varunnrao wrote: post #60
marcodiego wrote: post #24

Didn't read it but, couldn't this be a "battlefield" where LibreOffice could have unbeatable advantage?

Python on LibreOffice appears to be more concerned with moving GUI elements and not enough with manipulating cells. There doesn't appear to be a straightforward library/module which helps me access specific cells and get their values. This would be a greater value proposition than allowing me to control Calc as a puppet using a Python script which seems to be their main idea.

Did you try the ScriptForge library shipped with LibreOffice: https://help.libreoffice.org/latest/en-US/text/sbasic/shared...

See the SFDocuments.Calc service there. ScriptForge makes it more convenient to work with the API.

Using it with Python: https://help.libreoffice.org/latest/en-US/text/sbasic/shared...

Re: My thoughts on Python in Excel

#63

Post by Closi »

airstrike wrote: post #5

> We wanted an alternative to VBA, but got an alternative to the Excel formula language. indeed > Integrating the Jupyter notebook cells inside the Excel grid was a mistake. 100% agreed. this seems like the typical Microsoft behavior where the team A was pushing their product and won over team B, even though it's not really what users needed > Python in Excel isn’t suitable for Python beginners nor for interactive da…

You can do multiline formulas in the advanced formula environment, but still follows the same syntax as the formula language (with comments). i.e.:

=LET(

    x,1, // assign 1 to y

    y,2, // assign 2 to y

    x+y  // add x and y
)

Same with LAMBDAs

Re: My thoughts on Python in Excel

#64

Post by pmontra »

varunnrao wrote: post #59

I was quite excited when I heard Python was coming to Excel but the execution pretty much guarantees that adoption is going to be horrible. If I as MSFT wanted to get people to write Python scripts that can take advantage of Excel's great abilities this is probably the worst way I could have gone about it. A ham-fisted, cloud-first feature implementation that is basically jammed into the product without appropriate c…

> Make a special library that can be called from the language and which only works with Excel

There are plenty of those libraries without the "only works with Excel" part, if you mean to have Excel running on the machine. As a Python example, a customer of mine is using XlsxWriter and openpyxl for .xlsx files, xlrd for .xls plus python-docx for dealing with docx files.

I don't remember why the two modules for xlsx files and not just one. My customer runs that software on Linux inside a Django app. I expect that Microsoft is interested only in Excel on Windows and in running Python inside Excel. What I expected was a VBA editor for Python and maybe a library for Windows to access Excel objects from a Python script in a cmd or powershell prompt.

Re: My thoughts on Python in Excel

#65

Post by whywhywhywhy »

varunnrao wrote: post #59

I was quite excited when I heard Python was coming to Excel but the execution pretty much guarantees that adoption is going to be horrible. If I as MSFT wanted to get people to write Python scripts that can take advantage of Excel's great abilities this is probably the worst way I could have gone about it. A ham-fisted, cloud-first feature implementation that is basically jammed into the product without appropriate c…

>but didn't really want to mess too much with a product that has stabilized over the past 30 years.

This describes the feeling of all major features added to the native app incumbents that built their foundations in the 90s: Office, Creative Cloud, etc

You really get the feeling the core dev teams of these apps have a poor understanding of their own foundations and even adding a button is an ordeal for them.

Re: My thoughts on Python in Excel

#66

Post by ale42 »

So basically... formulas running in the cloud? Seems the best way to be stuck at every possible technical issue (ISP going down, Azure going down, whatever other reason to lose connectivity...). Let alone any considerations about privacy and data protection, for which I'm pretty sure those in charge of GDPR compliance at companies start having nightmares of cloudpythonized Excel.

Re: My thoughts on Python in Excel

#67

Post by fransje26 »

whywhywhywhy wrote: post #65
varunnrao wrote: post #59

I was quite excited when I heard Python was coming to Excel but the execution pretty much guarantees that adoption is going to be horrible. If I as MSFT wanted to get people to write Python scripts that can take advantage of Excel's great abilities this is probably the worst way I could have gone about it. A ham-fisted, cloud-first feature implementation that is basically jammed into the product without appropriate c…

>but didn't really want to mess too much with a product that has stabilized over the past 30 years. This describes the feeling of all major features added to the native app incumbents that built their foundations in the 90s: Office, Creative Cloud, etc You really get the feeling the core dev teams of these apps have a poor understanding of their own foundations and even adding a button is an ordeal for them.

> and even adding a button is an ordeal for them.

It probably is, though..

Re: My thoughts on Python in Excel

#68

Post by probably_wrong »

pmontra wrote: post #64
varunnrao wrote: post #59

I was quite excited when I heard Python was coming to Excel but the execution pretty much guarantees that adoption is going to be horrible. If I as MSFT wanted to get people to write Python scripts that can take advantage of Excel's great abilities this is probably the worst way I could have gone about it. A ham-fisted, cloud-first feature implementation that is basically jammed into the product without appropriate c…

> Make a special library that can be called from the language and which only works with Excel There are plenty of those libraries without the "only works with Excel" part, if you mean to have Excel running on the machine. As a Python example, a customer of mine is using XlsxWriter and openpyxl for .xlsx files, xlrd for .xls plus python-docx for dealing with docx files. I don't remember why the two modules for xlsx fi…

> I don't remember why the two modules for xlsx files and not just one.

Last time I checked OpenPyXl doesn't deal correctly with .xlsm files - there's a parameter for that but I believe it's still experimental. In my case this meant that, on a Mac, Excel would complain that a file generated with OpenPyXl was corrupt and then successfully "recover" every generated file.

My wild guess is: your customer reads the files with OpenPyXl, processes the data with Pandas, and then uses XlsxWriter as the custom Excel writing engine.

Re: My thoughts on Python in Excel

#69

Post by varunnrao »

pmontra wrote: post #64
varunnrao wrote: post #59

I was quite excited when I heard Python was coming to Excel but the execution pretty much guarantees that adoption is going to be horrible. If I as MSFT wanted to get people to write Python scripts that can take advantage of Excel's great abilities this is probably the worst way I could have gone about it. A ham-fisted, cloud-first feature implementation that is basically jammed into the product without appropriate c…

> Make a special library that can be called from the language and which only works with Excel There are plenty of those libraries without the "only works with Excel" part, if you mean to have Excel running on the machine. As a Python example, a customer of mine is using XlsxWriter and openpyxl for .xlsx files, xlrd for .xls plus python-docx for dealing with docx files. I don't remember why the two modules for xlsx fi…

> What I expected was a VBA editor for Python and maybe a library for Windows to access Excel objects from a Python script in a cmd or powershell prompt.

This is exactly it. The killer feature for including any modern scripting language in Excel. Both XlxsWriter and openpyxl can r/w from Excel files but I have to manipulate the data using another library like `pandas`. Instead if MSFT gave a library which I can import into a Python script and use like

  import msft_excel_lib as xl
  data = xl.get('A1:A3')
  sum = xl.sum(data)
  xl.write("B3", sum)
would be much better than whatever it is they have shipped today without having to make much changes to anything else. I wouldn't even grudge them if they say that this library can run under some weird virtual environment found only within Excel to maintain product retention.

Re: My thoughts on Python in Excel

#70

Post by varunnrao »

buovjaga wrote: post #62
varunnrao wrote: post #60

Earlier quoted context omitted.

Python on LibreOffice appears to be more concerned with moving GUI elements and not enough with manipulating cells. There doesn't appear to be a straightforward library/module which helps me access specific cells and get their values. This would be a greater value proposition than allowing me to control Calc as a puppet using a Python script which seems to be their main idea.

Did you try the ScriptForge library shipped with LibreOffice: https://help.libreoffice.org/latest/en-US/text/sbasic/shared... See the SFDocuments.Calc service there. ScriptForge makes it more convenient to work with the API. Using it with Python: https://help.libreoffice.org/latest/en-US/text/sbasic/shared...

I hadn't come across the ScriptForge library before so I just looked it up. It looks pretty cool and definitely looks like how MSFT should have worked on including Python in Excel. From my quick look, it looks like ScriptForge is primarily a BASIC which supports Python(?). So I can only imagine how much more powerful a Python specific library could be given the differences in Python and BASIC themselves.
Post reply on HN