Use Excel with Javascript

[Guillaume]

Hello,

I need to write a script in Javascript that creates and saves an Excel file but I'm getting errors. SpiderMonkey Mozilla site only has the following page and the info is not relevant + marked as obsolete: https://developer.mozilla.org/en-US/docs/Archive/Web/JavaScript/Microsoft_Extensions/ActiveXObject

The following leads to a "Class not saved" Exception
var oExcel = new COMObject("Excel.Application");

the following works ok:
var oExcel = new COMObject("Excel.Application",true);

I can see a Microsoft Excel process created in the task manager

The following generates an error : oWorkbook.sheets is undefined
var oWorkbook = oExcel.Workbooks.Add;
var oWorksheet = oWorkbook.Sheets.Add("",1);

Is there an example to create an Excel file and save it?

Thanks,
Guillaume

[Geert Bellekens]

I have a number of examples in vbscript: https://github.com/GeertBellekens/Enterprise-Architect-VBScript-Library/search?q=excel&unscoped_q=excel
In theory they should translate quite easily to Javascript.

Geert

[Guillaume]

Hi Geert,

Thank you for the link. Your Github project is really useful for VBScripts.
Unfortunately I'm struggling in the initial steps to initialize my Excel workbook.

Once "var oWorkbook = oExcel.Workbooks.Add(true);" is run, if I try to manipulate oWorkbook, it says that it's undefined.

Hence the need to have a simple example that creates an Excel file using a Javascript EA script.

Thanks,
Guillaume

[Geert Bellekens]

I can't really help you with that. Not too much examples to be found on the web for javascript.

I did find a page suggesting that you might need to use method CreateWorkbook(), but I'm not sure if that is relevant at all

https://docs.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-workbooks

Geert

[Guillaume]

I'm hoping to get some info from Sparx on getting this to work.

[Uffe]

Are you positive that the same script works when run outside EA?

I'm not too sure about that Workbooks.Add() there.

/Uffe

[Guillaume]

Hi,

The JS code I shared doesn't work but translating existing /working JScript or VBScript code (based on Geert's examples for instance) to similar calls in Javascript doesn't work either.
Hence I'm looking for a functional set of code to open an Excel file, read and write its content, and save the file.

One of Javascript advantages is its class support. I managed to get a Logger class to work and that's great. As an Excel process is started by creating an Excel.Application COMObject, I suppose this is supported. I don't have any feedback from Sparx support yet. Since I understand SpiderMonkey/Javascript is recommended by SparxSystems, functional samples to quickly move from VB of JScript should be available.

Thanks
Guillaume