See attached image and attached Perl script that creates test.ods that,
when opened, displays the bug shown in the image.
The attached Perl script does this:
- Use ODF::lpOD to create a new small spreadsheet, 5x10.
- Allocate the first two rows as header rows.
- Write identical formulae into both header cells and body cells.
- Create the output file "test.ods".
Now open the "test.ods" file in LibreOffice and see the problem.
Header cells that don't contain a formula with a dynamic function don't
refresh on document open and remain blank. Work around: Adding a useless
dynamic function expression such as 0*RAND() to the formulae cause the
cells to refresh properly on document open.
Body (non-header) cells always refresh correctly on document open.
This might be a LibreOffice problem, not an ODF::lpOD problem, since older
versions of LibreOffice (e.g. LibreOffice 3.5.7.2 Build ID: 350m1(Build:2)
under Ubuntu 12.04) do correctly refresh all the header cells on document
open; none of the cells are blank. In any case it would be nice if
ODF::lpOD had a work-around even if it is a LibreOffice problem.
Ubuntu Linux System info:
=========================
Package: JMGDOC/ODF-lpOD-1.126.tar.gz
$ uname -a
Linux linux 3.19.0-15-generic #15-Ubuntu SMP Thu Apr 16 23:32:37 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux
$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 15.04
Release: 15.04
Codename: vivid
$ perl -v
This is perl 5, version 20, subversion 2 (v5.20.2) built for x86_64-linux-gnu-thread-multi (with 39 registered patches, see perl -V for more detail)
LibreOffice Version Version: 4.4.2.2
Build ID: 40m0(Build:2)
Locale: en_CA
#!/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";
}
