Scripting with Excel can be done in two ways: one is Excel as a host calling the scripts inside Excel, and the other is controlling Excel in another hosting language. The first can be done with VBA or Excel Javascript. The second can be done via the COM Automation interface supported by Excel. Any language that can talk to a COM Automation server can start Excel as an embedded COM object and call Excel's COM Automation API to control it. Visual Basic, JScript (Windows' Javascript), C++, C#, Java, Mathlab, Python, and others can talk to the COM Automation servers, and can script against Excel.
Yes. I believe the way Excel hosting other languages inside it as scripting addon is via the COM Automation Server interface again. The other language needs to implement as a COM Automaton Server. Excel then instantiates the other language's Automation Server as an embedded COM object and calls its interface, which can call the functions implemented with the language.
There is already VisualBasic for Applications which is just similar to Python, only with begin/end-blocks instead of indentation. And with a little fiddling you can also use JScript/WSH to remote-control some parts of Excel, as well as all the .Net languages.
The libraries in VisualBasic (before .net) do suck. But syntactically, Python and Basic are very close. I hate both very much, because all the nice features that make programming fun are lacking. Both feel very much like using a childs' toy instead of proper tools.
I code in VBA constantly as a Finance Manager. I also spend a lot of time learning Python and used it throughout a CS Degree. VBA and Python are not alike at all IMO.
You are right that you can use something like C# to manipulate Excel files, but there isn't a ton of tutorials on it, which makes it difficult and prohibitive to learn.
It is already; VBA for the desktop versions isn’t going away anytime soon but it also not being developed apart from updates to the object model to reflect added features.
You can generate Excel files in Python with XlsxWriter. It has an API for formatting, charts, merging cells and what not. If you want to automate an open Excel file, you can use xlwings. You can write data to Excel from pandas inside a Jupyter notebook.
I think part of the problem is that any scripting language rolled directly into Excel will be expected to keep backwards compatibility. Microsoft doesn't have full control over Python/JS, and they would like to avoid issues such as the changeover from Python 2 to 3.
Microsoft could implement its own fork of those languages, but is that what customers actually want?
It's probably difficult. I'm sure Microsoft has no great love for VBA, after all, they effectively orphaned the language when they came out with VB dot net. Adding the scripting language per se would be relatively easy. But a big part of VBA is being able to navigate the Excel / Office object hierarchy. I believe making that easy in Python would be hard.
Just yesterday I stumbled across https://pyspread.gitlab.io/ after finding that coding Excel is a complete desaster from a software engineering point of view - no testing, no debugging, no version control, no structure. It must have costed humanity many billions in vain.
Whaddya mean no version control? What about CTRL + SHIFT + S every few minutes with incremental cryptic filename additions to USE_THIS_ONE_CopyofCopyof_VERY_IMPORTANT_myLatest_Edits_to_Copy_of(3)_DONT_DELETE.xlsx? ;-)
But if you use Office 365 for file storage, there is actual version history for Office documents.
Scripting with Excel can be done in two ways: one is Excel as a host calling the scripts inside Excel, and the other is controlling Excel in another hosting language. The first can be done with VBA or Excel Javascript. The second can be done via the COM Automation interface supported by Excel. Any language that can talk to a COM Automation server can start Excel as an embedded COM object and call Excel's COM Automation API to control it. Visual Basic, JScript (Windows' Javascript), C++, C#, Java, Mathlab, Python, and others can talk to the COM Automation servers, and can script against Excel.
One example is https://www.pyxll.com/ which lets you write Excel Add-ins using Python.
I've never used it myself, but I have tried to use the Excel JS API, and it was quite a pain.
Yes. I believe the way Excel hosting other languages inside it as scripting addon is via the COM Automation Server interface again. The other language needs to implement as a COM Automaton Server. Excel then instantiates the other language's Automation Server as an embedded COM object and calls its interface, which can call the functions implemented with the language.
2 replies →
There is already VisualBasic for Applications which is just similar to Python, only with begin/end-blocks instead of indentation. And with a little fiddling you can also use JScript/WSH to remote-control some parts of Excel, as well as all the .Net languages.
> which is just similar to Python
What? Am I the only one who hates VBA with a burning hatred of a thousand suns?
That's a common opinion.
The object model and IDE are a big part of it though, maybe even more so than syntax horrors like while..wend vs do while..loop.
The libraries in VisualBasic (before .net) do suck. But syntactically, Python and Basic are very close. I hate both very much, because all the nice features that make programming fun are lacking. Both feel very much like using a childs' toy instead of proper tools.
VBA is very 90s. As in, similar to 90s era java, right up to the built-in IDE.
1 reply →
I code in VBA constantly as a Finance Manager. I also spend a lot of time learning Python and used it throughout a CS Degree. VBA and Python are not alike at all IMO.
You are right that you can use something like C# to manipulate Excel files, but there isn't a ton of tutorials on it, which makes it difficult and prohibitive to learn.
Blockpad (https://blockpad.net) is a nice engineering focused alternative that shoots for this (with a JS like DSL)
You can use COM/OLE to script Excel with Python or JS.
I think Microsoft is making JS a first-class scripting language for Excel (and slowly deprecating VBA).
It is already; VBA for the desktop versions isn’t going away anytime soon but it also not being developed apart from updates to the object model to reflect added features.
You can generate Excel files in Python with XlsxWriter. It has an API for formatting, charts, merging cells and what not. If you want to automate an open Excel file, you can use xlwings. You can write data to Excel from pandas inside a Jupyter notebook.
Which version of Python/JS do they implement?
I think part of the problem is that any scripting language rolled directly into Excel will be expected to keep backwards compatibility. Microsoft doesn't have full control over Python/JS, and they would like to avoid issues such as the changeover from Python 2 to 3.
Microsoft could implement its own fork of those languages, but is that what customers actually want?
It's probably difficult. I'm sure Microsoft has no great love for VBA, after all, they effectively orphaned the language when they came out with VB dot net. Adding the scripting language per se would be relatively easy. But a big part of VBA is being able to navigate the Excel / Office object hierarchy. I believe making that easy in Python would be hard.
It's pretty easy to use C# (or even F#) to write excel add-ins that can be distributed as standalone XLL files.
The difference is that add-ins is installed at the application level instead of the spreadsheet level (like VBA macros).
Scripting in Excel is a complex mess of VBA and COM, it's probably far from trivial to provide (sane) bindings into other languages.
You can do it via .NET, but a lot of people prefer VBA for its "quick and dirty"-ness.
I wish there was a Python excel.
With the uno library, you can directly script LibreOffice Calc with python. https://help.libreoffice.org/6.3/en-US/text/sbasic/python/ma...
As a demonstration, I ran a ray tracing python script from Calc and it rendered to cells in the spreadsheet.
I mean, I'd like a spreadsheet software where the cell formulas are python.
Just yesterday I stumbled across https://pyspread.gitlab.io/ after finding that coding Excel is a complete desaster from a software engineering point of view - no testing, no debugging, no version control, no structure. It must have costed humanity many billions in vain.
Whaddya mean no version control? What about CTRL + SHIFT + S every few minutes with incremental cryptic filename additions to USE_THIS_ONE_CopyofCopyof_VERY_IMPORTANT_myLatest_Edits_to_Copy_of(3)_DONT_DELETE.xlsx? ;-) But if you use Office 365 for file storage, there is actual version history for Office documents.
1 reply →
There have been some tools before such as DataNitro, but they don't exist anymore.
I think they still have several Python libraries out there, but yeah I wish Excel included Python support along with VBA.
https://openpyxl.readthedocs.io/en/stable/index.html
Not that many features are applicable to Excel. I've tried these and eventually have to default back to VBA to do what I need.
there is already exceljs
You script it with VBA
Something that isn’t PTSD inducing would be better lol