NAME

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

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();

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.

FORMATTING EXCEL WORKSHEETS

COLUMNS

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 EXAMPLES below

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' (/* ... */) comments. ANSI 'double hyphen' (-- ...) comment style is not supported in this version.

Slash and asterisk C-style (/* ... */) 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 --- 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).

COMMENTS INSIDE YAML BLOCK

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

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

YAML KEYWORDS

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>'.

DEPENDENCIES

This module uses following Perl modules:

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

EXPORT

None.

METHODS

new()

  use DBIx::Report::Excel;

  my $report = DBIx::Report::Excel->new( "Excel.xls" );

Method new() creates new instance of Excel report object. It takes one required parameter- output Excel file name, and two optitonal parameters: database connection handler (dbh) and SQL query text (sql):

  my $report = DBIx::Report::Excel->new(
    "Excel.xls",
     dbh => DBI->connect("dbi:SQLite:dbname=testdb","",""),
     sql => 'SELECT * FROM names',
    );

dbh()

   $report->dbh(
       DBI->connect("dbi:Pg:dbname=$dbname;host=$host;port=$port;options=$options",
                     $username,
                     $password,
                     {AutoCommit => 0, RaiseError => 1, PrintError => 0}
                     )
                );

Sets database handler if it was not set by new() method. It also gives ability to change Db connection on the fly to query different databases in one report.

sql()

Defines SQL query for the report. Can contain either single SQL statement or multiple queris separated by semicolon followed by a new line (;\s*\n). Each separate query will produce its own workseet in multipage Excel workbook.

An example:

  $report->sql(
      qq{
  /*---
  title: People Names
  ---*/

  SELECT first_name as "First Name",
         last_name as "Family name"
  FROM people

  });

write()

Creates and writes new Excel worksheet for each SQL query (or multiple worksheets when several SQL queries, joined by ;\n).

Can accept one optional parameter: SQL statement string.

An example:

  $report->sql("SELECT first_name, last_name FROM people");
  $report->write();

  $report->write("SELECT f_name, color from fruits");

close()

Cleanly close Excel file.

PRIVATE METHODS

__new_page()

Adds new workseet to Excel workbook. This method is called for each separate SQL SELECT statement by write(). If SQL script passed to write(0 contains several SQL statements, then __new_page() is called for each of them.

__page_name()

Get a worksheet name from SQL. Parses YAML structure embedded in SQL comment block. If no such thig provided worksheet will have name 'Sheet+<number>'.

__parse_comments()

Extract all comments from SQL statement and parses them with YAML parser. Returns parsed hash.

EXAMPLES

COLUMN NAMES FROM SQL PARSING

EXAMPLE 1

Columns in Excel workseet have names FIRST_NAME and LAST_NAME:

   SELECT first_name,
          last_name
   FROM people

EXAMPLE 2

Excel columns have names First Name and Family Name:

   SELECT first_name as "First Name",
          last_name as "Family Name"
   FROM people

EXAMPLE 3

YAML block separateor placed on its own line. Excel worksheet name is "My Worksheet Name".

  /*
  ---
  title: My Worksheet Name
  ---
  */

EXAMPLE 4

YAML separators on the same line with comment start and end. Same worksheet name as above.

  /*---
  title: My Worksheet Name
  --- */

SEE ALSO

 As of 2010, November project repository moved to Github:
 https://github.com/dmytro/DBIx-Report-Excel
 

 Script example.pl provides full example of creating report from
 SQLite database. It uses "testdb" database in /tmp directory.

AUTHOR

Dmytro Kovalov, <dmytro.kovalov@gmail.com<gt>

COPYRIGHT AND LICENSE

Copyright (C) 2009, 2010 by Dmytro Kovalov

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.8.8 or, at your option, any later version of Perl 5 you may have available.

cpanm DBIx::Report::Excel

perl -MCPAN -e shell
install DBIx::Report::Excel