package Lire::ReportParser::ExcelWriter;
use strict;
use vars qw/ $DATE_SYSTEM @name_cols_width/;
use base qw/ Lire::ReportParser::RowColHandler /;
use Lire::ReportParser::AsciiDocBookFormatter qw/dbk2txt/;
use POSIX qw/ceil/;
use Spreadsheet::WriteExcel;
#use Time::Timezone;
use constant MAX_EXCEL_STRING => 255;
use constant MAX_SHEET_STRING => 31;
use constant DEFAULT_ROW_HEIGHT => 12.75;
# Those values are in Excel Points
#use constant CHAR_WIDTH => 5.3;
#use constant DEFAULT_CELL_WIDTH => 48;
#use constant MAX_CELL_WIDTH => 4 * 48;
# Spreadsheet::WriteExcel seems to work with characters
use constant CHAR_WIDTH => 1;
use constant DEFAULT_CELL_WIDTH => 9;
use constant MAX_CELL_WIDTH => 36;
BEGIN {
# Use 1900 date system on all platforms other than Apple Mac (for which
# use 1904 date system).
$DATE_SYSTEM = ($^O eq 'MacOS') ? 1 : 0;
}
#
# This was inspired by the comments and programs datecalc1.pl
# included in Spreadsheet::WriteExcel and is
# Copyright (c) 2000, Andrew Benham.
# This program is free software. It may be used, redistributed and/or
# modified under the same terms as Perl itself.
#
# It was modified to use Time::Timezone which is included in Lire.
#
sub epoch2excel_date {
my ($time) = @_;
# Divide timestamp by number of seconds in a day.
# This gives a date serial with '0' on 1 Jan 1970.
my $serial = $time / 86400;
# Adjust the date serial by the offset appropriate to the
# currently selected system (1900/1904).
if ($DATE_SYSTEM == 0) { # use 1900 system
$serial += 25569;
} else { # use 1904 system
$serial += 24107;
}
# Actually, this gives the wrong date. Maybe this is specific to
# gnumeric and OpenOffice on UNIX.
# The correct date appears if we don't correct the value for time zone.
#
# Fix for timezone
# my $offset = tz_local_offset( $time );
# $serial += $offset / 8640;
# Perpetuate the error that 1900 was a leap year by decrementing
# the serial if we're using the 1900 system and the date is prior to
# 1 Mar 1900. This has the effect of making serial value '60'
# 29 Feb 1900.
# This fix only has any effect if UNIX/Perl time on the platform
# can represent 1900. Many can't.
unless ($DATE_SYSTEM) {
$serial-- if ($serial < 61); # '61' is 1 Mar 1900
}
return $serial;
}
sub new {
my ( $self, %args ) = @_;
$self = $self->SUPER::new( %args,
'summary_when' => 'after',
);
$self->{'_output_file'} = $args{'output_file'} || '-',
return $self;
}
sub report_start {
my $self = shift;
$self->SUPER::report_start( @_ );
$self->{'report_date'} = undef;
$self->{'report_timespan'} = undef;
$self->{'workbook'} = new Spreadsheet::WriteExcel($self->{'_output_file'});
# Create some format that we are going to use
$self->{'title_fmt'} =
$self->{'workbook'}->addformat(
'size' => 18,
'bold' => 1,
);
$self->{'date_fmt'} =
$self->{'workbook'}->addformat(
'num_format' => "yyyy/mm/dd",
'align' => "left",
'valign' => "top",
);
$self->{'timestamp_fmt'} =
$self->{'workbook'}->addformat(
'num_format' => "yyyy/mm/dd hh:mm:ss",
'align' => "left",
'valign' => "top",
);
$self->{'datetime_fmt'} =
$self->{'workbook'}->addformat(
'num_format' => "yyyy/mm/dd hh:mm",
'align' => "left",
'valign' => "top",
);
$self->{'time_fmt'} =
$self->{'workbook'}->addformat(
'num_format' => "hh:mm",
'align' => "left",
'valign' => "top",
);
$self->{'name_fmt'} =
$self->{'workbook'}->addformat(
'num_format' => 0x31, # Builtin: @
'align' => "justify",
'valign' => "top",
);
$self->{'label_fmt'} =
$self->{'workbook'}->addformat(
'num_format' => 0x31, # Builtin: @
'align' => "left",
'valign' => "left",
'bold' => 1,
'size' => 12,
);
$self->{'header_fmt'} =
$self->{'workbook'}->addformat(
'num_format' => 0x31, # Builtin: @
'align' => "left",
'valign' => "left",
'bold' => 1,
);
$self->{'description_fmt'} =
$self->{'workbook'}->addformat(
'num_format' => 0x31, # Builtin: @
'align' => "left",
'valign' => "left",
);
$self->{'value_fmt'} =
$self->{'workbook'}->addformat(
'num_format' => 0x00, # Builtin: General
'align' => "right",
'valign' => "top",
);
}
sub report_end {
my $self = shift;
$self->SUPER::report_end( @_ );
# Activate the first sheet
my $first = ($self->{'workbook'}->sheets)[0];
if ( $first ) {
$first->set_first_sheet();
$first->activate();
}
$self->{'workbook'}->close();
return;
}
sub section_end {
my $self = shift;
$self->{'curr_section'}->write( $self->{'curr_row'}++, 0,
"No subreports were generated for this section.",
$self->{'label_fmt'},
)
if ( ! $self->current_section_subreport_count() );
$self->SUPER::section_end( @_ );
return;
}
sub handle_title {
my ( $self, $title ) = @_;
# Trim the title
$title =~ s/^\s*//;
$title =~ s/\s*$//;
if ($self->in_element( "lire:section" ) ) {
# Section's title
my $title = substr( $title, 0, MAX_SHEET_STRING );
my $first_sheet = ! $self->{'workbook'}->sheets;
# Create a new worksheet
$self->{'curr_section'} = $self->{'workbook'}->addworksheet( $title );
$self->{'curr_row'} = 0;
$self->{'curr_cols_width'} = [];
if ( $first_sheet ) {
# Put "header" information on first sheet
my @labels = ( "Report generated", "Log file started",
"Log file ended" );
my @dates = ( $self->{'report_date'}, @{$self->{'report_timespan'}} );
for (my $i=0; $i<@labels; $i++) {
my $date = epoch2excel_date( $dates[$i] );
$self->{'curr_section'}->write( $i, 0, $labels[$i],
$self->{'label_fmt'},
);
$self->{'curr_section'}->write( $i, 1, $date,
$self->{'timestamp_fmt'},
);
$self->{'curr_section'}->set_row( $i, 14 );
}
$self->{'curr_row'} = @labels + 1;
my $width = length("yyyy/mm/dd hh:mm:ss") * CHAR_WIDTH;
$self->{'curr_cols_width'}[1] = $width;
$self->{'curr_section'}->set_column( 1, 1, $width );
}
} else {
# Subreport's title
my $title = substr( $title, 0, MAX_EXCEL_STRING );
$self->{'curr_section'}->set_row( $self->{'curr_row'}, 22 );
$self->{'curr_section'}->write( $self->{'curr_row'}++, 0,
$title,
$self->{'title_fmt'},
);
}
return;
}
sub handle_description {
my ( $self, $docbook ) = @_;
my $desc = dbk2txt( $docbook );
# Put each line in a separate cell
foreach my $line ( split /\n/, $desc ) {
if ( defined $line ) {
# Trim spaces
$line =~ s/^\s+//;
$line =~ s/\s+$//;
$self->{'curr_section'}->write( $self->{'curr_row'}, 0,
$line,
$self->{'description_fmt'},
);
}
$self->{'curr_row'}++;
}
# Skip one row after the description
$self->{'curr_row'}++;
return;
}
sub handle_date {
my ( $self, $date, $time ) = @_;
$self->{'report_date'} = $time
unless defined $self->{'report_date'};
return;
}
sub handle_timespan {
my ( $self, $timespan, $start, $end ) = @_;
$self->{'report_timespan'} = [$start, $end]
unless defined $self->{'report_timespan'};
return;
}
sub subreport_end {
my $self = shift;
$self->SUPER::subreport_end( @_ );
# Leave one extra empty row
$self->{'curr_row'} += 2;
return;
}
sub table_start {
my $self = shift;
$self->SUPER::table_start( @_ );
$self->{'curr_headers'} = [];
return;
}
sub table_end {
my $self = shift;
# Skip one row
$self->{'curr_row'}++;
$self->{'curr_section'}->write( $self->{'curr_row'}++, 0,
"No content in report.",
$self->{'label_fmt'} )
if ! $self->current_table_entry_count();
$self->SUPER::table_end( @_ );
return;
}
sub table_info_end {
my $self = shift;
$self->SUPER::table_info_end( @_ );
# Upgrade the column's width of the sheet based
# on the suggested column's width
my $table_info = $self->current_table_info();
my @cols = $table_info->column_infos();
foreach my $col ( @cols ) {
my $i = $col->col_start;
my $curr_width = $self->{'curr_cols_width'}[$i] || DEFAULT_CELL_WIDTH;
my $width = $col->col_width * CHAR_WIDTH;
$width = MAX_CELL_WIDTH if $width > MAX_CELL_WIDTH;
if ( $width > $curr_width ) {
$self->{'curr_cols_width'}[$i] = $width;
$self->{'curr_section'}->set_column( $i, $i, $width );
}
}
return;
}
sub handle_header_row {
my ( $self, $row ) = @_;
push @{$self->{'curr_headers'}}, $row;
return;
}
sub write_header_rows {
my $self = $_[0];
foreach my $row ( @{$self->{'curr_headers'}} ) {
$self->{'curr_row'}++;
$self->{'curr_row_height'} = DEFAULT_ROW_HEIGHT;
foreach my $c ( @$row ) {
next unless defined $c;
my $s = substr $c->label, 0, MAX_EXCEL_STRING;
$self->{'curr_section'}->write( $self->{'curr_row'}, $c->col_start,
$s, $self->{'header_fmt'} );
}
}
return;
}
sub handle_row {
my ( $self, $row ) = @_;
if ( $self->{'curr_headers'} ) {
$self->write_header_rows;
delete $self->{'curr_headers'};
}
$self->{'curr_row'}++;
$self->{'curr_row_height'} = DEFAULT_ROW_HEIGHT;
foreach my $c ( @$row ) {
next unless defined $c;
if ($c->{'col_info'}->class eq 'categorical' ) {
$self->write_categorical_cell( $c );
} else {
$self->write_value_cell( $c );
}
}
return;
}
sub handle_table_summary {
my ( $self, $nrecords, $row ) = @_;
return unless $nrecords &&
$self->current_table_entry_count();
$self->{'curr_row_height'} = DEFAULT_ROW_HEIGHT;
# Write the summary cell
$self->{'curr_section'}->write( $self->{'curr_row'},
0,
"Total",
$self->{'header_fmt'},
);
foreach my $c ( @$row ) {
next unless defined $c;
$self->write_value_cell( $c );
}
# We increase it only after because it was already increased
# in table_end
$self->{'curr_row'}++;
return;
}
sub write_categorical_cell {
my ( $self, $name ) = @_;
my $s = substr $name->{'content'}, 0, MAX_EXCEL_STRING;
my $fmt = $self->{'name_fmt'};
# Convert time classes to their Excel representation
if ( $name->{'value'} =~ /^\d+$/ &&
$name->{'content'} !~ /^(\[|Week)/ && #! Skip Week and rangegroup
defined $name->{'range'} &&
$name->{'range'} =~ /^\d+$/
)
{
$s = epoch2excel_date( $name->{'value'} );
if ( $name->{'range'} < 86400 ) {
$fmt = $self->{'datetime_fmt'};
} else {
$fmt = $self->{'date_fmt'};
}
}
my $col = $name->{'col_info'}->col_start;
my $len = length $s;
# Enlarge the row's height i
if ( $len * CHAR_WIDTH > MAX_CELL_WIDTH ) {
my $row = ceil( $len * CHAR_WIDTH / MAX_CELL_WIDTH );
my $height = $row * DEFAULT_ROW_HEIGHT;
if ( $height > $self->{'curr_row_height'} ) {
$self->{'curr_section'}->set_row( $self->{'curr_row'}, $height, );
$self->{'curr_row_height'} = $height;
}
}
# Write it
$self->{'curr_section'}->write( $self->{'curr_row'}, $col, $s, $fmt );
return;
}
sub write_value_cell {
my ( $self, $value ) = @_;
$self->{'curr_section'}->write( $self->{'curr_row'},
$value->{'col_info'}->col_start,
$value->{'content'},
$self->{'value_fmt'} );
return;
}
1;
__END__
=pod
=head1 NAME
Lire::ReportParser::ExcelWriter - Transform Lire XML Reports into a Excel95(tm) spreadsheet.
=head1 SYNOPSIS
use Lire::ReportParser::ExceltWriter;
my $parser = new Lire::ReportParser::ExcelWriter();
eval { $parser->parsefile( "report.xml" ) };
print "Parse failed: $@" if $@;
=head1 DESCRIPTION
This is a Lire::ReportParser processor which will transform the Lire
XML report into a Excel95(tm) spreadsheet which will be output on
STDOUT.
Its constructor doesn't take any parameters.
=head1 SEE ALSO
Lire::ReportParser(3pm) Spreadsheet::WriteExcel(3pm)
=head1 VERSION
$Id: ExcelWriter.pm,v 1.19 2006/07/23 13:16:31 vanbaal Exp $
=head1 COPYRIGHT
Copyright (C) 2001-2002 Stichting LogReport Foundation LogReport@LogReport.org
This file is part of Lire.
Lire is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program (see COPYING); if not, check with
http://www.gnu.org/copyleft/gpl.html.
=head1 AUTHOR
Francis J. Lacoste <flacoste@logreport.org>
=cut
syntax highlighted by Code2HTML, v. 0.9.1