Tux has asked for the wisdom of the Perl Monks concerning the following question:

I've just updated Spreadsheet::Read with two major new features

The addition of OO is just to make the other changes easier. The functional API did not change at all:

my $book = ReadData ("file.xls");
my $book = Spreadsheet::Read->new ("file.xls");

[download]

That makes adding data from other sources possible (and intuitive)

Spreadsheet::Read::add ($book, "file.csv");
$book->add ("file.csv");

[download]

that makes it clear that when you go beyond a simple read of one Spreadsheet, this new OO interface is to be preferred.

For types that do not store labels on sheets, like CSV and SquirrelCalc, one can now pass a label. (the default label for CSV is its filename).

$book->add ("file.csv", label => "Testing");

[download]

and my personal fav new OO is that you can now take a sheet as object and use that with methods

my $sheet = $book->sheet ("Test"); # or numbered
my $maxcol = $sheet->maxcol;
my $maxrow = $sheet->maxrow;
my $label  = $sheet->label;
$sheet->label ("New label");
my @row = $sheet->row (2);
my @col = $sheet->col (6);

[download]

When I presented this yesterday at the Amsterdam Perl Mongers meeting, it was received very well. The support for labels was added today after feedback during the short presentation.

What was also asked is if it would now be possible to do more complicated tasks like merging, moving, copying rows/columns/blocks between sheets, but there was no useful example for that YET. We can all wander off in our fantasies and think of idiotic data moves, but that would result is an unworkable API.

My quest here is if people think that new functionality is indeed useful, to come up with suggestions for the API, like

# suggestions
$sheet2->copy ("C5", $sheet1->block ("F6", "H20"));
$sheet->deleterow (12);
$sheet->deletecol ("D" .. "F");
$sheet->block ("A1", "D5", sub { ... });

[download]

Open to useful suggestions. No promise all of them will be implemented and if, then no guarantee it will be available next week :P

SHOOT!


Enjoy, Have FUN! H.Merijn

Replies are listed 'Best First'.
Re: Spreadsheet::Read - Changes and quest for feedback
by Laurent_R (Canon) on Dec 07, 2016 at 21:24 UTC
    Hi Merijn,

    adding sheets to an existing book seems a fairly good idea. I fully support the idea.

    On the OO interface, why not, but let's say that I a bit skeptical: I have the feeling that there are too many modules out there (in the CPAN, that is) that use an OO interface which just tends to give them a more difficult syntax (especially for beginners) and to make them more difficult to use, without any obvious benefit. We don't need to fall into the trap of Java and other languages which force you to superimpose an OO approach for things which could be made far simpler with a procedural or functional orientation. Please do not misunderstand me: I am not against OO programming, I am against it when it makes things more complicated than they need to be. But that's only my two cents of personal taste.

    Concerning the idea of merging, copying, moving or deleting rows, columns or blocks, I have the feeling that it is something that you want to do using a spreadsheet interactively, but that is more difficult to figure out use cases where this could really be useful in a program, it seems that most of these operations, when needed, can be done easily with simple loops. So, again, why not, but do you really want to make your API much more complicated for something that is probably not very much needed in real situations.

    Or perhaps it could go into a sub-module for people wanting to use that type of functionality, but without cluttering the current interface with bells and whistles that most people are not going to use.

    Sorry if I may sound not very enthusiastic, but I tend to balk at using modules whose documentation requires many thousands of lines. In short, IMHO, keep it simple. Or put it in a sub-module.

[reply]
Re: Spreadsheet::Read - Changes and quest for feedback
by FreeBeerReekingMonk (Deacon) on Dec 07, 2016 at 21:42 UTC
    Looks like some neat changes. Thanks for all the hard work.

    I would like left, right, up and down.

    so, 

    $sheet->cellright("A1") # B1
$sheet->celldown("A1") # A2
$sheet->cellright(0,0) # @{1,0}

    [download]

    This is because I always mess up the numbers (1,1) to the right, is that (1,2) or (2,1) ?

    Also not sure what would happen if we go a cell that does not exist, like "A0". Does it croak?

    Also not sure why the formatted/unformatted for (0,0) and "A1" is, It seems more logic to have a ->unformatted() to me, but I guess that is how the API is already made and unmutable.

    Edit: I just thought that this should also be possible: 

    $sheet->cellright("A1",3) # D1

    [download]
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom