How can I access Excel from my backend?

Question

We have a lot of business logic in Excel files and we would like them to integrate in a web application (a Node.js web application). We don't want to rebuild the logic in a programming language. Instead, we would like to insert data into the Excel files we have, and read the calculation results from the same Excel file back.

Note: "We do not use Excel as data source. We want use Excel for the actual calculations."

What would be an appropriate setup for that? Is this possible?

(Brainstorming ideas: virtual machine with Windows and Office installed, OneDrive, SharePoint, etc.)

Answer 1

Not the answer you were hoping for

While this may be possible somehow, it is likely a dead-end solution. You should seriously reconsider the decision to not want to rebuild the logic in a language that is better suited for server operation.

Running Excel as a backend processor would create a number of difficulties:

• You need to design some way of running multiple instances of Excel without interference between them, which means that you would need to copy the spreadsheet file for each instance and use that instance for only one session. A related problem is to tear down the Excel process once the related session isn't active anymore, which isn't easy to detect.
• You create a dependency on a runtime backend that is able to run Excel in the way your application expects. Since Excel is intended as an interactive desktop application, your use case probably isn't covered in Microsoft's future plans, and it is possible that with a newer Excel version you will be forced to either rebuild the integration, or keep your old version that does not get security updates anymore.
• Speaking of security, you're probably (not) aware of the security issues of using an application that isn't meant to be accessed by internet users. Web applications using SQL database backends have been riddled with SQL injection vulnerabilities, and unless the interface between your web server and the Excel-based calculation backend is either really restricted or very well-designed to be secure, you might be in for some unpleasant surprises.

If you do a serious cost/risk analysis, your Excel based solution idea will probably come out way behind a rewrite (which isn't easy or cheap, but given well formulated requirements, can be done using a straightforward and reliable software development process).

Answer 2

Microsoft has an official in-depth response to this question: Considerations for server-side Automation of Office

They provide no support for automating Excel directly in the backend, and they recommend that you use various programming libraries for accessing and manipulating Excel documents directly.

Most server-side Automation tasks involve document creation or editing. Office 2007 supports new Open XML file formats that let developers create, edit, read, and transform file content on the server side. These file formats use the System.IO.Package.IO namespace in the Microsoft .NET 3.x Framework to edit Office files without using the Office client applications themselves. This is the recommended and supported method for handling changes to Office files from a service.

The Open XML file formats are a public standard.

You may be able to find a library for evaluating Excel formulas that works in your programming language of choice, or you could implement a basic one that supports the subset of functions you need for your business logic.

Answer 3

This support doc, while warning against attempting this, and clearly stating that it's a completely unsupported scenario, nevertheless lays out a roadmap for what you'd need to do if you attempt it.

1. User Identity: You must run as a regular user with a full profile loaded.

2. Interactivity with the desktop: You must run in a desktop session, and you need access to the desktop session.

3. Reentrancy and scalability: You must serialize access to the Office application to avoid potential deadlocks or data corruption.

4. Resiliency and stability: You must plan on the Office apps crashing, throwing up popup windows or becoming unresponsive

5. Server-side security: It's just not very secure.

6. Licensing: All your end users must have Office licenses.

Answer 4

If you can call out to a .NET or Java library or process, I can recommend SmartXLS (I have no affiliation other than a satisfied customer circa 2018).

It provides a headless object model/library that has absolutely none of the runtime issues of Excel itself (e.g. pop up dialogs, COM etc.). Unlike some that just allow you to build files, this one actually performs the calculations etc. and lets you read back calculated results.

Some of the object model & methods are not exactly how I'd do it but the underlying engine is sound, fast and very flexible. The support is pretty good too.

Answer 5

You might be interested in ExcelJS. Using this Node.js library, you can load, modify, and save Excel files. So instead of invoking Excel you would load the spreadsheet into your Node backend and persist it in memory, and then update the cells you need.

We want use Excel for the actual calculations.

ExcelJS does not perform calculations itself. To perform the calculations you could use the library formula.js. You would need to read the formulas and cells from ExcelJS first. Depending on the complexity of your spreadsheet this may or may not be a viable solution.

Answer 6

Excel is not meant to be a database. And it's not meant to be an application that is interconnected.

Still you might be able to transform your companies way of digitizing processes in a multi-step process. The easy way out of your current situation might be converting Excel to Google Spreadsheets. You get an API for free, which involves proxies, request throttling and everything else you can imagine. Google even hosts the whole stack for you. It's basically a whole app stack including a database.

While I have no idea what the actual logic is, you can write and update source cells, while reading derived and calculated values from target cells.

Keep the data limit in mind:

Your dataset is 5 million cells or fewer.