26

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.)

Improve this question
18
  • 22
    You will need Windows COM support for node.js. However, you should read this SO post, what you are trying has a high risk to become a pretty unstable solution which won't scale well.
    – Doc Brown
    Commented Jan 24, 2022 at 12:21
  • 40
    What is the fundamental reason that you don't want to move the calculation out of Excel? If the problem is the work involved in doing so, then be prepared for far more work getting the web interface to work properly and maintaining that approach over the long term.
    – Steve
    Commented Jan 24, 2022 at 13:41
  • 4
    Thank you very much for the engaging discussion so far. I have two more reasons why I would like to do this besides saving our invest in developing the excel logic. 1. I would like the business department to develop templates, so they can change the business logic in future without having to go through an IT change process. 2. Our calculations produce a bill, and I would like to hand over an excel with all the logic over to the customer, so in doubt he can follow the complete logic. Feel free to comment on these additional points. Thank you!
    – Martin Böschen
    Commented Jan 24, 2022 at 16:15
  • 12
    @MartinBöschen, why then are you thinking a web-based front end is desirable? It won't be possible for the departmental users to alter the Excel template without potentially breaking the web front end which interacts with it. And if you're still handing the Excel file out freely, then why have the complications of a separate non-Excel front end?
    – Steve
    Commented Jan 24, 2022 at 19:05
  • 4
    You might just want to use google sheets. You could cut and paste into it and there's a lot of solutions for interacting with them.
    – Mark Rogers
    Commented Jan 24, 2022 at 22:06

6 Answers 6

Reset to default
10

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.

Improve this answer
67

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).

Improve this answer
13
  • 3
    Indeed. Note that with your approach, it might still be a good idea to somehow be able to call Excel from a script. E.g. in Python, in order to automate tests, and compare the output of the new branch to the old spreadsheets.
    – Eric Duminil
    Commented Jan 24, 2022 at 19:24
  • 10
    I know way too many business that think excel is a good platform for software engineering. Every way you measure it, it's going to be cheaper in the long run to just re-write it in an actual programming language that can be maintained and managed properly.
    – Steve Butler
    Commented Jan 24, 2022 at 22:14
  • 8
    I did something similar (essentially, re-purposed a desktop application as a rendering engine for a custom file format), and you're missing what's probably the biggest downside: performance. I estimate that the desktop app is one to two orders of magnitude slower than a dedicated rendering engine would be, and I expect an Excel backend would have similar issues.
    – Mark
    Commented Jan 25, 2022 at 3:49
  • 8
    Is it not also the case that you would need an Excel licence for every user authorized to access the application?
    – grahamj42
    Commented Jan 25, 2022 at 7:43
  • 4
    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. Based on my experience with Excel automation over the decades, it's not "possible", it's guaranteed that updates will break stuff - the question is merely one of how long you've got until that happens
    – Chris H
    Commented Jan 26, 2022 at 15:51
23

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.

Improve this answer
1
  • 6
    This doesn't answer OPs question. Because his main concern is to use Excel as a Calculation Platform so he doesn't have to rewrite business logic. In this case, although useful, the mentioned platform only serves to manipulate the files, but doesn't get the calculations made by Excel.
    – fernando.reyes
    Commented Jan 25, 2022 at 21:55
7

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.

Improve this answer
4
  • This seems pretty good and actually answers the question! Formula support (absolute and relative references, names, 3D cell references, more than 260 supported functions). The OP never said he needed Excel to do the computations, just hand over the excel document and get the calculations back. This should fit like hand in glove. That being said, if you go this route, you need to have automated testing in place and make good use of Git so that you store all versions of the excel file, as it is a good chunk of the actual application and might break easily without the business dep knowing
    – oligofren
    Commented Jan 27, 2022 at 4:30
  • At 200 USD/developer for a year the price is an absolute steal too.
    – oligofren
    Commented Jan 27, 2022 at 4:36
  • Sounds interesting, maybe sufficient for the OP. However, I am pretty sure this solution has limits. For example, I guess it cannot evaluate Excel formulas using embedded UDFs (written in VBA), otherwise they must include the full VBA execution environment.
    – Doc Brown
    Commented Jan 27, 2022 at 6:41
  • 1
    @DocBrown Correct. Last time I used it there was no VBA support but all native Excel functions were implemented. Of course all solutions will have limitations but unless the OP engages my consultancy services, I'm not going to do a full analysis or speculate on whether they need VBA. :)
    – LoztInSpace
    Commented Jan 27, 2022 at 8:07
3

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.

Improve this answer
3
  • 2
    As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Commented Jan 25, 2022 at 12:38
  • 5
    What does the calculation? Some Excel server? Independent of Excel? Preferably, please respond by editing (changing) your answer, not here in comments (without "Edit:", "Update:", or similar - the answer should appear as if it was written today).
    – Peter Mortensen
    Commented Jan 25, 2022 at 16:11
  • 1
    Being able to access the cell values as a DB does not fix the issue of computation, e.g. find the result of a forumula, which was the whole point here. From its docs: Note that ExcelJS cannot process the formula to generate a result, it must be supplied. The answer mentioning SmartXLS is different in this regard as it actually computes.
    – oligofren
    Commented Jan 27, 2022 at 4:28
1

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.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.