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?

Whatever you do, start thinking about the circular reference problem *early*... — dmckee --- ex-moderator kitten, Aug 05 '09 at 19:14
And when/if someone mentions writing a compiler/interpreter for the language you're building, search first: your compiler/interpreter question has already been answered on SO. — dmckee --- ex-moderator kitten, Aug 05 '09 at 19:19
I'm not sure I even understand what you're asking here. You need columns and cells of numbers, and you need to allow users to calculate them? That could be done in, well, pretty much anything. How many users? If one user enters numbers, do the other users need to see it immediately? It sounds like what you're looking for could be done equally well using either J2EE or .NET, but that's based on my very incomplete understanding of your requirements. If you focus your question a little more tightly, I think you'll get better answers. — rtperson, Aug 06 '09 at 00:49

score 2 · Answer 1 · answered Aug 07 '09 at 21:57

2

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.

answered Aug 07 '09 at 21:57

subb

1,578
1
15
27

score 1 · Answer 2 · answered Aug 05 '09 at 19:21

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.

answered Aug 05 '09 at 19:21

Matthew Vines

27,253
7
76
97

At this point, it is more of "I need to do it". Although learning "how is it done" is always good. – Kdigital Aug 05 '09 at 19:42
This is very interesting as it allows a spreadsheet to be the calculation engine of your application – Kdigital Aug 05 '09 at 19:47

score 1 · Answer 3 · answered Aug 05 '09 at 19:30

1

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.

answered Aug 05 '09 at 19:30

Cheeso

189,189
101
473
713

Unfortunately, unless you build it all from scratch, you're going to be "married" to a vendor. What sorts of vendors are you willing to consider? What about open source projects? – Zian Choy Aug 05 '09 at 21:57

score 1 · Answer 4 · edited Apr 05 '19 at 01:59

Resolver One is a Spreadsheet app made in IronPython.

There is an explanation of the overall mechanic for the calculation [pythonology.org] it uses for user generated ecuations.

The relevant image showing Resolver One's overall algorithm.

Should note that users can write python code to be interpreted both on the cells and a special 'outside of sheet' place.

Look at another question here in SO, from where I reused my answer.

score 0 · Answer 5 · answered Aug 07 '09 at 21:32

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:

Create an empty Excel file object
Add cells (with either data or formulas) to the "Excel file"
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.

score 0 · Answer 6 · answered Aug 08 '09 at 07:17

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

score 0 · Answer 7 · answered Aug 17 '09 at 16:10

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

score -1 · Answer 8 · answered Aug 07 '09 at 22:59

-1

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

answered Aug 07 '09 at 22:59

kikito

51,734
32
149
189

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

8 Answers8