#!/usr/bin/perl -w
# Bug in ODF::lpOD or ODF - failure to refresh header row cells containing
# fixed formula on document open.
# Work-around is to add "0*RAND()" to every header cell formula, to
# make it appear dynamic and need refreshing on document open.
# -Ian! D. Allen - idallen@idallen.ca - www.idallen.com

use strict;
use ODF::lpOD;

# Name of default first sheet in spreadsheet.
# Name of output ODS file generated by this file.
#
my $SHEET1 = 'Sheet1';
my $OUTPUT = 'test.ods';

my $doc = odf_new_document('spreadsheet')
    or die "$0: Failed to create new spreadsheet\n   ";

my $content = $doc->get_body
    or die "$0: cannot get body content\n  ";
my $sheet = $content->get_table($SHEET1)
    or die "$0: cannot find '$SHEET1'\n  ";

# Create 5x10 small spreadsheet.
#
$sheet->add_column('number'=>10);
$sheet->add_row('number'=>5);

# Create a two-row header.
#
my $header;
until ( $header = $sheet->get_column_header ) {
    warn "$0: Creating header lines\n";
    $sheet->set_column_header(2);  # doesn't work for 1
}

# Put some numbers and formulae into the header rows.
# Some of these cells do not refresh when the document is opened.
# Cells that have a fixed formula aren't refreshing on document open.
# The formula without RAND() don't work in the header rows - the cells
# are blank when you open the document. Work around: Adding a useless
# RAND() call makes the cells refresh when opening the document.
#
# (Also, if you interactively modify any formula, even by adding a blank
# and deleting it, or if you modify any cell in the SUM, the blank
# problem is cleared for that header cell, perhaps because the
# modification forces a refresh of the formula.)
#
my $r = $header->get_row(0) or die;
&FillRow($r,0);
$r = $header->get_row(1) or die;
&FillRow($r,0);

# Put some numbers and formulae into the body rows.
# The formula work fine in these body row - the cells show the right
# values when you open the document, as expected.
#
$r = $sheet->get_row(0) or die;
&FillRow($r,2);
$r = $sheet->get_row(1) or die;
&FillRow($r,2);

sub FillRow {
    my ($r,$offset) = @_;
    my $c;
    my $f;

    $r->clear();
    my ($table,$row) = $r->get_position;
    $row += $offset;

    my @list = ();
    my $ix = 5;
    for ( my $i = 1; $i <= $ix; $i++ ) {
	my $c = $r->get_cell($i-1);
	$c->set_type('float');
	$c->set_style('Default');
	$c->set_value($i);
	push(@list,&NA($row,$i-1));
    }

    ++$ix;
    $c = $r->get_cell($ix-1);
    $c->set_style('Default');
    $f = 'of:=SUM(' . join(';',@list) . ';0*RAND())';
    $c->set_formula($f);
    $c->set_annotation('text'=>'Useless call to RAND()');

    ++$ix;
    $c = $r->get_cell($ix-1);
    $c->set_style('Default');
    $c->set_formula('of:=1+2+3+4+0*RAND()');
    $c->set_annotation('text'=>'Useless call to RAND()');

    ++$ix;
    $c = $r->get_cell($ix-1);
    $c->set_style('Default');
    $f = 'of:=SUM(' . join(';',@list) . ';0*0)';
    $c->set_formula($f);

    ++$ix;
    $c = $r->get_cell($ix-1);
    $c->set_style('Default');
    $c->set_formula('of:=1+2+3+4+0*0');
}

$doc->save('target'=>$OUTPUT );
print "Wrote $OUTPUT\n";

exit;

############################################################################

# convert numeric zero-offset column into alpha letter, e.g. 0->"A", 26->"AA"
sub AR {
    my $n = $_[0];
    die caller(2) unless defined($n);
    my $str = '';
    while(){
	my $r = $n % 26;
	$str = chr(ord('A')+$r) . $str;
	$n = int($n / 26);
	last unless $n;
	--$n;
    }
    return $str;
}
# convert zero-offset (row,col) into alpha, e.g. &NA(9,3)->"D10"
# if either field starts with a $, use absolute positioning
sub NA {
    my ($r,$c) = @_;
    my @c;
    die @c = caller(), "NA @c" unless defined($r);
    die @c = caller(), "NA @c" unless defined($c);
    if ( $r =~ s/^\$// ) {
	$r = '$' . ($r+1);
    } else {
	$r = $r+1;
    }
    if ( $c =~ s/^\$// ) {
	$c = '$' . &AR($c);
    } else {
	$c = &AR($c);
    }
    return "$c$r";
}
