package OpenOffice::OOCBuilder;

# Copyright 2004, Stefan Loones
# More info can be found at
# This library is free software; you can redistribute it and/or
# modify it under the same terms as Perl itself.

use v5.8.0;                   # lower versions not tested
use strict;
use warnings;
no warnings 'uninitialized';  # don't want this, because we use strict
use OpenOffice::OOBuilder;
our (@ISA);

my $VERSION=sprintf("%d.%02d", q$Revision: 0.6 $ =~ /(\d+)\.(\d+)/);

$MAXC=256;     # is column IV
# - possible types ($TYPES[0] is default type)
@TYPES=('standard', 'text', 'float', 'formula');

# ** TODO push & pop cell locations (incl sheetnb) - to make formulas easier to construct
#         create tags for cell locations
#         cell-format ? (seems with numeric styles, not possible in cell directly)

# - Object constructor
sub new {
  my ($class, $self);

  # - active data
  $self->{act}{1}{c}=1;       # {act}{sheetnb}{c}=
  # - general data (parameters)  
  # - data
  $self->{cdata}    = undef;    # {cdata}{sheetnb}{}{} 
  $self->{sheetname}= undef;    # {sheetname}{sheetnb}=name
  $self->{cstyle}   = undef;    # {cstyle}{sheetnb}{}{}
  $self->{colwidth} = undef;    # {colwidth}{sheetnb}{c}
  $self->{rowheight}= undef;    # {rowheight}{sheetnb}{r}

  # - defaults (specific ooc - see other defaults in parent class
  $self->{defcolwidth} = '0.8925inch';
# **  $self->{defrowheight} = '0.8925inch';
  return $self;
}   # - - End new (Object constructor)

sub add_sheet {
  my ($self);
  if ($self->{cpars}{sheets}<$MAXSHEETS) {

sub goto_sheet {
  my ($self, $sheet)=@_;
  if ($sheet > $self->{cpars}{sheets}) {
  } elsif ($sheet < 1) {
  } else {

sub set_sheet_name {
  my ($self, $name, $sheet)=@_;
# ** process name: check valid characters en length ?!
  if ($name) {
    $sheet=$self->{actsheet} if (! $sheet);
    if ($sheet>0 && $sheet <=$self->{cpars}{sheets}) {

sub set_colwidth {
  my ($self, $c, $width)=@_;
  $c=$self->_check_column ($c);
# ** do we need to check $width ?

sub set_rowheight {
  my ($self, $r, $height)=@_;
  $r=$self->_check_row ($r);

sub goto_xy {
  my ($self, $c, $r)=@_;
  $c=$self->_check_column ($c);
  $r=$self->_check_row ($r);

sub goto_cell {
  my ($self, $cell)=@_;
  $cell=~ s/^([A-Z]+)([0-9]+)/$1$2/;
  $self->goto_xy ($1, $2);

sub get_column {
  my $self=shift;
  return $self->_convert_column ($self->{act}{$self->{actsheet}}{c});

sub get_x {
  my $self=shift;
  return $self->{act}{$self->{actsheet}}{c};

sub get_row {
  my $self=shift;
  return $self->{act}{$self->{actsheet}}{r};

sub get_y {
  my $self=shift;
  return $self->{act}{$self->{actsheet}}{r};

sub get_xy {
  my $self=shift;
  return ($self->{act}{$self->{actsheet}}{c}, $self->{act}{$self->{actsheet}}{r});

sub get_cell_id {
  my $self=shift;
  my $cell=$self->_convert_column ($self->{act}{$self->{actsheet}}{c});
  return $cell . $self->{act}{$self->{actsheet}}{r};

# - PublicMethod: set_data : set_data in active sheet/cell, with active style
#     API: set_data ($data, $type, $format)
#                   $type && $format can be ommitted
sub set_data {
  my ($self, $data, $type, $format)=@_;
  return $self->set_data_sheet_xy(
           $self->{actsheet}, $self->{act}{1}{c}, $self->{act}{1}{r}, 
           $data, $type, $format);

sub set_data_xy {
  my ($self, $c, $r, $data, $type, $format)=@_;
  return $self->set_data_sheet_xy($self->{actsheet}, $c, $r, $data, $type, $format);

sub set_data_sheet_xy {
  my ($self, $sheet, $c, $r, $data, $type, $format)=@_;
  # - check sheet
  if ($sheet != $self->{actsheet}) {
    $self->goto_sheet ($sheet);
  # - check cell
  if ($c ne $self->{act}{$sheet}{c} || $r != $self->{act}{$sheet}{r}) {
    $self->goto_xy ($c, $r);

  # - check type
  my ($ok);
  if ($type) {
    foreach (@TYPES) {
       if ($type eq $_) {
  $type=$TYPES[0] if (! $ok);  # take $TYPES[0] as default type
  # - check format
# ** 
  # - check data
  $data=$self->encode_data ($data) if ($data);

  # - store (ATTENTION $r before $c because of the way we need to generate xml)
  $self->{cdata}{$sheet}{$r}{$c}{format}=$format if ($format);
  $self->cell_update if ($self->{cpars}{autoc} || $self->{cpars}{autor});
}   # - - End set_data_sheet_xy

sub set_auto_xy {
  my ($self, $c, $r)=@_;

sub get_auto_x {
  my $self=shift;
  return $self->{cpars}{autoc};

sub get_auto_y {
  my $self=shift;
  return $self->{cpars}{autor};

sub cell_update {
  my $self=shift;
  if ($self->{cpars}{autoc}) {
    if ($self->{cpars}{autoc}>0) {
    } else {
  if ($self->{cpars}{autor}) {
    if ($self->{cpars}{autor}>0) {
    } else {

sub move_cell {
  my ($self, $direction, $number)=@_;
  $number=1 if (! $number);
  if ($direction eq 'left') {
  } elsif ($direction eq 'right') {
  } elsif ($direction eq 'down') {
  } elsif ($direction eq 'up') {
  } else {
# ** direction unknown

# - generate ooc specific, then call parent to complete generation
sub generate {
  my ($self, $tgtfile)=@_;
  my ($subGetMaxRange);
  $subGetMaxRange=sub {
    my ($hr, $max, @keys);
    @keys=sort {$a <=> $b} (keys(%$hr));
    return (pop(@keys));
  # - Build content.xml  
  $self->{contentxml}=q{<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE office:document-content PUBLIC "-// OfficeDocument 1.0//EN" "office.dtd">
<office:document-content xmlns:office="" xmlns:style="" xmlns:text="" xmlns:table="" xmlns:draw="" xmlns:fo="" xmlns:xlink="" xmlns:number="" xmlns:svg="" xmlns:chart="" xmlns:dr3d="" xmlns:math="" xmlns:form="" xmlns:script="" office:class="spreadsheet" office:version="1.0">

  # Styles will be done later, because they depend on the content

# **  $self->{rowheight}{$self->{actsheet}}{c}=$height;  still to implement

  # Beginning of document content
  my ($content);

  my ($sheet, $sheetname, $c, $columns, $r, $rows, $type, $format, $data);
  my ($style, $stylexml);
  my (%cellstyleids, $cellmaxid, %cellstylexml);
  my (%colstyleids, $colmaxid, %colstylexml, $colwidth);
  my ($colid, $prevcolid, $width, $t);
  my (%rowstyleids, $rowmaxid, %rowstylexml);
  for (1 .. $self->{cpars}{sheets}) {
    if ($self->{sheetname}{$sheet}) {
    } else {
    $content.=qq{<table:table table:name="$sheetname" table:style-name="ta1">};
    foreach $c (sort {$a <=> $b} keys(%{$self->{colwidth}{$sheet}})) {
      if (! $colstyleids{$width} && $width) {
    if ($self->{colwidth}{$sheet}{1}) {
    } else {
    for ($c=2;$c<=256;++$c) {
      if ($self->{colwidth}{$sheet}{$c}) {
      } else {
      if ($colid eq $prevcolid) {
      } else {
        if ($t>1) {
          $content.=qq{<table:table-column table:style-name="$prevcolid" table:number-columns-repeated="$t" table:default-cell-style-name="Default"/>};
        } else {
          $content.=qq{<table:table-column table:style-name="$prevcolid" table:default-cell-style-name="Default"/>};
    if ($t>1) {
      $content.=qq{<table:table-column table:style-name="$prevcolid" table:number-columns-repeated="$t" table:default-cell-style-name="Default"/>};
    } else {
      $content.=qq{<table:table-column table:style-name="$prevcolid" table:default-cell-style-name="Default"/>};
    $rows=&$subGetMaxRange ($self->{cdata}{$sheet});
    for (1 .. $rows) {
# ** row style ?
      $content.=q{<table:table-row table:style-name="ro1">};
      $columns=&$subGetMaxRange ($self->{cdata}{$sheet}{$r});
      for (1 .. $columns) {
        if ($style eq $self->{defstyle} || ! $style) {
        } else {
          if (! exists($cellstyleids{$style})) {
            $cellstylexml{$cellstyleids{$style}}=qq{ table:style-name="$cellstyleids{$style}"};
        if ($type eq 'standard' || $type eq 'text') {
        } elsif ($type eq 'float') {
qq{<table:table-cell$stylexml table:value-type="float" table:value="$data">
        } elsif ($type eq 'formula') {
qq{<table:table-cell$stylexml table:value-type="float" table:formula="$data" table:value="">
        } elsif ($type eq 'others') {
# **
        } else {
  # - Process used fonts and used cell styles
  my ($bold, $italic, $underline, $align, $txtcolor, $bgcolor, $font, $size);
  my ($defbold, $defitalic, $defunderline, $defalign, $deftxtcolor, $defbgcolor);
  my ($deffont, $defsize, %usedfonts, $xml, %stylexml);
  ($defbold, $defitalic, $defunderline, $defalign, $deftxtcolor, $defbgcolor, 
   $deffont, $defsize)=split(/#/, $self->{defstyle});
  foreach $style (keys(%cellstyleids)) {
    ($bold, $italic, $underline, $align, $txtcolor, $bgcolor, $font, $size)=
      split(/#/, $style);
qq{<style:style style:name="$cellstyleids{$style}" style:family="table-cell" style:parent-style-name="Default">
    if ($bgcolor ne $defbgcolor) {
      $xml.=qq{ fo:background-color="#$bgcolor"};
    if ($align ne $defalign) {
      $xml.=qq{ fo:text-align="$align" style:text-align-source="fix" fo:margin-left="0inch"};
    if ($txtcolor ne $deftxtcolor) {
      $xml.=qq{ fo:color="#$txtcolor"};
    if ($font ne $deffont) {
      $xml.=qq{ style:font-name="$font"};
    if ($size ne $defsize) {
      $xml.=q{ fo:font-size="} . $size . q{pt"}
    if ($italic ne $defitalic) {
      if ($italic) {
        $xml.=q{ fo:font-style="italic"};
      } else {
        $xml.=q{ fo:font-style="normal"};
    if ($underline ne $defunderline) {
      if ($underline) {
        $xml.=q{ style:text-underline="single" style:text-underline-color="font-color"};
      } else {
        $xml.=q{ style:text-underline="normal"};
    if ($bold ne $defbold) {
      if ($bold) {
        $xml.=q{ fo:font-weight="bold"};
      } else {
        $xml.=q{ fo:font-weight="normal"};
  # - Fonts
  foreach $font (sort(keys(%usedfonts))) {
  # - col styles
  foreach $width (keys(%colstyleids)) {
      qq{<style:style style:name="$colstyleids{$width}" style:family="table-column">
<style:properties fo:break-before="auto" style:column-width="$width"/></style:style>};
  foreach $colid (sort(keys(%colstylexml))) {

# ** look at row styles ?
# qq{
# <style:style style:name="ro1" style:family="table-row">
# <style:properties fo:break-before="auto"/></style:style>
# <style:style style:name="ta1" style:family="table" style:master-page-name="Default">
# <style:properties table:display="true"/></style:style>};

  # - cell styles
  foreach $style (sort(keys(%stylexml))) {
  $self->SUPER::generate ($tgtfile);

# - * - PrivateMethods

sub _check_column {
  my ($self, $c)=@_;
  if ($c =~ /[A-Za-z]/) {
    # - convert to number
    my (@char, $char, $multi, $newx);
    $c=~ s/[^A-Za-z]//g;   # we don't want anything else when using letters
    while (@char) {
  $c=1 if ($c<1);
  $c=$MAXC if ($c>$MAXC);
  return $c;

sub _convert_column {
  my ($self, $col)=@_;
  my $cell;
  while ($col>26) {
    my $div=int($col/26);
  $cell.=chr($col+64) if ($col>0);
  return $cell;

sub _check_row {
  my ($self, $r)=@_;
  $r=1 if ($r<1);
  $r=$MAXR if ($r>$MAXR);
  return $r;

sub _cell_check {
  my ($self);

# ** check sheet ($self->{actsheet})
  my $sheet=$self->{actsheet};  # only for readability
  $self->{act}{$sheet}{c}=1 if ($self->{act}{$sheet}{c}<1);
  $self->{act}{$sheet}{r}=1 if ($self->{act}{$sheet}{r}<1);
  $self->{act}{$sheet}{c}=$MAXC if ($self->{act}{$sheet}{c}>$MAXC);
  $self->{act}{$sheet}{r}=$MAXR if ($self->{act}{$sheet}{r}>$MAXR);



=head1 NAME

OpenOffice::OOCBuilder - Perl OO interface for creating 
                         OpenOffice Spreadsheets


  use OpenOffice::OOCBuilder;

  This constructor will call the constructor of OOBuilder.


OOCBuilder is a Perl OO interface to create OpenOffice spreadsheets. 
Documents can be created with multiple sheets, different styles, 
cell types, formulas, column widths and so on.

=head1 METHODS


  Create a new spreadsheet object


  Add a new sheet within the document. Active sheet is not changed. 
  You need to call goto_sheet (sheetnumber) to change the active 

goto_sheet ($sheetnumber)

  Set $sheetnumber as active sheet.

set_sheet_name ($name, $sheetnumber)

  Set the name of the sheet. If $sheetnumber is ommitted (or 0), the 
  name of the active sheet is set.

set_colwidth ($c, $width)

  Set the column width for the specified column ($c). The column can 
  be a number or letter(s).

set_rowheight ($r, $height)

  Set the row height for the specified row ($r).

goto_xy ($c, $r)

  Set the active cell to ($c, $r). The column can be specified by 
  number or letter(s).

goto_cell ($cell_id)

  Set the active cell to ($cell_id). This way you can use spreadsheet
  notations (i.e. A5 or BA401 and so on)


  Returns the active column in letters.


  Returns the active column as a number (starting at 1).


  Returns the row as a number (starting at 1)


  Returns the row as a number (starting at 1). Same as get_row.


  Returns the column and row as two numbers in a list.


  Returns the cell id in the form A1, AB564, and so on. Especially handy
  to create formulas. When you are at the start position, memorise the 
  cell_id. See in the examples directory.

set_data ($data, $type, $format)

  Set data in the active cell within the active sheet. If type is ommitted
  the standard type is taken.

set_data_xy ($c, $r, $data, $type, $format)

  Same as set_data, but now with column and row to set the data in. The 
  column can be specified as a number or with letter(s). 

set_data_sheet_xy ($sheet, $c, $r, $data, $type, $format)

  Same as set_data, but now with sheet, column and row to set the data in.
  The column can be specified as a number or with letter(s). 

set_auto_xy ($x, $y)

  When entering data in a cell, we move to another cell if auto_x or y
  is set.
   X value: 0: no movement, negative: move left, positive: move right.
   Y valye: 0: no movement, negative: move up, positive: move down.


  Returns the auto_x value.


  Returns the auto_y value.


  This method is called always when entering data in a cell. If auto_x
  or auto_y is set, if will move to another active cell. You can also 
  use this method to move to another cell without entering data in the
  previous cell.

move_cell ($direction, $number)

  Move to cell in $direction where $direction is 'left', 'right', 'up' or
  'down'. If you ommit $number, the move will be one row or column.

generate ($tgtfile)

  Generates the sxc file. $tgtfile is the name of the target file without
  extension. If no name is supplied, the default name will be used,
  which is oo_doc. The target directory is '.', you can set this by 
  calling the OOBuilder method set_builddir ($builddir).

Setting the style and meta data

  See OpenOffice::OOBuilder, because these methods are directly 
  inherited from the base class.


Look at the examples directory supplied together with this distribution.

=head1 SEE ALSO

L<> - the base class

Bug reports and questions can be sent to <oobuilder(at)>. 
Attention: make sure the word <oobuilder> is in the subject or 
body of your e-mail. Otherwhise your e-mail will be taken as 
spam and will not be read.

=head1 AUTHOR

Stefan Loones


Copyright 2004 by Stefan Loones

This library is free software; you can redistribute it and/or modify
it under the same terms as Perl itself.