Spreadsheet-Engine
view release on metacpan or search on metacpan
lib/Spreadsheet/Engine/Sheet.pm view on Meta::CPAN
version:versionname - version of this format. Currently 1.3.
cell:coord:type:value...:type:value... - Types are as follows:
v:value - straight numeric value
t:value - straight text/wiki-text in cell, encoded to handle \, :, newlines
vt:fulltype:value - value with value type/subtype
vtf:fulltype:value:formulatext - formula resulting in value with value type/subtype, value and text encoded
vtc:fulltype:value:valuetext - formatted text constant resulting in value with value type/subtype, value and text encoded
vf:fvalue:formulatext - formula resulting in value, value and text encoded (obsolete: only pre format version 1.1)
fvalue - first char is "N" for numeric value, "T" for text value, "H" for HTML value, rest is the value
e:errortext - Error text. Non-blank means formula parsing/calculation results in error.
b:topborder#:rightborder#:bottomborder#:leftborder# - border# in sheet border list or blank if none
l:layout# - number in cell layout list
f:font# - number in sheet fonts list
c:color# - sheet color list index for text
bg:color# - sheet color list index for background color
cf:format# - sheet cell format number for explicit format (align:left, etc.)
cvf:valueformat# - sheet cell value format number (obsolete: only pre format v1.2)
tvf:valueformat# - sheet cell text value format number
ntvf:valueformat# - sheet cell non-text value format number
colspan:numcols - number of columns spanned in merged cell
rowspan:numrows - number of rows spanned in merged cell
cssc:classname - name of CSS class to be used for cell when published instead of one calculated here
csss:styletext - explicit CSS style information, encoded to handle :, etc.
mod:allow - if "y" allow modification of cell for live "view" recalc
col:
w:widthval - number, "auto" (no width in <col> tag), number%, or blank (use default)
hide: - yes/no, no is assumed if missing
row:
hide - yes/no, no is assumed if missing
sheet:
c:lastcol - number
r:lastrow - number
w:defaultcolwidth - number, "auto", number%, or blank (default->80)
h:defaultrowheight - not used
tf:format# - cell format number for sheet default for text values
ntf:format# - cell format number for sheet default for non-text values (i.e., numbers)
layout:layout# - default cell layout number in cell layout list
font:font# - default font number in sheet font list
vf:valueformat# - default number value format number in sheet valueformat list (obsolete: only pre format version 1.2)
ntvf:valueformat# - default non-text (number) value format number in sheet valueformat list
tvf:valueformat# - default text value format number in sheet valueformat list
color:color# - default number for text color in sheet color list
bgcolor:color# - default number for background color in sheet color list
circularreferencecell:coord - cell coord with a circular reference
recalc:value - on/off (on is default). If "on", appropriate changes to the sheet cause a recalc
needsrecalc:value - yes/no (no is default). If "yes", formula values are not up to date
name:name:description:value - name definition, name in uppercase, with value being "B5", "A1:B7", or "=formula"
font:fontnum:value - text of font definition (style weight size family) for font fontnum
"*" for "style weight", size, or family, means use default (first look to sheet, then builtin)
color:colornum:rgbvalue - text of color definition (e.g., rgb(255,255,255)) for color colornum
border:bordernum:value - text of border definition (thickness style color) for border bordernum
layout:layoutnum:value - text of vertical alignment and padding style for cell layout layoutnum:
vertical-alignment:vavalue;padding topval rightval bottomval leftval;
cellformat:cformatnum:value - text of cell alignment (left/center/right) for cellformat cformatnum
valueformat:vformatnum:value - text of number format (see format_value_for_display) for valueformat vformatnum (changed in v1.2)
clipboardrange:upperleftcoord:bottomrightcoord - origin of clipboard data. Not present if clipboard empty.
There must be a clipboardrange before any clipboard lines
clipboard:coord:type:value:... - clipboard data, in same format as cell data
The resulting $sheetdata data structure is as follows:
$sheetdata{version} - version of save file read in
$sheetdata{datatypes}->{$coord} - Origin of {datavalues} value:
v - typed in numeric value of some sort, constant, no formula
t - typed in text, constant, no formula
f - result of formula calculation ({formulas} has formula to calculate)
c - constant of some sort with typed in text in {formulas} and value in {datavalues}
$sheetdata{formulas}->{$coord} - Text of formula if {datatypes} is "f", no leading "=", or text of constant if "c"
$sheetdata{datavalues}->{$coord} - a text or numeric value ready to be formatted for display or used in calculation
$sheetdata{valuetypes}->{$coord} - the value type of the datavalue as 1 or more characters
First char is "n" for numeric or "t" for text
Second chars, if present, are sub-type, like "l" for logical (0=false, 1=true)
$sheetdata{cellerrors}->{$coord} - If non-blank, error text for error in formula calculation
$sheetdata{cellattribs}->{$coord}->
{coord} - coord of cell - existence means non-blank cell
{bt}, {br}, {bb}, {bl} - border number or null if no border
{layout} - cell layout number or blank for default
{font} - font number or blank for default
{color} - color number for text or blank for default
{bgcolor} - color number for the cell background or blank for default
{cellformat} - cell format number if not default - controls horizontal alignment
{textvalueformat} - value format number if not default - controls how the cell's text values are formatted into text for display
{nontextvalueformat} - value format number if not default - controls how the cell's non-text values are turned into text for display
{colspan}, {rowspan} - column span and row span for merged cells or blank for 1
{cssc}, {csss} - explicit CSS class and CSS style for cell
{mod} - if "y" allow modification in live view
$sheetdata{colattribs}->{$colcoord}->
{width} - column width if not default
{hide} - hide column if yes
$sheetdata{rowattribs}->{$rowcoord}->
{height} - ignored
{hide} - hide row if yes
$sheetdata{sheetattribs}->{$attrib}->
{lastcol} - number of columns in sheet
{lastrow} - number of rows in sheet (more may be displayed when editing)
{defaultcolwidth} - number, "auto", number%, or blank (default->80)
{defaultrowheight} - not used
{defaulttextformat} - cell format number for sheet default for text values
{defaultnontextformat} - cell format number for sheet default for non-text values (i.e., numbers)
{defaultlayout} - default cell layout number in sheet cell layout list
{defaultfont} - default font number in sheet font list
{defaulttextvalueformat} - default text value format number in sheet valueformat list
{defaultnontextvalueformat} - default number value format number in sheet valueformat list
{defaultcolor} - default number for text color in sheet color list
{defaultbgcolor} - default number for background color in sheet color list
{circularreferencecell} - cell coord with a circular reference
{recalc} - on/off (on is default). If "on", appropriate changes to the sheet cause a recalc
{needsrecalc} - yes/no (no is default). If "yes", formula values are not up to date
$sheetdata{names}->{$name}-> - name is uppercase
{desc} - description (optional)
{definiton} - in the form of B5, A1:B7, or =formula
$sheetdata{fonts}->[$index] - font specifications addressable by array position
$sheetdata{fonthash}->{$value} - hash with font specification as keys and {fonts}->[] index position as values
$sheetdata{colors}->[$index] - color specifications addressable by array position
$sheetdata{colorhash}->{$value} - hash with color specification as keys and {colors}->[] index position as values
$sheetdata{borderstyles}->[$index] - border style specifications addressable by array position
$sheetdata{borderstylehash}->{$value} - hash with border style specification as keys and {borderstyles}->[] index position as values
$sheetdata{layoutstyles}->[$index] - cell layout specifications addressable by array position
$sheetdata{layoutstylehash}->{$value} - hash with cell layout specification as keys and {layoutstyle}->[] index position as values
$sheetdata{cellformats}->[$index] - cell format specifications addressable by array position
$sheetdata{cellformathash}->{$value} - hash with cell format specification as keys and {cellformats}->[] index position as values
$sheetdata{valueformats}->[$index] - value format specifications addressable by array position
$sheetdata{valueformathash}->{$value} - hash with value format specification as keys and {valueformats}->[] index position as values
$sheetdata{clipboard}-> - the sheet's clipboard
{range} - coord:coord range of where the clipboard contents came from or null if empty
{datavalues} - like $sheetdata{datavalues} but for clipboard copy of cells
{datatypes} - like $sheetdata{datatypes} but for clipboard copy of cells
{valuetypes} - like $sheetdata{valuetypes} but for clipboard copy of cells
{formulas} - like $sheetdata{formulas} but for clipboard copy of cells
{cellerrors} - like $sheetdata{cellerrors} but for clipboard copy of cells
{cellattribs} - like $sheetdata{cellattribs} but for clipboard copy of cells
$sheetdata{loaderror} - if non-blank, there was an error loading this sheet and this is the text of that error
=cut
sub parse_sheet_save {
my ($lines, $sheetdata) = @_;
# Initialize sheetdata structure
$sheetdata->{datavalues} = {};
$sheetdata->{datatypes} = {};
$sheetdata->{valuetypes} = {};
$sheetdata->{formulas} = {};
$sheetdata->{cellerrors} = {};
$sheetdata->{cellattribs} = {};
$sheetdata->{colattribs} = {};
$sheetdata->{rowattribs} = {};
$sheetdata->{sheetattribs} = {};
$sheetdata->{layoutstyles} = [];
$sheetdata->{layoutstylehash} = {};
$sheetdata->{names} = {};
$sheetdata->{fonts} = [];
$sheetdata->{fonthash} = {};
$sheetdata->{colors} = [];
$sheetdata->{colorhash} = {};
$sheetdata->{borderstyles} = [];
$sheetdata->{borderstylehash} = {};
$sheetdata->{cellformats} = [];
$sheetdata->{cellformathash} = {};
$sheetdata->{valueformats} = [];
$sheetdata->{valueformathash} = {};
# Get references to the parts
my $datavalues = $sheetdata->{datavalues};
my $datatypes = $sheetdata->{datatypes};
my $valuetypes = $sheetdata->{valuetypes};
my $dataformulas = $sheetdata->{formulas};
my $cellerrors = $sheetdata->{cellerrors};
my $cellattribs = $sheetdata->{cellattribs};
my $colattribs = $sheetdata->{colattribs};
my $rowattribs = $sheetdata->{rowattribs};
my $sheetattribs = $sheetdata->{sheetattribs};
my $layoutstyles = $sheetdata->{layoutstyles};
my $layoutstylehash = $sheetdata->{layoutstylehash};
my $names = $sheetdata->{names};
my $fonts = $sheetdata->{fonts};
my $fonthash = $sheetdata->{fonthash};
my $colors = $sheetdata->{colors};
my $colorhash = $sheetdata->{colorhash};
my $borderstyles = $sheetdata->{borderstyles};
my $borderstylehash = $sheetdata->{borderstylehash};
my $cellformats = $sheetdata->{cellformats};
my $cellformathash = $sheetdata->{cellformathash};
my $valueformats = $sheetdata->{valueformats};
my $valueformathash = $sheetdata->{valueformathash};
my ($coord, $type, $rest);
my ($linetype, $value, $valuetype, $formula, $style, $namename, $namedesc);
my ($fontnum, $layoutnum, $colornum, $check, $row, $col);
lib/Spreadsheet/Engine/Sheet.pm view on Meta::CPAN
($value, $type, $rest) = split (/:/, $rest, 3);
$sheetattribs->{defaultbgcolor} = $value;
} elsif ($type eq "circularreferencecell")
{ #cell with a circular reference
($value, $type, $rest) = split (/:/, $rest, 3);
$sheetattribs->{circularreferencecell} = $value;
} elsif ($type eq "recalc") { #recalc on or off
($value, $type, $rest) = split (/:/, $rest, 3);
$sheetattribs->{recalc} = $value;
} elsif ($type eq "needsrecalc")
{ #recalculation needed, computed values may not be correct
($value, $type, $rest) = split (/:/, $rest, 3);
$sheetattribs->{needsrecalc} = $value;
} else {
$errortext = "Unknown type '$type' in line:\n$_\n";
last;
}
}
} elsif ($linetype eq "name") {
($namename, $namedesc, $value) = split (/:/, $rest, 3);
$namename = uc(decode_from_save($namename));
$names->{$namename}->{desc} = decode_from_save($namedesc);
$names->{$namename}->{definition} = decode_from_save($value);
} elsif ($linetype eq "layout") {
($layoutnum, $value) = split (/:/, $rest, 2);
$layoutstyles->[$layoutnum] = $value;
$layoutstylehash->{$value} = $layoutnum;
} elsif ($linetype eq "font") {
($fontnum, $value) = split (/:/, $rest, 2);
$fonts->[$fontnum] = $value;
$fonthash->{$value} = $fontnum;
} elsif ($linetype eq "color") {
($colornum, $value) = split (/:/, $rest, 2);
$colors->[$colornum] = $value;
$colorhash->{$value} = $colornum;
} elsif ($linetype eq "border") {
($style, $value) = split (/:/, $rest, 2);
$borderstyles->[$style] = $value;
$borderstylehash->{$value} = $style;
} elsif ($linetype eq "cellformat") {
($style, $value) = split (/:/, $rest, 2);
$cellformats->[$style] = decode_from_save($value);
$cellformathash->{$value} = $style;
} elsif ($linetype eq "valueformat") {
($style, $value) = split (/:/, $rest, 2);
$value = decode_from_save($value);
if ($sheetdata->{version} < 1.2) { # old format definitions - convert
$value =
length($old_formats_map{$value}) >= 1
? $old_formats_map{$value}
: $value;
}
if ($value eq "General-separator") { # convert from 0.91
$value = "[,]General";
}
$valueformats->[$style] = $value;
$valueformathash->{$value} = $style;
} elsif ($linetype eq "version") {
$sheetdata->{version} = $rest;
} elsif ($linetype eq "") {
} elsif ($linetype eq "clipboardrange") {
$sheetdata->{clipboard} = {}; # clear and create clipboard
$sheetdata->{clipboard}->{datavalues} = {};
$clipdatavalues = $sheetdata->{clipboard}->{datavalues};
$sheetdata->{clipboard}->{datatypes} = {};
$clipdatatypes = $sheetdata->{clipboard}->{datatypes};
$sheetdata->{clipboard}->{valuetypes} = {};
$clipvaluetypes = $sheetdata->{clipboard}->{valuetypes};
$sheetdata->{clipboard}->{formulas} = {};
$clipdataformulas = $sheetdata->{clipboard}->{formulas};
$sheetdata->{clipboard}->{cellerrors} = {};
$clipcellerrors = $sheetdata->{clipboard}->{cellerrors};
$sheetdata->{clipboard}->{cellattribs} = {};
$clipcellattribs = $sheetdata->{clipboard}->{cellattribs};
$coord = uc($rest);
$sheetdata->{clipboard}->{range} = $coord;
} elsif ($linetype eq "clipboard")
{ # must have a clipboardrange command somewhere before it
($coord, $type, $rest) = split (/:/, $rest, 3);
$coord = uc($coord);
if (!$sheetdata->{clipboard}->{range}) {
$errortext = "Missing clipboardrange before clipboard data in file\n";
$type = "norange";
}
$clipcellattribs->{$coord} = { 'coord', $coord };
while ($type) {
if ($type eq "v") {
($value, $type, $rest) = split (/:/, $rest, 3);
$clipdatavalues->{$coord} = decode_from_save($value);
$clipdatatypes->{$coord} = "v";
$clipvaluetypes->{$coord} = "n";
} elsif ($type eq "t") {
($value, $type, $rest) = split (/:/, $rest, 3);
$clipdatavalues->{$coord} = decode_from_save($value);
$clipdatatypes->{$coord} = "t";
$clipvaluetypes->{$coord} =
"tw"; # Typed in text is treated as wiki text by default
} elsif ($type eq "vt") {
($valuetype, $value, $type, $rest) = split (/:/, $rest, 4);
$clipdatavalues->{$coord} = decode_from_save($value);
if (substr($valuetype, 0, 1) eq "n") {
$clipdatatypes->{$coord} = "v";
} else {
$clipdatatypes->{$coord} = "t";
}
$clipvaluetypes->{$coord} = $valuetype;
} elsif ($type eq "vtf") {
($valuetype, $value, $formula, $type, $rest) =
split (/:/, $rest, 5);
$clipdatavalues->{$coord} = decode_from_save($value);
$clipdataformulas->{$coord} = decode_from_save($formula);
$clipdatatypes->{$coord} = "f";
$clipvaluetypes->{$coord} = $valuetype;
} elsif ($type eq "vtc") {
($valuetype, $value, $formula, $type, $rest) =
split (/:/, $rest, 5);
$clipdatavalues->{$coord} = decode_from_save($value);
$clipdataformulas->{$coord} = decode_from_save($formula);
$clipdatatypes->{$coord} = "c";
$clipvaluetypes->{$coord} = $valuetype;
} elsif ($type eq "vf") { # old format
($value, $formula, $type, $rest) = split (/:/, $rest, 4);
$clipdatavalues->{$coord} = decode_from_save($value);
$clipdataformulas->{$coord} = decode_from_save($formula);
$clipdatatypes->{$coord} = "f";
if (substr($value, 0, 1) eq "N") {
$clipvaluetypes->{$coord} = "n";
$clipdatavalues->{$coord} =
substr($clipdatavalues->{$coord}, 1); # remove initial type code
} elsif (substr($value, 0, 1) eq "T") {
$clipvaluetypes->{$coord} = "t";
$clipdatavalues->{$coord} =
substr($clipdatavalues->{$coord}, 1); # remove initial type code
} elsif (substr($value, 0, 1) eq "H") {
$clipvaluetypes->{$coord} = "th";
$clipdatavalues->{$coord} =
substr($clipdatavalues->{$coord}, 1); # remove initial type code
} else {
$clipvaluetypes->{$coord} =
$clipvaluetypes->{$coord} =~ m/[^0-9+\-\.]/ ? "t" : "n";
}
} elsif ($type eq "e") {
lib/Spreadsheet/Engine/Sheet.pm view on Meta::CPAN
$outstr .= "\n";
}
}
for (my $col = 1 ; $col <= $sheetattribs->{lastcol} ; $col++) {
$coord = cr_to_coord($col, 1);
$coord =~ s/\d+//;
$outstr .= "col:$coord:w:$colattribs->{$coord}->{width}\n"
if $colattribs->{$coord}->{width};
$outstr .= "col:$coord:hide:$colattribs->{$coord}->{hide}\n"
if $colattribs->{$coord}->{hide};
}
for (my $row = 1 ; $row <= $sheetattribs->{lastrow} ; $row++) {
$outstr .= "row:$row:w:$rowattribs->{$row}->{height}\n"
if $rowattribs->{$row}->{height};
$outstr .= "row:$row:hide:$rowattribs->{$row}->{hide}\n"
if $rowattribs->{$row}->{hide};
}
$outstr .= "sheet";
foreach my $field (keys %sheetfields) {
my $value = encode_for_save($sheetattribs->{$field});
$outstr .= ":$sheetfields{$field}:$value" if $value;
}
$outstr .= "\n";
foreach my $namename (sort keys %$names) {
my $namesc = encode_for_save(uc $namename);
my $namedescsc = encode_for_save($names->{$namename}->{desc});
my $namedefinitionsc = encode_for_save($names->{$namename}->{definition});
$outstr .= "name:$namesc:$namedescsc:$namedefinitionsc\n";
}
for (my $i = 1 ; $i < @$layoutstyles ; $i++) {
$outstr .= "layout:$i:$layoutstyles->[$i]\n";
}
for (my $i = 1 ; $i < @$fonts ; $i++) {
$outstr .= "font:$i:$fonts->[$i]\n";
}
for (my $i = 1 ; $i < @$colors ; $i++) {
$outstr .= "color:$i:$colors->[$i]\n";
}
for (my $i = 1 ; $i < @$borderstyles ; $i++) {
$outstr .= "border:$i:$borderstyles->[$i]\n";
}
for (my $i = 1 ; $i < @$cellformats ; $i++) {
$style = encode_for_save($cellformats->[$i]);
$outstr .= "cellformat:$i:$style\n";
}
for (my $i = 1 ; $i < @$valueformats ; $i++) {
$style = encode_for_save($valueformats->[$i]);
$outstr .= "valueformat:$i:$style\n";
}
if ($sheetdata->{clipboard}) {
my $clipdatavalues = $sheetdata->{clipboard}->{datavalues};
my $clipdatatypes = $sheetdata->{clipboard}->{datatypes};
my $clipvaluetypes = $sheetdata->{clipboard}->{valuetypes};
my $clipdataformulas = $sheetdata->{clipboard}->{formulas};
my $clipcellerrors = $sheetdata->{clipboard}->{cellerrors};
my $clipcellattribs = $sheetdata->{clipboard}->{cellattribs};
$outstr .= "clipboardrange:$sheetdata->{clipboard}->{range}\n";
foreach my $coord (sort keys %$clipcellattribs) {
$outstr .= "clipboard:$coord";
if ($clipdatatypes->{$coord} eq "v") {
$value = encode_for_save($clipdatavalues->{$coord});
if (!$clipvaluetypes->{$coord} || $clipvaluetypes->{$coord} eq "n")
{ # use simpler version
$outstr .= ":v:$value";
} else { # if we do fancy parsing to determine a type
$outstr .= ":vt:$clipvaluetypes->{$coord}:$value";
}
} elsif ($clipdatatypes->{$coord} eq "t") {
$value = encode_for_save($clipdatavalues->{$coord});
if (!$clipvaluetypes->{$coord} || $clipvaluetypes->{$coord} eq "tw")
{ # use simpler version
$outstr .= ":t:$value";
} else { # if we do fancy parsing to determine a type
$outstr .= ":vt:$clipvaluetypes->{$coord}:$value";
}
} elsif ($clipdatatypes->{$coord} eq "f") {
$value = encode_for_save($clipdatavalues->{$coord});
$formula = encode_for_save($clipdataformulas->{$coord});
$outstr .= ":vtf:$clipvaluetypes->{$coord}:$value:$formula";
} elsif ($clipdatatypes->{$coord} eq "c") {
$value = encode_for_save($clipdatavalues->{$coord});
$formula = encode_for_save($clipdataformulas->{$coord});
$outstr .= ":vtc:$clipvaluetypes->{$coord}:$value:$formula";
}
if ($clipcellerrors->{$coord}) {
$value = encode_for_save($clipcellerrors->{$coord});
$outstr .= ":e:$value";
}
my ($t, $r, $b, $l);
$t = $clipcellattribs->{$coord}->{bt};
$r = $clipcellattribs->{$coord}->{br};
$b = $clipcellattribs->{$coord}->{bb};
$l = $clipcellattribs->{$coord}->{bl};
$outstr .= ":b:$t:$r:$b:$l" if ($t || $r || $b || $l);
$outstr .= ":l:$clipcellattribs->{$coord}->{layout}"
if $clipcellattribs->{$coord}->{layout};
$outstr .= ":f:$clipcellattribs->{$coord}->{font}"
if $clipcellattribs->{$coord}->{font};
$outstr .= ":c:$clipcellattribs->{$coord}->{color}"
if $clipcellattribs->{$coord}->{color};
$outstr .= ":bg:$clipcellattribs->{$coord}->{bgcolor}"
if $clipcellattribs->{$coord}->{bgcolor};
$outstr .= ":cf:$clipcellattribs->{$coord}->{cellformat}"
if $clipcellattribs->{$coord}->{cellformat};
$outstr .= ":tvf:$clipcellattribs->{$coord}->{textvalueformat}"
if $clipcellattribs->{$coord}->{textvalueformat};
$outstr .= ":ntvf:$clipcellattribs->{$coord}->{nontextvalueformat}"
if $clipcellattribs->{$coord}->{nontextvalueformat};
$outstr .= ":colspan:$clipcellattribs->{$coord}->{colspan}"
if $clipcellattribs->{$coord}->{colspan};
$outstr .= ":rowspan:$clipcellattribs->{$coord}->{rowspan}"
if $clipcellattribs->{$coord}->{rowspan};
$outstr .= ":cssc:$clipcellattribs->{$coord}->{cssc}"
if $clipcellattribs->{$coord}->{cssc};
$outstr .=
":csss:" . encode_for_save($clipcellattribs->{$coord}->{csss})
if $clipcellattribs->{$coord}->{csss};
$outstr .= ":mod:$clipcellattribs->{$coord}->{mod}"
if $clipcellattribs->{$coord}->{mod};
$outstr .= "\n";
}
}
return $outstr;
}
=head2 execute_sheet_command
$ok = execute_sheet_command($sheetdata, $command);
Executes commands that modify the sheet data. Sets sheet "needsrecalc" as needed.
The commands are in the forms:
set sheet attributename value (plus lastcol and lastrow)
set 22 attributename value
set B attributename value
set A1 attributename value1 value2... (see each attribute below for details)
set A1:B5 attributename value1 value2...
erase/copy/cut/paste/fillright/filldown A1:B5 all/formulas/format
clearclipboard
merge C3:F3
unmerge C3
insertcol/insertrow C5
deletecol/deleterow C5:E7
name define NAME definition
name desc NAME description
name delete NAME
=cut
sub execute_sheet_command {
my ($sheetdata, $command) = @_;
# Get references to the parts
my $datavalues = $sheetdata->{datavalues};
my $datatypes = $sheetdata->{datatypes};
my $valuetypes = $sheetdata->{valuetypes};
my $dataformulas = $sheetdata->{formulas};
my $cellerrors = $sheetdata->{cellerrors};
my $cellattribs = $sheetdata->{cellattribs};
my $colattribs = $sheetdata->{colattribs};
my $rowattribs = $sheetdata->{rowattribs};
my $sheetattribs = $sheetdata->{sheetattribs};
my $layoutstyles = $sheetdata->{layoutstyles};
my $layoutstylehash = $sheetdata->{layoutstylehash};
my $names = $sheetdata->{names};
my $fonts = $sheetdata->{fonts};
my $fonthash = $sheetdata->{fonthash};
my $colors = $sheetdata->{colors};
my $colorhash = $sheetdata->{colorhash};
my $borderstyles = $sheetdata->{borderstyles};
my $borderstylehash = $sheetdata->{borderstylehash};
my $cellformats = $sheetdata->{cellformats};
my $cellformathash = $sheetdata->{cellformathash};
my $valueformats = $sheetdata->{valueformats};
my $valueformathash = $sheetdata->{valueformathash};
my (
$what, $coord1, $coord2, $attrib, $name,
$value, $v1, $v2, $v3, $errortext
);
my ($cmd1, $rest) = split (/ /, $command, 2);
return unless $cmd1;
if ($cmd1 eq "set") {
($what, $attrib, $rest) = split (/ /, $rest, 3);
if ($what eq "sheet") { # sheet attributes
if ($attrib eq "defaultcolwidth") {
$sheetattribs->{defaultcolwidth} = $rest;
} elsif ($attrib eq "defaultcolor" || $attrib eq "defaultbgcolor") {
my $colordef = 0;
$colordef = $colorhash->{$rest} if $rest;
if (!$colordef) {
if ($rest) {
push @$colors, "" unless scalar @$colors;
$colordef = (push @$colors, $rest) - 1;
$colorhash->{$rest} = $colordef;
lib/Spreadsheet/Engine/Sheet.pm view on Meta::CPAN
}
}
}
$sheetdata->{sheetattribs}->{needsrecalc} = "yes";
}
elsif ($cmd1 eq "fillright" || $cmd1 eq "filldown") {
my ($fillright, $rowstart, $colstart);
if ($cmd1 eq "fillright") {
$fillright = 1;
$rowstart = $r1;
$colstart = $c1 + 1;
} else {
$rowstart = $r1 + 1;
$colstart = $c1;
}
for (my $r = $rowstart ; $r <= $r2 ; $r++) {
for (my $c = $colstart ; $c <= $c2 ; $c++) {
my $cr = cr_to_coord($c, $r);
my ($crbase, $rowoffset, $coloffset);
if ($fillright) {
$crbase = cr_to_coord($c1, $r);
$coloffset = $c - $colstart + 1;
$rowoffset = 0;
} else {
$crbase = cr_to_coord($c, $r1);
$coloffset = 0;
$rowoffset = $r - $rowstart + 1;
}
if ($rest eq "all" || $rest eq "formats") {
$cellattribs->{$cr} = { 'coord' => $cr }; # Start with minimal set
foreach my $attribtype (keys %{ $cellattribs->{$crbase} }) {
if ($attribtype ne "coord") {
$cellattribs->{$cr}->{$attribtype} =
$cellattribs->{$crbase}->{$attribtype};
}
}
}
if ($rest eq "all" || $rest eq "formulas") {
$cellattribs->{$cr} = { 'coord' => $cr }
unless $cellattribs->{$cr}->{coord}; # Make sure this exists
$datavalues->{$cr} = $datavalues->{$crbase};
$datatypes->{$cr} = $datatypes->{$crbase};
$valuetypes->{$cr} = $valuetypes->{$crbase};
if ($datatypes->{$cr} eq "f")
{ # offset relative coords, even in sheet references
$dataformulas->{$cr} =
offset_formula_coords($dataformulas->{$crbase},
$coloffset, $rowoffset);
} else {
$dataformulas->{$cr} = $dataformulas->{$crbase};
}
$cellerrors->{$cr} = $cellerrors->{$crbase};
}
}
}
$sheetdata->{sheetattribs}->{needsrecalc} = "yes";
}
elsif ($cmd1 eq "copy" || $cmd1 eq "cut") {
$sheetdata->{clipboard} = {}; # clear and create clipboard
$sheetdata->{clipboard}->{datavalues} = {};
my $clipdatavalues = $sheetdata->{clipboard}->{datavalues};
$sheetdata->{clipboard}->{datatypes} = {};
my $clipdatatypes = $sheetdata->{clipboard}->{datatypes};
$sheetdata->{clipboard}->{valuetypes} = {};
my $clipvaluetypes = $sheetdata->{clipboard}->{valuetypes};
$sheetdata->{clipboard}->{formulas} = {};
my $clipdataformulas = $sheetdata->{clipboard}->{formulas};
$sheetdata->{clipboard}->{cellerrors} = {};
my $clipcellerrors = $sheetdata->{clipboard}->{cellerrors};
$sheetdata->{clipboard}->{cellattribs} = {};
my $clipcellattribs = $sheetdata->{clipboard}->{cellattribs};
for (my $r = $r1 ; $r <= $r2 ; $r++) {
for (my $c = $c1 ; $c <= $c2 ; $c++) {
my $cr = cr_to_coord($c, $r);
$clipcellattribs->{$cr}->{ 'coord' => $cr } =
''; # make sure something (used for save)
if ($rest eq "all" || $rest eq "formats") {
foreach my $attribtype (keys %{ $cellattribs->{$cr} }) {
$clipcellattribs->{$cr}->{$attribtype} =
$cellattribs->{$cr}->{$attribtype};
}
if ($cmd1 eq "cut") {
delete $cellattribs->{$cr};
$cellattribs->{$cr} = { 'coord' => $cr } if $rest eq "formats";
}
}
if ($rest eq "all" || $rest eq "formulas") {
$clipcellattribs->{$cr}->{coord} =
$cellattribs->{$cr}->{coord}; # used by save
$clipdatavalues->{$cr} = $datavalues->{$cr};
$clipdataformulas->{$cr} = $dataformulas->{$cr};
$clipcellerrors->{$cr} = $cellerrors->{$cr};
$clipdatatypes->{$cr} = $datatypes->{$cr};
$clipvaluetypes->{$cr} = $valuetypes->{$cr};
if ($cmd1 eq "cut") {
delete $datavalues->{$cr};
delete $dataformulas->{$cr};
delete $cellerrors->{$cr};
delete $datatypes->{$cr};
delete $valuetypes->{$cr};
}
}
}
}
$sheetdata->{clipboard}->{range} =
$coord2 ? "$coord1:$coord2" : "$coord1:$coord1";
$sheetdata->{sheetattribs}->{needsrecalc} = "yes" if $cmd1 eq "cut";
}
elsif ($cmd1 eq "paste") {
my $crbase = $sheetdata->{clipboard}->{range};
if (!$crbase) {
$errortext = "Empty clipboard\n";
return 0;
}
my $clipdatavalues = $sheetdata->{clipboard}->{datavalues};
my $clipdatatypes = $sheetdata->{clipboard}->{datatypes};
my $clipvaluetypes = $sheetdata->{clipboard}->{valuetypes};
my $clipdataformulas = $sheetdata->{clipboard}->{formulas};
my $clipcellerrors = $sheetdata->{clipboard}->{cellerrors};
my $clipcellattribs = $sheetdata->{clipboard}->{cellattribs};
my ($clipcoord1, $clipcoord2) = split (/:/, $crbase);
$clipcoord2 = $clipcoord1 unless $clipcoord2;
my ($clipc1, $clipr1) = coord_to_cr($clipcoord1);
my ($clipc2, $clipr2) = coord_to_cr($clipcoord2);
my $coloffset = $c1 - $clipc1;
my $rowoffset = $r1 - $clipr1;
my $numcols = $clipc2 - $clipc1 + 1;
my $numrows = $clipr2 - $clipr1 + 1;
$sheetattribs->{lastcol} = $c1 + $numcols - 1
if $c1 + $numcols - 1 > $sheetattribs->{lastcol};
$sheetattribs->{lastrow} = $r1 + $numrows - 1
if $r1 + $numrows - 1 > $sheetattribs->{lastrow};
for (my $r = 0 ; $r < $numrows ; $r++) {
for (my $c = 0 ; $c < $numcols ; $c++) {
my $cr = cr_to_coord($c1 + $c, $r1 + $r);
my $clipcr = cr_to_coord($clipc1 + $c, $clipr1 + $r);
if ($rest eq "all" || $rest eq "formats") {
$cellattribs->{$cr} = { 'coord' => $cr }; # Start with minimal set
foreach my $attribtype (keys %{ $clipcellattribs->{$clipcr} }) {
if ($attribtype ne "coord") {
$cellattribs->{$cr}->{$attribtype} =
$clipcellattribs->{$clipcr}->{$attribtype};
}
}
}
if ($rest eq "all" || $rest eq "formulas") {
$cellattribs->{$cr} = { 'coord' => $cr }
unless $cellattribs->{$cr}->{coord}; # Make sure this exists
$datavalues->{$cr} = $clipdatavalues->{$clipcr};
$datatypes->{$cr} = $clipdatatypes->{$clipcr};
$valuetypes->{$cr} = $clipvaluetypes->{$clipcr};
if ($datatypes->{$cr} eq "f")
{ # offset coord refs, even to *** relative *** coords in other sheets
$dataformulas->{$cr} =
offset_formula_coords($clipdataformulas->{$clipcr},
$coloffset, $rowoffset);
} else {
$dataformulas->{$cr} = $clipdataformulas->{$clipcr};
}
$cellerrors->{$cr} = $clipcellerrors->{$clipcr};
}
}
}
$sheetdata->{sheetattribs}->{needsrecalc} = "yes";
}
elsif ($cmd1 eq "sort")
{ # sort cr1:cr2 col1 up/down col2 up/down col3 up/down
my @col_dirs = split (/\s+/, $rest);
my (@cols, @dirs);
($cols[1], $dirs[1], $cols[2], $dirs[2], $cols[3], $dirs[3]) =
@col_dirs;
my $nsortcols = int((scalar @col_dirs) / 2);
my $sortdata = {}; # make a place to hold data to sort
$sortdata->{datavalues} = {};
my $sortdatavalues = $sortdata->{datavalues};
$sortdata->{datatypes} = {};
my $sortdatatypes = $sortdata->{datatypes};
lib/Spreadsheet/Engine/Sheet.pm view on Meta::CPAN
$cresult = $sortvalues[$a1][$i] <=> $sortvalues[$b1][$i];
} elsif ($tb eq "b") {
$cresult = $dirs[ $i + 1 ] eq "up" ? -1 : 1;
} else {
$cresult = 1;
}
} elsif ($ta eq "b") {
if ($tb eq "b") {
$cresult = 0;
} else {
$cresult = $dirs[ $i + 1 ] eq "up" ? 1 : -1;
}
}
return $cresult if $cresult;
}
return $a cmp $b;
} @sortlist;
my $originalrow;
for (my $r = $r1 ; $r <= $r2 ; $r++)
{ # copy original back over in new rows
$originalrow = $sortlist[ $r - $r1 ];
for (my $c = $c1 ; $c <= $c2 ; $c++) {
my $cr = cr_to_coord($c, $r);
my $sortedcr = cr_to_coord($c, $r1 + $originalrow);
if (!$sortcellattribs->{$sortedcr}->{coord})
{ # copying an empty cell
delete $cellattribs->{$cr};
delete $datavalues->{$cr};
delete $dataformulas->{$cr};
delete $cellerrors->{$cr};
delete $datatypes->{$cr};
delete $valuetypes->{$cr};
next;
}
$cellattribs->{$cr} = { 'coord' => $cr };
foreach my $attribtype (keys %{ $sortcellattribs->{$sortedcr} }) {
if ($attribtype ne "coord") {
$cellattribs->{$cr}->{$attribtype} =
$sortcellattribs->{$sortedcr}->{$attribtype};
}
}
$datavalues->{$cr} = $sortdatavalues->{$sortedcr};
$datatypes->{$cr} = $sortdatatypes->{$sortedcr};
$valuetypes->{$cr} = $sortvaluetypes->{$sortedcr};
if ($sortdatatypes->{$sortedcr} eq "f")
{ # offset coord refs, even to ***relative*** coords in other sheets
$dataformulas->{$cr} =
offset_formula_coords($sortdataformulas->{$sortedcr},
0, ($r - $r1) - $originalrow);
} else {
$dataformulas->{$cr} = $sortdataformulas->{$sortedcr};
}
$cellerrors->{$cr} = $sortcellerrors->{$sortedcr};
}
}
$sheetdata->{sheetattribs}->{needsrecalc} = "yes";
}
}
elsif ($cmd1 eq "clearclipboard") {
delete $sheetdata->{clipboard};
}
elsif ($cmd1 eq "merge") {
($what, $rest) = split (/ /, $rest, 2);
$what = uc($what);
($coord1, $coord2) = split (/:/, $what);
my ($c1, $r1) = coord_to_cr($coord1);
my $c2 = $c1;
my $r2 = $r1;
($c2, $r2) = coord_to_cr($coord2) if $coord2;
$sheetattribs->{lastcol} = $c2 if $c2 > $sheetattribs->{lastcol};
$sheetattribs->{lastrow} = $r2 if $r2 > $sheetattribs->{lastrow};
$cellattribs->{$coord1} = { 'coord' => $coord1 }
unless $cellattribs->{$coord1}->{coord};
delete $cellattribs->{$coord1}->{colspan};
$cellattribs->{$coord1}->{colspan} = $c2 - $c1 + 1 if $c2 > $c1;
delete $cellattribs->{$coord1}->{rowspan};
$cellattribs->{$coord1}->{rowspan} = $r2 - $r1 + 1 if $r2 > $r1;
}
elsif ($cmd1 eq "unmerge") {
($what, $rest) = split (/ /, $rest, 2);
$what = uc($what);
($coord1, $coord2) = split (/:/, $what);
$cellattribs->{$coord1} = { 'coord' => $coord1 }
unless $cellattribs->{$coord1}->{coord};
delete $cellattribs->{$coord1}->{colspan};
delete $cellattribs->{$coord1}->{rowspan};
}
elsif ($cmd1 eq "insertcol" || $cmd1 eq "insertrow") {
($what, $rest) = split (/ /, $rest, 2);
$what = uc($what);
($coord1, $coord2) = split (/:/, $what);
my ($c1, $r1) = coord_to_cr($coord1);
my $lastcol = $sheetattribs->{lastcol};
my $lastrow = $sheetattribs->{lastrow};
my (
$coloffset, $rowoffset, $colend, $rowend,
$newcolstart, $newcolend, $newrowstart, $newrowend
);
if ($cmd1 eq "insertcol") {
$coloffset = 1;
$colend = $c1;
$rowend = 1;
$newcolstart = $c1;
$newcolend = $c1;
$newrowstart = 1;
$newrowend = $lastrow;
} else {
$rowoffset = 1;
$rowend = $r1;
$colend = 1;
$newcolstart = 1;
$newcolend = $lastcol;
$newrowstart = $r1;
( run in 0.994 second using v1.01-cache-2.11-cpan-2398b32b56e )