[varunnrao]

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 contextual abstractions is a pretty poor way to get things done. Given that Excel itself has a strong tabular object model built in, why would a DataFrame be required as a separate construct?

> We wanted an alternative to VBA, but got an alternative to the Excel formula language

Optimistically, I would guess that the powers that be inside MSFT wanted to show they "integrated" Python into Excel but didn't really want to mess too much with a product that has stabilized over the past 30 years. Cynically, I would say that they've messed up the implementation on purpose so they can put a bullet through the "Python in Excel" idea without actually doing anything useful.

A much better way to add Python (or any modern scripting language really) to Excel is to 1. Make a special library that can be called from the language and which only works with Excel. 2. Bundle a minimal interpreter so that the scripts can be run locally without pushing everything to some godforsaken Azure datacenter. Make this a downloadable plugin for all currently supported versions to ensure backwards-compatibility.

This is a much better way to ensure that Python becomes the glue language for Excel. There are so many applications where reporting, analysis and visualization can be better achieved through automation using scripting languages. VBA is old, limited and is yet another thing for someone to learn if they want to script any Office product. All of these are hurdles that can be easily removed. I guess though that the part that really sucks is that these are all things that the MSFT of the 80s/early 90s would have done in a heart beat if it meant more people would use Excel (they built in bugs from Lotus 1-2-3 for God's sake) but I guess it's a different story today when everybody does use Excel.

[whywhywhywhy]

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

[fransje26]

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

It probably is, though..

[pmontra]

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

[varunnrao]

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

[qsi]

You can do this through COM. I forgot which Python library I used but once you have an Excel application object you can do

Data = xl.range('a1:a3')

Sum = xl.worksheetfunction.sum(data)

Xl.range('b3').value = sum

Any library enabling the COM link will do.

(sorry, typing this on my phone so formatting and capitalization are screwy)

[probably_wrong]

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

[greentxt]

"Cynically, I would say that they've messed up the implementation on purpose so they can put a bullet through the "Python in Excel" idea without actually doing anything useful."

Sounds exactly right. Embrace/extinguish. It's The Way.