Spreadsheet::Perl (was New User)
Hi David, Thank you for your answer.
> -Original Message-
> From: David Manura
> Nadim,
> This module looks neat. The way speadsheets automatically
> update data on dependencies, like a continually running makefile,
> seem to be their main benefit, but they do have some limitations
> since the data in a problem must be flattened onto a two-dimensional
> grid. Being a programmer, I feel that the structure, size, and type of
the
> data gets lost. For example, if cell C5 contains the average of cells
> B5:B10, and then you append a new value to cell B11, you have to correct
> the formula in C5 to cover the new range B5:B11. Software like Mathcad
> offers the best of both worlds by preserving the concept of data
> structures while also having the spreadsheet-like data-update
dependencies.
> That makes me wonder whether something similar could be done in Perl,
> and if so how useful it would be.
About your concerns,
> For example, if cell C5 contains the average of cells
> B5:B10, and then you append a new value to cell B11,
> you have to correct the formula in C5 to cover the new
> range B5:B11. Software like Mathcad
I don't know how MathCAD handles the problem you described (at the user
input level) so if you could flesh it up a bit, I'll try to see what is
possible. There is a way of doing something similar in S:P. If you name your
input range, you just need to update that lookup.
$ss->SetRangeName
(
'input_to_sum' => 'B1:B10'
, 'addresses' => ...
, 'ss_numbers' => ...
) ;
# as of 0.03, range and cell naming use different functions but I'll change
that tonight.
$ss{A1} = Formula($ss->Sum('input_to_sum') ;
I agree that a way of describing a range without naming one of the limits
should exist, I just don't know how to make it visually clear. What about
'B1:B*' 'B*:B10' 'B*:B*' ?
> ... but they do have some limitations since the data in a
> problem must be flattened onto a two-dimensional grid. Being a
> programmer, I feel that the structure, size, and type of the
> data gets lost.
I see this as a different problem and I do agree with you that we shouldn't
have to flatten and reconstruct structures.
SP has 'Fetch Functions'.
my $structure = ... something very complicated ...
$ss{A9} = NoCache() ; # don't use the cached value, call fetch function at
each access
$ss{A9} = FetchFunction(sub{$structure->{...}[...]{...}}) ;
There is a similar way to store data, this lets us manipulate data in place.
I can think about a neater syntax:
$ss{A9} = FetchFunction(sub->{$structure{...}[...]{...}}) ;
vs.
$ss{A9} = NoCache() ;
$ss{A9} = Ref($structure->{...}[...]{...}) ;
or
$ss{A9} = RefNoCache($structure->{...}[...]{...}) ;
You can still have a formula attached to the cell.
This takes me to another problem (that I thought I had decided over). The
formulas are executed in the Spreadsheet package not the callers. This is
IMO the neatest but we have to go through loops to get the data in to the
spreadsheet. Here is an example (all this is in working condition and open
to suggestions)
# as of 0.03
my $data ;
$ss{A1} = Formula($ss->Sum('$data') ; # error
my $data ;
sub MySub{}
$ss{A1} = Formula($ss->Sum('MySub()') ; # error
1/ It possible to use "Ref" and "DefineFunction" to make it work as intended
2/ It possible to make these two example work right away but I think it
opens doors to nasty debugging sessions
Please keep coming with your suggestions, the module is still small enough
to allow for drastic changes :-)
Cheers, Nadim.
PS. I hope the mail looks "normal" on your side. I still have problems
making "Outbreak (tm)" work as I want it it to.
Re: Spreadsheet::Perl (was New User)
khemir nadim wrote:
I agree that a way of describing a range without naming one of the limits
should exist, I just don't know how to make it visually clear. What about
'B1:B*' 'B*:B10' 'B*:B*' ?
'*' might be ambiguous. Does it mean infinity? Does it mean the last
defined value in the column? The latter seems more useful, so if you
state B1:B* and the only defined cells in the B column are B1:B10, then
you could infer B1:B* to mean B1:B10. What then if the only defined
values in the B column are B1:B10 and B20:B30? Would B5:B* mean B5:B10
and B25:B* mean B25:B30? I don't know if Excel supports such
functionality (I've looked before.)
One thing I noticed is that the OO interface can be long-winded:
$ss{A3} = Spreadsheet::Perl::Formula('$ss->AddOne("A5") + $ss{A5}') ;
With some reimplementation work and some string parsing, this might be
simplified to
$ss{A3} = '=AddOne(A5) + A5';
at the expense of some generality. Of course, you then have to be
careful when you want to literally store the string '=AddOne(A5) + A5'
rather than the formula 'AddOne(A5) + A5'. Maybe,
$ss->set_formula('A3', 'AddOne(A5) + A5');
Just a few ideas. (I also just noticed the method names are the of the
style GetFormulaText rather than of the more Perlish get_formula_text.)
For a module with similar functionality but which does not flatten the
problem onto a 2D grid, the psuedo-code for its usage might look like
my $total = $subtotal * 1.05 + 5;
my $subtotal = &sum(@v) + 100;
my @v = (1..8);
print $total; # prints (&sum(1..8) + 100) * 1.05 + 5
push @v, 9;
print $total; # prints (&sum(1..9) + 100) * 1.05 + 5
Of course, there's a variety of ways that might achive a workable syntax
for that--tied variables, OO, source filtering, tricks with operator
overloading, callback functions, or writing a full parser/interpreter.
-davidm
Re: Spreadsheet::Perl (was New User)
"David Manura" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> khemir nadim wrote:
>
> > I agree that a way of describing a range without naming one of the
limits
> > should exist, I just don't know how to make it visually clear. What
about
> > 'B1:B*' 'B*:B10' 'B*:B*' ?
>
> '*' might be ambiguous. Does it mean infinity? Does it mean the last
> defined value in the column? The latter seems more useful, so if you
> state B1:B* and the only defined cells in the B column are B1:B10, then
> you could infer B1:B* to mean B1:B10. What then if the only defined
> values in the B column are B1:B10 and B20:B30? Would B5:B* mean B5:B10
> and B25:B* mean B25:B30? I don't know if Excel supports such
> functionality (I've looked before.)
Excel doe support this but it has meaning in the context of a formula only
while we also have the assignement context.
I completely agree with you. I have implemented this but I don't "release"
it since I don't know the semantics myself ( '*' is A or 1 as of today :-)
Your guess is as good as mine. I can even give another example, what do
$ss{'*'} or $ss{B*} mean? In the TODO there is the question about
cell/row/spreadsheet default value. We may need diffrent syntaxes for 'last'
and 'infinity'. And need to know if we need infinity and what we mean with
it.
> One thing I noticed is that the OO interface can be long-winded:
>$ss{A3} = Spreadsheet::Perl::Formula('$ss->AddOne("A5") + $ss{A5}') ;
This is patialy fixed.
$ss{A3} = Formula('$ss->AddOne("A5") + $ss{A5}') ;
I don't know if I have done that yet the following might be a good thing
$ss->Formula
(
A1 => '',
A2 => '',
A3 => '',
A4 => '',
) ;
> With some reimplementation work and some string parsing, this might be
> simplified to
>$ss{A3} = '=AddOne(A5) + A5';
No, No and Yes. No because I want SS::P to be perl and the formula should
be valid perl. No because I want to be able to store the string '=AddOne(A5)
+ A5' as a value and keep general. Yes, because many people may want to do
this. If someone is whiling to write a lexer/sub_builder (or a converter)
for those formulas, I can think about this syntax:
$ss{A3} = XXX('=AddOne(A5) + A5') ; #name may vary
> Just a few ideas. (I also just noticed the method names are the of the
> style GetFormulaText rather than of the more Perlish get_formula_text.)
Thats the man's coding style. I don't want to be dissolved into the age old,
ugly and unreadable KR style. You can always define an alias module.
> For a module with similar functionality but which does not flatten the
> problem onto a 2D grid, the psuedo-code for its usage might look like
I'll look at that separately
There is a new release in the pipeline (0.04); somewhere around sunday night
but you can get a beta if you'd like to.
I have partially fixed the mapping to spreadsheet, and back, problem:
$ss{A1} = Ref(\$my_variable) ;
or
$ss->Ref(A1 => \$x, B5 => \($struct->{xxx}), 'A2:A3' => ..) ;
$ss{A1} = 25 ; # set the scalar
We can now:
- Read the spreadsheet formulas
- Map our structures
- Recalculate()
- Go on with the perl structures
Changing the scalar directly doesn't change anything in the spreadsheet.
That would be a cool trick to have this work but I can't say that it's an
easy programming model but it seriously cool and difficult to debug.
The usual fixes and the documentation are going forward too.
I am looking at defining a standard (that's one function name) for database
connectivity (using DBI as interface).
I also have some discussion with another guy (I'll ask him to join here or
I'll CC) who wants to use SS::P for a web
based application.
I also think that the question of the format of the files generated by SS:P
as well as a "real" read and write are going to get important soon. IMO the
canonical format is perl, like in the setup example (in the tests
directory).
Thanks a lot for your input, Nadim.
RE: Spreadsheet::Perl (was New User)
Title: RE: Spreadsheet::Perl (was New User) > > What about 'B1:B*' 'B*:B10' 'B*:B*' ? and what about 'A1:*10' or 'A*:**' or > > '*' might be ambiguous. Does it mean infinity? Does it mean the last > defined value in the column? I would say this is correct. Basically like pressing ctrl-cursor in excel on a given row or column. > I don't know if Excel supports such functionality (I've looked before.) In VBA it does for sure. > (I also just noticed the method names are the of the > style GetFormulaText rather than of the more Perlish > get_formula_text.) Which might be useful for VB types, but probably not real exciting for Perl types (for whom this is intended I would have thought). > Of course, there's a variety of ways that might achive a > workable syntax > for that--tied variables, OO, source filtering, tricks with operator > overloading, callback functions, or writing a full parser/interpreter. OO and operator overloading sounds to me the way to go. But im just kibitzing while eating a schoko-croissant. :-) Yves
Re: Spreadsheet::Perl (was New User)
khemir nadim wrote: I also have some discussion with another guy (I'll ask him to join here or I'll CC) who wants to use SS::P for a web based application. Why don't you create a project for SS::P at: http://developer.berlios.de Then we can discuss things on the project mailing list
Re: Spreadsheet::Perl (was New User)
Terrence Brannon wrote: >> > Why don't you create a project for SS::P at: > > http://developer.berlios.de > > Then we can discuss things on the project mailing list Hi, thank you for your idea but I don't think I will (for the time being) for the following reasons: - So far nobody has proposed to help, even if I have been asking for it - I have my own mail server and can setup a list there - I can hardly call SS::P a project, it's a little hobby so far - Berlios.de has 4 perl projects (search perl => 4 hits) - the total mail amount I got about this module is around 6 or 7 Now what I would realy have like is to see people come forward with ideas (like you Terrence) and Another thing that I would like is to see a site devoted to host perl development. version 0.04 will hopefuly have a complete documentation so people can start using this stuff. After version 0.05, if there is interrest (that is people pussing for it) I will make SS::P a project somewhere or at least provide a mailing list. Cheers, Nadim.
Re: Spreadsheet::Perl (was New User)
Version 0.04 is out. Check out the documentation at: http://search.cpan.org/src/NKH/Spreadsheet-Perl-0.04/README http://search.cpan.org/~nkh/Spreadsheet-Perl-0.04/Perl.pm I still need your input and I still need some help if any brave soul is available. Nadim.
