What are good methods to perform spreadsheet-like calculations in a programming language?

Question

What's the best way to do spreadsheet-like calculations in a programming language? Example: A multi-user application needs to be available over the web that crunches columns and cells of numbers like a spread-sheet based on user submission. What are the best data structures/ database models/patterns to handle this type of work so that handling the different columns are done efficiently and easily in php, java, or even .Net. Is it better to use data structures within the language, or is it better to use a database? If using a database is the way, how does one go about doing this?

Answer 1

Aspose.Cells (formerly Aspose.Excel.Web) is a good way to get the functionality you are looking for.

Unless you are asking more for a "How is it done?" than "I need to do it." Then I would look at the other answers given.

Answer 2

Along the lines of "I need to do it"
Microsoft has Excel Services which does just what you want. Spreadsheet operations on the server. It is available via a web services interface, so you can connect and drive calculations from Java, PHP, .NET, whatever.

Excel Services is part of Sharepoint 2007.

Answer 3

I can't tell you how to do it. But I would recommend you to look at the code of PHPExcel. PHPExcel is a library that allows you to create Excel files within PHP.

The workflow of PHPExcel is simplified like this:

1. Create an empty Excel file object
2. Add cells (with either data or formulas) to the "Excel file"
3. Call the create function which is generating the file itself

In your case you would have to replace 3. with something like "Create web interface".

Therefore I would recommend you to look at the code of this open source project and look how the general structure is. This should help you solving your problem.

Answer 4

To do the actual calculation, look at graph theory. Basically you want to represent each cell as a node in a graph and each dependency as a directed edge. Next, do a topological sort to calculate the value of each cell in the right order.

Answer 5

I must point out that google spreadsheets already does this kind of stuff.

Answer 6

I once used a binary tree to store the output of parsing a string using BODMAS. Each node was an operation between two other nodes, which could be a number, a variable or another operation.

So y = x * x + 2

became:

          +
      *       2
   x     x

Sadly this was at school in Pascal and is stored on a 5 1/4" disk, so you don't want it :)

Answer 7

SpreadsheetGear for .NET will let you load Excel workbooks, plug in values, calculate and then get the results.

You can see a few simple ASP.NET calculation samples here, other ASP.NET samples here and download a free trial here.

Disclaimer: I own SpreadsheetGear LLC