Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"

Spreadsheet CGI

by jeffa (Bishop)
on Nov 10, 2001 at 22:04 UTC ( #124581=CUFP: print w/replies, xml ) Need Help??

DBIx::XHTML_Table Cookbook receipe #42

The Spreadsheet From Hell

(or more silly things you can do with this module)

Turns out that DBIx::XTHML_Table can be used to create a 'spreadsheet' application via CGI. The map_cell() method can be used to interpolate the current cell value inside an <input> tag - and that's the reason I added a callback mechanism in the first place, for others to find new and creative things to do with it. But, how do you name the individual names of each <input> tag? They have to be unique.

If you wanted to use a naming convention like a conventional spreadsheet (A1 - A9, B1 - B9, etc.) then you could try something like so:

my ($row,$col) = ('a',0); my $total_cols = $table->get_col_count(); $table->map_cell( sub { my $old = shift; $col++; my $new = qq|<input type="text" name="_$row$col" value="$old">|; $row++ and $col = 0 unless $col % $total_cols; return $new; });
After the form has been submitted, you can pick apart the rows from the cols by running each element from's param() method through a regex. The _ at the beginning of each name is used to seperate the 'spreadsheet' <input>'s from the other form elements. Not necessarily the best way, but it works.

At the time the idea of creating a spreadsheet out of my module was presented to me by Jim Cromie, I thought my above solution was pretty slick - for about 3 seconds. Then I realized that I don't have a corresponding B52 or A1 in my database, and hard coding some kind of mapping between database rows and columns to spreadsheet rows and columns is rather tedious and well, silly.

In order to put the proper information into the name, that is, in order to be able to know which row to update with what, I added two new methods that are meant to be used inside the map_cell() callback:

That should do it. Now each individual <input> tag can be identified by a primary key and a column name. But . . . . }:(

DBIx::XHTML_Table version 0.95 prints out every column you select, and displaying primary keys so prominently (and allowing the user to CHANGE them!!) just isn't good. Alas, time to add another method, but this new method can do one of two things:

  1. allow a user to say "hey! i know i said select these 5 columns, but i really only wanted to see 3 of them"
  2. allow the user to say "hey! this here column is really the primary key - don't display it, but do something special with it in the background instead"
Well, I chose the second option for better or worse. My reasoning is that all of this extra stuff is just for the new 'spreadsheet' feature (and any thing new to be concocted from the beauty of callbacks), and if the user doesn't want to see a column - then don't select it!

The new method is set_pk(), and you send it the name of your primary key column (the next version will accept no arg and default to 'id'). By doing so, you mark that column as the primary key and it is not displayed with the other columns. The cool part is that each row can be tagged with the primary key value, which means we can now know which database row our spreadsheet row corresponds with.

The result is the following script - replace <INSERT_TABLE_NAME> with the desired database table name, replace <INSERT_PK_NAME> and supply the proper DB connection credentials and it should work without a hitch. Don't forget the 'shebang' line if you are on *nix.

Receipe #42 Code

use CGI qw(:standard); use CGI::Carp qw(fatalsToBrowser); use DBI; use DBIx::XHTML_Table; print header(), start_form(); my $DBH = DBI->connect( qw(DB CONNECT INFO HERE), {RaiseError => 1, AutoCommit => 1} ); if (param('go')) { my %hash; foreach (param()) { my ($pk,$field) = $_ =~ /_(\d+)=(\w+)/; next unless $pk; $hash{$pk}->{$field} = param($_); } while (my($pk,$row) = each %hash) { $DBH->prepare( 'update <INSERT_TABLE_NAME> set ' . join(',', map {"$_=?"} keys %$row) . ' where id=?' )->execute((values %$row),$pk); } } my $table = DBIx::XHTML_Table->new($DBH) or die 'whoops'; $table->set_pk(<INSERT_PK_NAME>); $table->exec_query('select * from <INSERT_TABLE_NAME>'); $table->map_cell(sub { my $datum = shift; my $row = $table->get_current_row(); my $col = $table->get_current_col(); return qq|<input type="text" name="_$row=$col" value="$datum">|; }); print $table->output(), submit(-name=>"go"), end_form(), end_html(); $DBH->disconnect();

The first thing is to connect to your database via DBI (not DBIx::XHTML_Table) so you can re-use the handle. (and yes, Apache::DBI would be much better!).

Next is a simple check to see if the form was submitted. If not, then a new DBIx::XHTML_Table object is created, modified, and displayed. Nothing new here, except the naming convention i chose for each input - an _ to sererate it from the other form controls, and an = sign to delimit $row from $col so i can split it later. It's the 'was submitted' part that's tricky:

The foreach loop munges the params into a data structure. To paint a picture: (and please forgive my blantent abuse of the 2nd normal form, by the way)

param() = ( '_926=title', '_926=artist', '_926=album', '_926=year', '_926=genre', '_927=title', '_927=artist', '_927=album', '_927=year', '_927=genre', 'Submit Button' );
%hash = ( '926' => { 'artist' => 'Rush', 'genre' => 'rock', 'album' => 'Fly By Night', 'title' => 'Making Memories', 'year' => '1975' }, '927' => { 'artist' => 'Rush', 'genre' => 'rock', 'album' => 'Fly By Night', 'title' => 'Anthem', 'year' => '1975' } );

...which is what the while loop uses to actually create the SQL update statement. Using bind vars and ? is good for portability among different databases, by the way. (big thanks to maverick for reminding me)

The next big thing that this puppy needs is CACHING!! Every row is updated, even the ones that didn't change - if the user only changes one cell, then every row that was selected will be updated. Ughh. I'll leave caching as an 'exercise for the reader' (aka 'pass the buck'). I also did not address the issue of joins, but in this case I was aiming for simplicity. Besides, that's receipe #43 ...


Is this the next best thing since slice bread? Is this the be-all end-all way to to make a spreadsheet app? Of course not - it's just another Cool Use For Perl. ;)


Replies are listed 'Best First'.
Re: Spreadsheet CGI
by osfameron (Hermit) on Nov 11, 2001 at 01:06 UTC
    Yes! That's definitely a Cool Use For Perl. The CGI/DBI stuff looks cool, and I think that the whole idea of using Perl embedded in a spreadsheet (e.g. possibly using Tk even) would really be the "best thing since sliced bread"!

    In my work we use Excel a lot to munge .csv files of names and telephone numbers to prepare them for import into the Remedy workflow/call-logging ARS system.

    <grudging>Microsoft Excel is actually quite a good spreadsheet</grudging>, but it actually sucks at this job: because of being too 'helpful' at converting your numbers (like phone numbers) into different notation. There's also no really easy way of transforming a whole column - e.g. adding the international dialling code to a column, trimming the name, correcting capitalisation etc.

    (it also has various bugs like assuming that if your .csv file begins with the header 'ID' (as our Remedy imports do...) that it's a Symbolic Link!)

    So for a lot of the tasks I'd like to do, Excel and VBA are, er... suboptimal, while Perl would be excellent.


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: CUFP [id://124581]
Approved by root
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (4)
As of 2017-09-24 13:24 GMT
Find Nodes?
    Voting Booth?
    During the recent solar eclipse, I:

    Results (274 votes). Check out past polls.