App-SocialCalc-Multiplayer
view release on metacpan or search on metacpan
socialcalc/SocialCalcServersideUtilities.pm view on Meta::CPAN
# This code is also based in part on the SocialCalc JavaScript implementation of 2008.
#
# The new rights in this new code:
#
# (c) Copyright 2008 Socialtext, Inc.
# All Rights Reserved.
#
# This code has NOT been released under an Open Source license at this point.
# It is not to be used without permission of Socialtext, Inc.
#
# Known problems:
# Doesn't handle UTF-8 currency symbols (and who knows what else)
# Doesn't handle hidden rows and columns correctly.
#
use strict;
use utf8;
use Time::Local;
use Digest::SHA ();
require Exporter;
our @ISA = qw(Exporter);
our @EXPORT = qw(CreateSheet ParseSheetSave CreateSheetSave DecodeSpreadsheetSave
CreateCellHTML CreateCellHTMLSave
CoordToCR CRToCoord ParseRange
CreateCSV
CellToString
CreateRenderContext RenderSheet CreateCSV
CalculateCellSkipData PrecomputeSheetFontsAndLayouts CalculateColWidthData
RenderTableTag RenderColGroup RenderSizingRow RenderCell);
our $VERSION = '1.0.0';
use constant ColToCoord => [do {
my $sym = 'A';
('A', map { $sym++ } (1..27*26));
}];
#
# CONSTANTS
#
#
# Date/time constants
#
our $julian_offset = 2415019;
our $seconds_in_a_day = 24 * 60 * 60;
our $seconds_in_an_hour = 60 * 60;
#
# SHEET DATA FORMAT
#
# When parsed, the sheet is stored in a sheet data structure.
# The structure is a hash with a format as follows:
#
# Cells:
#
# The sheet's cell data is stored as the value of the "cells" key.
# The $sheet{cells} value is a hash with cell coordinates as keys
# and a hash with the cell attributes as the value. For example,
# $sheet{cells}{A1}{formula} might have a value of "SUM(A1:A10)".
#
# Not all cells need an entry nor a data structure. If they are blank and use
# default values for their attributes, then they need not be present.
#
# Cell data is stored in a hash with the following "key: value" pairs:
#
# coord: the column/row as a string, e.g., "A1"
# datavalue: the value to be used for computation and formatting for display,
# string or numeric (tolerant of numbers stored as strings)
# datatype: if present, v=numeric value, t=text value, f=formula,
# or c=constant that is not a simple number (like "$1.20")
# formula: if present, the formula (without leading "=") for computation or the constant
# valuetype: first char is main type, the following are sub-types.
# Main types are b=blank cell, n=numeric, t=text, e=error
# Examples of using sub-types would be "nt" for a numeric time value, "n$" for currency, "nl" for logical
#
# The following optional values, if present, are mainly used in rendering, overriding defaults:
#
# bt, br, bb, bl: number of border's definition
# layout: layout (vertical alignment, padding) definition number
# font: font definition number
# color: text color definition number
# bgcolor: background color definition number
# cellformat: cell format (horizontal alignment) definition number
# nontextvalueformat: custom format definition number for non-text values, e.g., numbers
# textvalueformat: custom format definition number for text values
# colspan, rowspan: number of cells to span for merged cells (only on main cell)
# cssc: custom css classname for cell, as text (no special chars)
# csss: custom css style definition
# mod: modification allowed flag "y" if present
# comment: cell comment string
#
# Sheet attributes:
# Global values, such as default column width, are stored as "attribs".
# For example, $sheet{attribs}{defaultcolwidth} = 80.
#
# The sheet attribute key/values are:
#
# lastcol - number
# lastrow - number
# defaultcolwidth - number or blank (use system default)
# defaultrowheight - not used
# defaulttextformat:format# - cell format number (alignment) for sheet default for text values
# defaultnontextformat:format# - cell format number for sheet default for non-text values (i.e., numbers)
# defaultlayout:layout# - default cell layout number in cell layout list
# defaultfont:font# - default font number in sheet font list
# defaultnontextvalueformat:valueformat# - default non-text (number) value format number in sheet valueformat list
# defaulttextvalueformat:valueformat# - default text value format number in sheet valueformat list
# defaultcolor:color# - default number for text color in sheet color list
# defaultbgcolor:color# - default number for background color in sheet color list
# circularreferencecell:coord - cell coord with a circular reference
# recalc:value - on/off (on is default). If not "off", appropriate changes to the sheet cause a recalc
# needsrecalc:value - yes/no (no is default). If "yes", formula values are not up to date
#
# The Column attributes:
# Column attributes are stored in $sheet{colattribs}.
# $sheet{colattribs}{width}{col-letter(s)}: width or blank for column
# $sheet{colattribs}{hide}{col-letter(s)}: yes/no (default is no)
#
# The Row attributes:
# Row attributes are stored in $sheet{rowattribs}.
# $sheet{rowattribs}{hide}{rownumber}: yes/no (default is no)
#
# Names are stored as:
# $sheet{names}{"name"} = {desc => "description", definition => "definition"}
#
# The lookup value lists are stored as an array of strings and a hash to do reverse lookups.
# The lookup value lists are: layout, font, color, borderstyle, cellformat, and valueformat.
# $sheet{list-name . "s"}[$num] = value
# $sheet{list-name . "hash"}{"value"} = $num
#
# The range this was copied from (for partial saves) is in $sheet{copiedfrom}.
#
#
# The following data structures are used as lookups by the save and parse code to make it
# more general and easier to extend:
#
our %sheetAttribsLongToShort = (lastcol => "c", lastrow => "r", defaultcolwidth => "w", defaultrowheight => "h",
defaulttextformat => "tf", defaultnontextformat => "ntf", defaulttextvalueformat => "tvf", defaultnontextvalueformat => "ntvf",
defaultlayout => "layout", defaultfont => "font", defaultcolor => "color", defaultbgcolor => "bgcolor",
circularreferencecell => "circularreferencecell", recalc => "recalc", needsrecalc => "needsrecalc");
our %sheetAttribsShortToLong = ("c" => "lastcol", "r" => "lastrow", "w" => "defaultcolwidth",
"h" => "defaultrowheight", "tf" => "defaulttextformat", "ntf" => "defaultnontextformat",
"tvf" => "defaulttextvalueformat", "ntvf" => "defaultnontextvalueformat",
"layout" => "defaultlayout", "font" => "defaultfont", "color" => "defaultcolor",
"bgcolor" => "defaultbgcolor", "circularreferencecell" => "circularreferencecell",
"recalc" => "recalc", "needsrecalc" => "needsrecalc");
our %sheetAttribsStyle = (lastcol => 1, lastrow => 1, defaultcolwidth => 2, defaultrowheight => 1,
defaulttextformat => 1, defaultnontextformat => 1, defaulttextvalueformat => 1, defaultnontextvalueformat => 1,
defaultlayout => 1, defaultfont => 1, defaultcolor => 1, defaultbgcolor => 1,
circularreferencecell => 2, recalc => 2, needsrecalc => 2);
our %cellAttribsStyle = (v => "v", t => "t", vt => "vt", vtf => "vtf", vtc => "vtc",
e => "decode", l => "plain", f => "plain", c => "plain", bg => "plain", cf => "plain", cvf => "plain",
ntvf => "plain", tvf => "plain", colspan => "plain", rowspan => "plain", cssc => "plain",
csss => "decode", mod => "plain", comment => "decode", b => "b");
our %cellAttribTypeLong = (e => "errors", l => "layout", f => "font", c => "color", bg => "bgcolor", cf => "cellformat",
ntvf => "nontextvalueformat", tvf => "textvalueformat", colspan => "colspan", rowspan => "rowspan", cssc => "cssc",
csss => "csss", mod => "mod", comment => "comment");
our %vlistNames = (layout => "layout", font => "font", color => "color", border => "borderstyle",
cellformat => "cellformat", valueformat => "valueformat");
#
# $newsheet = CreateSheet();
#
# Returns a new sheet structure with all of the parts initialized.
#
sub CreateSheet {
my $sheet = {
version => "", # start with not set
cells => {},
attribs => {lastcol => 1, lastrow => 1, defaultlayout => 0},
rowattribs => {hide => {}, height => {}},
colattribs => {hide => {}, width => {}},
names => {},
layouts => [],
layouthash => {},
fonts => [],
fonthash => {},
colors => [],
colorhash => {},
borderstyles => [],
borderstylehash => {},
cellformats => [],
cellformathash => {},
valueformats => [],
valueformathash => {}
};
return $sheet;
}
#
# ParseSheetSave($sheet, $str);
#
# Parses the string $str in SocialCalc Sheet Save format and sets the
# values in $sheet.
#
sub ParseSheetSave {
my ($sheet, $str) = @_;
my $maxcol = 1;
my $maxrow = 1;
$sheet->{attribs}{lastcol} = 0;
$sheet->{attribs}{lastrow} = 0;
my ($linetype, $value, $coord, $type, $rest, $cell, $cr, $style, $valuetype,
$formula, $attrib, $num, $name, $desc);
while ($str =~ /([^\r\n]+)/g) {
($linetype, $rest) = split(/:/, $1, 2);
if ($linetype eq "cell") {
($coord, $type, $rest) = split(/:/, $rest, 3);
$coord =~ tr/a-z/A-Z/; # in-place; cheaper than calling uc()
$sheet->{cells}{$coord} = $cell = {
coord => $coord,
_cache_key => (
(length($rest) > 30)
? Digest::SHA::sha1("$type:$rest")
: "$type:$rest"
),
} if $type;
$sheet->{cells}{$coord} = $cell;
while ( ($style = $cellAttribsStyle{$type}) ) {
if ($style eq "t") {
($value, $type, $rest) = split(/:/, $rest, 3);
$cell->{datavalue} = ($value =~ /\\[cnb]/) ? DecodeFromSave($value) : $value;
$cell->{datatype} = $cell->{valuetype} = "t"; # !! should be Constants.textdatadefaulttype
}
elsif ($style eq "v") {
($value, $type, $rest) = split(/:/, $rest, 3);
$cell->{datavalue} = ($value =~ /\\[cnb]/) ? DecodeFromSave($value) : $value;
$cell->{datatype} = "v";
$cell->{valuetype} = "n";
}
elsif ($style eq "vt") {
($valuetype, $value, $type, $rest) = split(/:/, $rest, 4);
$cell->{datavalue} = ($value =~ /\\[cnb]/) ? DecodeFromSave($value) : $value;
if (substr($valuetype,0,1) eq "n") {
$cell->{datatype} = "v";
}
else {
$cell->{datatype} = "t";
}
$cell->{valuetype} = $valuetype;
}
elsif ($style eq "vtf") {
($valuetype, $value, $formula, $type, $rest) = split(/:/, $rest, 5);
$cell->{datavalue} = ($value =~ /\\[cnb]/) ? DecodeFromSave($value) : $value;
$cell->{formula} = ($value =~ /\\[cnb]/) ? DecodeFromSave($formula) : $formula;
$cell->{datatype} = "f";
$cell->{valuetype} = $valuetype;
}
elsif ($style eq "vtc") {
($valuetype, $value, $formula, $type, $rest) = split(/:/, $rest, 5);
$cell->{datavalue} = ($value =~ /\\[cnb]/) ? DecodeFromSave($value) : $value;
$cell->{formula} = ($value =~ /\\[cnb]/) ? DecodeFromSave($formula) : $formula;
$cell->{datatype} = "c";
$cell->{valuetype} = $valuetype
}
elsif ($style eq "b") {
my ($t, $r, $b, $l);
($t, $r, $b, $l, $type, $rest) = split(/:/, $rest, 6);
$cell->{bt} = $t;
$cell->{br} = $r;
$cell->{bb} = $b;
$cell->{bl} = $l;
}
elsif ($style eq "plain") {
$attrib = $cellAttribTypeLong{$type};
($value, $type, $rest) = split(/:/, $rest, 3);
$cell->{$attrib} = $value;
}
elsif ($style eq "decode") {
$attrib = $cellAttribTypeLong{$type};
($value, $type, $rest) = split(/:/, $rest, 3);
$cell->{$attrib} = ($value =~ /\\[cnb]/) ? DecodeFromSave($value) : $value;
}
else {
last;
}
}
}
elsif ($linetype eq "version") {
$sheet->{version} = $rest;
}
elsif ($linetype eq "col") {
($coord, $type, $rest) = split(/:/, $rest, 3);
$coord = uc($coord); # normalize to upper case
while ($type) {
if ($type eq "w") {
($value, $type, $rest) = split(/:/, $rest, 3);
$sheet->{colattribs}{width}{$coord} = $value;
}
elsif ($type eq "hide") {
($value, $type, $rest) = split(/:/, $rest, 3);
$sheet->{colattribs}{hide}{$coord} = $value;
}
else {
last;
}
}
}
elsif ($linetype eq "row") {
($coord, $type, $rest) = split(/:/, $rest, 3);
while ($type) {
if ($type eq "h") {
($value, $type, $rest) = split(/:/, $rest, 3);
$sheet->{rowattribs}{height}{$coord} = $value;
}
elsif ($type eq "hide") {
($value, $type, $rest) = split(/:/, $rest, 3);
$sheet->{rowattribs}{hide}{$coord} = $value;
socialcalc/SocialCalcServersideUtilities.pm view on Meta::CPAN
$line .= ":$sheetAttribsLongToShort{$attrib}:$value";
}
}
$outstr .= "$line\n";
foreach my $name (keys %{$sheet->{names}}) {
$outstr .= "name:" . uc(EncodeForSave($name)) . ":" .
EncodeForSave($sheet->{names}{$name}{desc}) . ":" .
EncodeForSave($sheet->{names}{$name}{definition}) . "\n";
}
foreach my $type (keys %vlistNames) { # check each value list
my $vlist = $sheet->{$vlistNames{$type} . "s"};
for (my $i=1; $i < @$vlist; $i++) { # output all values present
my $value = EncodeForSave($vlist->[$i]);
$outstr .= "$type:$i:$value\n";
}
}
if ($range) {
$outstr .= "copiedfrom:$range\n";
}
return $outstr;
}
#
# $str = CellToString($cell)
#
# Returns string in cell save format.
#
sub CellToString {
my $cell = shift @_;
my $str = "";
return $str if (!$cell);
my $value = EncodeForSave($cell->{datavalue});
if ($cell->{datatype} eq "v") {
if ($cell->{valuetype} eq "n") {
$str .= ":v:$value";
}
else {
$str .= ":vt:$cell->{valuetype}:$value";
}
}
elsif ($cell->{datatype} eq "t") {
if ($cell->{valuetype} eq "t") { # !!! should be Constants.textdatadefaulttype
$str .= ":t:$value";
}
else {
$str .= ":vt:$cell->{valuetype}:$value";
}
}
else {
my $formula = EncodeForSave($cell->{formula});
if ($cell->{datatype} eq "f") {
$str .= ":vtf:$cell->{valuetype}:$value:$formula";
}
elsif ($cell->{datatype} eq "c") {
$str .= ":vtc:$cell->{valuetype}:$value:$formula";
}
}
if ($cell->{errors}) {
$str .= ":e:" . EncodeForSave($cell->{errors});
}
my $t = $cell->{bt} || "";
my $r = $cell->{br} || "";
my $b = $cell->{bb} || "";
my $l = $cell->{bl} || "";
if ($t || $r || $b || $l) {
$str .= ":b:$t:$r:$b:$l";
}
if ($cell->{layout}) {
$str .= ":l:$cell->{layout}";
}
if ($cell->{font}) {
$str .= ":f:$cell->{font}";
}
if ($cell->{color}) {
$str .= ":c:$cell->{color}";
}
if ($cell->{bgcolor}) {
$str .= ":bg:$cell->{bgcolor}";
}
if ($cell->{cellformat}) {
$str .= ":cf:$cell->{cellformat}";
}
if ($cell->{textvalueformat}) {
$str .= ":tvf:$cell->{textvalueformat}";
}
if ($cell->{nontextvalueformat}) {
$str .= ":ntvf:$cell->{nontextvalueformat}";
}
if ($cell->{colspan}) {
$str .= ":colspan:$cell->{colspan}";
}
if ($cell->{rowspan}) {
$str .= ":rowspan:$cell->{rowspan}";
}
if ($cell->{cssc}) {
$str .= ":cssc:$cell->{cssc}";
}
if ($cell->{csss}) {
$str .= ":csss:" . EncodeForSave($cell->{csss});
}
if ($cell->{mod}) {
$str .= ":mod:$cell->{mod}";
}
if ($cell->{comment}) {
$str .= ":comment:" . EncodeForSave($cell->{comment});
}
return $str;
}
# *************************************
#
# Rendering Code
#
socialcalc/SocialCalcServersideUtilities.pm view on Meta::CPAN
return $estring;
}
# * * * * * * * * * * * * * * * * * * * *
#
# Number Formatting code from SocialCalc 1.1.0
#
# * * * * * * * * * * * * * * * * * * * *
our %WKCStrings = (
"decimalchar" => ".",
"separatorchar" => ",",
"currencychar" => '$',
"daynames" => "Sunday Monday Tuesday Wednesday Thursday Friday Saturday",
"daynames3" => "Sun Mon Tue Wed Thu Fri Sat ",
"monthnames3" => "Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec",
"monthnames" => "January February March April May June July August September October November December",
"sheetdefaultlayoutstyle" => "padding:2px 2px 1px 2px;\nvertical-align:top;\n",
"sheetdefaultfontfamily" => "Verdana,Arial,Helvetica,sans-serif",
"linkformatstring" => '<span style="font-size:smaller;text-decoration:none !important;background-color:#66B;color:#FFF;">Link</span>', # you could make this an img tag if desired:
#"linkformatstring" => '<img border="0" src="http://www.domain.com/link.gif">',
"defaultformatdt" => 'd-mmm-yyyy h:mm:ss',
"defaultformatd" => 'd-mmm-yyyy',
"defaultformatt" => '[h]:mm:ss',
"displaytrue" => 'TRUE', # how TRUE shows when rendered
"displayfalse" => 'FALSE',
);
# # # # # # # # #
#
# $displayvalue = FormatValueForDisplay($sheet, $value, $coord, $options)
#
# # # # # # # # #
sub FormatValueForDisplay {
my ($sheet, $value, $coord, $options) = @_;
my $cell = $sheet->{cells}{$coord};
my $sheetattribs = $sheet->{attribs};
my ($valueformat, $has_parens, $has_commas, $valuetype, $valuesubtype);
# Get references to the parts
my $displayvalue = $value;
$valuetype = $cell->{valuetype}; # get type of value to determine formatting
$valuesubtype = substr($valuetype,1);
$valuetype = substr($valuetype,0,1);
if ($cell->{errors} || $valuetype eq "e") {
$displayvalue = ExpandMarkup($cell->{errors}, $sheet, $options) || $valuesubtype || "Error in cell";
return $displayvalue;
}
if ($valuetype eq "t") {
$valueformat = $sheet->{valueformats}->[($cell->{textvalueformat} || $sheetattribs->{defaulttextvalueformat})] || "";
if ($valueformat eq "formula") {
if ($cell->{datatype} eq "f") {
$displayvalue = SpecialChars("=$cell->{formula}") || " ";
}
elsif ($cell->{datatype} eq "c") {
$displayvalue = SpecialChars("'$cell->{formula}") || " ";
}
else {
$displayvalue = SpecialChars("'$displayvalue") || " ";
}
return $displayvalue;
}
$displayvalue = format_text_for_display($displayvalue, $cell->{valuetype}, $valueformat, $sheet, $options);
}
elsif ($valuetype eq "n") {
$valueformat = $cell->{nontextvalueformat};
if (length($valueformat) == 0) { # "0" is a legal value format
$valueformat = $sheetattribs->{defaultnontextvalueformat};
}
$valueformat = $sheet->{valueformats}->[$valueformat];
if (length($valueformat) == 0) {
$valueformat = "";
}
$valueformat = "" if $valueformat eq "none";
if ($valueformat eq "formula") {
if ($cell->{datatype} eq "f") {
$displayvalue = SpecialChars("=$cell->{formula}") || " ";
}
elsif ($cell->{datatype} eq "c") {
$displayvalue = SpecialChars("'$cell->{formula}") || " ";
}
else {
$displayvalue = SpecialChars("'$displayvalue") || " ";
}
return $displayvalue;
}
elsif ($valueformat eq "forcetext") {
if ($cell->{datatype} eq "f") {
$displayvalue = SpecialChars("=$cell->{formula}") || " ";
}
elsif ($cell->{datatype} eq "c") {
$displayvalue = SpecialChars($cell->{formula}) || " ";
}
else {
$displayvalue = SpecialChars($displayvalue) || " ";
}
return $displayvalue;
}
$displayvalue = format_number_for_display($displayvalue, $cell->{valuetype}, $valueformat);
}
else { # unknown type - probably blank
$displayvalue = " ";
}
return $displayvalue;
}
# # # # # # # # #
#
# $displayvalue = format_text_for_display($rawvalue, $valuetype, $valueformat, $sheetdata, $options)
#
# # # # # # # # #
sub format_text_for_display {
my ($rawvalue, $valuetype, $valueformat, $sheet, $options) = @_;
my $valuesubtype = substr($valuetype,1);
my $displayvalue = $rawvalue;
$valueformat = "" if $valueformat eq "none";
$valueformat = "" unless $valueformat =~ m/^(text-|custom|hidden)/;
if (!$valueformat || $valueformat eq "General") { # determine format from type
$valueformat = "text-html" if ($valuesubtype eq "h");
$valueformat = "text-wiki" if ($valuesubtype eq "w" or $valuesubtype eq "r");
$valueformat = "text-link" if ($valuesubtype eq "l");
$valueformat = "text-plain" unless $valuesubtype;
}
if ($valueformat eq "text-html") { # HTML - output as it as is
if ($displayvalue =~ /^<!-- wiki:(.*?) -->/s) {
my $wikitext = $1;
$wikitext =~ s/>/>/g;
$wikitext =~ s/&/&/g;
$displayvalue = ExpandWikitext($wikitext, $sheet, $options, 'text-wiki');
}
}
elsif ($valueformat =~ m/^text-wiki/) { # wiki text
$displayvalue = ExpandWikitext($displayvalue, $sheet, $options, $valueformat); # do wiki markup
}
elsif ($valueformat eq "text-url") { # text is a URL for a link with optional description
my $dvsc = SpecialChars($displayvalue);
my $dvue = URLEncode($displayvalue);
$dvue =~ s/\Q{{amp}}/%26/g;
$displayvalue = qq!<a href="$dvue">$dvsc</a>!;
}
elsif ($valueformat eq "text-link") { # more extensive link capabilities for regular web links
$displayvalue = ExpandTextLink($displayvalue, $sheet, $options, $valueformat);
}
elsif ($valueformat eq "text-image") { # text is a URL for an image
( run in 0.918 second using v1.01-cache-2.11-cpan-ba708fea25c )