App-SocialCalc-Multiplayer
view release on metacpan or search on metacpan
socialcalc/SocialCalcServersideUtilities.pm view on Meta::CPAN
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();
socialcalc/SocialCalcServersideUtilities.pm view on Meta::CPAN
}
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
#
# *************************************
#
# $context = CreateRenderContext($sheet)
#
sub CreateRenderContext {
my $sheet = shift @_;
my $context = {
sheet => $sheet,
hideRowsCols => 0, # pay attention to row/col hide settings (currently ignored)
cellIDprefix => "", # if non-null, each cell will render with an ID starting with this
defaultcolwidth => 80,
defaultlayout => "padding:2px 2px 1px 2px;vertical-align:top;",
globaldefaultfontstyle => "normal normal",
globaldefaultfontsize => "small",
globaldefaultfontfamily => "Verdana,Arial,Helvetica,sans-serif",
explicitStyles => {
skippedcell => "font-size:small;background-color:#CCC",
comment => "background-repeat:no-repeat;background-position:top right;background-image:url(images/sc-commentbg.gif);"
},
classnames => {
skippedcell => "",
comment => ""
},
# initialize calculated values to be filled in later:
cellskip => {}, # this-cell => coord of cell covering this cell (only for covered cells)
colwidth => [], # column widths, taking into account defaults
totalwidth => 0, # total table width
maxcol => 0, # max col to display, adding long spans, etc.
maxrow => 0, # max row to display, adding long spans, etc.
defaultfontstyle => "",
defaultfontsize => "",
defaultfontfamily => "",
fonts => [], # for each fontnum, {style: fs, weight: fw, size: fs, family: ff}
layouts => [], # for each layout, "padding:Tpx Rpx Bpx Lpx;vertical-align:va;"
};
}
#
# $outstr = RenderSheet($context, $options)
#
# Returns HTML for table rendering the sheet in that context.
#
# The options (passed to cell rendering code) are:
#
# newwinlinks => t/f (default is true) - whether text-link has target="_blank" by default or not
#
sub RenderSheet {
my ($context, $options) = @_;
my $sheet = $context->{sheet};
CalculateCellSkipData($context, $options);
PrecomputeSheetFontsAndLayouts($context, $options);
CalculateColWidthData($context, $options);
# Make a reasonable guess about the size of our rendered sheet, then
# pre-extend that SV. This is *critical* for mod_perl performance,
# as malloc there is much slower there than the command line.
my $outstr = ("\x00" x ($context->{maxrow} * $context->{maxcol} * 100));
$outstr = '';
$outstr .= RenderTableTag($context, $options); # start with table tag
$outstr .= RenderColGroup($context, $options); # then colgroup section
$outstr .= RenderSizingRow($context, $options); # add tiny row so all cols have something despite spans
$outstr .= "<tbody>";
my $id_prefix = $context->{cellIDprefix} || 'cell_';
for (my $row=1; $row <= $context->{maxrow}; $row++) {
socialcalc/SocialCalcServersideUtilities.pm view on Meta::CPAN
my $sheetattribs = $sheet->{attribs};
my $outstr = "";
my $tagstr = "";
my $stylestr = "";
my $classstr = "";
my $displayvalue = "";
my $cell = $orig_cell || $sheet->{cells}{$coord ||= (ColToCoord()->[$col]).$row};
if ($cell->{colspan} > 1) {
my $span = 1;
for (my $num=1; $num<$cell->{colspan}; $num++) {
if ($sheet->{colattribs}{hide}{NumberToCol($col+$num)} ne "yes") {
$span++;
}
}
$tagstr .= " " if $tagstr;
$tagstr .= qq!colspan="$span"\n!;
}
if ($cell->{rowspan} > 1) {
my $span = 1;
for (my $num=1; $num<$cell->{rowspan}; $num++) {
if ($sheet->{colattribs}{hide}{$row+$num} ne "yes") {
$span++;
}
}
$tagstr .= " " if $tagstr;
$tagstr .= qq!rowspan="$span"\n!;
}
my $num = $cell->{layout} || $sheetattribs->{defaultlayout};
if ($num) {
$stylestr .= $context->{layouts}[$num]; # use precomputed layout with "*"'s filled in
}
else {
$stylestr .= $context->{defaultlayout};
}
$num = $cell->{font} || $sheetattribs->{defaultfont};
if ($num) { # get expanded font strings in context
my $t = $context->{fonts}->[$num]; # do each - plain "font:" style sets all sorts of other values, too (Safari font-stretch problem on cssText)
$stylestr .= "font-style:$t->{style};\nfont-weight:$t->{weight};\nfont-size:$t->{size};\nfont-family:$t->{family};\n";
}
else {
if ($context->{defaultfontsize}) {
$stylestr .= "font-size:$context->{defaultfontsize};\n";
}
if ($context->{defaultfontfamily}) {
$stylestr .= "font-family:$context->{defaultfontfamily};\n";
}
}
$num = $cell->{color} || $sheetattribs->{defaultcolor};
if ($num) {
$stylestr .= "color:$sheet->{colors}->[$num];\n";
}
$num = $cell->{bgcolor} || $sheetattribs->{defaultbgcolor};
if ($num) {
$stylestr .= "background-color:$sheet->{colors}->[$num];\n";
}
$num = $cell->{cellformat};
if ($num) {
$stylestr .= "text-align:$sheet->{cellformats}->[$num];\n";
}
else {
my $t = substr($cell->{valuetype}, 0, 1);
if ($t eq "t") {
$num = $sheetattribs->{defaulttextformat};
if ($num) {
$stylestr .= "text-align:$sheet->{cellformats}->[$num];\n";
}
}
elsif ($t eq "n") {
$num = $sheetattribs->{defaultnontextformat};
if ($num) {
$stylestr .= "text-align:$sheet->{cellformats}->[$num];\n";
}
else {
$stylestr .= "text-align:right;\n";
}
}
else {
$stylestr .= "text-align:left;\n";
}
}
if ($cell->{bt} &&
($cell->{bt}==$cell->{br} && $cell->{bt}==$cell->{bb} && $cell->{bt}==$cell->{bl})) {
$stylestr .= "border:$sheet->{borderstyles}->[$cell->{bt}];\n";
}
else {
$num = $cell->{bt};
if ($num) {
$stylestr .= "border-top:$sheet->{borderstyles}->[$num];\n";
}
$num = $cell->{br};
if ($num) {
$stylestr .= "border-right:$sheet->{borderstyles}->[$num];\n";
}
$num = $cell->{bb};
if ($num) {
$stylestr .= "border-bottom:$sheet->{borderstyles}->[$num];\n";
}
$num = $cell->{bl};
if ($num) {
$stylestr .= "border-left:$sheet->{borderstyles}->[$num];\n";
}
}
if ($cell->{comment}) {
if ($context->{classnames}{comment}) {
$classstr .= " " if $classstr;
$classstr .= $context->{classnames}{comment};
}
socialcalc/SocialCalcServersideUtilities.pm view on Meta::CPAN
$str .= "<li>$2</li>";
}
next;
}
while (@closingtag) {
$str .= pop @closingtag;
}
if ($line =~ m/^(={1,5})\s(.+)\s\1$/) { # = heading =, with equal number of equals on both sides
my $neq = length($1);
$str .= "<h$neq>$2</h$neq>";
next;
}
if ($line =~ m/^(:{1,5})\s{0,1}(.+)$/) { # indent 20pts for each :
my $nindent = length($1) * 20;
$str .= "<div style=\"padding-left:${nindent}pt;\">$2</div>";
next;
}
$str .= "$line\n";
}
while (@closingtag) { # just in case any left at the end
$str .= pop @closingtag;
}
$estring = $str;
}
$estring =~ s/\n/<br>/g; # Line breaks are preserved
$estring =~ s/('*)'''(.*?)'''/$1<b>$2<\/b>/gs; # Wiki-style bold/italics
$estring =~ s/''(.*?)''/<i>$1<\/i>/gs;
$estring =~ s/\[b:(.+?)\:b]/<b>$1<\/b>/gs; # [b:text:b] for bold
$estring =~ s/\[i:(.+?)\:i]/<i>$1<\/i>/gs; # [i:text:i] for italic
$estring =~ s/\[quote:(.+?)\:quote]/<blockquote>$1<\/blockquote>/gs; # [quote:text:quote] to indent
$estring =~ s/\{\{amp}}/&/gs; # {{amp}} for ampersand
$estring =~ s/\{\{lt}}/</gs; # {{lt}} for less than
$estring =~ s/\{\{gt}}/>/gs; # {{gt}} for greater than
$estring =~ s/\{\{quot}}/"/gs; # {{quot}} for quote
$estring =~ s/\{\{lbracket}}/[/gs; # {{lbracket}} for left bracket
$estring =~ s/\{\{rbracket}}/]/gs; # {{rbracket}} for right bracket
$estring =~ s/\{\{lbrace}}/{/gs; # {{lbrace}} for brace
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) {
( run in 3.063 seconds using v1.01-cache-2.11-cpan-d8267643d1d )