Published on November 29, 2022.

A few weeks ago, I used Xojo to develop a custom macOS / Windows desktop application for a client. The app generates price lists using somewhat complex business logic. I reached a point where I needed to export the price lists as Excel files.

In the past, I've developed Web applications that also needed to generate Excel files. For those projects I used the popular SheetJS JavaScript library, and had a lot of success with it.

That got me thinking: I could utilize SheetJS by using a Xojo DesktopHTMLViewer control and its ExecuteJavaScriptSync method.

In this post, I'll show how you can use popular JavaScript libraries in Xojo desktop projects. I'll also share two classes - JSEngine (a subclass of Xojo's DesktopHTMLViewer control that makes it easy to work with external JavaScript libraries) and ExcelEngine (a subclass of JSEngine that makes it easy to work with SheetJS).

Here's a link to a zip file that contains two Xojo project files: https://tdietrich-opensource.s3.amazonaws.com/Xojo-JavaScript-Libraries-20221130-01.zip The projects include the classes and code that I'm about to discuss.

JSEngine

To get started, I developed a subclass of the DesktopHTMLViewer control called "JSEngine." My goal with JSEngine was to make it easy to load JavaScript libraries.

JSEngine has a "Libraries" property, which is an array of strings. Each element of the array represents a remote JavaScript library's URL. (For example, the URL to the SheetJS library is: https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js) To load a JavaScript library, you add an element to the array, and do so in the Opening event handler for the class instance.

The Libraries property is an array, so you can use it to load multiple libraries. When an instance of the JSEngine class is opened, it automatically loads a simple HTML page that includes references to the remote libraries (via "script" tags in the document's head). At that point, you can use JSEngine's ExecuteJavaScriptSync method to run JavaScript code and get back the results. And most importantly, you can utilize the functionality of any remote libraries that you've loaded.

To test JSEngine, I created an app with a simple interface, consisting of an instance of the JSEngine class, two text areas (one used to enter adhoc JavaScript code, and the other to display the result), a button to execute the code (using the ExecuteJavaScriptSync method), and a button that resets the engine (refreshes the control and reloads any of the libraries). I position the JSEngine control so that it is offscreen (with a width and height of 50 pixels, and with a left position of -100).

The app that I developed can be found in the zip file. It's the "JSEngine" project.

Here's what the app's window looks like in the Xojo IDE.

Click the image to view a larger version.

The Opening event for the instance of JSEngine adds two libraries to the engine. It looks like this:

Me.Libraries.Add( "https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.29.4/moment.min.js" )
Me.Libraries.Add( "https://cdn.jsdelivr.net/npm/lodash@4.17.11/lodash.min.js" )

In this example, I'm loading Moment (a popular library that can be used to parse, validate, manipulate, and display dates and times) and Lodash (which makes it easy to work with arrays, numbers, objects, and strings).

The Pressed event for the button looks like this:

TextArea2.Text = JSEngine1.ExecuteJavaScriptSync( TextArea1.Text )

And that's all there is to it.

Here's a short animation showing how the app works. The JavaScript being run first uses functionality provided by Moment, and then by Lodash.

Click the image to view a larger version.

Using SheetJS

I mentioned SheetJS earlier. It's available in two versions. The "Community Edition" is an open source version, while the "Professional Edition" is a paid version that provides some additional functionality (as well as priority support). You can learn more about the Professional Edition here: https://sheetjs.com/pro (In the examples that follow, I'm using the Community Edition.)

To use SheetJS, in the Opening event for the instance of JSEngine, add:

Me.Libraries.Add( "https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js" )

Here's an example of JavaScript that uses SheetJS to create a simple spreadsheet with a single workbook.

try {

	var workbook = XLSX.utils.book_new();

	var data = [
		["Jane", "Doe", "janedoe@test.com"],
		["John", "Doe", "johndoe@test.com"],
		["William", "Smith", "wsmith@example.com"]
	];
	
	worksheet = XLSX.utils.aoa_to_sheet(data);
	workbook.SheetNames.push("People");
	workbook.Sheets["People"] = worksheet;	

	XLSX.write(workbook, { bookType: 'xlsx', type: 'base64' } );

} catch(e) {
	e.message;
}

The value being returned is a Base64-encoded string that represents an XLSX file.

Here's another example of JavaScript that creates a spreadsheet with two workbooks.

try {

	var workbook = XLSX.utils.book_new();

	var data = [
		["Jane", "Doe", "janedoe@test.com"],
		["John", "Doe", "johndoe@test.com"],
		["William", "Smith", "wsmith@example.com"]
	];
	
	worksheet = XLSX.utils.aoa_to_sheet(data);
	workbook.SheetNames.push("People");
	workbook.Sheets["People"] = worksheet;	

	data = [
		["Northern", 120456.78],
		["Eastern", 100552.62],
		["Southern", 99256.22],
		["Western", 242356.34]
	];
	
	worksheet = XLSX.utils.aoa_to_sheet(data);
	workbook.SheetNames.push("Sales");
	workbook.Sheets["Sales"] = worksheet;	

	XLSX.write(workbook, { bookType: 'xlsx', type: 'base64' } );

} catch(e) {
	e.message;
}

To save the resulting XLSX file to disk, in Xojo, you would do something like this:

Var speadsheet As String = JSEngine1.ExecuteJavaScriptSync( TextArea1.Text )
Dim file As FolderItem = SpecialFolder.Desktop.Child( "sheetsjs-demo.xlsx" )
Var fileStream As TextOutputStream
fileStream = TextOutputStream.Create( file )
fileStream.Write( DecodeBase64( speadsheet ) )
fileStream.Close

In the example above, I'm using the UI to specify the JavaScript to be run, and to display the result. To test the code above, where an XLSX file is being written to disk, I added the code to the Pressed event handler of the button.

Of course, you could use this same approach to programmatically generate the JavaScript, run it, and then save the file.

ExcelEngine

To make is easier to use SheetJS, I created an ExcelEngine class, which is a subclass of JSEngine. ExcelEngine automatically loads the SheetJS library, and provides methods to help create the data needed by SheetJS to create workbooks.

You can find the ExcelEngine class, and the code that I'm about to discuss, in the Xojo project file named ExcelEngine.

To use ExcelEngine, you'd add it to a window, just as you'd do with JSEngine. You can then use the class's methods to create sheets, add rows to sheets, delete rows, update rows, and so on.

In the demo app, Window1's "Button1" control includes the following Pressed event handler. It's a good example of how you can use ExcelEngine's method to create a spreadsheet.

// Add a sheet.
ExcelEngine1.AddSheet("Sheet 1")

// Add rows.
ExcelEngine1.AddRow(0, array("A1", "B1", "C1"))
ExcelEngine1.AddRow(0, array("A2", "B2", "C2"))
ExcelEngine1.AddRow(0, array("A3", "B3", "C3"))
ExcelEngine1.AddRow(0, array("A4", "B4"))

// Add a row at a specific row number.
ExcelEngine1.AddRow(0, array("A3.1", "B3.1", "C3.1", "Inserted Row"), 3)

// Replace an existing row.
ExcelEngine1.ReplaceRow(0, 1, array("A2X", "B2X", "C2X", "Updated Row"))

// Another technique for adding a row:
Var Row() As Variant
Row.Add("X1")
Row.Add("X2")
Row.Add(123456.78)
ExcelEngine1.AddRow(0, Row)

// Delete a row.
'ExcelEngine1.DeleteRow(0, 1)

// Delete all rows.
'ExcelEngine1.DeleteAllRows(0)

// Add a second sheet.
ExcelEngine1.AddSheet("Sheet 2")
ExcelEngine1.AddRow(1, array("Region", "Sales"))
ExcelEngine1.AddRow(1, array("North", 123456.78))
ExcelEngine1.AddRow(1, array("South", 786543.21))
ExcelEngine1.AddRow(1, array("East", 1122334.56))
ExcelEngine1.AddRow(1, array("South", 554433.21))

// Get a "pretty" JSON representation of the data.
TextArea1.Text = ExcelEngine1.GetJSON

// Generate the spreadsheet and display the Base64-encoded result.
// For "BookType" options, see: https://docs.sheetjs.com/docs/api/write-options
// The default is xlsx ("Excel 2007+ XML Format").
ExcelEngine1.GenerateSpeadsheet
TextArea2.Text = ExcelEngine1.Speadsheet

// Save the spreadsheet to the desktop.
Dim File As FolderItem = SpecialFolder.Desktop.Child( "SheetsJS-Demo.xlsx" )
ExcelEngine1.SaveSpeadsheet(File)

Var MD As New MessageDialog
MD.Show("The spreadsheet has been saved to your desktop.")

Notice that ExcelEngine's GenerateSpeadsheet method generates a Base64-encoded representation of an Excel file. If you wanted to, you could use that value to transfer the file to an FTP server, save it to an S3 bucket, and so on. In other words, you don't have to save the file to disk.

But if you do want to save the file to disk, you can call ExcelEngine's SaveSpeadsheet. Just pass it a FolderItem that represents the file to be saved. In the example above, I'm saving a file named "SheetsJS-Demo.xlsx" to the desktop.

Here's an animation showing the ExcelEngine app.

Click the image to view a larger version.

Wrapping Up

Being able to use JavaScript libraries has changed the way that I develop Xojo desktop apps. I hope you find the techniques and code that I've shared in this post to be helpful.