Spreadsheet-Engine

 view release on metacpan or  search on metacpan

lib/Spreadsheet/Engine/Sheet.pm  view on Meta::CPAN

    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"

lib/Spreadsheet/Engine/Sheet.pm  view on Meta::CPAN

   $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}      = {};

lib/Spreadsheet/Engine/Sheet.pm  view on Meta::CPAN

          : $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") {

lib/Spreadsheet/Engine/Sheet.pm  view on Meta::CPAN

  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});

lib/Spreadsheet/Engine/Sheet.pm  view on Meta::CPAN

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

lib/Spreadsheet/Engine/Sheet.pm  view on Meta::CPAN

              $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};

lib/Spreadsheet/Engine/Sheet.pm  view on Meta::CPAN

            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

lib/Spreadsheet/Engine/Sheet.pm  view on Meta::CPAN

          } 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;