Excel Macros with Javascript

Question

I want to manipulate Excel tables using macros in Javascript, not the default VBA. I can execute javascript code using the following VBA code

'javascript to execute Dim b As String b = "function meaningOfLife(a,b) {return 42;}" 'VBA tool to run it Dim o As New ScriptControl o.Language = "JScript" o.AddCode b MsgBox o.Run("meaningOfLife", 0, 1) 

this allows me to execute arbitrary javascript, however I do not have access to the excel spreadsheet from the javascript environment. Is there a way that I can set and get the sheet values ​​in the active sheet from javascript?

Answer 1

It depends on what you want Excel Programming to do.

If you want to manage Excel files, you can do this through COM automation or even ODBC to some extent, using JavaScript running in a Windows scripting environment.

But if you want your code to work in an active Excel session, which the visitor does, you will have fewer options.

See this question. I published a few years ago when I had JavaScript that I wanted to run in Excel, and I did not have a budget to convert to VBA:

How to use JavaScript in Excel macro?

Answer 2

Of course, now in Excel 2013 you have a JavaScript API ( http://zoom.it/UAzs#full ) and you can interact with the spreadsheet using the taskbar and the content of the PROGRAM.

Answer 3

For users using Excel 2016 or later, the add-in has an Excel Funfun add- in that actually allows you to write and run JavaScript code directly in Excel. And of course, your JavaScript code also has access to data stored in a spreadsheet. Here is a screenshot of how it looks in Excel 2016.

In the middle of the interface, you have a section in which you can write JavaScript, CSS and HTML code. It looks like a playground built into Excel. But Funfun also has an online editor where you can test your code. You can see it in fig. Below. I also posted an example link in the first picture so you can play.

https://www.funfun.io/1/#/edit/5a4e0d461010eb73fe125c4e

What is especially important in the online editor Funfun is that it contains a "table", like Excel. Although you cannot format at all, you can copy your data into cells and check your code directly.

To use the data stored in the spreadsheet, all you have to do is write some configuration in the short.io Funfun file to tell JavaScript about which area in the spreadsheet contains your data. For example, in the example above, all you need to write is

 { "data": "=A2:B9" } 

And the JavaScript code uses an object called $ internal to read data. Therefore, to read the data stored in A2: B9, you need to write

 var data = $internal.data; 

And everything is done. You can go to the Funfu documentation if you want to know more.

If you are satisfied with the result achieved in the online editor, you can easily download the result in Excel using the above URL. First you need to insert the Funfun add-in from Insert - My Add -Ins. Here are some screenshots showing how you could do this.

Disclosure: I'm a Funfun Developer

Answer 4

Try Excel-DNA for the program interface for Excel. Two other good tools are xlwings and Excel-REPL , which support the Python and Clojure interfaces, respectively.