SocialCalc Engine Example Documentation
Revision 2, 24 July 2007
Originally authored by Dan Bricklin
The goal of this piece of documentation is to help show how to add simple spreadsheet embedding into a web page authoring, wiki, or blogging system. It gives a specific example of a simple editing system and then shows added code that implements spreadsheet capabilities.
We start with a simple system that lets you edit individual pages that are wiki-like (with simple wiki markup). Each page is stored in the server as a single file in wiki-markup format. When the system is accessed with a browser, the page with the name "firstpage" is displayed in a rendered, preview mode. Above it is an "Edit Page" button. Under the preview is a list of links to other pages that may be viewed (the list starts out empty). Finally, there is a text box next to a "Create New Page" button.
Pressing the "Edit Page" button brings up a web page displaying the raw text of the page in an editable textarea. Above that are "Save" and "Cancel" buttons. The text may be edited like in a traditional, Socialtext-like wiki, with explicit line breaks preserved, lines starting and ending with "= " and " =" treated as headings, '' bracketing italic text and ''' bracketing bold text, and [page:pagename link text] specifying links to other pages.
Here is the code that implements the functionality. (The SocialCalc modules are only there to give access to the special_chars routine in Sheet.pm, but they will be needed for the enhanced version we describe later.) As with most of the other examples used as part of the SocialCalc Engine documentation, this program is run as a CGI script on a server.
--- Start source of simpleeditplain2.pl ---
#!/usr/bin/perl
use strict;
use SocialCalc::Sheet;
use SocialCalc::SheetFunctions;
use SocialCalc::Strings;
use CGI qw(:standard);
my $q = new CGI;
my ($statusmessage);
my $pagename = $q->param('pagename');
if ($q->param('newpage')) {
$pagename = $q->param('newpagename');
}
$pagename =~ s/[^a-zA-Z0-9_\-]*//g;
if (!$pagename) {
$pagename = "firstpage";
$statusmessage .= "No pagename given - using '$pagename'<br>";
}
if ($q->param('savepageedit')) {
open (PAGEFILEOUT, ">$pagename.page.txt");
print PAGEFILEOUT $q->param('pagetext');
close PAGEFILEOUT;
$statusmessage .= "Saved updated page '$pagename'.<br>";
}
if ($q->param("editpage")) { # when "editpage" button is pressed
do_editpage($q, $pagename, $statusmessage);
exit;
}
do_displaypage($q, $pagename, $statusmessage); # Otherwise, display page
exit;
#
# do_displaypage($q, $pagename, $statusmessage) - Display page
#
sub do_displaypage {
my ($q, $pagename, $statusmessage) = @_;
open (PAGEFILEIN, "$pagename.page.txt");
my $pagestr;
while (my $line = <PAGEFILEIN>) {
$line =~ s/\r//g;
$pagestr .= expand_wikitext($line);
}
print <<"EOF";
Content-type: text/html
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
"http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Simple Page Editor</title>
<style>
body, td, input, texarea
{font-family:verdana,helvetica,sans-serif;font-size:small;}
</style>
</head>
<body>
<h2>SIMPLE SYSTEM FOR EDITING PAGES</h2>
<span style="color:red;font-weight:bold;">$statusmessage</span>
<h3>Preview of page "$pagename":</h3>
<form action="" method="POST">
<input type="submit" name="editpage" value="Edit Page">
<hr>
$pagestr
<hr>
<h3>Pages:</h3>
EOF
my @pagefiles = glob("*.page.txt"); # Get list of all pages
for (my $pnum=0; $pnum <= $#pagefiles; $pnum++) {
$pagefiles[$pnum] =~ m/^(.*).page.txt$/;
print qq!<a href="?pagename=$1">$1</a><br>!;
}
print <<"EOF";
<br>
<input type="text" name="newpagename" value="">
<input type="submit" name="newpage" value="Create New Page">
<input type="hidden" name="pagename" value="$pagename">
</form>
</body>
</html>
EOF
return;
}
#
# do_editpage($q, $pagename, $statusmessage) - Show page editing display
#
sub do_editpage {
my ($q, $pagename, $statusmessage) = @_;
open (PAGEFILEIN, "$pagename.page.txt");
my ($pagestr, $line);
while (my $line = <PAGEFILEIN>) {
$line =~ s/\r//g;
$pagestr .= $line;
}
$pagestr = special_chars($pagestr);
print <<"EOF";
Content-type: text/html
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
"http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Simple Page Editor</title>
<style>
body, td, input, texarea
{font-family:verdana,helvetica,sans-serif;font-size:small;}
</style>
</head>
<body>
<h2>SIMPLE SYSTEM FOR EDITING PAGES</h2>
<span style="color:red;font-weight:bold;">$statusmessage</span>
<h3>Edit text of page "$pagename":</h3>
<form name="f0" action="" method="POST">
<input type="submit" name="savepageedit" value="Save">
<input type="submit" name="canceledit" value="Cancel">
<hr>
<textarea name="pagetext" rows="20" style="width:100%;">$pagestr</textarea>
<input type="hidden" name="pagename" value="$pagename">
</form>
</body>
</html>
EOF
return;
}
#
# expand_wikitext($string) - Returns $string doing wiki-style formatting
#
sub expand_wikitext {
my $string = shift @_;
# Process forms that use URL encoding first
# [page:pagename text] to link to other pages on this site
$string =~ s!\[page:(.+?)\s+(.+?)?]!'{{lt}}a href={{quot}}?pagename=' .
$1 . "{{quot}}{{gt}}$2\{{lt}}/a{{gt}}"!xegs;
# Convert &, <, >, "
$string = special_chars($string);
$string =~ s/^\= (.*) \=$/<h2>$1<\/h2>/gs;
$string =~ s/\n/<br>/g; # Line breaks are preserved
$string =~ s/('*)'''(.*?)'''/$1<b>$2<\/b>/gs; # Wiki-style bold/italics
$string =~ s/''(.*?)''/<i>$1<\/i>/gs;
$string =~ s/\{\{amp}}/&/gs; # {{amp}} for ampersand
$string =~ s/\{\{lt}}/</gs; # {{lt}} for less than
$string =~ s/\{\{gt}}/>/gs; # {{gt}} for greater than
$string =~ s/\{\{quot}}/"/gs; # {{quot}} for quote
$string =~ s/\{\{lbracket}}/[/gs; # {{lbracket}} for left bracket
$string =~ s/\{\{rbracket}}/]/gs; # {{rbracket}} for right bracket
$string =~ s/\{\{lbrace}}/{/gs; # {{lbrace}} for brace
return $string;
}
--- End source of simpleeditplain2.pl ---
This program starts by retrieving the name of the page to be acted upon from the HTTP parameters. This may be explicit, implicit (defaults to "firstpage"), or come from a "New Page" button press. The name is "cleaned" to make it consist of only alphanumerics and a few special characters.
If the "Save" button was pushed on the editing page, the updated text of the page is retrieved from the pagetext textarea and written to the appropriate file. The file is kept in the same directory as the running CGI script, and given a name in the form "pagename.page.txt". After saving, the page is treated as a normal page to display in preview form by the code that follows.
If the "Edit Page" button was pushed on this preview page, the program invokes the do_editpage routine and then exits.
If the page is not to be edited, it is displayed in a preview form. This is done by the do_displaypage routine. That routine reads in the raw text of the appropriate file for the requested page, passes each line to a wiki-text processing routine (expand_wikitext), and then displays that rendered page along with the appropriate buttons and information. It follows with a list of pages in the directory and the Create New Page button.
If the page is to be edited, the do_editpage routine reads in the raw text of the page and displays it in a textarea for editing, along with appropriate buttons.
So far this is a very simple, straightforward system, generally similar to the operation of many other systems.
Extending the program to handle embedded spreadsheets
To understand how embedded spreadsheets are added, it is assumed that you are familiar with the simpleedit1.pl example in the Programmer Documentation for the SocialCalc Javascript. That example implemented XMLHttpRequest (XHR) interaction with the browser for simple spreadsheet editing. It includes the prepare_celldata, ajax_call, fill_in_colors, initialize_colorlookup, decode_from_ajax, and encode_for_ajax routines. Those routines are included in this example, too.
This example, simpleedit2.pl, starts with the simple wiki-like editor of simpleeditplain2.pl and then adds a new wiki-directive that may appear in the raw text: [spreadsheet:...spreadsheet-save-data...]. This directive lets you include sections in the wiki-text that define embedded spreadsheets. When "viewed" in preview mode the spreadsheets are rendered without the grid and Edit buttons are displayed next to each spreadsheet. Pressing one of these buttons brings up just that sheet in an interactive spreadsheet editor. The spreadsheet editor page also has a few buttons to insert/delete rows/columns, make white on black text, put borders around cells, etc. Pressing the Save button on the editing page updates that saved data in the wiki page and re-previews the rendered page.
The main difference between this example and the one in the Programmer Documentation for the SocialCalc Javascript is that this one includes the saving and retrieving of the spreadsheet from a file. Unlike that other example where the spreadsheet sheetdata is passed back and forth in HTTP POST requests and responses, and disappears when the browser is closed, in this example when the Edit button is pressed the sheetdata starts out being loaded from contents retrieved from a file on the server, and when the Save button is pressed the changed sheetdata is written back to a file on the server. Only during editing does the latest version of the sheetdata mainly reside in the HTTP requests and responses.
Unlike the normal SocialCalc operation, in this example the spreadsheet sheetdata does not reside in a file by itself, with one file for each sheet. In this example the sheetdata is included as part of an otherwise normal wiki-like page along with regular text data. There can be any number of separate spreadsheets as part of each page. Each of those spreadsheets has its own name that is unique within the page. This style of operation lets you add spreadsheets to an existing system without needing to add major new facilities to the data storage system.
Here is the source code to simpleedit2.pl:
--- Start source of simpleedit2.pl ---
#!/usr/bin/perl
use strict;
use SocialCalc::Sheet;
use SocialCalc::SheetFunctions;
use SocialCalc::Strings;
use CGI qw(:standard);
my %colorlookup;
initialize_colorlookup();
fill_in_colors(\$SocialCalc::Sheet::sheetstyleheader);
my $q = new CGI;
my ($statusmessage);
my $pagename = $q->param('pagename');
if ($q->param('newpage')) {
$pagename = $q->param('newpagename');
}
$pagename =~ s/[^a-zA-Z0-9_\-]*//g;
if (!$pagename) {
$pagename = "firstpage";
$statusmessage .= "No pagename given - using '$pagename'<br>";
}
if ($q->param('savepageedit')) {
open (PAGEFILEOUT, ">$pagename.page.txt");
print PAGEFILEOUT $q->param('pagetext');
close PAGEFILEOUT;
$statusmessage .= "Saved updated page '$pagename'.<br>";
}
if ($q->param("editpage")) { # when "editpage" button is pressed
do_editpage($q, $pagename, $statusmessage);
exit;
}
foreach my $p ($q->param) { # go through all the parameters
if ($p =~ /^editspreadsheet:(.*)/) { # "editsheet:sheetname" pressed
start_editsheet($pagename, $1, $q, $statusmessage);
exit;
}
}
if ($q->param('executecommand')) { # spreadsheet editing command
start_editsheet($pagename, $q->param('sheetname'), $q, $statusmessage);
exit;
}
if ($q->param('savespreadsheet')) { # save the edited spreadsheet
open (PAGEFILEIN, "$pagename.page.txt"); # get old version
my $sheetname = $q->param('sheetname');;
my $pagestr;
while (my $line = <PAGEFILEIN>) {
$line =~ s/\r//g;
if ($line =~ m/^\[spreadsheet\:(.*?)\:/) {
my $thissheet = $1;
my $oldsheetstr = $line;
while (my $sline = <PAGEFILEIN>) {
if ($thissheet ne $sheetname) { # copy other ones
$oldsheetstr .= $sline;
}
if ($sline =~ m/^\:spreadsheet\]/) { # at end
if ($thissheet eq $sheetname) { # insert this one
$oldsheetstr .= $q->param('savestr');
$oldsheetstr .= $sline;
}
$pagestr .= $oldsheetstr;
last;
}
}
}
else {
$pagestr .= $line;
}
}
close PAGEFILEIN;
open (PAGEFILEOUT, "> $pagename.page.txt");
print PAGEFILEOUT $pagestr;
close PAGEFILEOUT;
$statusmessage =
"Saved updated spreadsheet '$sheetname' on page '$pagename'.<br>";
}
if ($q->param("ajaxsetcell")) { # Ajax call to change cell value
my @lines = split(/\n/,$q->param("savestr")); # sheet data in textarea
my %sheetdata;
parse_sheet_save(\@lines, \%sheetdata);
ajax_call(\%sheetdata, $q->param("ajaxsetcell"));
exit;
}
do_displaypage($q, $pagename, $statusmessage); # Otherwise, display page
exit;
#
# do_displaypage($q, $pagename, $statusmessage) - Display page
#
sub do_displaypage {
my ($q, $pagename, $statusmessage) = @_;
open (PAGEFILEIN, "$pagename.page.txt");
my $pagestr;
while (my $line = <PAGEFILEIN>) {
$line =~ s/\r//g;
if ($line =~ m/^\[spreadsheet\:(.*?)\:/) {
my $sheetname = $1;
my @sheetlines;
while (my $sline = <PAGEFILEIN>) {
last if $sline =~ m/^:spreadsheet]/;
push @sheetlines, $sline;
}
my %sheetdata;
my $parseok = parse_sheet_save(\@sheetlines, \%sheetdata);
my $linkstyle = "?page=[[pagename]]";
my ($stylestr, $sheetstr) = render_sheet(\%sheetdata,
'class="wkcsheet"', "", "s", "a", "inline",
"", "", $linkstyle);
$pagestr .= <<"EOF";
<table cellspacing="0" cellpadding="0">
<tr><td valign="top">
$sheetstr
</td><td width="20"> </td><td width="1" valign="middle"
style="border-left:3px solid gray;padding:6px;font-size:smaller;">
Spreadsheet:<br>$sheetname<br><br>
<input type="submit" name="editspreadsheet:$sheetname"
value="Edit" style="font-size:smaller;">
</td></tr></table>
EOF
}
else {
$pagestr .= expand_wikitext($line);
}
}
print <<"EOF";
Content-type: text/html
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
"http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Simple Page Editor With Spreadsheets</title>
<style>
body, td, input, texarea
{font-family:verdana,helvetica,sans-serif;font-size:small;}
</style>
</head>
<body>
<h2>SIMPLE SYSTEM FOR EDITING PAGES WITH SPREADSHEETS</h2>
<span style="color:red;font-weight:bold;">$statusmessage</span>
<h3>Preview of page "$pagename":</h3>
<form action="" method="POST">
<input type="submit" name="editpage" value="Edit Page">
<hr>
$pagestr
<hr>
<h3>Pages:</h3>
EOF
my @pagefiles = glob("*.page.txt"); # Get list of all pages
for (my $pnum=0; $pnum <= $#pagefiles; $pnum++) {
$pagefiles[$pnum] =~ m/^(.*).page.txt$/;
print qq!<a href="?pagename=$1">$1</a><br>!;
}
print <<"EOF";
<br>
<input type="text" name="newpagename" value="">
<input type="submit" name="newpage" value="Create New Page">
<input type="hidden" name="pagename" value="$pagename">
</form>
</body>
</html>
EOF
return;
}
#
# do_editpage($q, $pagename, $statusmessage) - Show page editing display
#
sub do_editpage {
my ($q, $pagename, $statusmessage) = @_;
open (PAGEFILEIN, "$pagename.page.txt");
my ($pagestr, $line);
while (my $line = <PAGEFILEIN>) {
$line =~ s/\r//g;
$pagestr .= $line;
}
$pagestr = special_chars($pagestr);
print <<"EOF";
Content-type: text/html
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
"http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Simple Page Editor With Spreadsheets</title>
<style>
body, td, input, texarea
{font-family:verdana,helvetica,sans-serif;font-size:small;}
</style>
</head>
<body>
<h2>SIMPLE SYSTEM FOR EDITING PAGES WITH SPREADSHEETS</h2>
<span style="color:red;font-weight:bold;">$statusmessage</span>
<h3>Edit text of page "$pagename":</h3>
<script>
function addss() {
var sname =
prompt("New sheet name (alphanumeric only, unique on page):");
if (!sname) return false;
document.f0.pagetext.value =
document.f0.pagetext.value + "\\n[spreadsheet:" + sname +
":\\nsheet:r:1:c:5\\n:spreadsheet]\\n";
return false;
}
</script>
<form name="f0" action="" method="POST">
<input type="submit" name="savepageedit" value="Save">
<input type="submit" name="canceledit" value="Cancel">
<input type="submit" value="Add Spreadsheet"
onclick="return addss();">
<hr>
<textarea name="pagetext" rows="20" style="width:100%;">$pagestr</textarea>
<input type="hidden" name="pagename" value="$pagename">
</form>
</body>
</html>
EOF
return;
}
#
# start_editsheet($pagename, $sheetname, $q, $statusmessage)
# - render initial editing display
#
sub start_editsheet {
my ($pagename, $sheetname, $q, $statusmessage) = @_;
my @sheetlines;
my $savestr;
if ($q->param('executecommand')) { # continue editing after executing command
$savestr = $q->param('savestr');
@sheetlines = split(/\n/, $savestr);
}
else { # edit from page file
open (PAGEFILEIN, "$pagename.page.txt");
while (my $line = <PAGEFILEIN>) {
$line =~ s/\r//g;
if ($line =~ m/^\[spreadsheet\:(.*?)\:/) {
my $thissheet = $1;
while (my $sline = <PAGEFILEIN>) {
last if $sline =~ m/^\:spreadsheet\]/;
next if $thissheet ne $sheetname;
push @sheetlines, $sline;
$savestr .= $sline;
}
last if $thissheet eq $sheetname;
}
}
}
my %sheetdata;
my $sheetdata = \%sheetdata;
parse_sheet_save(\@sheetlines, $sheetdata);
if ($q->param('executecommand') &&
$q->param('spreadsheetcommand')) { # execute command
foreach my $cmd (split(/\n/,
$q->param('spreadsheetcommand'))) { # could be multiple
$cmd =~ s/\r//g;
execute_sheet_command($sheetdata, $cmd);
}
recalc_sheet($sheetdata);
$savestr = create_sheet_save($sheetdata);
}
$savestr = special_chars($savestr); # this goes in textarea
my $editcoords = $q->param('editcoords');
if ($editcoords) {
$editcoords =~ s/^([^\:]*)\:(.*)$/$1/;
}
else {
$editcoords = "A1";
}
# Load scripts from a file
my $inlinescripts .= $WKCStrings{"jsdefinestrings"};
open JSFILE, "jslib/SocialCalc/Common.js";
while (my $line = <JSFILE>) {
$inlinescripts .= $line;
}
close JSFILE;
$inlinescripts .= $WKCStrings{"editjsdefinestrings"};
open JSFILE, "jslib/SocialCalc/Edit.js";
while (my $line = <JSFILE>) {
$inlinescripts .= $line;
}
close JSFILE;
fill_in_colors(\$inlinescripts);
# patch in passing savestr back and forth
$inlinescripts =~ # add to ajaxsetcell_request function
s/
(function\ ajaxsetcell_request\(.*?)\ makeRequest
/
$1
reqcontents = reqcontents +
"&savestr="+encodeURIComponent(document.f0.savestr.value);
makeRequest/xs;
$inlinescripts =~ # add to parse_sheet function
s/
(function\ parse_sheet\(.*?)\ var\ col
/
$1
else if (cr=="sheettext") {
document.f0.savestr.value=decode_field(parts[1]);
continue;
}
var col/xs;
my ($sheetstyle, $sheetstr) =
render_sheet($sheetdata, 'id="sheet0" class="wkcsheet"',
"", "s", "a", "ajax", $editcoords,
q! onclick="rc0('$coord');"!, "");
my %celldata; # get initial cell settings
my ($lcol, $lrow) = render_values_only($sheetdata, \%celldata, "");
my $jsdata = qq!var isheet="";\nisheet="!; # cell information
foreach my $cr (sort keys %celldata) { # construct output
my $str = prepare_celldata(\%celldata, $sheetdata, $cr);
$str =~ s/\\/\\\\/g;
$str =~ s/"/\\x22/g;
$str =~ s/</\\x3C/g;
$jsdata .= "$str\\n";
}
$jsdata .= qq!"\n!; # end isheet definition
$inlinescripts .= <<"EOF"; # put together initializing JS code
<script>
$jsdata
sheetlastcol=$lcol;
sheetlastrow=$lrow;
parse_sheet(isheet);
ecell="$editcoords";
needsrecalc="";
cliprange="A1"; //dummy to avoid msg
</script>
<script>
function choosecmd(c) {
var cmd;
var col=rcColnum(ecell);
var rangecells=ecell;
if (highlighton) {
rangecells = rangecells+":"+highlightpos;
}
if (c=="insertrow") cmd="insertrow "+ecell;
else if (c=="deleterow") cmd="deleterow "+ecell;
else if (c=="insertcol") cmd="insertcol "+ecell;
else if (c=="deletecol") cmd="deletecol "+ecell;
else if (c=="sort1")
cmd="sort "+ecell+":"+highlightpos+" "+rcColname(col)+" up";
else if (c=="sort2")
cmd="sort "+ecell+":"+highlightpos+" "+rcColname(col+1)+" up";
else if (c=="widthauto")
cmd="set "+rcColname(col)+" width auto";
else if (c=="width80")
cmd="set "+rcColname(col)+" width 80";
else if (c=="colorrev") {
cmd="set "+rangecells+" color rgb(255,255,255)\\n";
cmd+="set "+rangecells+" bgcolor rgb(0,0,0)";
}
else if (c=="colordef") {
cmd="set "+rangecells+" color\\n";
cmd+="set "+rangecells+" bgcolor";
}
else if (c=="border4") {
cmd="set "+rangecells+" bt 1px solid rgb(0,0,0)\\n";
cmd+="set "+rangecells+" br 1px solid rgb(0,0,0)\\n";
cmd+="set "+rangecells+" bb 1px solid rgb(0,0,0)\\n";
cmd+="set "+rangecells+" bl 1px solid rgb(0,0,0)";
}
else if (c=="border0") {
cmd="set "+rangecells+" bt\\n";
cmd+="set "+rangecells+" br\\n";
cmd+="set "+rangecells+" bb\\n";
cmd+="set "+rangecells+" bl";
}
document.f0.spreadsheetcommand.value=cmd;
return true;
}
</script>
EOF
print <<"EOF"; # output page with edit JS code
Content-type: text/html
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
"http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Simple Page Editor With Spreadsheets</title>
<style>
body, td, input, texarea
{font-family:verdana,helvetica,sans-serif;font-size:small;}
.smaller {font-size:smaller;}
.cellnormal {}
.cellcursor {color:#FFFFFF;background-color:#394F87}
$sheetstyle
.selectedcolname
{text-align: center;color: #FFFFFF;background-color: #394F87;
border-left:0px none;border-right:0px none;}
.selectedrowname
{text-align: right;color: #FFFFFF;background-color: #394F87;
padding-left:1em;border-top:0px none;border-bottom:0px none;}
</style>
</head>
<body onKeydown="return ev1(event);"
onKeypress="return ev2(event);"
onLoad="save_initial_sheet_data();move_cursor(ecell,ecell,true);">
<h2>SIMPLE SYSTEM FOR EDITING PAGES WITH SPREADSHEETS</h2>
$inlinescripts
<h3>Editing Sheet '$sheetname':</h3>
<span style="color:red;font-weight:bold;">$statusmessage</span>
<form name="f0" action="" method="POST">
<input type="submit" name="savespreadsheet" value="Save">
<input type="submit" name="cancelspreadsheet" value="Cancel">
<br>
<input type="submit" name="executecommand" value="Insert Row"
onclick="choosecmd('insertrow')">
<input type="submit" name="executecommand" value="Delete Row"
onclick="choosecmd('deleterow')">
<input type="submit" name="executecommand" value="Insert Col"
onclick="choosecmd('insertcol')">
<input type="submit" name="executecommand" value="Delete Col"
onclick="choosecmd('deletecol')"><br>
<input type="submit" name="executecommand" value="Col Width Auto"
onclick="choosecmd('widthauto')">
<input type="submit" name="executecommand" value="Col Width 80"
onclick="choosecmd('width80')">
<input type="submit" name="executecommand" value="Sort 1"
onclick="choosecmd('sort1')">
<input type="submit" name="executecommand" value="Sort 2"
onclick="choosecmd('sort2')"><br>
<input type="submit" name="executecommand" value="Borders On"
onclick="choosecmd('border4')">
<input type="submit" name="executecommand" value="Borders Off"
onclick="choosecmd('border0')">
<input type="submit" name="executecommand" value="Color Reverse"
onclick="choosecmd('colorrev')">
<input type="submit" name="executecommand" value="Color Default"
onclick="choosecmd('colordef')">
<hr>
<table cellpadding="0" cellspacing="0">
<tr>
<td class="smaller"> </td>
<td valign="bottom">
<div id="mode1"><span class="smaller" id="valuetype">Loading...
</span><span id="warning" class="warning"></span>
<span id="recalcmsg" class="smaller" style="display:none"></span>
</div>
<div id="mode4" style="display:none;"></div>
<div id="mode5" style="display:none;"></div>
<div id="mode6" style="display:none;"></div>
<div id="mode7" style="display:none;"></div>
</td>
</tr>
<tr>
<td valign="top" width="1">
<span id="coordtext"> </span>
</td>
<td valign="bottom" width="100%" nowrap><span id="config1a">
<input class="smaller" type="text" size="80" name="valueedit"
value="" autocomplete="off" onFocus="ve_focus()">
<input class="smaller" type="submit" name="okedit" id="okeditve"
value="OK" onClick="this.blur();return process_OK();">
</span><span id="config2a" style="display:none;">
<input class="smaller" style="font-style:italic;" type="text"
size="80" value="Multi-line edit required" disabled>
</span><span id="config45a" style="display:none;"><span
id="rangeend"></span>
<span id="kbdprompt" class="smaller"></span></span><span id="config3a"
style="display:none;">
<textarea cols="80" rows="10" name="valueedittext"
onFocus="vet_focus()"></textarea><br>
<input class="smaller" type="submit" name="okeditvet" value="OK"
onClick="this.blur();return process_OK();">
<input class="smaller" type="submit" name="canceleditvet" value="Cancel"
onClick="this.blur();process_typed_char('[esc]');return false;">
</span>
</td>
</tr>
</table>
<div id="draghandle" style="display:none;"></div>
<div style="height:6px;"></div>
$sheetstr
<hr>
<textarea name="savestr" style="display:none;">$savestr</textarea>
<input type="hidden" name="scrollrow" value="0">
<input type="hidden" name="editcoords" value="$editcoords">
<input type="hidden" name="sitename" value="">
<input type="hidden" name="datafilename" value="">
<input type="hidden" name="loggedinusername" value="">
<input type="hidden" name="loggedinuserpassword" value="">
<input type="hidden" name="pagename" value="$pagename">
<input type="hidden" name="sheetname" value="$sheetname">
<input type="hidden" name="spreadsheetcommand" value="">
</form>
<form name="ftabs">
<input type="hidden" name="scrollrow" value="1">
<input type="hidden" name="editcoords" value="$editcoords">
</form>
<form name="fsl">
<input type="hidden" name="scrollrow" value="1">
<input type="hidden" name="editcoords" value="$editcoords">
</form>
</body>
</html>
EOF
}
#
# prepare_celldata(\%celldata, \%sheetdata, $cr) - render definition
# line for $cr in isheet and ajax response data format
#
sub prepare_celldata {
my ($celldata, $sheetdata, $cr) = @_;
my $cellspecifics = $celldata->{$cr};
my $displayvalue = encode_for_save($cellspecifics->{display});
$displayvalue = "" if $displayvalue eq " "; # this is the default
my $csssvalue = encode_for_save($sheetdata->{cellattribs}->{$cr}->{csss});
my $editvalue;
if ($sheetdata->{datatypes}->{$cr} eq 'f' ||
$sheetdata->{datatypes}->{$cr} eq 'c') { # formula or constant
$editvalue = encode_for_save($sheetdata->{formulas}->{$cr});
}
else {
$editvalue = encode_for_save($sheetdata->{datavalues}->{$cr});
}
my $str = "$cr:$cellspecifics->{type}:$displayvalue:$editvalue:"
. "$cellspecifics->{align}:$cellspecifics->{colspan}:"
. "$cellspecifics->{rowspan}:$cellspecifics->{skip}:$csssvalue";
return $str;
}
#
# ajax_call(\%sheetdata, $command) - respond to ajaxsetcell request
#
sub ajax_call {
my ($sheetdata, $command) = @_;
my ($psite, $pname, $coord, $value) = split(/:/, $command, 4);
$value = decode_from_ajax($value);
my %celldatabefore;
render_values_only($sheetdata, \%celldatabefore, "");
# Determine value type and do appropriate command to set it
my $type;
my $fch = substr($value, 0, 1);
if ($fch eq "=" && $value !~ m/\n/) {
$type = "formula";
$value = substr($value, 1);
}
elsif ($fch eq "'") {
$type = "text t";
$value = substr($value, 1);
}
elsif (length $value == 0) {
$type = "empty";
}
else {
my $v1 = determine_value_type($value, \$type);
if ($type eq 'n' && $v1 == $value) { # check we don't need "constant"
$type = "value n";
}
elsif ($type eq 't') {
$type = "text t";
}
else { # handle all the special types
$type = "constant $type $v1";
}
}
my $cmdline = "set $coord $type $value"; # create command
my $ok = execute_sheet_command($sheetdata, $cmdline); # set cell value
my $aerrtext = recalc_sheet($sheetdata);
my %celldataafter;
render_values_only($sheetdata, \%celldataafter, "");
my $response;
foreach my $cr (sort keys %celldataafter) { # construct output
my $cdbefore = $celldatabefore{$cr};
my $cdafter = $celldataafter{$cr};
next
if $cdbefore->{type} eq $cdafter->{type}
&& $cdbefore->{display} eq $cdafter->{display}
&& $cdbefore->{align} eq $cdafter->{align}
&& $cr ne $coord; # only stuff that has changed unknown to client
# (but send at least one -- the one with "loading")
my $str = prepare_celldata(\%celldataafter, $sheetdata, $cr);
$str =~ s/\\r//g;
$str =~ s/]]>/\\e/g;
$response .= "$str\n";
}
my $savestr = create_sheet_save($sheetdata);
$response .= "sheettext:" . encode_for_ajax($savestr) . "\n";
print <<"EOF";
Content-type: text/xml
<?xml version="1.0" ?>
<root><![CDATA[
$response
]]></root>
EOF
}
#
# fill_in_colors(\$str) - Replaces {[colorname]} in $$str with color value
#
sub fill_in_colors {
my $str = shift @_;
$$str =~ s/\{\[(\w+?)\]\}/$colorlookup{$1}/ge;
}
#
# initialize_colorlookup() - Fills in %colorlookup
#
sub initialize_colorlookup {
my %colordata = ( # normally this comes from $WKCStrings
"colornames" => "error cursor rangebody rangeend
gridheaderbackground gridheadertext
gridheaderbackgroundselected gridheadertextselected
skippedcellbackground
draghandleup draghandledown draghandleoff
scrolloff scrollon
",
"color-error" => "#FF0000",
"color-cursor" => "#394F87",
"color-rangebody" => "#EEEEEE",
"color-rangeend" => "#80A9F3", #99CC99
"color-gridheaderbackground" => "#80A9F3",
"color-gridheadertext" => "#FFFFFF", #FFFFFF
"color-gridheaderbackgroundselected" => "#394F87",
"color-gridheadertextselected" => "#FFFFFF",
"color-skippedcellbackground" => "#CCCCCC",
"color-draghandleup" => "#CCCCCC",
"color-draghandledown" => "#999999",
"color-draghandleoff" => "#80A9F3",
"color-scrolloff" => "#FFFFFF",
"color-scrollon" => "#999999",
);
foreach my $c (split(/\s/, $colordata{colornames})) {
$colorlookup{$c} = $colordata{"color-$c"};
}
}
#
# decode_from_ajax($string) - Returns a string with
# \n, \b, and \c escaped to \n, \, and :
#
sub decode_from_ajax {
my $string = shift @_;
$string =~ s/\\n/\n/g;
$string =~ s/\\c/:/g;
$string =~ s/\\b/\\/g;
return $string;
}
#
# encode_for_ajax($string) - Returns a string with
# \n, \, :, and ]]> escaped to \n, \b, \c, and \e
#
sub encode_for_ajax {
my $string = shift @_;
$string =~ s/\\/\\b/g;
$string =~ s/\n/\\n/g;
$string =~ s/\r//g;
$string =~ s/:/\\c/g;
$string =~ s/]]>/\\e/g;
return $string;
}
#
# expand_wikitext($string) - Returns $string doing wiki-style formatting
#
sub expand_wikitext {
my $string = shift @_;
# Process forms that use URL encoding first
# [page:pagename text] to link to other pages on this site
$string =~ s!\[page:(.+?)\s+(.+?)?]!'{{lt}}a href={{quot}}?pagename=' .
$1 . "{{quot}}{{gt}}$2\{{lt}}/a{{gt}}"!xegs;
# Convert &, <, >, "
$string = special_chars($string);
$string =~ s/^\= (.*) \=$/<h2>$1<\/h2>/gs;
$string =~ s/\n/<br>/g; # Line breaks are preserved
$string =~ s/('*)'''(.*?)'''/$1<b>$2<\/b>/gs; # Wiki-style bold/italics
$string =~ s/''(.*?)''/<i>$1<\/i>/gs;
$string =~ s/\{\{amp}}/&/gs; # {{amp}} for ampersand
$string =~ s/\{\{lt}}/</gs; # {{lt}} for less than
$string =~ s/\{\{gt}}/>/gs; # {{gt}} for greater than
$string =~ s/\{\{quot}}/"/gs; # {{quot}} for quote
$string =~ s/\{\{lbracket}}/[/gs; # {{lbracket}} for left bracket
$string =~ s/\{\{rbracket}}/]/gs; # {{rbracket}} for right bracket
$string =~ s/\{\{lbrace}}/{/gs; # {{lbrace}} for brace
return $string;
}
--- End source of simpleedit2.pl ---
Like the other examples, you load this program into a directory on a web server for execution as a CGI script (with appropriate permission settings). Like the simpleedit1.pl program, it should be installed in a directory with a sub-directory of "SocialCalc" containing Sheet.pm, SheetFunctions.pm, and Strings.pm from the /lib/SocialCalc directory in the distribution, and a sub-directory of jslib with a subdirectory of SocialCalc containing the Common.js and Edit.js files. It assumes the Javascript files are the same as those in the SocialCalc 1.1.0 release.
When accessed from a browser, this program starts out just like simpleeditplain2.pl. The difference is when editing the wiki-text you can press an "Add Spreadsheet" button. This prompts for a name (which must be unique within the page and should be only letters and numbers) to distinguish the embedded item from others and then inserts the following at the end of the wiki-text:
[spreadsheet:spreadsheet-name:
sheet:r:1:c:5
:spreadsheet]
The text between the first and last line is normal spreadsheet save format data. In this case it is just settings to start with an empty sheet with one row and five columns.
If you then press the Save button, you will see the rendered page with the empty spreadsheet embedded. Next to the spreadsheet will be a vertical line showing the extent of the sheet as well as its name and an "Edit" button.
If you press the Edit button you will be brought to a page showing an editable spreadsheet with the grid, a simple formula bar, and several buttons for performing operations. (This removes the need to type in commands in Sheet.pm's language for this demo.) The Sort 1, Sort 2, Borders On, Borders Off, Color Reverse, and Color Default buttons apply to either the selected cell or to a selected range of cells. You turn on range selection by pressing the ":" key and then clicking on (or moving the cursor to with the arrow keys) the bottom-right-most cell in the desired range. Sort 1 sorts based on the values in the first column of the range and Sort 2 sorts based on the values in the second column in the range.
Press the Save button returns to the page preview display with the spreadsheet edits applied.
Here is an explanation of the code of simpleedit2.pl that differs from simpleeditplain2.pl and simpleedit1.pl:
In the beginning of the code, where it looks through the CGI parameters, it looks for a button that was pressed with a name in the form of "editspreadsheet:sheetname". If it finds one, then it calls the start_editsheet routine. This is for executing the "Edit" buttons next to the previewed spreadsheets.
If a button with the name "executecommand" is pressed, then start_editsheet is called. This is in response to the buttons on the spreadsheet editing page that require a complete re-rendering, such as Insert Row, Sort 1, etc.
If the "savespreadsheet" button was pressed, then the specified wiki-text page is read in, and copied back out to itself. If a spreadsheet is encountered in the text (starting with "[spreadsheet:") then the lines of the spreadsheet in the file are read. If this is not the spreadsheet that was just edited the line is passed through to the output. If it is the spreadsheet that was just edited, then all the lines are replaced by the updated save data. A status message is created and the code is set to render the updated sheet as a normal page preview.
If the request coming in to the CGI script is "ajaxsetcell", then the ajax_call routine is invoked. Its operation is explained in the Programmer Documentation for the SocialCalc Javascript.
The do_displaypage routine has added code for rendering spreadsheets. When it encounters lines starting with "[spreadsheet" it gathers up the data save format lines, parses it, and uses render_sheet in Sheet.pm to render the table. It uses the "inline" mode so that styles are used for each table cell instead of CSS classes. The resulting HTML is output along with a corresponding "Edit" button.
The do_editpage routine has added code to output Javascript for performing the "Add Spreadsheet" operation.
There is a new routine, start_editsheet, to render the spreadsheet editing display page. This is similar to the start_edit routine in the simpleedit1.pl example program. It has added code to load the sheetdata from the file in the case of initially starting editing. It also has code to execute commands passed from the browser when invoked by an "executecommand" button. It includes code to output the choosecmd Javascript routine for turning the command button presses into command strings for execution after the "executecommand" buttons are pressed but before a request is submitted to the server. It includes the many command buttons, each with an "onClick" handler to invoke that choosecmd routine.
Using this in a real product
This example is not sufficient for copying directly into a "real" product for production use. It was written to be as simple as possible and to use the released SocialCalc Engine files unmodified. A production use would need further code and modifications to the released files.
The main changes for minimal use would probably be to close the obvious security holes in the Engine code listed in the Security section of the Programmer Documentation for the SocialCalc Engine. When used on an editing tool that the user controls, though, you may not want to close off the WKCHTTP function. The other changes would be to the Javascript code, mainly in Edit.js, to disable the unused commands (and their display) and provide an interface to the range-specification. The simpleedit2.pl program does not disable these commands, so pressing the "/" or ":" keys can cause menu displays that are not appropriate.
In this example the spreadsheet data is made visible to the user in the raw wiki-text. In a more user-friendly system, this data may be hidden through use of a smarter editing system. For example, something like [spreadsheet:name] could be included and visible to the user, but the system would take care of storing the data in a database or other storage related to the page being edited. Instead of just removing the Sheet.pm find_sheet_in_cache capabilities to refer to other sheets, you might want to allow access to sheets on the same or other page through some syntax. This could lead to being able to create very complex systems of spreadsheets that refer to each other, though recalculation would not ripple through from sheet to sheet automatically without additional coding.