DBIx::Report::Excel - creating Excel reports from SQL statements
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();
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.
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
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).
---
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 --- */
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>'.
This module uses following Perl modules:
Data::Tabular::Dumper Data::Tabular::Dumper::Excel SQL::Parser SQL::Script YAML
None.
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', );
$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.
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.
;\s*\n
An example:
$report->sql( qq{ /*--- title: People Names ---*/ SELECT first_name as "First Name", last_name as "Family name" FROM people });
Creates and writes new Excel worksheet for each SQL query (or multiple worksheets when several SQL queries, joined by ;\n).
;\n
Can accept one optional parameter: SQL statement string.
$report->sql("SELECT first_name, last_name FROM people"); $report->write(); $report->write("SELECT f_name, color from fruits");
Cleanly close Excel file.
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.
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>'.
Extract all comments from SQL statement and parses them with YAML parser. Returns parsed hash.
Columns in Excel workseet have names FIRST_NAME and LAST_NAME:
FIRST_NAME
LAST_NAME
SELECT first_name, last_name FROM people
Excel columns have names First Name and Family Name:
First Name
Family Name
SELECT first_name as "First Name", last_name as "Family Name" FROM people
YAML block separateor placed on its own line. Excel worksheet name is "My Worksheet Name".
/* --- title: My Worksheet Name --- */
YAML separators on the same line with comment start and end. Same worksheet name as above.
/*--- title: My Worksheet Name --- */
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.
Dmytro Kovalov, <dmytro.kovalov@gmail.com<gt>
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.
To install DBIx::Report::Excel, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::Report::Excel
CPAN shell
perl -MCPAN -e shell install DBIx::Report::Excel
For more information on module installation, please visit the detailed CPAN module installation guide.