view release on metacpan or  search on metacpan

lib/DBIx/Report/Excel.pm  view on Meta::CPAN

package DBIx::Report::Excel;

use 5.008008;
use strict;
use warnings;

our $VERSION = '0.4';

=head1 NAME

DBIx::Report::Excel - creating Excel reports from SQL statements

=head1 SYNOPSIS

  use DBIx::Report::Excel;
  my $report = DBIx::Report::Excel->new (
	"SQLite.xls",
     	sql => 'SELECT first_n, last_n FROM people',
     	dbh => DBI->connect("dbi:SQLite:dbname=testdb","","")
  );
  $report->write();
  $report->close();


=head1 DESCRIPTION

DBIx::Report::Excel's goal is to make creating reports in Excel from
databases easy. It's aimed at SQL developers and/or DBA's who don't
know much about Perl programming. I.e. most of the information needed
to create Excel file is provided directly in SQL statement (script)
itself.

If SQL script contains multiple statements, resulting Excel file is
formatted as multi-page spreadsheet with each result set on it's own
worksheet.

=head2 FORMATTING EXCEL WORKSHEETS

=head3 COLUMNS

B<Column names> on each worksheet are defined from table column names
or aliases provided by 'AS' directive in SQL statement.

Excel column names are defined from parsing of SQL statement, not from
actual name of columns in table(s). If SQL staements does not
explicitly have column names or aliases listed (as for example, in
case of SELECT * query), Excel columns will have generic names
'Column+<number>'. See L<EXAMPLES> below


=head3 EMBEDDING YAML IN SQL COMMENT BLOCK

All additional directives for formatting Excel output are provided as
YAML structure, embedded in SQL comment blocks. Supported comment style
is C-style 'slash and asterisk' (C</* ... */>) comments.  ANSI 'double
hyphen' (C<-- ...>) comment style is not supported in this version.

Slash and asterisk C-style (C</* ... */>) includes multi-line
comment blocks conforming to YAML specifications.

YAML statements embedded in multiline comment block must start from
the beginning of each new line. All spaces are significant during YAML
processing. Statement indentation must correspond to YAML
specifications.


YAML directives must have separators C<---> at start and at the
end. Seaparator can be written either on the same line with commetn
start/end or on its own line. Extra spaces between comment start/end
and separator are ignored if separator is written on the same
line. (See EXAMPLES 3 and 4 below).


=head3 COMMENTS INSIDE YAML BLOCK

To isolate actual comments from YAML processing, use YAML comments
(lines starting with hash symbol C<#>) inside SQL comment blocks:-

  /*
  # This comment is not processed by YAML parser.
  ---
  title: My Worksheet Name
  ---
  */

=head3 YAML KEYWORDS

=head4 title:

Only one keyword is suported in this version: 'title'. It defines
Excel worksheet name. If no workshet name is provided, then worksheet
is created with generic name 'Sheet+<number>'.

=cut

=head1 DEPENDENCIES

This module uses following Perl modules:

 Data::Tabular::Dumper
 Data::Tabular::Dumper::Excel
 SQL::Parser
 SQL::Script
 YAML

=cut

# --------------------------------------------------------------------------------
use Data::Tabular::Dumper;
use Data::Tabular::Dumper::Excel;
use SQL::Parser;