How to implement a spreadsheet?

[Shawn Pearce]

The definition of the function is actually done by (roughly) creating
a hidden function within the module which implements the code, and
then calling it with a tuple.  Its roughly as if you had written:

calculate_cell(Data, Context, Formula) ->
	DataFunction = {?MODULE, calculate_cell, {Data, Context}},
	...
	% to call:
	{M,F,{A,B}} = DataFunction,
	M:F(A, B, X, Y)

calculate_cell_0(Data, Context, X, Y) ->
	lookup_data(Data, Context, X, Y).

But a lot faster and makes a heck of a lot more sense.  So you will
get one copy of the code, and when you actually bind DataFunction
to the fun, you actually get a tiny little data value which contains
references to the code, Data and Context.  Therefore if you made one
million funs, you would still have just one copy of Data and just one
copy of Context.

However, if you send the fun in a message to another process, the
entire set of data in Data and Context will get copied to during transit,
so you don't want to put a huge amount of data in there if you are
sending it around often.

I'd suggest you look at ets if you want to implement a spreadsheet.  It
may work better for mutable term storage when dealing with huge amounts
of data.  Its a mutable data structure and its global, so multiple
processes can access it at once without having to copy the data back
and forth all of the time.

Your little example already indicates one reason you may need to do
the parser yourself.  Erlang can't do:

	157> 2^4.
	** 1: syntax error before: '^' **

So you'll have to at least parse that expression into:

	157> math:pow(2,4).
	16.0000

which may be easy, but then you might want to make cell references
like Excel and other popular spreadsheets do:

	158> D18 * D12 - D15.
	** 1: variable 'D18' is unbound **

So of course this requires you to scan the string, or bind every cell
into a variable in the interpreter...  ugly.

As a matter of style, don't name functions with uppercase names in
your modules - they look like variable names, and it looks out of
place relative to everything else.  (Not to mention that erlc will
choke on it with an error message.)

As for importing into your local module, look at the -import attribute.
I think it's something like:

	-module(foo).
	-import(math, [sin/2, pow/2]).

but I don't use it in my code.  :-)


Cedric Shock <cedricpublic@REDACTED> wrote:
> Hello,
> 
> Short version:
> When a bound variable is used in the definition of a new function via 
> fun() is the value of the variable copied into the function definition, 
> or is a reference used?
> 
> Is there a short way to import a function from another module into yours 
> so you can use it without the module: prefix?
> 
> Long version:
> 
> I would like to implement something like a spreadsheet in erlang. The 
> ultimate goal in a project like this is to provide the simplest, most 
> general, possible interface to the user. I have decided that the best 
> possible solution would be if the formulas allowable for the user were 
> plain erlang code. This would allow a user to do amazing things like 
> make a formula that generates a function and stores it in a cell, and at 
> a later timeframe a formula that applies a stored function to other data 
> or functions. In addition it would remove other possible burdens like 
> writing a parser for things like addition and subtraction or making a 
> generator for functions that will work in formulas.
> 
> So, the problem breaks down like this: We have some data and a context, 
> and a formula from the user for the value of one cell of data in the 
> next time frame. Somehow we need to slip the data and context into the 
> user's formula without them seeing it. A nice formula would be something 
> like:
> 
> "sqrt(Data(1, 1)^2 + Data(1, 2)^2)"
> 
> Here's the idea for how to do this. Define a function which takes as its 
> arguments the position in the spreadsheet, and returns the value at that 
> location. Then assign that function to the variable Data bound inside 
> the evaluator. Then we will evaluate the function Formula in that 
> context. It would look something like this:
> 
> CalculateCell(Data, Context, Formula) ->
> 	DataFunction = fun(X, Y) -> LookupData(Data, Context, X, Y) end,
> 	...
> 	Bindings = erl_eval:add_binding('Data', DataFunction, ...),
> 	...
> 	eval(Formula, Bindings)
> 	...
> 
> So here's the question. Erlang does not have destructive assignment, so 
> a variable like Data (which is going to be huge) can be safely passed 
> around all over the place almost entirely by reference; I assume Erlang 
> takes full advantage of an optimization like this. However, I do not 
> know if it will do the same thing in my definition of a function. Will 
> the function definition be in terms of references to Data and Context 
> that would only get de-referenced when serialized, or is the entirety of 
> Data copied into the definition of the function?
> 
> Another issue is that it would be very desirable to have the math 
> functions available when the formula is evaluated. What module does an 
> evaluation happen in? I assume it happens in the module of the code that 
> called it. It would be desirable for things like math:sin to be usable 
> in a formula just through calling sin. Is there a shorter way to import 
> them into your module than:
> 
> sin(Theta) ->
> 	math:sin(Theta).
> 
> cos(Theta) ->
> 	math:cos(Theta).
> 
> etc...?
> 
> Thanks in advance for your time and advice,
> 
> Cedric A. Shock
> 
> 
> 

-- 
Shawn.

  Q:	How do you play religious roulette?
  A:	You stand around in a circle and blaspheme and see who gets
  	struck by lightning first.