lib/auto/share/dist/App-SocialCalc-Multiplayer/SocialCalcServersideUtilities.pm untested

filter: Statementstmt 0.0% 0 / 1483 Branchbran 0.0% 0 / 740 Conditioncond 0.0% 0 / 259 MC/DCmcdc 0.0% 0 / 146 Subroutinesub 0.0% 0 / 47 Podpod 0.0% 0 / 41 totaltotal 0.0% 0 / 2716 CC 371 SCAR 82.4
CC
371
Cov
0%
format_number_with_format_string
12432.0
ParseSheetSave
1640.0
parse_format_string
1482.0
CRAP 3787.0
1 package Socialtext::SocialCalcServersideUtilities;
2 #
3 # SocialCalcServersideUtilites.pm
4 #
5  
6 #
7 # Based in part on the SocialCalc 1.1 code, which is based on wikiCalc 1.0.
8 # SocialCalc 1.1 was:
9 # Portions (c) Copyright 2005, 2006, 2007 Software Garden, Inc.
10 # All Rights Reserved.
11 # Portions (c) Copyright 2007 Socialtext, Inc.
12 # All Rights Reserved.
13 # This code is also based in part on the SocialCalc JavaScript implementation of 2008.
14 #
15 # The new rights in this new code:
16 #
17 # (c) Copyright 2008 Socialtext, Inc.
18 # All Rights Reserved.
19 #
20 # This code has NOT been released under an Open Source license at this point.
21 # It is not to be used without permission of Socialtext, Inc.
22 #
23 # Known problems:
24 # Doesn't handle UTF-8 currency symbols (and who knows what else)
25 # Doesn't handle hidden rows and columns correctly.
26 #
27  
28 use strict;
29 use utf8;
30 use Time::Local;
31 use Digest::SHA ();
32  
33 require Exporter;
34 our @ISA = qw(Exporter);
35 our @EXPORT = qw(CreateSheet ParseSheetSave CreateSheetSave DecodeSpreadsheetSave
36 CreateCellHTML CreateCellHTMLSave
37 CoordToCR CRToCoord ParseRange
38 CreateCSV
39 CellToString
40 CreateRenderContext RenderSheet CreateCSV
41 CalculateCellSkipData PrecomputeSheetFontsAndLayouts CalculateColWidthData
42 RenderTableTag RenderColGroup RenderSizingRow RenderCell);
43 our $VERSION = '1.0.0';
44  
45 use constant ColToCoord => [do {
46 my $sym = 'A';
47 ('A', map { $sym++ } (1..27*26));
48 }];
49  
50 #
51 # CONSTANTS
52 #
53  
54 #
55 # Date/time constants
56 #
57  
58 our $julian_offset = 2415019;
59 our $seconds_in_a_day = 24 * 60 * 60;
60 our $seconds_in_an_hour = 60 * 60;
61  
62 #
63 # SHEET DATA FORMAT
64 #
65 # When parsed, the sheet is stored in a sheet data structure.
66 # The structure is a hash with a format as follows:
67 #
68 # Cells:
69 #
70 # The sheet's cell data is stored as the value of the "cells" key.
71 # The $sheet{cells} value is a hash with cell coordinates as keys
72 # and a hash with the cell attributes as the value. For example,
73 # $sheet{cells}{A1}{formula} might have a value of "SUM(A1:A10)".
74 #
75 # Not all cells need an entry nor a data structure. If they are blank and use
76 # default values for their attributes, then they need not be present.
77 #
78 # Cell data is stored in a hash with the following "key: value" pairs:
79 #
80 # coord: the column/row as a string, e.g., "A1"
81 # datavalue: the value to be used for computation and formatting for display,
82 # string or numeric (tolerant of numbers stored as strings)
83 # datatype: if present, v=numeric value, t=text value, f=formula,
84 # or c=constant that is not a simple number (like "$1.20")
85 # formula: if present, the formula (without leading "=") for computation or the constant
86 # valuetype: first char is main type, the following are sub-types.
87 # Main types are b=blank cell, n=numeric, t=text, e=error
88 # Examples of using sub-types would be "nt" for a numeric time value, "n$" for currency, "nl" for logical
89 #
90 # The following optional values, if present, are mainly used in rendering, overriding defaults:
91 #
92 # bt, br, bb, bl: number of border's definition
93 # layout: layout (vertical alignment, padding) definition number
94 # font: font definition number
95 # color: text color definition number
96 # bgcolor: background color definition number
97 # cellformat: cell format (horizontal alignment) definition number
98 # nontextvalueformat: custom format definition number for non-text values, e.g., numbers
99 # textvalueformat: custom format definition number for text values
100 # colspan, rowspan: number of cells to span for merged cells (only on main cell)
101 # cssc: custom css classname for cell, as text (no special chars)
102 # csss: custom css style definition
103 # mod: modification allowed flag "y" if present
104 # comment: cell comment string
105 #
106 # Sheet attributes:
107 # Global values, such as default column width, are stored as "attribs".
108 # For example, $sheet{attribs}{defaultcolwidth} = 80.
109 #
110 # The sheet attribute key/values are:
111 #
112 # lastcol - number
113 # lastrow - number
114 # defaultcolwidth - number or blank (use system default)
115 # defaultrowheight - not used
116 # defaulttextformat:format# - cell format number (alignment) for sheet default for text values
117 # defaultnontextformat:format# - cell format number for sheet default for non-text values (i.e., numbers)
118 # defaultlayout:layout# - default cell layout number in cell layout list
119 # defaultfont:font# - default font number in sheet font list
120 # defaultnontextvalueformat:valueformat# - default non-text (number) value format number in sheet valueformat list
121 # defaulttextvalueformat:valueformat# - default text value format number in sheet valueformat list
122 # defaultcolor:color# - default number for text color in sheet color list
123 # defaultbgcolor:color# - default number for background color in sheet color list
124 # circularreferencecell:coord - cell coord with a circular reference
125 # recalc:value - on/off (on is default). If not "off", appropriate changes to the sheet cause a recalc
126 # needsrecalc:value - yes/no (no is default). If "yes", formula values are not up to date
127 #
128 # The Column attributes:
129 # Column attributes are stored in $sheet{colattribs}.
130 # $sheet{colattribs}{width}{col-letter(s)}: width or blank for column
131 # $sheet{colattribs}{hide}{col-letter(s)}: yes/no (default is no)
132 #
133 # The Row attributes:
134 # Row attributes are stored in $sheet{rowattribs}.
135 # $sheet{rowattribs}{hide}{rownumber}: yes/no (default is no)
136 #
137 # Names are stored as:
138 # $sheet{names}{"name"} = {desc => "description", definition => "definition"}
139 #
140 # The lookup value lists are stored as an array of strings and a hash to do reverse lookups.
141 # The lookup value lists are: layout, font, color, borderstyle, cellformat, and valueformat.
142 # $sheet{list-name . "s"}[$num] = value
143 # $sheet{list-name . "hash"}{"value"} = $num
144 #
145 # The range this was copied from (for partial saves) is in $sheet{copiedfrom}.
146 #
147  
148 #
149 # The following data structures are used as lookups by the save and parse code to make it
150 # more general and easier to extend:
151 #
152  
153 our %sheetAttribsLongToShort = (lastcol => "c", lastrow => "r", defaultcolwidth => "w", defaultrowheight => "h",
154 defaulttextformat => "tf", defaultnontextformat => "ntf", defaulttextvalueformat => "tvf", defaultnontextvalueformat => "ntvf",
155 defaultlayout => "layout", defaultfont => "font", defaultcolor => "color", defaultbgcolor => "bgcolor",
156 circularreferencecell => "circularreferencecell", recalc => "recalc", needsrecalc => "needsrecalc");
157  
158 our %sheetAttribsShortToLong = ("c" => "lastcol", "r" => "lastrow", "w" => "defaultcolwidth",
159 "h" => "defaultrowheight", "tf" => "defaulttextformat", "ntf" => "defaultnontextformat",
160 "tvf" => "defaulttextvalueformat", "ntvf" => "defaultnontextvalueformat",
161 "layout" => "defaultlayout", "font" => "defaultfont", "color" => "defaultcolor",
162 "bgcolor" => "defaultbgcolor", "circularreferencecell" => "circularreferencecell",
163 "recalc" => "recalc", "needsrecalc" => "needsrecalc");
164  
165 our %sheetAttribsStyle = (lastcol => 1, lastrow => 1, defaultcolwidth => 2, defaultrowheight => 1,
166 defaulttextformat => 1, defaultnontextformat => 1, defaulttextvalueformat => 1, defaultnontextvalueformat => 1,
167 defaultlayout => 1, defaultfont => 1, defaultcolor => 1, defaultbgcolor => 1,
168 circularreferencecell => 2, recalc => 2, needsrecalc => 2);
169  
170 our %cellAttribsStyle = (v => "v", t => "t", vt => "vt", vtf => "vtf", vtc => "vtc",
171 e => "decode", l => "plain", f => "plain", c => "plain", bg => "plain", cf => "plain", cvf => "plain",
172 ntvf => "plain", tvf => "plain", colspan => "plain", rowspan => "plain", cssc => "plain",
173 csss => "decode", mod => "plain", comment => "decode", b => "b");
174  
175 our %cellAttribTypeLong = (e => "errors", l => "layout", f => "font", c => "color", bg => "bgcolor", cf => "cellformat",
176 ntvf => "nontextvalueformat", tvf => "textvalueformat", colspan => "colspan", rowspan => "rowspan", cssc => "cssc",
177 csss => "csss", mod => "mod", comment => "comment");
178  
179 our %vlistNames = (layout => "layout", font => "font", color => "color", border => "borderstyle",
180 cellformat => "cellformat", valueformat => "valueformat");
181  
182 #
183 # $newsheet = CreateSheet();
184 #
185 # Returns a new sheet structure with all of the parts initialized.
186 #
187  
188 sub CreateSheet {
189  
190 my $sheet = {
191 version => "", # start with not set
192 cells => {},
193 attribs => {lastcol => 1, lastrow => 1, defaultlayout => 0},
194 rowattribs => {hide => {}, height => {}},
195 colattribs => {hide => {}, width => {}},
196 names => {},
197 layouts => [],
198 layouthash => {},
199 fonts => [],
200 fonthash => {},
201 colors => [],
202 colorhash => {},
203 borderstyles => [],
204 borderstylehash => {},
205 cellformats => [],
206 cellformathash => {},
207 valueformats => [],
208 valueformathash => {}
209 };
210  
211 return $sheet;
212  
213 }
214  
215 #
216 # ParseSheetSave($sheet, $str);
217 #
218 # Parses the string $str in SocialCalc Sheet Save format and sets the
219 # values in $sheet.
220 #
221  
222 sub ParseSheetSave {
223  
224 my ($sheet, $str) = @_;
225  
226 my $maxcol = 1;
227 my $maxrow = 1;
228 $sheet->{attribs}{lastcol} = 0;
229 $sheet->{attribs}{lastrow} = 0;
230  
231 my ($linetype, $value, $coord, $type, $rest, $cell, $cr, $style, $valuetype,
232 $formula, $attrib, $num, $name, $desc);
233  
234 while ($str =~ /([^\r\n]+)/g) {
235 ($linetype, $rest) = split(/:/, $1, 2);
236  
237 if ($linetype eq "cell") {
238 ($coord, $type, $rest) = split(/:/, $rest, 3);
239 $coord =~ tr/a-z/A-Z/; # in-place; cheaper than calling uc()
240 $sheet->{cells}{$coord} = $cell = {
241 coord => $coord,
242 _cache_key => (
243 (length($rest) > 30)
244 ? Digest::SHA::sha1("$type:$rest")
245 : "$type:$rest"
246 ),
247 } if $type;
248  
249 $sheet->{cells}{$coord} = $cell;
250 while ( ($style = $cellAttribsStyle{$type}) ) {
251 if ($style eq "t") {
252 ($value, $type, $rest) = split(/:/, $rest, 3);
253 $cell->{datavalue} = ($value =~ /\\[cnb]/) ? DecodeFromSave($value) : $value;
254 $cell->{datatype} = $cell->{valuetype} = "t"; # !! should be Constants.textdatadefaulttype
255 }
256 elsif ($style eq "v") {
257 ($value, $type, $rest) = split(/:/, $rest, 3);
258 $cell->{datavalue} = ($value =~ /\\[cnb]/) ? DecodeFromSave($value) : $value;
259 $cell->{datatype} = "v";
260 $cell->{valuetype} = "n";
261 }
262 elsif ($style eq "vt") {
263 ($valuetype, $value, $type, $rest) = split(/:/, $rest, 4);
264 $cell->{datavalue} = ($value =~ /\\[cnb]/) ? DecodeFromSave($value) : $value;
265 if (substr($valuetype,0,1) eq "n") {
266 $cell->{datatype} = "v";
267 }
268 else {
269 $cell->{datatype} = "t";
270 }
271 $cell->{valuetype} = $valuetype;
272 }
273 elsif ($style eq "vtf") {
274 ($valuetype, $value, $formula, $type, $rest) = split(/:/, $rest, 5);
275 $cell->{datavalue} = ($value =~ /\\[cnb]/) ? DecodeFromSave($value) : $value;
276 $cell->{formula} = ($value =~ /\\[cnb]/) ? DecodeFromSave($formula) : $formula;
277 $cell->{datatype} = "f";
278 $cell->{valuetype} = $valuetype;
279 }
280 elsif ($style eq "vtc") {
281 ($valuetype, $value, $formula, $type, $rest) = split(/:/, $rest, 5);
282 $cell->{datavalue} = ($value =~ /\\[cnb]/) ? DecodeFromSave($value) : $value;
283 $cell->{formula} = ($value =~ /\\[cnb]/) ? DecodeFromSave($formula) : $formula;
284 $cell->{datatype} = "c";
285 $cell->{valuetype} = $valuetype
286 }
287 elsif ($style eq "b") {
288 my ($t, $r, $b, $l);
289 ($t, $r, $b, $l, $type, $rest) = split(/:/, $rest, 6);
290 $cell->{bt} = $t;
291 $cell->{br} = $r;
292 $cell->{bb} = $b;
293 $cell->{bl} = $l;
294 }
295 elsif ($style eq "plain") {
296 $attrib = $cellAttribTypeLong{$type};
297 ($value, $type, $rest) = split(/:/, $rest, 3);
298 $cell->{$attrib} = $value;
299 }
300 elsif ($style eq "decode") {
301 $attrib = $cellAttribTypeLong{$type};
302 ($value, $type, $rest) = split(/:/, $rest, 3);
303 $cell->{$attrib} = ($value =~ /\\[cnb]/) ? DecodeFromSave($value) : $value;
304 }
305 else {
306 last;
307 }
308 }
309 }
310  
311 elsif ($linetype eq "version") {
312 $sheet->{version} = $rest;
313 }
314  
315  
316 elsif ($linetype eq "col") {
317 ($coord, $type, $rest) = split(/:/, $rest, 3);
318 $coord = uc($coord); # normalize to upper case
319 while ($type) {
320 if ($type eq "w") {
321 ($value, $type, $rest) = split(/:/, $rest, 3);
322 $sheet->{colattribs}{width}{$coord} = $value;
323 }
324 elsif ($type eq "hide") {
325 ($value, $type, $rest) = split(/:/, $rest, 3);
326 $sheet->{colattribs}{hide}{$coord} = $value;
327 }
328 else {
329 last;
330 }
331 }
332 }
333  
334 elsif ($linetype eq "row") {
335 ($coord, $type, $rest) = split(/:/, $rest, 3);
336 while ($type) {
337 if ($type eq "h") {
338 ($value, $type, $rest) = split(/:/, $rest, 3);
339 $sheet->{rowattribs}{height}{$coord} = $value;
340 }
341 elsif ($type eq "hide") {
342 ($value, $type, $rest) = split(/:/, $rest, 3);
343 $sheet->{rowattribs}{hide}{$coord} = $value;
344 }
345 else {
346 last;
347 }
348 }
349 }
350  
351 elsif ($linetype eq "sheet") {
352 ($type, $rest) = split(/:/, $rest, 2);
353 while ($type) {
354 $attrib = $sheetAttribsShortToLong{$type};
355 $style = $sheetAttribsStyle{$attrib};
356 last if !$style; # process known, non-null types
357 if ($style==1) { # plain number
358 ($value, $type, $rest) = split(/:/, $rest, 3);
359 $sheet->{attribs}{$attrib} = $value-0;
360 }
361 elsif ($style==2) { # text
362 ($value, $type, $rest) = split(/:/, $rest, 3);
363 $sheet->{attribs}{$attrib} = $value;
364 }
365 else {
366 last;
367 }
368 }
369 }
370  
371 elsif ($linetype eq "name") {
372 ($name, $desc, $value) = split(/:/, $rest, 3);
373 $name = uc (DecodeFromSave($name));
374 $sheet->{names}{$name}{desc} = ($desc =~ /\\[cnb]/) ? DecodeFromSave($desc) : $desc;
375 $sheet->{names}{$name}{definition} = ($value =~ /\\[cnb]/) ? DecodeFromSave($value) : $value;
376 }
377  
378 elsif ($vlistNames{$linetype}) { # if one of the value lists, process
379 $style = $vlistNames{$linetype}; # get base name
380 ($num, $value) = split(/:/, $rest, 2);
381 $value = DecodeFromSave($value) if $value =~ /\\[cnb]/;
382 $sheet->{$style . "s"}->[$num] = $value;
383 $sheet->{$style . "hash"}{$value} = $num;
384 }
385  
386 elsif ($linetype eq "") { # skip blank lines
387 }
388  
389 }
390  
391 for (keys %{$sheet->{cells}}) {
392 s/^([A-Z]+)//; # Take only 0-9; $_ is now the row and $1 is now the col
393 $maxcol = $1 if $1 gt $maxcol or length($1) > length($maxcol);
394 $maxrow = $_ if $_ > $maxrow;
395 }
396 $maxcol = ColToNumber($maxcol);
397  
398 $sheet->{attribs}{lastcol} ||= $maxcol || 1;
399 $sheet->{attribs}{lastrow} ||= $maxrow || 1;
400  
401 }
402  
403  
404 #
405 # $outstr = CreateSheetSave($sheet, $range);
406 #
407 # Returns the sheet as a string in SocialCalc Sheet Save format.
408 # If $range is present, it is in the form similar to "A1:B7",
409 # and just those cells will be used.
410 #
411  
412 sub CreateSheetSave {
413  
414 my ($sheet, $range) = @_;
415 my $outstr;
416  
417 my $prange;
418  
419 if ($range) {
420 $prange = ParseRange($range);
421 }
422 else {
423 $prange = ();
424 $prange->{cr1}{row} = 1;
425 $prange->{cr1}{col} = 1;
426 $prange->{cr2}{row} = $sheet->{attribs}{lastrow};
427 $prange->{cr2}{col} = $sheet->{attribs}{lastcol};
428 }
429  
430 my $cr1 = $prange->{cr1};
431 my $cr2 = $prange->{cr2};
432  
433 for (my $row=$cr1->{row}; $row <= $cr2->{row}; $row++) {
434 for (my $col=$cr1->{col}; $col <= $cr2->{col}; $col++) {
435 my $coord = (ColToCoord()->[$col]).$row;
436 my $cell = $sheet->{cells}{$coord};
437 next if !$cell;
438 my $line = CellToString($cell);
439 next if !$line; # ignore completely empty cells
440 $line = "cell:$coord$line\n";
441 $outstr .= $line;
442 }
443 }
444  
445 for (my $col=1; $col <= $sheet->{attribs}{lastcol}; $col++) {
446 my $coord = NumberToCol($col);
447 if ($sheet->{colattribs}{width}{$coord}) {
448 $outstr .= "col:$coord:w:$sheet->{colattribs}{width}{$coord}\n";
449 }
450 if ($sheet->{colattribs}{hide}{$coord}) {
451 $outstr .= "col:$coord:hide:$sheet->{colattribs}{hide}{$coord}\n";
452 }
453 }
454  
455 for (my $row=1; $row <= $sheet->{attribs}{lastrow}; $row++) {
456 if ($sheet->{rowattribs}{height}{$row}) {
457 $outstr .= "row:$row:h:$sheet->{rowattribs}{height}{$row}\n";
458 }
459 if ($sheet->{rowattribs}{hide}{$row}) {
460 $outstr .= "row:$row:hide:$sheet->{rowattribs}{hide}{$row}\n";
461 }
462 }
463  
464 my $line = "sheet";
465 for my $attrib (keys %sheetAttribsLongToShort) {
466 my $value = EncodeForSave($sheet->{attribs}{$attrib});
467 if ($value) {
468 $line .= ":$sheetAttribsLongToShort{$attrib}:$value";
469 }
470 }
471 $outstr .= "$line\n";
472  
473 foreach my $name (keys %{$sheet->{names}}) {
474 $outstr .= "name:" . uc(EncodeForSave($name)) . ":" .
475 EncodeForSave($sheet->{names}{$name}{desc}) . ":" .
476 EncodeForSave($sheet->{names}{$name}{definition}) . "\n";
477 }
478  
479 foreach my $type (keys %vlistNames) { # check each value list
480 my $vlist = $sheet->{$vlistNames{$type} . "s"};
481 for (my $i=1; $i < @$vlist; $i++) { # output all values present
482 my $value = EncodeForSave($vlist->[$i]);
483 $outstr .= "$type:$i:$value\n";
484 }
485 }
486  
487 if ($range) {
488 $outstr .= "copiedfrom:$range\n";
489 }
490  
491 return $outstr;
492  
493 }
494  
495  
496 #
497 # $str = CellToString($cell)
498 #
499 # Returns string in cell save format.
500 #
501  
502 sub CellToString {
503  
504 my $cell = shift @_;
505  
506 my $str = "";
507  
508 return $str if (!$cell);
509  
510 my $value = EncodeForSave($cell->{datavalue});
511 if ($cell->{datatype} eq "v") {
512 if ($cell->{valuetype} eq "n") {
513 $str .= ":v:$value";
514 }
515 else {
516 $str .= ":vt:$cell->{valuetype}:$value";
517 }
518 }
519 elsif ($cell->{datatype} eq "t") {
520 if ($cell->{valuetype} eq "t") { # !!! should be Constants.textdatadefaulttype
521 $str .= ":t:$value";
522 }
523 else {
524 $str .= ":vt:$cell->{valuetype}:$value";
525 }
526 }
527 else {
528 my $formula = EncodeForSave($cell->{formula});
529 if ($cell->{datatype} eq "f") {
530 $str .= ":vtf:$cell->{valuetype}:$value:$formula";
531 }
532 elsif ($cell->{datatype} eq "c") {
533 $str .= ":vtc:$cell->{valuetype}:$value:$formula";
534 }
535 }
536 if ($cell->{errors}) {
537 $str .= ":e:" . EncodeForSave($cell->{errors});
538 }
539 my $t = $cell->{bt} || "";
540 my $r = $cell->{br} || "";
541 my $b = $cell->{bb} || "";
542 my $l = $cell->{bl} || "";
543 if ($t || $r || $b || $l) {
544 $str .= ":b:$t:$r:$b:$l";
545 }
546 if ($cell->{layout}) {
547 $str .= ":l:$cell->{layout}";
548 }
549 if ($cell->{font}) {
550 $str .= ":f:$cell->{font}";
551 }
552 if ($cell->{color}) {
553 $str .= ":c:$cell->{color}";
554 }
555 if ($cell->{bgcolor}) {
556 $str .= ":bg:$cell->{bgcolor}";
557 }
558 if ($cell->{cellformat}) {
559 $str .= ":cf:$cell->{cellformat}";
560 }
561 if ($cell->{textvalueformat}) {
562 $str .= ":tvf:$cell->{textvalueformat}";
563 }
564 if ($cell->{nontextvalueformat}) {
565 $str .= ":ntvf:$cell->{nontextvalueformat}";
566 }
567 if ($cell->{colspan}) {
568 $str .= ":colspan:$cell->{colspan}";
569 }
570 if ($cell->{rowspan}) {
571 $str .= ":rowspan:$cell->{rowspan}";
572 }
573 if ($cell->{cssc}) {
574 $str .= ":cssc:$cell->{cssc}";
575 }
576 if ($cell->{csss}) {
577 $str .= ":csss:" . EncodeForSave($cell->{csss});
578 }
579 if ($cell->{mod}) {
580 $str .= ":mod:$cell->{mod}";
581 }
582 if ($cell->{comment}) {
583 $str .= ":comment:" . EncodeForSave($cell->{comment});
584 }
585  
586 return $str;
587  
588 }
589  
590 # *************************************
591 #
592 # Rendering Code
593 #
594 # *************************************
595  
596 #
597 # $context = CreateRenderContext($sheet)
598 #
599  
600 sub CreateRenderContext {
601  
602 my $sheet = shift @_;
603  
604 my $context = {
605  
606 sheet => $sheet,
607 hideRowsCols => 0, # pay attention to row/col hide settings (currently ignored)
608 cellIDprefix => "", # if non-null, each cell will render with an ID starting with this
609 defaultcolwidth => 80,
610 defaultlayout => "padding:2px 2px 1px 2px;vertical-align:top;",
611  
612 globaldefaultfontstyle => "normal normal",
613 globaldefaultfontsize => "small",
614 globaldefaultfontfamily => "Verdana,Arial,Helvetica,sans-serif",
615  
616 explicitStyles => {
617 skippedcell => "font-size:small;background-color:#CCC",
618 comment => "background-repeat:no-repeat;background-position:top right;background-image:url(images/sc-commentbg.gif);"
619 },
620  
621 classnames => {
622 skippedcell => "",
623 comment => ""
624 },
625  
626 # initialize calculated values to be filled in later:
627  
628 cellskip => {}, # this-cell => coord of cell covering this cell (only for covered cells)
629 colwidth => [], # column widths, taking into account defaults
630 totalwidth => 0, # total table width
631 maxcol => 0, # max col to display, adding long spans, etc.
632 maxrow => 0, # max row to display, adding long spans, etc.
633 defaultfontstyle => "",
634 defaultfontsize => "",
635 defaultfontfamily => "",
636 fonts => [], # for each fontnum, {style: fs, weight: fw, size: fs, family: ff}
637 layouts => [], # for each layout, "padding:Tpx Rpx Bpx Lpx;vertical-align:va;"
638  
639 };
640  
641 }
642  
643 #
644 # $outstr = RenderSheet($context, $options)
645 #
646 # Returns HTML for table rendering the sheet in that context.
647 #
648 # The options (passed to cell rendering code) are:
649 #
650 # newwinlinks => t/f (default is true) - whether text-link has target="_blank" by default or not
651 #
652  
653 sub RenderSheet {
654  
655 my ($context, $options) = @_;
656 my $sheet = $context->{sheet};
657  
658 CalculateCellSkipData($context, $options);
659 PrecomputeSheetFontsAndLayouts($context, $options);
660 CalculateColWidthData($context, $options);
661  
662 # Make a reasonable guess about the size of our rendered sheet, then
663 # pre-extend that SV. This is *critical* for mod_perl performance,
664 # as malloc there is much slower there than the command line.
665 my $outstr = ("\x00" x ($context->{maxrow} * $context->{maxcol} * 100));
666 $outstr = '';
667  
668 $outstr .= RenderTableTag($context, $options); # start with table tag
669  
670 $outstr .= RenderColGroup($context, $options); # then colgroup section
671  
672 $outstr .= RenderSizingRow($context, $options); # add tiny row so all cols have something despite spans
673  
674 $outstr .= "<tbody>";
675  
676 my $id_prefix = $context->{cellIDprefix} || 'cell_';
677  
678 for (my $row=1; $row <= $context->{maxrow}; $row++) {
679 $outstr .= qq!<tr><th height="1"><span>$row</span></th>!;
680 for (my $col=1; $col <= $context->{maxcol}; $col++) {
681 my $coord = (ColToCoord()->[$col]).$row;
682  
683 # Skip if within a span masked by merged cells
684 next if $context->{cellskip}{$coord};
685  
686 my $cell = $context->{sheet}{cells}{$coord};
687  
688 $outstr .= qq{<td id="$id_prefix$coord"\n} . (
689 $context->{_render_cache_cell_html}{$cell->{_cache_key}}
690 ||= RenderCell($context, $row, $col, $options, $coord, $cell || { datatype => 'b' })
691 );
692 }
693 $outstr .= "</tr>";
694 }
695  
696 $outstr .= "</tbody>";
697 $outstr .= "</table>";
698  
699 my $stylestr = "<style><!--\n";
700 while (my ($short_class, $long_class) = each %{ $context->{_render_cache_cell_style_short} }) {
701 my $style = delete $context->{_render_cache_cell_style_long}{$long_class};
702 # Here the ", td.$long_class" is not strictly neccessary, but contained
703 # for the case of possible e.g. concating two stylesheet renderings.
704 $stylestr .= "td.$short_class, td.$long_class {\n$style}\n";
705 }
706  
707 while (my ($long_class, $style) = each %{ $context->{_render_cache_cell_style_long} }) {
708 $stylestr .= "td.$long_class {\n$style}\n";
709 }
710 $stylestr .= "--></style>";
711  
712 delete $context->{_render_cache_cell_style_long};
713 delete $context->{_render_cache_cell_style_short};
714 delete $context->{_render_cache_cell_html};
715  
716 return $stylestr . $outstr;
717  
718 }
719  
720 #
721 # CalculateCellSkipData($context, $options)
722 #
723 # Figures out which cells are to be skipped because of row and column spans.
724 #
725  
726 sub CalculateCellSkipData {
727  
728 my ($context, $options) = @_;
729  
730 my $sheet = $context->{sheet};
731 my $sheetattribs = $sheet->{attribs};
732  
733 $context->{maxcol} = $sheetattribs->{lastcol};
734 $context->{maxrow} = $sheetattribs->{lastrow};
735 $context->{cellskip} = {}; # reset
736  
737 # Calculate cellskip data
738  
739 for my $row (1..$sheetattribs->{lastrow}) {
740 for my $col (1..$sheetattribs->{lastcol}) { # look for spans and set cellskip for skipped cells
741 my $coord = (ColToCoord()->[$col]).$row;
742 my $cell = $sheet->{cells}{$coord} or next;
743 next unless exists $cell->{colspan} or exists $cell->{rowspan};
744  
745 # don't look at undefined cells (they have no spans) or skipped cells
746 next if $context->{cellskip}{$coord};
747  
748 my $colspan = $cell->{colspan} || 1;
749 my $rowspan = $cell->{rowspan} || 1;
750 if ($colspan > 1 or $rowspan > 1) {
751 for (my $skiprow=$row; $skiprow<$row+$rowspan; $skiprow++) {
752 for (my $skipcol=$col; $skipcol<$col+$colspan; $skipcol++) { # do the setting on individual cells
753 my $skipcoord = (ColToCoord()->[$skipcol]).$skiprow;
754 if ($skipcoord ne $coord) { # flag other cells to point back here
755 $context->{cellskip}{$skipcoord} = $coord;
756 }
757 if ($skiprow>$context->{maxrow}) {
758 $context->{maxrow} = $skiprow;
759 }
760 if ($skipcol>$context->{maxcol}) {
761 $context->{maxcol} = $skipcol;
762 }
763 }
764 }
765 }
766 }
767 }
768  
769 return;
770  
771 }
772  
773  
774 #
775 # PrecomputeSheetFontsAndLayouts($context, $options)
776 #
777 # Fills out the fonts and layouts arrays in the context.
778 #
779  
780 sub PrecomputeSheetFontsAndLayouts {
781  
782 my ($context, $options) = @_;
783 my $sheet = $context->{sheet};
784 my $attribs = $sheet->{attribs};
785  
786 if ($attribs->{defaultfont}) {
787 my $defaultfont = $sheet->{fonts}->[$attribs->{defaultfont}];
788 $defaultfont =~ s/^\*/$context->{globaldefaultfontstyle}/e;
789 $defaultfont =~ s/(.+)\*(.+)/$1.$context->{globaldefaultfontsize}.$2/e;
790 $defaultfont =~ s/\*$/$context->{globaldefaultfontfamily}/e;
791 $defaultfont =~ m/^(\S+? \S+?) (\S+?) (\S.*)$/;
792 $context->{defaultfontstyle} = $1;
793 $context->{defaultfontsize} = $2;
794 $context->{defaultfontfamily} = $3;
795 }
796 else {
797 $context->{defaultfontstyle} = $context->{globaldefaultfontstyle};
798 $context->{defaultfontsize} = $context->{globaldefaultfontsize};
799 $context->{defaultfontfamily} = $context->{globaldefaultfontfamily};
800 }
801  
802 for (my $num=1; $num<@{$sheet->{fonts}}; $num++) { # precompute fonts by filling in the *'s
803 my $s = $sheet->{fonts}->[$num];
804 $s =~ s/^\*/$context->{defaultfontstyle}/e;
805 $s =~ s/(.+)\*(.+)/$1.$context->{defaultfontsize}.$2/e;
806 $s =~ s/\*$/$context->{defaultfontfamily}/e;
807 $s =~ m/^(\S+?) (\S+?) (\S+?) (\S.*)$/;
808 $context->{fonts}->[$num] = {style => $1, weight => $2, size => $3, family => $4};
809 }
810  
811 my $layoutre = qr/^padding:\s*(\S+)\s+(\S+)\s+(\S+)\s+(\S+);vertical-align:\s*(\S+);/;
812 my @dparts = ($context->{defaultlayout} =~ m/$layoutre/);
813 my @sparts;
814 if ($attribs->{defaultlayout}) {
815 @sparts = ($sheet->{layouts}->[$attribs->{defaultlayout}] =~ m/$layoutre/); # get sheet defaults, if set
816 }
817 else {
818 @sparts = ("*", "*", "*", "*", "*");
819 }
820  
821 for (my $num=1; $num<@{$sheet->{layouts}}; $num++) { # precompute layouts by filling in the *'s
822 my $s = $sheet->{layouts}->[$num];
823 my @parts = ($s =~ m/$layoutre/);
824 for (my $i=0; $i<=4; $i++) {
825 if ($parts[$i] eq "*") {
826 $parts[$i] = ($sparts[$i] ne "*" ? $sparts[$i] : $dparts[$i]); # if *, sheet default or built-in
827 }
828 }
829 $context->{layouts}->[$num] = "padding:$parts[0] $parts[1] $parts[2] $parts[3];vertical-align:$parts[4];";
830 }
831  
832 return;
833  
834 }
835  
836  
837 #
838 # CalculateColWidthData($context, $options)
839 #
840 # Saves the width of all the columns, taking the default into account,
841 # and calculates the total width of the table.
842 #
843 # Do CalculateCellSkipData first to calculate the max col and row.
844 #
845  
846 sub CalculateColWidthData {
847  
848 my ($context, $options) = @_;
849 my $sheet = $context->{sheet};
850  
851 my $totalwidth = 0;
852  
853 for (my $col=1; $col <= $context->{maxcol}; $col++) {
854 my $colwidth = $sheet->{colattribs}{width}{NumberToCol($col)} ||
855 $sheet->{attribs}->{defaultcolwidth} || $context->{defaultcolwidth};
856 if ($colwidth eq "blank" || $colwidth eq "auto" || $colwidth eq "") {
857 $colwidth = "";
858 }
859 $context->{colwidth}->[$col] = "$colwidth";
860 $totalwidth += ($colwidth && $colwidth>0) ? $colwidth : 10;
861 }
862  
863 $context->{totalwidth} = $totalwidth;
864  
865 return;
866  
867 }
868  
869  
870 #
871 # $outstr = RenderTableTag($context, $options)
872 #
873 # Returns "<table ...>"
874 #
875  
876 sub RenderTableTag {
877  
878 my ($context, $options) = @_;
879  
880 return join "\n",
881 qw(<table class="st-ss" cellspacing="0" cellpadding="0" style="border-collapse:collapse;),
882 qq/width:$context->{totalwidth}px"/,
883 ">";
884 }
885  
886 #
887 # $outstr = RenderColGroup($context, $options)
888 #
889 # Returns "<colgroup><col width=...>...</colgroup>"
890 #
891  
892 sub RenderColGroup {
893  
894 my ($context, $options) = @_;
895 my $colwidths = $context->{colwidth};
896  
897 my $outstr = '<colgroup><col width="1" />';
898  
899 for (my $col=1; $col <= $context->{maxcol}; $col++) {
900 if ($colwidths->[$col]) {
901 $outstr .= qq!<col\nwidth="$colwidths->[$col]"/>!;
902 }
903 else {
904 $outstr .= "<col/>";
905 }
906 }
907 $outstr .= "</colgroup>";
908  
909 return $outstr;
910  
911 }
912  
913 #
914 # $outstr = RenderSizingRow($context, $options)
915 #
916 # Returns row with blank cells for all columns just in case spans leave columns
917 # with no cells -- this has bugs in some cases with some browsers.
918 #
919  
920 sub RenderSizingRow {
921  
922 my ($context, $options) = @_;
923 my $colwidths = $context->{colwidth};
924  
925 my $outstr = qq!<thead><tr><th height="1"></th>!;
926  
927 my $label = 'A';
928 for (my $col=1; $col <= $context->{maxcol}; $col++) {
929 my $content = qq!<span>$label</span>!;
930 if ($colwidths->[$col]) {
931 $outstr .= qq!<th height="1" width="$colwidths->[$col]">$content</th>!;
932 }
933 else {
934 $outstr .= qq!<th height="1">$content</th>!;
935 }
936 $label++;
937 }
938 $outstr .= "</tr></thead>";
939  
940 return $outstr;
941  
942 }
943  
944  
945 #
946 # $outstr = RenderCell($context, $row, $col, $options)
947 #
948 # Returns "<td ...>...</td>"
949 # If skipped cell (because of span) returns "".
950 #
951 # If $options->{parts}, returns:
952 # {tag => "span=...", style => "padding:...", classes => "commentclass",
953 # value => "displayvalue", all => "<td.."}
954 #
955  
956 sub RenderCell {
957 my ($context, $row, $col, $options, $coord, $orig_cell) = @_;
958  
959 my $sheet = $context->{sheet};
960 my $sheetattribs = $sheet->{attribs};
961 my $outstr = "";
962 my $tagstr = "";
963 my $stylestr = "";
964 my $classstr = "";
965 my $displayvalue = "";
966  
967 my $cell = $orig_cell || $sheet->{cells}{$coord ||= (ColToCoord()->[$col]).$row};
968  
969 if ($cell->{colspan} > 1) {
970 my $span = 1;
971 for (my $num=1; $num<$cell->{colspan}; $num++) {
972 if ($sheet->{colattribs}{hide}{NumberToCol($col+$num)} ne "yes") {
973 $span++;
974 }
975 }
976 $tagstr .= " " if $tagstr;
977 $tagstr .= qq!colspan="$span"\n!;
978 }
979  
980 if ($cell->{rowspan} > 1) {
981 my $span = 1;
982 for (my $num=1; $num<$cell->{rowspan}; $num++) {
983 if ($sheet->{colattribs}{hide}{$row+$num} ne "yes") {
984 $span++;
985 }
986 }
987 $tagstr .= " " if $tagstr;
988 $tagstr .= qq!rowspan="$span"\n!;
989 }
990  
991 my $num = $cell->{layout} || $sheetattribs->{defaultlayout};
992 if ($num) {
993 $stylestr .= $context->{layouts}[$num]; # use precomputed layout with "*"'s filled in
994 }
995 else {
996 $stylestr .= $context->{defaultlayout};
997 }
998  
999 $num = $cell->{font} || $sheetattribs->{defaultfont};
1000 if ($num) { # get expanded font strings in context
1001 my $t = $context->{fonts}->[$num]; # do each - plain "font:" style sets all sorts of other values, too (Safari font-stretch problem on cssText)
1002 $stylestr .= "font-style:$t->{style};\nfont-weight:$t->{weight};\nfont-size:$t->{size};\nfont-family:$t->{family};\n";
1003 }
1004 else {
1005 if ($context->{defaultfontsize}) {
1006 $stylestr .= "font-size:$context->{defaultfontsize};\n";
1007 }
1008 if ($context->{defaultfontfamily}) {
1009 $stylestr .= "font-family:$context->{defaultfontfamily};\n";
1010 }
1011 }
1012  
1013 $num = $cell->{color} || $sheetattribs->{defaultcolor};
1014 if ($num) {
1015 $stylestr .= "color:$sheet->{colors}->[$num];\n";
1016 }
1017  
1018 $num = $cell->{bgcolor} || $sheetattribs->{defaultbgcolor};
1019 if ($num) {
1020 $stylestr .= "background-color:$sheet->{colors}->[$num];\n";
1021 }
1022  
1023 $num = $cell->{cellformat};
1024 if ($num) {
1025 $stylestr .= "text-align:$sheet->{cellformats}->[$num];\n";
1026 }
1027 else {
1028 my $t = substr($cell->{valuetype}, 0, 1);
1029 if ($t eq "t") {
1030 $num = $sheetattribs->{defaulttextformat};
1031 if ($num) {
1032 $stylestr .= "text-align:$sheet->{cellformats}->[$num];\n";
1033 }
1034 }
1035 elsif ($t eq "n") {
1036 $num = $sheetattribs->{defaultnontextformat};
1037 if ($num) {
1038 $stylestr .= "text-align:$sheet->{cellformats}->[$num];\n";
1039 }
1040 else {
1041 $stylestr .= "text-align:right;\n";
1042 }
1043 }
1044 else {
1045 $stylestr .= "text-align:left;\n";
1046 }
1047 }
1048  
1049 if ($cell->{bt} &&
1050 ($cell->{bt}==$cell->{br} && $cell->{bt}==$cell->{bb} && $cell->{bt}==$cell->{bl})) {
1051 $stylestr .= "border:$sheet->{borderstyles}->[$cell->{bt}];\n";
1052 }
1053  
1054 else {
1055 $num = $cell->{bt};
1056 if ($num) {
1057 $stylestr .= "border-top:$sheet->{borderstyles}->[$num];\n";
1058 }
1059  
1060 $num = $cell->{br};
1061 if ($num) {
1062 $stylestr .= "border-right:$sheet->{borderstyles}->[$num];\n";
1063 }
1064  
1065 $num = $cell->{bb};
1066 if ($num) {
1067 $stylestr .= "border-bottom:$sheet->{borderstyles}->[$num];\n";
1068 }
1069  
1070 $num = $cell->{bl};
1071 if ($num) {
1072 $stylestr .= "border-left:$sheet->{borderstyles}->[$num];\n";
1073 }
1074 }
1075  
1076 if ($cell->{comment}) {
1077 if ($context->{classnames}{comment}) {
1078 $classstr .= " " if $classstr;
1079 $classstr .= $context->{classnames}{comment};
1080 }
1081 $stylestr .= $context->{explicitStyles}{comment};
1082 #!! put comment text in title tag...?
1083 }
1084  
1085 $displayvalue = FormatValueForDisplay($sheet, $cell->{datavalue}, $coord, $options);
1086  
1087 # Assemble output
1088  
1089 if ($stylestr) {
1090 $classstr .= " " if $classstr;
1091  
1092 my $long_class = "ss-" . Digest::SHA::sha1_hex($stylestr);
1093  
1094 # Use 32 bits prefix, which allows 3000 style to have 0.1% chance of
1095 # collision when concating two sheets together. (cf. Birthday Attack)
1096 my $short_class = substr($long_class, 0, 7); # ss-ABCD
1097  
1098 if ($context->{_render_cache_cell_style_long}{$long_class}) {
1099 # If this is not a new long class...
1100 # ... check if the short class expands to the long class ...
1101 if ($context->{_render_cache_cell_style_short}{$short_class} eq $long_class) {
1102 # ... if so, use that short form
1103 $classstr .= $short_class;
1104 }
1105 else {
1106 # ... otherwise, use long form
1107 $classstr .= $long_class;
1108 }
1109 }
1110 else {
1111 $context->{_render_cache_cell_style_long}{$long_class} = $stylestr;
1112  
1113 if ($context->{_render_cache_cell_style_short}{$short_class}) {
1114 # A new longclass, but shortclass is expanded to someone else.
1115 # Use the long form...
1116 $classstr .= $long_class;
1117 }
1118 else {
1119 # Register this short class for us
1120 $context->{_render_cache_cell_style_short}{$short_class} = $long_class;
1121 $classstr .= $short_class;
1122 }
1123 }
1124 }
1125  
1126 unless ($orig_cell) {
1127 $outstr .= qq{<td id="}.($context->{cellIDprefix}||'cell_').qq{$coord"\n};
1128 }
1129  
1130 if ($tagstr) {
1131 $outstr .= qq! $tagstr\n!;
1132 }
1133  
1134 if ($classstr) {
1135 $outstr .= qq!class="$classstr"\n!;
1136 }
1137  
1138 $outstr .= ">";
1139 $outstr .= "$displayvalue";
1140 $outstr .= "</td>";
1141  
1142 if ($options->{parts}) {
1143 return {tag => $tagstr, style => $stylestr, classes => $classstr,
1144 value => $displayvalue, all => $outstr};
1145 }
1146  
1147 return $outstr;
1148 }
1149  
1150  
1151 #
1152 # $parts = DecodeSpreadsheetSave($str)
1153 #
1154 # Separates the parts from a spreadsheet save string, returning reference to
1155 # a hash with the sub-strings:
1156 #
1157 # {type1 => $string, type2 => ...}
1158 #
1159  
1160 sub DecodeSpreadsheetSave {
1161  
1162 my $str = shift @_;
1163  
1164 my $parts = {};
1165 my @partlist;
1166  
1167 # Parse multipart-MIME setup
1168  
1169 my $OK = $str =~ m/^MIME-Version:\s1\.0/mig;
1170 return $parts unless $OK; # no parts
1171 $OK = $str =~ /(.*?)\nContent-Type:\s*multipart\/mixed;\s*boundary=(\S+)/mig;
1172 return $parts unless $OK;
1173 my $boundary = $2;
1174  
1175 # First part is header which says what comes next
1176  
1177 $OK = $str =~ m/^--$boundary$/mg; # find header top boundary
1178 return $parts unless $OK;
1179 $OK = $str =~ m/^$/mg; # skip to after blank line
1180 return $parts unless $OK;
1181 $OK = $str =~ m/(.*?)\n--$boundary$/msig; # get header part
1182 return $parts unless $OK;
1183 my $header = $1;
1184  
1185 my @lines = split(/\r\n|\n/, $header); # parse header
1186 for (my $i=0; $i<@lines; $i++) {
1187 my @line = split(":", $lines[$i]);
1188 if ($line[0] eq "version") {
1189 }
1190 elsif ($line[0] eq "part") {
1191 push @partlist, $line[1];
1192 }
1193 }
1194  
1195 # Get each part
1196  
1197 for (my $pnum=0; $pnum<@partlist; $pnum++) {
1198 $OK = $str =~ m/^(\r\n|\n)/mg; # skip to after blank line ending mime-part header
1199 return $parts unless $OK;
1200  
1201 $boundary .= "--" if ($pnum == @partlist-1); # last part has different boundary
1202 $OK = $str =~ m/(.*?)\n--$boundary$/msig; # get art's string
1203 return $parts unless $OK;
1204 $parts->{$partlist[$pnum]} = $1; # save string to return
1205 }
1206  
1207 return $parts;
1208  
1209 }
1210  
1211  
1212  
1213 #
1214 # $result = CreateCellHTML($sheet, $coord, $options)
1215 #
1216 # Returns the HTML representation of a cell. Blank is "", not "&nbsp;".
1217 #
1218  
1219 sub CreateCellHTML {
1220  
1221 my ($sheet, $coord, $options) = @_;
1222  
1223 my $result = "";
1224 my $cell = $sheet->{cells}{$coord};
1225  
1226 if (!$cell) {
1227 return "";
1228 }
1229  
1230 $result = FormatValueForDisplay($sheet, $cell->{datavalue}, $coord, $options);
1231  
1232 if ($result eq "&nbsp;") {
1233 $result = "";
1234 }
1235  
1236 return $result;
1237  
1238 }
1239  
1240 #
1241 # $result = CreateCellHTMLSave($sheet, $range, $options)
1242 #
1243 # Returns the HTML representation of a range of cells, or the whole sheet if range is null.
1244 # The form is:
1245 # version:1.0
1246 # coord:cell-HTML
1247 # coord:cell-HTML
1248 # ...
1249 #
1250 # Empty cells are skipped. The cell-HTML is encoded with ":"=>"\c", newline=>"\n", and "\"=>"\b".
1251 #
1252  
1253 sub CreateCellHTMLSave {
1254  
1255 my ($sheet, $range, $options) = @_;
1256  
1257 my $prange;
1258  
1259 if ($range) {
1260 $prange = ParseRange($range);
1261 }
1262 else {
1263 $prange = {cr1 => {row => 1, col => 1},
1264 cr2 => {row => $sheet->{attribs}{lastrow}, col => $sheet->{attribs}{lastcol}}};
1265 }
1266 my $cr1 = $prange->{cr1};
1267 my $cr2 = $prange->{cr2};
1268  
1269 my $result = "version:1.0\n";
1270  
1271 for (my $row=$cr1->{row}; $row <= $cr2->{row}; $row++) {
1272 for (my $col=$cr1->{col}; $col <= $cr2->{col}; $col++) {
1273 my $coord = (ColToCoord()->[$col]).$row;
1274 my $cell = $sheet->{cells}{$coord};
1275 if (!$cell) {
1276 next;
1277 }
1278 my $cellHTML = FormatValueForDisplay($sheet, $cell->{datavalue}, $coord, $options);
1279 if ($cellHTML eq "&nbsp;") {
1280 next;
1281 }
1282 my $ecellHTML = EncodeForSave($cellHTML);
1283 $result .= "$coord:$ecellHTML\n";
1284 }
1285 }
1286  
1287 return $result;
1288  
1289 }
1290  
1291  
1292 #
1293 # $result = CreateCSV($sheet, $range, $options)
1294 #
1295 # Returns the CSV representation of a range of cells, or of the whole sheet if range is null.
1296 # It returns the raw values, not formatted as dates, etc.
1297 #
1298  
1299 sub CreateCSV {
1300  
1301 my ($sheet, $range, $options) = @_;
1302  
1303 my ($prange, $str);
1304  
1305 if ($range) {
1306 $prange = ParseRange($range);
1307 }
1308 else {
1309 $prange = {cr1 => {row => 1, col => 1},
1310 cr2 => {row => $sheet->{attribs}{lastrow}, col => $sheet->{attribs}{lastcol}}};
1311 }
1312 my $cr1 = $prange->{cr1};
1313 my $cr2 = $prange->{cr2};
1314  
1315 my $result = "";
1316  
1317 for (my $row=$cr1->{row}; $row <= $cr2->{row}; $row++) {
1318 for (my $col=$cr1->{col}; $col <= $cr2->{col}; $col++) {
1319 my $coord = (ColToCoord()->[$col]).$row;
1320 my $cell = $sheet->{cells}{$coord};
1321  
1322 if ($cell->{errors}) {
1323 $str = $cell->{errors};
1324 }
1325 else {
1326 $str = $cell->{datavalue} || "";
1327 }
1328  
1329 $str =~ s/"/""/g; # double quotes
1330 if ($str =~ m/[, \n"]/) {
1331 $str = qq!"$str"!; # add quotes
1332 }
1333 if ($col > $cr1->{col}) {
1334 $str = ",$str"; # add commas
1335 }
1336 $result .= $str;
1337 }
1338 $result .= "\n";
1339 }
1340  
1341 return $result;
1342  
1343 }
1344  
1345  
1346 # * * * * * * * * * * * * * * * * * * * *
1347 #
1348 # Common helping routines
1349 #
1350 # * * * * * * * * * * * * * * * * * * * *
1351  
1352 #
1353 # $rangehash = ParseRange($range)
1354 #
1355 # Takes $range with "A1:B7" and returns hash with:
1356 # cr1 => {row => rownumber, col => colnumber, coord => CR},
1357 # cr2 => {row => rownumber, col => colnumber, coord => CR}
1358 #
1359  
1360 sub ParseRange {
1361  
1362 my $range = shift @_;
1363  
1364 if (!$range) {
1365 $range = "A1:A1"; # error return, hopefully benign
1366 }
1367 $range = uc $range;
1368  
1369 my ($cr1, $cr2);
1370 $range =~ m/^(.+):(.+)$/;
1371 if ($2) { # has two parts
1372 $cr1 = CoordToCR($1);
1373 $cr2 = CoordToCR($2);
1374 }
1375 else {
1376 $cr1 = CoordToCR($range);
1377 $cr2 = CoordToCR($range);
1378 }
1379 return {cr1 => $cr1, cr2 => $cr2};
1380  
1381 }
1382  
1383  
1384 #
1385 # (col => $col, row => $row) = CoordToCR($coord)
1386 #
1387 # Turns B3 into (col => 2, row => 3, coord => "B3"). The default for both is 1.
1388 # If range, only do this to first coord
1389 #
1390  
1391 sub CoordToCR {
1392  
1393 my $coord = shift @_;
1394  
1395 $coord = lc($coord);
1396 $coord =~ s/\$//g;
1397 $coord =~ m/([a-z])([a-z])?(\d+)/;
1398 my $col = ord($1) - ord('a') + 1 ;
1399 $col = 26 * $col + ord($2) - ord('a') + 1 if $2;
1400  
1401 return {col => $col, row => $3, coord => uc $coord};
1402  
1403 }
1404  
1405  
1406 #
1407 # $coord = CRToCoord($col, $row)
1408 #
1409 # Turns (2, 3) into B3. The default for both is 1.
1410 #
1411  
1412 sub CRToCoord {
1413 my ($col, $row) = @_;
1414  
1415 $row = 1 if $row < 1;
1416 $col = 1 if $col < 1;
1417  
1418 return (ColToCoord()->[$col]).$row;
1419 }
1420  
1421  
1422 #
1423 # $col = ColToNumber($colname)
1424 #
1425 # Turns B into 2. The default is 1.
1426 #
1427  
1428 sub ColToNumber {
1429  
1430 my $coord = shift @_;
1431  
1432 $coord = lc($coord);
1433 $coord =~ m/([a-z])([a-z])?/;
1434 return 1 unless $1;
1435 my $col = ord($1) - ord('a') + 1 ;
1436 $col = 26 * $col + ord($2) - ord('a') + 1 if $2;
1437  
1438 return $col;
1439  
1440 }
1441  
1442  
1443 #
1444 # $coord = NumberToCol($col)
1445 #
1446 # Turns 2 into B. The default is 1.
1447 #
1448  
1449 sub NumberToCol {
1450  
1451 my $col = shift @_;
1452  
1453 $col = $col > 1 ? $col : 1;
1454  
1455 my $col_high = int(($col - 1) / 26);
1456 my $col_low = ($col - 1) % 26;
1457  
1458 my $coord = chr(ord('A') + $col_low);
1459 $coord = chr(ord('A') + $col_high - 1) . $coord if $col_high;
1460  
1461 return $coord;
1462  
1463 }
1464  
1465  
1466 # # # # # # # # # #
1467 # EncodeForSave($string)
1468 #
1469 # Returns $estring where :, \n, and \ are escaped
1470 #
1471  
1472 sub EncodeForSave {
1473 my $string = shift @_;
1474  
1475 $string =~ s/\\/\\b/g; # \ to \b
1476 $string =~ s/:/\\c/g; # : to \c
1477 $string =~ s/\n/\\n/g; # line end to \n
1478  
1479 return $string;
1480 }
1481  
1482  
1483 # # # # # # # # # #
1484 # DecodeFromSave($string)
1485 #
1486 # Returns $estring with \c, \n, \b and \\ un-escaped
1487 #
1488  
1489 sub DecodeFromSave {
1490 my $string = shift @_;
1491  
1492 $string =~ s/\\c/:/g;
1493 $string =~ s/\\n/\n/g;
1494 $string =~ s/\\b/\\/g;
1495  
1496 return $string;
1497 }
1498  
1499  
1500 # # # # # # # # # #
1501 # SpecialChars($string)
1502 #
1503 # Returns $estring where &, <, >, " are HTML escaped
1504 #
1505  
1506 sub SpecialChars {
1507 my $string = shift @_;
1508  
1509 $string =~ s/&/&amp;/g;
1510 $string =~ s/</&lt;/g;
1511 $string =~ s/>/&gt;/g;
1512 $string =~ s/"/&quot;/g;
1513  
1514 return $string;
1515 }
1516  
1517  
1518 # # # # # # # # # #
1519 # SpecialCharsNL($string)
1520 #
1521 # Returns $estring where &, <, >, ", and LF are HTML escaped, CR's are removed
1522 #
1523  
1524 sub SpecialCharsNL {
1525 my $string = shift @_;
1526  
1527 $string =~ s/&/&amp;/g;
1528 $string =~ s/</&lt;/g;
1529 $string =~ s/>/&gt;/g;
1530 $string =~ s/"/&quot;/g;
1531 $string =~ s/\r//gs;
1532 $string =~ s/\n/&#10;/gs;
1533  
1534 return $string;
1535 }
1536  
1537  
1538 # # # # # # # # # #
1539 # URLEncode($string)
1540 #
1541 # Returns $estring with special chars URL encoded
1542 #
1543 # Based on Mastering Regular Expressions, Jeffrey E. F. Friedl, additional legal characters added
1544 #
1545  
1546 sub URLEncode {
1547 my $string = shift @_;
1548  
1549 $string =~ s!([^a-zA-Z0-9_\-;/?:@=#.])!sprintf('%%%02X', ord($1))!ge;
1550 $string =~ s/%26/{{amp}}/gs; # let ampersands in URLs through -- convert to {{amp}}
1551  
1552 return $string;
1553 }
1554  
1555  
1556 # # # # # # # # # #
1557 # URLEncodePlain($string)
1558 #
1559 # Returns $estring with special chars URL encoded for sending to others by HTTP, not publishing
1560 #
1561 # Based on Mastering Regular Expressions, Jeffrey E. F. Friedl, additional legal characters added
1562 #
1563  
1564 sub URLEncodePlain {
1565 my $string = shift @_;
1566  
1567 $string =~ s!([^a-zA-Z0-9_\-/?:@=#.])!sprintf('%%%02X', ord($1))!ge;
1568  
1569 return $string;
1570 }
1571  
1572  
1573 # # # # # # # # # #
1574 # estring = ExpandMarkup($string, $sheet, $options)
1575 #
1576 # Returns $estring with expanded simple wikitext - used for error messages
1577 #
1578  
1579 sub ExpandMarkup {
1580  
1581 my ($estring, $sheet, $options) = @_;
1582  
1583 return $estring;
1584  
1585 }
1586  
1587  
1588 # # # # # # # # # #
1589 # estring = ExpandWikitext($estring, $sheet, $options, $valueformat)
1590 #
1591 # Returns $estring with expanded wikitext - used for cell data
1592 #
1593  
1594 sub ExpandWikitext {
1595  
1596 my ($estring, $sheet, $options, $valueformat) = @_;
1597  
1598 if (ref $options->{callback_object}) {
1599 return $options->{callback_object}->expand_wikitext(@_);
1600 }
1601  
1602 # handle the cases of text-wiki...
1603  
1604 if ($valueformat =~ m/^text-wiki(.+)$/) { # something more than just plain wikitext
1605 my $subtype = $1;
1606 my $result = "";
1607  
1608 if ($subtype eq "-pagelink") { # not very secure or robust -- just a demo
1609 if ($estring =~ m/\[(\S+)\s+(.+)\]$/) {
1610 my $url = URLEncode("$1");
1611 my $scestring = SpecialChars($2);
1612 $result = qq!<a href="?pagename=$url">$scestring</a>!;
1613 }
1614 elsif ($estring =~ m/\[(.*)\]$/) {
1615 my $url = URLEncode("$1");
1616 my $scestring = SpecialChars($1);
1617 $result = qq!<a href="?pagename=$url">$scestring</a>!;
1618 }
1619 else {
1620 my $url = URLEncode($estring);
1621 my $scestring = SpecialChars($estring);
1622 $result = qq!<a href="?pagename=$url">$scestring</a>!;
1623 }
1624 }
1625 else {
1626 $result = SpecialChars("$subtype: $estring");
1627 }
1628  
1629 return $result || "&nbsp;";
1630  
1631 }
1632  
1633 $estring =~ s!\[(http:.+?)\s+(.+?)\]!'{{lt}}a href={{quot}}' . URLEncode("$1") . "{{quot}}{{gt}}$2\{{lt}}/a{{gt}}"!egs; # Wiki-style links
1634 $estring =~ s!\[link:(.+?)\s+(.+?)\:link]!'{{lt}}a href={{quot}}' . URLEncode("$1") . "{{quot}}{{gt}}$2\{{lt}}/a{{gt}}"!egs; # [link:url text:link] to link
1635 $estring =~ s!\[popup:(.+?)\s+(.+?)\:popup]!'{{lt}}a href={{quot}}' . URLEncode("$1") . "{{quot}} target={{quot}}_blank{{quot}}{{gt}}$2\{{lt}}/a{{gt}}"!egs; # [popup:url text:popup] to link with popup result
1636 $estring =~ s!\[image:(.+?)\s+(.+?)\:image]!'{{lt}}img src={{quot}}' . URLEncode("$1") . '{{quot}} alt={{quot}}' . SpecialCharsNL("$2") . '{{quot}}{{gt}}'!egs; # [image:url alt-text:image] for images
1637 # $string =~ s!\[page:(.+?)(\s+(.+?))?]!wiki_page_command($1,$3, $linkstyle)!egs; # [page:pagename text] to link to other pages on this site
1638  
1639 # Convert &, <, >, "
1640  
1641 $estring = SpecialChars($estring);
1642  
1643 # Multi-line text has additional formatting options ignored for single line
1644  
1645 if ($estring =~ m/\n/) {
1646 my ($str, @closingtag);
1647 foreach my $line (split /\n/, $estring) { # do things on a line-by-line basis
1648 $line =~ s/\r//g;
1649 if ($line =~ m/^([\*|#|;]{1,5})\s{0,1}(.+)$/) { # do list items
1650 my $lnest = length($1);
1651 my $lchr = substr($1,-1);
1652 my $ltype;
1653 if ($lnest > @closingtag) {
1654 for (my $i=@closingtag; $i<$lnest; $i++) {
1655 if ($lchr eq '*') {
1656 $ltype = "ul";
1657 }
1658 elsif ($lchr eq '#') {
1659 $ltype = 'ol';
1660 }
1661 else {
1662 $ltype = 'dl';
1663 }
1664 $str .= "<$ltype>";
1665 push @closingtag, "</$ltype>";
1666 }
1667 }
1668 elsif ($lnest < @closingtag) {
1669 for (my $i=@closingtag; $i>$lnest; $i--) {
1670 $str .= pop @closingtag;
1671 }
1672 }
1673 if ($lchr eq ';') {
1674 my $rest = $2;
1675 if ($rest =~ m/\s*(.*?):(.*)$/) {
1676 $str .= "<dt>$1</dt><dd>$2</dd>";
1677 }
1678 else {
1679 $str .= "<dt>$rest</dt>";
1680 }
1681 }
1682 else {
1683 $str .= "<li>$2</li>";
1684 }
1685 next;
1686 }
1687 while (@closingtag) {
1688 $str .= pop @closingtag;
1689 }
1690 if ($line =~ m/^(={1,5})\s(.+)\s\1$/) { # = heading =, with equal number of equals on both sides
1691 my $neq = length($1);
1692 $str .= "<h$neq>$2</h$neq>";
1693 next;
1694 }
1695 if ($line =~ m/^(:{1,5})\s{0,1}(.+)$/) { # indent 20pts for each :
1696 my $nindent = length($1) * 20;
1697 $str .= "<div style=\"padding-left:${nindent}pt;\">$2</div>";
1698 next;
1699 }
1700  
1701 $str .= "$line\n";
1702 }
1703 while (@closingtag) { # just in case any left at the end
1704 $str .= pop @closingtag;
1705 }
1706 $estring = $str;
1707 }
1708  
1709 $estring =~ s/\n/<br>/g; # Line breaks are preserved
1710 $estring =~ s/('*)'''(.*?)'''/$1<b>$2<\/b>/gs; # Wiki-style bold/italics
1711 $estring =~ s/''(.*?)''/<i>$1<\/i>/gs;
1712 $estring =~ s/\[b:(.+?)\:b]/<b>$1<\/b>/gs; # [b:text:b] for bold
1713 $estring =~ s/\[i:(.+?)\:i]/<i>$1<\/i>/gs; # [i:text:i] for italic
1714 $estring =~ s/\[quote:(.+?)\:quote]/<blockquote>$1<\/blockquote>/gs; # [quote:text:quote] to indent
1715 $estring =~ s/\{\{amp}}/&/gs; # {{amp}} for ampersand
1716 $estring =~ s/\{\{lt}}/</gs; # {{lt}} for less than
1717 $estring =~ s/\{\{gt}}/>/gs; # {{gt}} for greater than
1718 $estring =~ s/\{\{quot}}/"/gs; # {{quot}} for quote
1719 $estring =~ s/\{\{lbracket}}/[/gs; # {{lbracket}} for left bracket
1720 $estring =~ s/\{\{rbracket}}/]/gs; # {{rbracket}} for right bracket
1721 $estring =~ s/\{\{lbrace}}/{/gs; # {{lbrace}} for brace
1722  
1723 return $estring;
1724  
1725 }
1726  
1727 # * * * * * * * * * * * * * * * * * * * *
1728 #
1729 # Number Formatting code from SocialCalc 1.1.0
1730 #
1731 # * * * * * * * * * * * * * * * * * * * *
1732  
1733 our %WKCStrings = (
1734 "decimalchar" => ".",
1735 "separatorchar" => ",",
1736 "currencychar" => '$',
1737 "daynames" => "Sunday Monday Tuesday Wednesday Thursday Friday Saturday",
1738 "daynames3" => "Sun Mon Tue Wed Thu Fri Sat ",
1739 "monthnames3" => "Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec",
1740 "monthnames" => "January February March April May June July August September October November December",
1741 "sheetdefaultlayoutstyle" => "padding:2px 2px 1px 2px;\nvertical-align:top;\n",
1742 "sheetdefaultfontfamily" => "Verdana,Arial,Helvetica,sans-serif",
1743 "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:
1744 #"linkformatstring" => '<img border="0" src="http://www.domain.com/link.gif">',
1745 "defaultformatdt" => 'd-mmm-yyyy h:mm:ss',
1746 "defaultformatd" => 'd-mmm-yyyy',
1747 "defaultformatt" => '[h]:mm:ss',
1748 "displaytrue" => 'TRUE', # how TRUE shows when rendered
1749 "displayfalse" => 'FALSE',
1750 );
1751  
1752 # # # # # # # # #
1753 #
1754 # $displayvalue = FormatValueForDisplay($sheet, $value, $coord, $options)
1755 #
1756 # # # # # # # # #
1757  
1758 sub FormatValueForDisplay {
1759  
1760 my ($sheet, $value, $coord, $options) = @_;
1761  
1762 my $cell = $sheet->{cells}{$coord};
1763 my $sheetattribs = $sheet->{attribs};
1764  
1765 my ($valueformat, $has_parens, $has_commas, $valuetype, $valuesubtype);
1766  
1767 # Get references to the parts
1768  
1769 my $displayvalue = $value;
1770  
1771 $valuetype = $cell->{valuetype}; # get type of value to determine formatting
1772 $valuesubtype = substr($valuetype,1);
1773 $valuetype = substr($valuetype,0,1);
1774  
1775 if ($cell->{errors} || $valuetype eq "e") {
1776 $displayvalue = ExpandMarkup($cell->{errors}, $sheet, $options) || $valuesubtype || "Error in cell";
1777 return $displayvalue;
1778 }
1779  
1780 if ($valuetype eq "t") {
1781 $valueformat = $sheet->{valueformats}->[($cell->{textvalueformat} || $sheetattribs->{defaulttextvalueformat})] || "";
1782 if ($valueformat eq "formula") {
1783 if ($cell->{datatype} eq "f") {
1784 $displayvalue = SpecialChars("=$cell->{formula}") || "&nbsp;";
1785 }
1786 elsif ($cell->{datatype} eq "c") {
1787 $displayvalue = SpecialChars("'$cell->{formula}") || "&nbsp;";
1788 }
1789 else {
1790 $displayvalue = SpecialChars("'$displayvalue") || "&nbsp;";
1791 }
1792 return $displayvalue;
1793 }
1794 $displayvalue = format_text_for_display($displayvalue, $cell->{valuetype}, $valueformat, $sheet, $options);
1795 }
1796  
1797 elsif ($valuetype eq "n") {
1798 $valueformat = $cell->{nontextvalueformat};
1799 if (length($valueformat) == 0) { # "0" is a legal value format
1800 $valueformat = $sheetattribs->{defaultnontextvalueformat};
1801 }
1802 $valueformat = $sheet->{valueformats}->[$valueformat];
1803 if (length($valueformat) == 0) {
1804 $valueformat = "";
1805 }
1806 $valueformat = "" if $valueformat eq "none";
1807 if ($valueformat eq "formula") {
1808 if ($cell->{datatype} eq "f") {
1809 $displayvalue = SpecialChars("=$cell->{formula}") || "&nbsp;";
1810 }
1811 elsif ($cell->{datatype} eq "c") {
1812 $displayvalue = SpecialChars("'$cell->{formula}") || "&nbsp;";
1813 }
1814 else {
1815 $displayvalue = SpecialChars("'$displayvalue") || "&nbsp;";
1816 }
1817 return $displayvalue;
1818 }
1819 elsif ($valueformat eq "forcetext") {
1820 if ($cell->{datatype} eq "f") {
1821 $displayvalue = SpecialChars("=$cell->{formula}") || "&nbsp;";
1822 }
1823 elsif ($cell->{datatype} eq "c") {
1824 $displayvalue = SpecialChars($cell->{formula}) || "&nbsp;";
1825 }
1826 else {
1827 $displayvalue = SpecialChars($displayvalue) || "&nbsp;";
1828 }
1829 return $displayvalue;
1830 }
1831 $displayvalue = format_number_for_display($displayvalue, $cell->{valuetype}, $valueformat);
1832 }
1833 else { # unknown type - probably blank
1834 $displayvalue = "&nbsp;";
1835 }
1836  
1837 return $displayvalue;
1838  
1839 }
1840  
1841  
1842 # # # # # # # # #
1843 #
1844 # $displayvalue = format_text_for_display($rawvalue, $valuetype, $valueformat, $sheetdata, $options)
1845 #
1846 # # # # # # # # #
1847  
1848 sub format_text_for_display {
1849  
1850 my ($rawvalue, $valuetype, $valueformat, $sheet, $options) = @_;
1851  
1852 my $valuesubtype = substr($valuetype,1);
1853  
1854 my $displayvalue = $rawvalue;
1855  
1856 $valueformat = "" if $valueformat eq "none";
1857 $valueformat = "" unless $valueformat =~ m/^(text-|custom|hidden)/;
1858 if (!$valueformat || $valueformat eq "General") { # determine format from type
1859 $valueformat = "text-html" if ($valuesubtype eq "h");
1860 $valueformat = "text-wiki" if ($valuesubtype eq "w" or $valuesubtype eq "r");
1861 $valueformat = "text-link" if ($valuesubtype eq "l");
1862 $valueformat = "text-plain" unless $valuesubtype;
1863 }
1864 if ($valueformat eq "text-html") { # HTML - output as it as is
1865 if ($displayvalue =~ /^<!-- wiki:(.*?) -->/s) {
1866 my $wikitext = $1;
1867 $wikitext =~ s/&gt;/>/g;
1868 $wikitext =~ s/&amp;/&/g;
1869 $displayvalue = ExpandWikitext($wikitext, $sheet, $options, 'text-wiki');
1870 }
1871 }
1872 elsif ($valueformat =~ m/^text-wiki/) { # wiki text
1873 $displayvalue = ExpandWikitext($displayvalue, $sheet, $options, $valueformat); # do wiki markup
1874 }
1875 elsif ($valueformat eq "text-url") { # text is a URL for a link with optional description
1876 my $dvsc = SpecialChars($displayvalue);
1877 my $dvue = URLEncode($displayvalue);
1878 $dvue =~ s/\Q{{amp}}/%26/g;
1879 $displayvalue = qq!<a href="$dvue">$dvsc</a>!;
1880 }
1881 elsif ($valueformat eq "text-link") { # more extensive link capabilities for regular web links
1882 $displayvalue = ExpandTextLink($displayvalue, $sheet, $options, $valueformat);
1883 }
1884 elsif ($valueformat eq "text-image") { # text is a URL for an image
1885 my $dvue = URLEncode($displayvalue);
1886 $dvue =~ s/\Q{{amp}}/%26/g;
1887 $displayvalue = qq!<img src="$dvue">!;
1888 }
1889 elsif ($valueformat =~ m/^text-custom\:/) { # construct a custom text format: @r = text raw, @s = special chars, @u = url encoded
1890 my $dvsc = SpecialChars($displayvalue); # do special chars
1891 $dvsc =~ s/ /&nbsp; /g; # keep multiple spaces
1892 $dvsc =~ s/\n/<br>/g; # keep line breaks
1893 my $dvue = URLEncode($displayvalue);
1894 $dvue =~ s/\Q{{amp}}/%26/g;
1895 my %textval;
1896 $textval{r} = $displayvalue;
1897 $textval{s} = $dvsc;
1898 $textval{u} = $dvue;
1899 $displayvalue = $valueformat;
1900 $displayvalue =~ s/^text-custom\://;
1901 $displayvalue =~ s/@(r|s|u)/$textval{$1}/ge;
1902 }
1903 elsif ($valueformat =~ m/^custom/) { # custom
1904 $displayvalue = SpecialChars($displayvalue); # do special chars
1905 $displayvalue =~ s/ /&nbsp; /g; # keep multiple spaces
1906 $displayvalue =~ s/\n/<br>/g; # keep line breaks
1907 $displayvalue .= " (custom format)";
1908 }
1909 elsif ($valueformat eq "hidden") {
1910 $displayvalue = "&nbsp;";
1911 }
1912 else { # plain text
1913 $displayvalue = SpecialChars($displayvalue); # do special chars
1914 $displayvalue =~ s/ /&nbsp; /g; # keep multiple spaces
1915 $displayvalue =~ s/\n/<br>/g; # keep line breaks
1916 }
1917  
1918 return $displayvalue;
1919  
1920 }
1921  
1922  
1923 # # # # # # # # #
1924 #
1925 # $displayvalue = ExpandTextLink($displayvalue, $sheet, $options, $valueformat)
1926 #
1927 # Given: url = http://www.someurl.com/more, desc = Some descriptive text
1928 #
1929 # Takes the following:
1930 #
1931 # url => "url" as a link to url or other html (depending on SocialCalc.Constants)
1932 # <url> => "Link" or image as link (depending on SocialCalc.Constants)
1933 # desc<url> => "desc" as a link to url
1934 # "desc"<url> => "desc" as a link to url
1935 # <<>> instead of <> => target="_blank" (new window) even when not editing
1936 #
1937 # # # # # # # # #
1938  
1939  
1940 sub ExpandTextLink {
1941  
1942 my ($displayvalue, $sheet, $options, $valueformat) = @_;
1943  
1944 my ($desc, $url);
1945  
1946 my $parts = ParseCellLinkText($displayvalue);
1947  
1948 if ($parts->{desc}) {
1949 $desc = SpecialChars($parts->{desc});
1950 }
1951 else {
1952 $desc = $WKCStrings{linkformatstring};
1953 }
1954  
1955 if (length($displayvalue) > 7 && lc(substr($displayvalue,0,7)) eq "http://"
1956 && substr($displayvalue,-1,1) ne ">") {
1957 $desc = substr($desc,7); # remove http:// unless explicit
1958 }
1959  
1960 my $tb = ($parts->{newwin} || $options->{newwinlinks}) ? ' target="_blank"' : "";
1961  
1962 if ($parts->{pagename}) {
1963 $url = MakePageLink($parts->{pagename}, $parts->{workspace}, $options, $valueformat);
1964 }
1965 else {
1966 $url = URLEncode($parts->{url});
1967 }
1968  
1969 my $str = qq!<a href="$url"$tb>$desc</a>!;
1970  
1971 return $str;
1972  
1973 }
1974  
1975  
1976 # # # # # # # # #
1977 #
1978 # $result = ParseCellLinkText($str)
1979 #
1980 # Given: url = http://www.someurl.com/more, desc = Some descriptive text
1981 #
1982 # Takes the following:
1983 #
1984 # url
1985 # <url>
1986 # desc<url>
1987 # "desc"<url>
1988 # <<>> instead of <> => target="_blank" (new window)
1989 #
1990 # [page name]
1991 # "desc"[page name]
1992 # desc[page name]
1993 # {workspace name [page name]}
1994 # "desc"{workspace name [page name]}
1995 # [[]] instead of [] => target="_blank" (new window)
1996 #
1997 # Returns: {url => url, desc => desc, newwin => t/f, pagename => pagename, workspace => workspace}
1998 #
1999 # # # # # # # # #
2000  
2001 sub ParseCellLinkText {
2002  
2003 my ($str) = @_;
2004  
2005 my $result = {url => "", desc => "", newwin => 0, pagename => "", workspace => ""};
2006  
2007 if ($str !~ /<.*>$/ && $str !~ /\[.*\]$/ && $str !~ /\{.*\[.*\]\}$/) { # plain url
2008 $result->{url} = $str;
2009 $result->{desc} = $str;
2010 return $result;
2011 }
2012  
2013 my $desc;
2014  
2015 if ($str =~ /^(.*)\[\[(.*?)\]\]$/) {
2016 $desc = $1;
2017 $result->{pagename} = $2;
2018 $result->{newwin} = 1;
2019 }
2020 elsif ($str =~ /^(.*)\[(.*?)\]$/) {
2021 $desc = $1;
2022 $result->{pagename} = $2;
2023 }
2024 elsif ($str =~ /^(.*)\{(.*?)(\s{0,1})\[\[(.*?)\]\]\}$/) {
2025 $desc = $1;
2026 $result->{workspace} = $2;
2027 $result->{pagename} = $4;
2028 $result->{newwin} = 1;
2029 }
2030 elsif ($str =~ /^(.*)\{(.*?)(\s{0,1})\[(.*?)\]\}$/) {
2031 $desc = $1;
2032 $result->{workspace} = $2;
2033 $result->{pagename} = $4;
2034 }
2035 elsif ($str =~ /^(.*)<<(.*?)>>$/) {
2036 $desc = $1;
2037 $result->{url} = $2;
2038 $result->{newwin} = 1;
2039 }
2040 else {
2041 $str =~ /^(.*)<(.*?)>$/;
2042 $desc = $1;
2043 $result->{url} = $2;
2044 }
2045  
2046 if ($desc =~ /^"(.*)"$/) {
2047 $desc = $1;
2048 }
2049  
2050 $result->{desc} = $desc;
2051  
2052 return $result;
2053  
2054 }
2055  
2056  
2057 # # # # # # # # # #
2058 # $estring = MakePageLink($pagename, $workspacename, $options, $valueformat);
2059 #
2060 # Returns $estring with expanded link to page in workspace
2061 #
2062  
2063 sub MakePageLink {
2064  
2065 my ($pagename, $workspacename, $options, $valueformat) = @_;
2066  
2067 my $url = "";
2068  
2069 if ($workspacename) {
2070 $url = "?&workspace=" . URLEncode($workspacename) . "&pagename=" . URLEncode($pagename);
2071 }
2072 else {
2073 $url = "?&pagename=" . URLEncode($pagename);
2074 }
2075  
2076 return $url;
2077  
2078 }
2079  
2080  
2081 # # # # # # # # #
2082 #
2083 # $displayvalue = format_number_for_display($rawvalue, $valuetype, $valueformat)
2084 #
2085 # # # # # # # # #
2086  
2087 sub format_number_for_display {
2088  
2089 my ($rawvalue, $valuetype, $valueformat) = @_;
2090  
2091 my ($has_parens, $has_commas);
2092  
2093 my $displayvalue = $rawvalue;
2094 my $valuesubtype = substr($valuetype,1);
2095  
2096 if ($valueformat eq "Auto" || length($valueformat) == 0) { # cases with default format
2097 if ($valuesubtype eq "%") { # will display a % character
2098 $valueformat = "#,##0.0%";
2099 }
2100 elsif ($valuesubtype eq '$') {
2101 $valueformat = '[$]#,##0.00';
2102 }
2103 elsif ($valuesubtype eq 'dt') {
2104 $valueformat = $WKCStrings{"defaultformatdt"};
2105 }
2106 elsif ($valuesubtype eq 'd') {
2107 $valueformat = $WKCStrings{"defaultformatd"};
2108 }
2109 elsif ($valuesubtype eq 't') {
2110 $valueformat = $WKCStrings{"defaultformatt"};
2111 }
2112 elsif ($valuesubtype eq 'l') {
2113 $valueformat = 'logical';
2114 }
2115 else {
2116 $valueformat = "General";
2117 }
2118 }
2119  
2120 if ($valueformat eq "logical") { # do logical format
2121 return $rawvalue ? $WKCStrings{"displaytrue"} : $WKCStrings{"displayfalse"};
2122 }
2123  
2124 if ($valueformat eq "hidden") { # do hidden format
2125 return "&nbsp;";
2126 }
2127  
2128 # Use format
2129  
2130 return format_number_with_format_string($rawvalue, $valueformat);
2131  
2132 }
2133  
2134  
2135 # # # # # # # # #
2136 #
2137 # $result = format_number_with_format_string($value, $format_string, $currency_char)
2138 #
2139 # Use a format string to format a numeric value. Returns a string with the result.
2140 # This is a subset of the normal styles accepted by many other spreadsheets, without fractions, E format, and @,
2141 # and with any number of comparison fields and with [style=style-specification] (e.g., [style=color:red])
2142 #
2143 # # # # # # # # #
2144  
2145 my %allowedcolors = (BLACK => "#000000", BLUE => "#0000FF", CYAN => "#00FFFF", GREEN => "#00FF00", MAGENTA => "#FF00FF",
2146 RED => "#FF0000", WHITE => "#FFFFFF", YELLOW => "#FFFF00");
2147  
2148 my %alloweddates = (H => "h]", M => "m]", MM => "mm]", "S" => "s]", "SS" => "ss]");
2149  
2150 my %format_definitions;
2151 my $cmd_copy = 1;
2152 my $cmd_color = 2;
2153 my $cmd_integer_placeholder = 3;
2154 my $cmd_fraction_placeholder = 4;
2155 my $cmd_decimal = 5;
2156 my $cmd_currency = 6;
2157 my $cmd_general = 7;
2158 my $cmd_separator = 8;
2159 my $cmd_date = 9;
2160 my $cmd_comparison = 10;
2161 my $cmd_section = 11;
2162 my $cmd_style = 12;
2163  
2164 sub format_number_with_format_string {
2165  
2166 my ($rawvalue, $format_string, $currency_char) = @_;
2167  
2168 $currency_char ||= '$';
2169  
2170 my ($op, $operandstr, $fromend, $cval, $operandstrlc);
2171 my ($yr, $mn, $dy, $hrs, $mins, $secs, $ehrs, $emins, $esecs, $ampmstr);
2172 my $result;
2173  
2174 my $value = $rawvalue+0; # get a working copy that's numeric
2175  
2176 my $negativevalue = $value < 0 ? 1 : 0; # determine sign, etc.
2177 $value = -$value if $negativevalue;
2178 my $zerovalue = $value == 0 ? 1 : 0;
2179  
2180 parse_format_string(\%format_definitions, $format_string); # make sure format is parsed
2181 my $thisformat = $format_definitions{$format_string}; # Get format structure
2182  
2183 return "Format error!" unless $thisformat;
2184  
2185 my $section = (scalar @{$thisformat->{sectioninfo}}) - 1; # get number of sections - 1
2186  
2187 if ($thisformat->{hascomparison}) { # has comparisons - determine which section
2188 $section = 0; # set to which section we will use
2189 my $gotcomparison = 0; # this section has no comparison
2190 for (my $cpos; ;$cpos++) { # scan for comparisons
2191 $op = $thisformat->{operators}->[$cpos];
2192 $operandstr = $thisformat->{operands}->[$cpos]; # get next operator and operand
2193 if (!$op) { # at end with no match
2194 if ($gotcomparison) { # if comparison but no match
2195 $format_string = "General"; # use default of General
2196 parse_format_string(\%format_definitions, $format_string);
2197 $thisformat = $format_definitions{$format_string};
2198 $section = 0;
2199 }
2200 last; # if no comparision, matchines on this section
2201 }
2202 if ($op == $cmd_section) { # end of section
2203 if (!$gotcomparison) { # no comparison, so it's a match
2204 last;
2205 }
2206 $gotcomparison = 0;
2207 $section++; # check out next one
2208 next;
2209 }
2210 if ($op == $cmd_comparison) { # found a comparison - do we meet it?
2211 my ($compop, $compval) = split(/:/, $operandstr, 2);
2212 $compval = 0+$compval;
2213 if (($compop eq "<" && $rawvalue < $compval) ||
2214 ($compop eq "<=" && $rawvalue <= $compval) ||
2215 ($compop eq "=" && $rawvalue == $compval) ||
2216 ($compop eq "<>" && $rawvalue != $compval) ||
2217 ($compop eq ">=" && $rawvalue >= $compval) ||
2218 ($compop eq ">" && $rawvalue > $compval)) { # a match
2219 last;
2220 }
2221 $gotcomparison = 1;
2222 }
2223 }
2224 }
2225 elsif ($section > 0) { # more than one section (separated by ";")
2226 if ($section == 1) { # two sections
2227 if ($negativevalue) {
2228 $negativevalue = 0; # sign will provided by section, not automatically
2229 $section = 1; # use second section for negative values
2230 }
2231 else {
2232 $section = 0; # use first for all others
2233 }
2234 }
2235 elsif ($section == 2) { # three sections
2236 if ($negativevalue) {
2237 $negativevalue = 0; # sign will provided by section, not automatically
2238 $section = 1; # use second section for negative values
2239 }
2240 elsif ($zerovalue) {
2241 $section = 2; # use third section for zero values
2242 }
2243 else {
2244 $section = 0; # use first for positive
2245 }
2246 }
2247 }
2248  
2249 # Get values for our section
2250 my ($sectionstart, $integerdigits, $fractiondigits, $commas, $percent, $thousandssep) =
2251 @{%{$thisformat->{sectioninfo}->[$section]}}{qw(sectionstart integerdigits fractiondigits commas percent thousandssep)};
2252  
2253 if ($commas > 0) { # scale by thousands
2254 for (my $i=0; $i<$commas; $i++) {
2255 $value /= 1000;
2256 }
2257 }
2258 if ($percent > 0) { # do percent scaling
2259 for (my $i=0; $i<$percent; $i++) {
2260 $value *= 100;
2261 }
2262 }
2263  
2264 my $decimalscale = 1; # cut down to required number of decimal digits
2265 for (my $i=0; $i<$fractiondigits; $i++) {
2266 $decimalscale *= 10;
2267 }
2268 my $scaledvalue = int($value * $decimalscale + 0.5);
2269 $scaledvalue = $scaledvalue / $decimalscale;
2270  
2271 $negativevalue = 0 if ($scaledvalue == 0 && ($fractiondigits || $integerdigits)); # no "-0" unless using multiple sections or General
2272  
2273 my $strvalue = "$scaledvalue"; # convert to string
2274 if ($strvalue =~ m/e/) { # converted to scientific notation
2275 return "$rawvalue"; # Just return plain converted raw value
2276 }
2277 $strvalue =~ m/^\+{0,1}(\d*)(?:\.(\d*)){0,1}$/; # get integer and fraction as character arrays
2278 my $integervalue = $1;
2279 $integervalue = "" if ($integervalue == 0);
2280 my @integervalue = split(//, $integervalue);
2281 my $fractionvalue = $2;
2282 $fractionvalue = "" if ($fractionvalue == 0);
2283 my @fractionvalue = split(//, $fractionvalue);
2284  
2285 if ($thisformat->{sectioninfo}->[$section]->{hasdate}) { # there are date placeholders
2286 if ($rawvalue < 0) { # bad date
2287 return "??-???-??&nbsp;??:??:??";
2288 }
2289 my $startval = ($rawvalue-int($rawvalue)) * $seconds_in_a_day; # get date/time parts
2290 my $estartval = $rawvalue * $seconds_in_a_day; # do elapsed time version, too
2291 $hrs = int($startval / $seconds_in_an_hour);
2292 $ehrs = int($estartval / $seconds_in_an_hour);
2293 $startval = $startval - $hrs * $seconds_in_an_hour;
2294 $mins = int($startval / 60);
2295 $emins = int($estartval / 60);
2296 $secs = $startval - $mins * 60;
2297 $decimalscale = 1; # round appropriately depending if there is ss.0
2298 for (my $i=0; $i<$fractiondigits; $i++) {
2299 $decimalscale *= 10;
2300 }
2301 $secs = int($secs * $decimalscale + 0.5);
2302 $secs = $secs / $decimalscale;
2303 $esecs = int($estartval * $decimalscale + 0.5);
2304 $esecs = $esecs / $decimalscale;
2305 if ($secs >= 60) { # handle round up into next second, minute, etc.
2306 $secs = 0;
2307 $mins++; $emins++;
2308 if ($mins >= 60) {
2309 $mins = 0;
2310 $hrs++; $ehrs++;
2311 if ($hrs >= 24) {
2312 $hrs = 0;
2313 $rawvalue++;
2314 }
2315 }
2316 }
2317 @fractionvalue = split(//, $secs-int($secs)); # for "hh:mm:ss.00"
2318 shift @fractionvalue; shift @fractionvalue;
2319 ($yr, $mn, $dy) = convert_date_julian_to_gregorian(int($rawvalue+$julian_offset));
2320  
2321 my $minOK; # says "m" can be minutes
2322 my $mspos = $sectionstart; # m scan position in ops
2323 for ( ; ; $mspos++) { # scan for "m" and "mm" to see if any minutes fields, and am/pm
2324 $op = $thisformat->{operators}->[$mspos];
2325 $operandstr = $thisformat->{operands}->[$mspos]; # get next operator and operand
2326 last unless $op; # don't go past end
2327 last if $op == $cmd_section;
2328 if ($op == $cmd_date) {
2329 if ((lc($operandstr) eq "am/pm" || lc($operandstr) eq "a/p") && !$ampmstr) {
2330 if ($hrs >= 12) {
2331 $hrs -= 12;
2332 $ampmstr = lc($operandstr) eq "a/p" ? "P" : "PM";
2333 }
2334 else {
2335 $ampmstr = lc($operandstr) eq "a/p" ? "A" : "AM";
2336 }
2337 $ampmstr = lc $ampmstr if $operandstr !~ m/$ampmstr/;
2338 }
2339 if ($minOK && ($operandstr eq "m" || $operandstr eq "mm")) {
2340 $thisformat->{operands}->[$mspos] .= "in"; # turn into "min" or "mmin"
2341 }
2342 if (substr($operandstr,0,1) eq "h") {
2343 $minOK = 1; # m following h or hh or [h] is minutes not months
2344 }
2345 else {
2346 $minOK = 0;
2347 }
2348 }
2349 elsif ($op != $cmd_copy) { # copying chars can be between h and m
2350 $minOK = 0;
2351 }
2352 }
2353 $minOK = 0;
2354 for (--$mspos; ; $mspos--) { # scan other way for s after m
2355 $op = $thisformat->{operators}->[$mspos];
2356 $operandstr = $thisformat->{operands}->[$mspos]; # get next operator and operand
2357 last unless $op; # don't go past end
2358 last if $op == $cmd_section;
2359 if ($op == $cmd_date) {
2360 if ($minOK && ($operandstr eq "m" || $operandstr eq "mm")) {
2361 $thisformat->{operands}->[$mspos] .= "in"; # turn into "min" or "mmin"
2362 }
2363 if ($operandstr eq "ss") {
2364 $minOK = 1; # m before ss is minutes not months
2365 }
2366 else {
2367 $minOK = 0;
2368 }
2369 }
2370 elsif ($op != $cmd_copy) { # copying chars can be between ss and m
2371 $minOK = 0;
2372 }
2373 }
2374 }
2375  
2376 my $integerdigits2 = 0; # init counters, etc.
2377 my $integerpos = 0;
2378 my $fractionpos = 0;
2379 my $textcolor = "";
2380 my $textstyle = "";
2381 my $separatorchar = $WKCStrings{"separatorchar"};
2382 $separatorchar =~ s/ /&nbsp;/g;
2383 my $decimalchar = $WKCStrings{"decimalchar"};
2384 $decimalchar =~ s/ /&nbsp;/g;
2385  
2386 my $oppos = $sectionstart;
2387  
2388 while ($op = $thisformat->{operators}->[$oppos]) { # execute format
2389 $operandstr = $thisformat->{operands}->[$oppos++]; # get next operator and operand
2390 if ($op == $cmd_copy) { # put char in result
2391 $result .= $operandstr;
2392 }
2393  
2394 elsif ($op == $cmd_color) { # set color
2395 $textcolor = $operandstr;
2396 }
2397  
2398 elsif ($op == $cmd_style) { # set style
2399 $textstyle = $operandstr;
2400 }
2401  
2402 elsif ($op == $cmd_integer_placeholder) { # insert number part
2403 if ($negativevalue) {
2404 $result .= "-";
2405 $negativevalue = 0;
2406 }
2407 $integerdigits2++;
2408 if ($integerdigits2 == 1) { # first one
2409 if ((scalar @integervalue) > $integerdigits) { # see if integer wider than field
2410 for (;$integerpos < ((scalar @integervalue) - $integerdigits); $integerpos++) {
2411 $result .= $integervalue[$integerpos];
2412 if ($thousandssep) { # see if this is a separator position
2413 $fromend = (scalar @integervalue) - $integerpos - 1;
2414 if ($fromend > 2 && $fromend % 3 == 0) {
2415 $result .= $separatorchar;
2416 }
2417 }
2418 }
2419 }
2420 }
2421 if ((scalar @integervalue) < $integerdigits
2422 && $integerdigits2 <= $integerdigits - (scalar @integervalue)) { # field is wider than value
2423 if ($operandstr eq "0" || $operandstr eq "?") { # fill with appropriate characters
2424 $result .= $operandstr eq "0" ? "0" : "&nbsp;";
2425 if ($thousandssep) { # see if this is a separator position
2426 $fromend = $integerdigits - $integerdigits2;
2427 if ($fromend > 2 && $fromend % 3 == 0) {
2428 $result .= $separatorchar;
2429 }
2430 }
2431 }
2432 }
2433 else { # normal integer digit - add it
2434 $result .= $integervalue[$integerpos];
2435 if ($thousandssep) { # see if this is a separator position
2436 $fromend = (scalar @integervalue) - $integerpos - 1;
2437 if ($fromend > 2 && $fromend % 3 == 0) {
2438 $result .= $separatorchar;
2439 }
2440 }
2441 $integerpos++;
2442 }
2443 }
2444 elsif ($op == $cmd_fraction_placeholder) { # add fraction part of number
2445 if ($fractionpos >= scalar @fractionvalue) {
2446 if ($operandstr eq "0" || $operandstr eq "?") {
2447 $result .= $operandstr eq "0" ? "0" : "&nbsp;";
2448 }
2449 }
2450 else {
2451 $result .= $fractionvalue[$fractionpos];
2452 }
2453 $fractionpos++;
2454 }
2455  
2456 elsif ($op == $cmd_decimal) { # decimal point
2457 if ($negativevalue) {
2458 $result .= "-";
2459 $negativevalue = 0;
2460 }
2461 $result .= $decimalchar;
2462 }
2463  
2464 elsif ($op == $cmd_currency) { # currency symbol
2465 if ($negativevalue) {
2466 $result .= "-";
2467 $negativevalue = 0;
2468 }
2469 $result .= $operandstr;
2470 }
2471  
2472 elsif ($op == $cmd_general) { # insert "General" conversion
2473  
2474 # *** Cut down number of significant digits to avoid floating point artifacts matching JavaScript:
2475  
2476 if ($value != 0) { # only if non-zero
2477 my $factor = 0.43429448190325181667 * log($value); # get magnitude as a power of 10 the same way as JavaScript
2478 if ($factor >= 0) { # as an integer (truncating down)
2479 $factor = int($factor);
2480 }
2481 else {
2482 my $mfactor = -int(-$factor);
2483 $factor = $factor == $mfactor ? $factor : $mfactor - 1;
2484 }
2485 $factor = 10 ** (13-$factor); # turn into scaling factor
2486 $value = int($factor * $value + 0.5) / $factor; # scale positive value, round, undo scaling
2487 }
2488  
2489 if ($negativevalue) {
2490 $result .= "-";
2491 }
2492 $strvalue = "$value"; # convert original value to string
2493 if ($strvalue =~ m/e/) { # converted to scientific notation
2494 $result .= "$strvalue";
2495 next;
2496 }
2497 $strvalue =~ m/^\+{0,1}(\d*)(?:\.(\d*)){0,1}$/;
2498 $integervalue = $1;
2499 $integervalue = "" if ($integervalue == 0);
2500 @integervalue = split(//, $integervalue);
2501 $fractionvalue = $2;
2502 $fractionvalue = "" if ($fractionvalue == 0);
2503 @fractionvalue = split(//, $fractionvalue);
2504 $integerpos = 0;
2505 $fractionpos = 0;
2506 if (scalar @integervalue) {
2507 for (;$integerpos < scalar @integervalue; $integerpos++) {
2508 $result .= $integervalue[$integerpos];
2509 if ($thousandssep) { # see if this is a separator position
2510 $fromend = (scalar @integervalue) - $integerpos - 1;
2511 if ($fromend > 2 && $fromend % 3 == 0) {
2512 $result .= $separatorchar;
2513 }
2514 }
2515 }
2516 }
2517 else {
2518 $result .= "0";
2519 }
2520 if (scalar @fractionvalue) {
2521 $result .= $decimalchar;
2522 for (;$fractionpos < scalar @fractionvalue; $fractionpos++) {
2523 $result .= $fractionvalue[$fractionpos];
2524 }
2525 }
2526 }
2527  
2528 elsif ($op == $cmd_date) { # date placeholder
2529 $operandstrlc = lc $operandstr;
2530 if ($operandstrlc eq "y" || $operandstrlc eq "yy") {
2531 $result .= substr("$yr",-2);
2532 }
2533 elsif ($operandstrlc eq "yyyy") {
2534 $result .= "$yr";
2535 }
2536 elsif ($operandstrlc eq "d") {
2537 $result .= "$dy";
2538 }
2539 elsif ($operandstrlc eq "dd") {
2540 $cval = 1000 + $dy;
2541 $result .= substr("$cval", -2);
2542 }
2543 elsif ($operandstrlc eq "ddd") {
2544 $cval = int($rawvalue+6) % 7;
2545 $result .= (split(/ /, $WKCStrings{"daynames3"}))[$cval];
2546 }
2547 elsif ($operandstrlc eq "dddd") {
2548 $cval = int($rawvalue+6) % 7;
2549 $result .= (split(/ /, $WKCStrings{"daynames"}))[$cval];
2550 }
2551 elsif ($operandstrlc eq "m") {
2552 $result .= "$mn";
2553 }
2554 elsif ($operandstrlc eq "mm") {
2555 $cval = 1000 + $mn;
2556 $result .= substr("$cval", -2);
2557 }
2558 elsif ($operandstrlc eq "mmm") {
2559 $result .= (split(/ /, $WKCStrings{"monthnames3"}))[$mn-1];
2560 }
2561 elsif ($operandstrlc eq "mmmm") {
2562 $result .= (split(/ /, $WKCStrings{"monthnames"}))[$mn-1];
2563 }
2564 elsif ($operandstrlc eq "mmmmm") {
2565 $result .= substr((split(/ /, $WKCStrings{"monthnames"}))[$mn-1], 0, 1);
2566 }
2567 elsif ($operandstrlc eq "h") {
2568 $result .= "$hrs";
2569 }
2570 elsif ($operandstrlc eq "h]") {
2571 $result .= "$ehrs";
2572 }
2573 elsif ($operandstrlc eq "mmin") {
2574 $cval = 1000 + $mins;
2575 $result .= substr("$cval", -2);
2576 }
2577 elsif ($operandstrlc eq "mm]") {
2578 if ($emins < 100) {
2579 $cval = 1000 + $emins;
2580 $result .= substr("$cval", -2);
2581 }
2582 else {
2583 $result .= "$emins";
2584 }
2585 }
2586 elsif ($operandstrlc eq "min") {
2587 $result .= "$mins";
2588 }
2589 elsif ($operandstrlc eq "m]") {
2590 $result .= "$emins";
2591 }
2592 elsif ($operandstrlc eq "hh") {
2593 $cval = 1000 + $hrs;
2594 $result .= substr("$cval", -2);
2595 }
2596 elsif ($operandstrlc eq "s") {
2597 $cval = int($secs);
2598 $result .= "$cval";
2599 }
2600 elsif ($operandstrlc eq "ss") {
2601 $cval = 1000 + int($secs);
2602 $result .= substr("$cval", -2);
2603 }
2604 elsif ($operandstrlc eq "am/pm" || $operandstrlc eq "a/p") {
2605 $result .= $ampmstr;
2606 }
2607 elsif ($operandstrlc eq "ss]") {
2608 if ($esecs < 100) {
2609 $cval = 1000 + int($esecs);
2610 $result .= substr("$cval", -2);
2611 }
2612 else {
2613 $cval = int($esecs);
2614 $result = "$cval";
2615 }
2616 }
2617 }
2618  
2619 elsif ($op == $cmd_section) { # end of section
2620 last;
2621 }
2622  
2623 elsif ($op == $cmd_comparison) { # ignore
2624 next;
2625 }
2626  
2627 else {
2628 $result .= "!! Parse error !!";
2629 }
2630 }
2631  
2632 if ($textcolor) {
2633 $result = qq!<span style="color:$textcolor;">$result</span>!;
2634 }
2635 if ($textstyle) {
2636 $result = qq!<span style="$textstyle;">$result</span>!;
2637 }
2638  
2639 return $result;
2640 }
2641  
2642 # # # # # # # # #
2643 #
2644 # parse_format_string(\%format_defs, $format_string)
2645 #
2646 # Takes a format string (e.g., "#,##0.00_);(#,##0.00)") and fills in %foramt_defs with the parsed info
2647 #
2648 # %format_defs
2649 # {"#,##0.0"}->{} # elements in the hash are one hash for each format
2650 # {operators}->[] # array of operators from parsing the format string (each a number)
2651 # {operands}->[] # array of corresponding operators (each usually a string)
2652 # {sectioninfo}->[] # one hash for each section of the format
2653 # {start}
2654 # {integerdigits}
2655 # {fractiondigits}
2656 # {commas}
2657 # {percent}
2658 # {thousandssep}
2659 # {hasdates}
2660 # {hascomparison} # true if any section has [<100], etc.
2661 #
2662 # # # # # # # # #
2663  
2664 sub parse_format_string {
2665  
2666 my ($format_defs, $format_string) = @_;
2667  
2668 return if ($format_defs->{$format_string}); # already defined - nothing to do
2669  
2670 my $thisformat = {operators => [], operands => [], sectioninfo => [{}]}; # create info structure for this format
2671 $format_defs->{$format_string} = $thisformat; # add to other format definitions
2672  
2673 my $section = 0; # start with section 0
2674 my $sectioninfo = $thisformat->{sectioninfo}->[$section]; # get reference to info for current section
2675 $sectioninfo->{sectionstart} = 0; # position in operands that starts this section
2676  
2677 my @formatchars = split //, $format_string; # break into individual characters
2678  
2679 my $integerpart = 1; # start out in integer part
2680 my $lastwasinteger; # last char was an integer placeholder
2681 my $lastwasslash; # last char was a backslash - escaping following character
2682 my $lastwasasterisk; # repeat next char
2683 my $lastwasunderscore; # last char was _ which picks up following char for width
2684 my ($inquote, $quotestr); # processing a quoted string
2685 my ($inbracket, $bracketstr, $cmd); # processing a bracketed string
2686 my ($ingeneral, $gpos); # checks for characters "General"
2687 my $ampmstr; # checks for characters "A/P" and "AM/PM"
2688 my $indate; # keeps track of date/time placeholders
2689  
2690 foreach my $ch (@formatchars) { # parse
2691 if ($inquote) {
2692 if ($ch eq '"') {
2693 $inquote = 0;
2694 push @{$thisformat->{operators}}, $cmd_copy;
2695 push @{$thisformat->{operands}}, $quotestr;
2696 next;
2697 }
2698 $quotestr .= $ch;
2699 next;
2700 }
2701 if ($inbracket) {
2702 if ($ch eq ']') {
2703 $inbracket = 0;
2704 ($cmd, $bracketstr) = parse_format_bracket($bracketstr);
2705 if ($cmd == $cmd_separator) {
2706 $sectioninfo->{thousandssep} = 1; # explicit [,]
2707 next;
2708 }
2709 if ($cmd == $cmd_date) {
2710 $sectioninfo->{hasdate} = 1;
2711 }
2712 if ($cmd == $cmd_comparison) {
2713 $thisformat->{hascomparison} = 1;
2714 }
2715 push @{$thisformat->{operators}}, $cmd;
2716 push @{$thisformat->{operands}}, $bracketstr;
2717 next;
2718 }
2719 $bracketstr .= $ch;
2720 next;
2721 }
2722 if ($lastwasslash) {
2723 push @{$thisformat->{operators}}, $cmd_copy;
2724 push @{$thisformat->{operands}}, $ch;
2725 $lastwasslash = 0;
2726 next;
2727 }
2728 if ($lastwasasterisk) {
2729 push @{$thisformat->{operators}}, $cmd_copy;
2730 push @{$thisformat->{operands}}, $ch x 5;
2731 $lastwasasterisk = 0;
2732 next;
2733 }
2734 if ($lastwasunderscore) {
2735 push @{$thisformat->{operators}}, $cmd_copy;
2736 push @{$thisformat->{operands}}, "&nbsp;";
2737 $lastwasunderscore = 0;
2738 next;
2739 }
2740 if ($ingeneral) {
2741 if (substr("general", $ingeneral, 1) eq lc $ch) {
2742 $ingeneral++;
2743 if ($ingeneral == 7) {
2744 push @{$thisformat->{operators}}, $cmd_general;
2745 push @{$thisformat->{operands}}, $ch;
2746 $ingeneral = 0;
2747 }
2748 next;
2749 }
2750 $ingeneral = 0;
2751 }
2752 if ($indate) { # last char was part of a date placeholder
2753 if (substr($indate,0,1) eq $ch) { # another of the same char
2754 $indate .= $ch; # accumulate it
2755 next;
2756 }
2757 push @{$thisformat->{operators}}, $cmd_date; # something else, save date info
2758 push @{$thisformat->{operands}}, $indate;
2759 $sectioninfo->{hasdate} = 1;
2760 $indate = "";
2761 }
2762 if ($ampmstr) {
2763 $ampmstr .= $ch;
2764 if ("am/pm" =~ m/^$ampmstr/i || "a/p" =~ m/^$ampmstr/i) {
2765 if (("am/pm" eq lc $ampmstr) || ("a/p" eq lc $ampmstr)) {
2766 push @{$thisformat->{operators}}, $cmd_date;
2767 push @{$thisformat->{operands}}, $ampmstr;
2768 $ampmstr = "";
2769 }
2770 next;
2771 }
2772 $ampmstr = "";
2773 }
2774 if ($ch eq "#" || $ch eq "0" || $ch eq "?") { # placeholder
2775 if ($integerpart) {
2776 $sectioninfo->{integerdigits}++;
2777 if ($sectioninfo->{commas}) { # comma inside of integer placeholders
2778 $sectioninfo->{thousandssep} = 1; # any number is thousands separator
2779 $sectioninfo->{commas} = 0; # reset count of "thousand" factors
2780 }
2781 $lastwasinteger = 1;
2782 push @{$thisformat->{operators}}, $cmd_integer_placeholder;
2783 push @{$thisformat->{operands}}, $ch;
2784 }
2785 else {
2786 $sectioninfo->{fractiondigits}++;
2787 push @{$thisformat->{operators}}, $cmd_fraction_placeholder;
2788 push @{$thisformat->{operands}}, $ch;
2789 }
2790 }
2791 elsif ($ch eq ".") { # decimal point
2792 $lastwasinteger = 0;
2793 push @{$thisformat->{operators}}, $cmd_decimal;
2794 push @{$thisformat->{operands}}, $ch;
2795 $integerpart = 0;
2796 }
2797 elsif ($ch eq '$') { # currency char
2798 $lastwasinteger = 0;
2799 push @{$thisformat->{operators}}, $cmd_currency;
2800 push @{$thisformat->{operands}}, $ch;
2801 }
2802 elsif ($ch eq ",") {
2803 if ($lastwasinteger) {
2804 $sectioninfo->{commas}++;
2805 }
2806 else {
2807 push @{$thisformat->{operators}}, $cmd_copy;
2808 push @{$thisformat->{operands}}, $ch;
2809 }
2810 }
2811 elsif ($ch eq "%") {
2812 $lastwasinteger = 0;
2813 $sectioninfo->{percent}++;
2814 push @{$thisformat->{operators}}, $cmd_copy;
2815 push @{$thisformat->{operands}}, $ch;
2816 }
2817 elsif ($ch eq '"') {
2818 $lastwasinteger = 0;
2819 $inquote = 1;
2820 $quotestr = "";
2821 }
2822 elsif ($ch eq '[') {
2823 $lastwasinteger = 0;
2824 $inbracket = 1;
2825 $bracketstr = "";
2826 }
2827 elsif ($ch eq '\\') {
2828 $lastwasslash = 1;
2829 $lastwasinteger = 0;
2830 }
2831 elsif ($ch eq '*') {
2832 $lastwasasterisk = 1;
2833 $lastwasinteger = 0;
2834 }
2835 elsif ($ch eq '_') {
2836 $lastwasunderscore = 1;
2837 $lastwasinteger = 0;
2838 }
2839 elsif ($ch eq ";") {
2840 $section++; # start next section
2841 $thisformat->{sectioninfo}->[$section] = {}; # create a new section
2842 $sectioninfo = $thisformat->{sectioninfo}->[$section]; # set to point to the new section
2843 $sectioninfo->{sectionstart} = 1 + scalar @{$thisformat->{operators}}; # remember where it starts
2844 $integerpart = 1; # reset for new section
2845 $lastwasinteger = 0;
2846 push @{$thisformat->{operators}}, $cmd_section;
2847 push @{$thisformat->{operands}}, $ch;
2848 }
2849 elsif ((lc $ch) eq "g") {
2850 $ingeneral = 1;
2851 $lastwasinteger = 0;
2852 }
2853 elsif ((lc $ch) eq "a") {
2854 $ampmstr = $ch;
2855 $lastwasinteger = 0;
2856 }
2857 elsif ($ch =~ m/[dmyhHs]/) {
2858 $indate = $ch;
2859 }
2860 else {
2861 $lastwasinteger = 0;
2862 push @{$thisformat->{operators}}, $cmd_copy;
2863 push @{$thisformat->{operands}}, $ch;
2864 }
2865 }
2866  
2867 if ($indate) { # last char was part of unsaved date placeholder
2868 push @{$thisformat->{operators}}, $cmd_date; # save what we got
2869 push @{$thisformat->{operands}}, $indate;
2870 $sectioninfo->{hasdate} = 1;
2871 }
2872  
2873 return;
2874  
2875 }
2876  
2877  
2878 # # # # # # # # #
2879 #
2880 # ($operator, $operand) = parse_format_bracket($bracketstr)
2881 #
2882 # # # # # # # # #
2883  
2884 sub parse_format_bracket {
2885  
2886 my $bracketstr = shift @_;
2887  
2888 my ($operator, $operand);
2889  
2890 if (substr($bracketstr, 0, 1) eq '$') { # currency
2891 $operator = $cmd_currency;
2892 if ($bracketstr =~ m/^\$(.+?)(\-.+?){0,1}$/) {
2893 $operand = $1 || $WKCStrings{"currencychar"} || '$';
2894 }
2895 else {
2896 $operand = substr($bracketstr,1) || $WKCStrings{"currencychar"} || '$';
2897 }
2898 }
2899 elsif ($bracketstr eq '?$') {
2900 $operator = $cmd_currency;
2901 $operand = '[?$]';
2902 }
2903 elsif ($allowedcolors{uc $bracketstr}) {
2904 $operator = $cmd_color;
2905 $operand = $allowedcolors{uc $bracketstr};
2906 }
2907 elsif ($bracketstr =~ m/^style=([^"]*)$/) { # [style=...]
2908 $operator = $cmd_style;
2909 $operand = $1;
2910 }
2911 elsif ($bracketstr eq ",") {
2912 $operator = $cmd_separator;
2913 $operand = $bracketstr;
2914 }
2915 elsif ($alloweddates{uc $bracketstr}) {
2916 $operator = $cmd_date;
2917 $operand = $alloweddates{uc $bracketstr};
2918 }
2919 elsif ($bracketstr =~ m/^[<>=]/) { # comparison operator
2920 $bracketstr =~ m/^([<>=]+)(.+)$/; # split operator and value
2921 $operator = $cmd_comparison;
2922 $operand = "$1:$2";
2923 }
2924 else { # unknown bracket
2925 $operator = $cmd_copy;
2926 $operand = "[$bracketstr]";
2927 }
2928  
2929 return ($operator, $operand);
2930  
2931 }
2932  
2933 # # # # # # # # #
2934 #
2935 # $juliandate = convert_date_gregorian_to_julian($year, $month, $day)
2936 #
2937 # From: http://aa.usno.navy.mil/faq/docs/JD_Formula.html
2938 # Uses: Fliegel, H. F. and van Flandern, T. C. (1968). Communications of the ACM, Vol. 11, No. 10 (October, 1968).
2939 # Translated from the FORTRAN
2940 #
2941 # I= YEAR
2942 # J= MONTH
2943 # K= DAY
2944 #C
2945 # JD= K-32075+1461*(I+4800+(J-14)/12)/4+367*(J-2-(J-14)/12*12)
2946 # 2 /12-3*((I+4900+(J-14)/12)/100)/4
2947 #
2948 # # # # # # # # #
2949  
2950 sub convert_date_gregorian_to_julian {
2951  
2952 my ($year, $month, $day) = @_;
2953  
2954 my $juliandate= $day-32075+int(1461*($year+4800+int(($month-14)/12))/4);
2955 $juliandate += int(367*($month-2-int(($month-14)/12)*12)/12);
2956 $juliandate = $juliandate -int(3*int(($year+4900+int(($month-14)/12))/100)/4);
2957  
2958 return $juliandate;
2959  
2960 }
2961  
2962  
2963 # # # # # # # # #
2964 #
2965 # ($year, $month, $day) = convert_date_julian_to_gregorian($juliandate)
2966 #
2967 # From: http://aa.usno.navy.mil/faq/docs/JD_Formula.html
2968 # Uses: Fliegel, H. F. and van Flandern, T. C. (1968). Communications of the ACM, Vol. 11, No. 10 (October, 1968).
2969 # Translated from the FORTRAN
2970 #
2971 # # # # # # # # #
2972  
2973 sub convert_date_julian_to_gregorian {
2974  
2975 my $juliandate = shift @_;
2976  
2977 my ($L, $N, $I, $J, $K);
2978  
2979 $L = $juliandate+68569;
2980 $N = int(4*$L/146097);
2981 $L = $L-int((146097*$N+3)/4);
2982 $I = int(4000*($L+1)/1461001);
2983 $L = $L-int(1461*$I/4)+31;
2984 $J = int(80*$L/2447);
2985 $K = $L-int(2447*$J/80);
2986 $L = int($J/11);
2987 $J = $J+2-12*$L;
2988 $I = 100*($N-49)+$I+$L;
2989  
2990 return ($I, $J, $K);
2991  
2992 }
2993  
2994 1;