Spreadsheet::WriteExcel::FromDB - Convert a database table to an Excel spreadsheet

Spreadsheet-WriteExcel-FromDB documentation Contained in the Spreadsheet-WriteExcel-FromDB distribution.

Index

Code Index:

NAME

Top

Spreadsheet::WriteExcel::FromDB - Convert a database table to an Excel spreadsheet

SYNOPSIS

Top

  use Spreadsheet::WriteExcel::FromDB;

  my $dbh = DBI->connect(...);

  my $ss = Spreadsheet::WriteExcel::FromDB->read($dbh, $table_name);
  $ss->ignore_columns(qw/foo bar/); 
  # or
  $ss->include_columns(qw/foo bar/); 

  $ss->restrict_rows('age > 10');

  print $ss->as_xls;
  # or
	$ss->write_xls('spreadsheet.xls');

DESCRIPTION

Top

This module exports a database table as an Excel Spreadsheet.

The data is not returned in any particular order, as it is a simple task to perform this in Excel. However, you may choose to ignore certain columns, using the 'ignore_columns' method.

METHODS

Top

read

Creates a spreadsheet object from a database handle and a table name.

dbh / table

Accessor / mutator methods for the database handle and table name.

restrict_rows

  $ss->restrict_rows('age > 10');

An optional 'WHERE' clause for restricting the rows returned from the database.

ignore_columns

  $ss->ignore_columns(qw/foo bar/);

Output all columns, except these ones, to the spreadsheet.

include_columns

  $ss->include_columns(qw/foo bar/);

Only include these columns into the spreadsheet.

as_xls

  print $ss->as_xls;

Return the table as an Excel spreadsheet.

write_xls

	$ss->write_xls('spreadsheet.xls');

Write the table to a spreadsheet with the given filename.

BUGS

Top

Dates are handled as strings, rather than dates.

AUTHOR

Top

Tony Bowden

BUGS and QUERIES

Top

Please direct all correspondence regarding this module to: bug-Spreadsheet-WriteExcel-Simple@rt.cpan.org

COPYRIGHT AND LICENSE

Top

  Copyright (C) 2001-2005 Tony Bowden.

  This program is free software; you can redistribute it and/or modify it under
  the terms of the GNU General Public License; 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 ALSO

Top

Spreadsheet::WriteExcel::Simple. Spreadsheet::WriteExcel. DBI

Spreadsheet-WriteExcel-FromDB documentation Contained in the Spreadsheet-WriteExcel-FromDB distribution. 
package Spreadsheet::WriteExcel::FromDB;

$VERSION = '1.00';

use strict;

use Spreadsheet::WriteExcel::Simple 1.03;

sub _croak { require Carp; Carp::croak(@_) }


sub read {
  my $class = shift;
  my $dbh   = shift or _croak "Need a dbh";
  my $table = shift or _croak "Need a table";
  bless {
    _table           => $table,
    _dbh             => $dbh,
    _where           => "",
    _ignore_columns  => [],
    _include_columns => [],
  }, $class;
}


sub dbh {
  my $self = shift;
  $self->{_dbh} = shift if $_[0];
  $self->{_dbh};
}

sub table {
  my $self = shift;
  $self->{_table} = shift if $_[0];
  $self->{_table};
}


sub restrict_rows {
  my $self = shift;
  $self->{_where} = shift if $_[0];
  $self->{_where};
}


sub ignore_columns {
  my $self = shift;
  $self->{_ignore_columns} = [ @_ ];
}


sub include_columns {
  my $self = shift;
  $self->{_include_columns} = [ @_ ];
}


sub as_xls { 
	shift->_xls->data;
}

sub _xls {
  my $self  = shift;
	$self->{_xls} ||= do { 
		my $ss = Spreadsheet::WriteExcel::Simple->new;
		$ss->write_bold_row([$self->_columns_wanted]);
		$ss->write_row($_) for @{$self->dbh->selectall_arrayref($self->_data_query)};
		$ss;
	};
}


sub write_xls { 
	my ($self, $filename) = @_;
	$self->_xls->save($filename);
}

sub _data_query {
  my $self   = shift;
  my $query = sprintf 'SELECT %s FROM %s',
    (join ', ', $self->_columns_wanted), $self->table;
  $query .= " WHERE " . $self->restrict_rows if $self->restrict_rows;
  return $query;
}

sub _columns_wanted {
  my $self = shift;
	my @include = @{$self->{_include_columns}};
	@include = $self->_columns_in_table unless @include;
  my %ignore_columns = map { $_ => 1 } @{$self->{_ignore_columns}};
  return grep !$ignore_columns{$_}, $self->_columns_in_table;
}

sub _columns_in_table {
  my $self = shift;
	my $query = sprintf "SELECT * FROM %s WHERE 1 = 0", $self->table;
	(my $sth = $self->dbh->prepare($query))->execute;
	my @cols = @{$sth->{NAME}};
	$sth->finish;
	return @cols;
}


1;