File Coverage

blib/lib/DBIx/Report/Excel.pm
Criterion Covered Total %
statement 94 94 100.0
branch 15 20 75.0
condition 6 10 60.0
subroutine 16 16 100.0
pod 5 5 100.0
total 136 145 93.7


line stmt bran cond sub pod time code
1             package DBIx::Report::Excel;
2              
3 2     2   90925 use 5.008008;
  2         8  
  2         95  
4 2     2   11 use strict;
  2         3  
  2         78  
5 2     2   10 use warnings;
  2         8  
  2         147  
6              
7             our $VERSION = '0.4';
8              
9             =head1 NAME
10              
11             DBIx::Report::Excel - creating Excel reports from SQL statements
12              
13             =head1 SYNOPSIS
14              
15             use DBIx::Report::Excel;
16             my $report = DBIx::Report::Excel->new (
17             "SQLite.xls",
18             sql => 'SELECT first_n, last_n FROM people',
19             dbh => DBI->connect("dbi:SQLite:dbname=testdb","","")
20             );
21             $report->write();
22             $report->close();
23              
24              
25             =head1 DESCRIPTION
26              
27             DBIx::Report::Excel's goal is to make creating reports in Excel from
28             databases easy. It's aimed at SQL developers and/or DBA's who don't
29             know much about Perl programming. I.e. most of the information needed
30             to create Excel file is provided directly in SQL statement (script)
31             itself.
32              
33             If SQL script contains multiple statements, resulting Excel file is
34             formatted as multi-page spreadsheet with each result set on it's own
35             worksheet.
36              
37             =head2 FORMATTING EXCEL WORKSHEETS
38              
39             =head3 COLUMNS
40              
41             B on each worksheet are defined from table column names
42             or aliases provided by 'AS' directive in SQL statement.
43              
44             Excel column names are defined from parsing of SQL statement, not from
45             actual name of columns in table(s). If SQL staements does not
46             explicitly have column names or aliases listed (as for example, in
47             case of SELECT * query), Excel columns will have generic names
48             'Column+'. See L below
49              
50              
51             =head3 EMBEDDING YAML IN SQL COMMENT BLOCK
52              
53             All additional directives for formatting Excel output are provided as
54             YAML structure, embedded in SQL comment blocks. Supported comment style
55             is C-style 'slash and asterisk' (C) comments. ANSI 'double
56             hyphen' (C<-- ...>) comment style is not supported in this version.
57              
58             Slash and asterisk C-style (C) includes multi-line
59             comment blocks conforming to YAML specifications.
60              
61             YAML statements embedded in multiline comment block must start from
62             the beginning of each new line. All spaces are significant during YAML
63             processing. Statement indentation must correspond to YAML
64             specifications.
65              
66              
67             YAML directives must have separators C<---> at start and at the
68             end. Seaparator can be written either on the same line with commetn
69             start/end or on its own line. Extra spaces between comment start/end
70             and separator are ignored if separator is written on the same
71             line. (See EXAMPLES 3 and 4 below).
72              
73              
74             =head3 COMMENTS INSIDE YAML BLOCK
75              
76             To isolate actual comments from YAML processing, use YAML comments
77             (lines starting with hash symbol C<#>) inside SQL comment blocks:-
78              
79             /*
80             # This comment is not processed by YAML parser.
81             ---
82             title: My Worksheet Name
83             ---
84             */
85              
86             =head3 YAML KEYWORDS
87              
88             =head4 title:
89              
90             Only one keyword is suported in this version: 'title'. It defines
91             Excel worksheet name. If no workshet name is provided, then worksheet
92             is created with generic name 'Sheet+'.
93              
94             =cut
95              
96             =head1 DEPENDENCIES
97              
98             This module uses following Perl modules:
99              
100             Data::Tabular::Dumper
101             Data::Tabular::Dumper::Excel
102             SQL::Parser
103             SQL::Script
104             YAML
105              
106             =cut
107              
108             # --------------------------------------------------------------------------------
109 2     2   2194 use Data::Tabular::Dumper;
  2         4974  
  2         104  
110 2     2   2333 use Data::Tabular::Dumper::Excel;
  2         239180  
  2         86  
111 2     2   3125 use SQL::Parser;
  2         137307  
  2         140  
112 2     2   1988 use SQL::Script;
  2         1951  
  2         52  
113 2     2   1598 use YAML;
  2         15168  
  2         2168  
114             # --------------------------------------------------------------------------------
115              
116             require Exporter;
117              
118             our @ISA = qw(Exporter);
119              
120             =head2 EXPORT
121              
122             None.
123              
124             =cut
125              
126             our @EXPORT = qw( &write );
127              
128             =head1 METHODS
129              
130             =head2 new()
131              
132             use DBIx::Report::Excel;
133              
134             my $report = DBIx::Report::Excel->new( "Excel.xls" );
135              
136             Method new() creates new instance of Excel report object. It takes one
137             required parameter- output Excel file name, and two optitonal parameters:
138             database connection handler (dbh) and SQL query text (sql):
139              
140             my $report = DBIx::Report::Excel->new(
141             "Excel.xls",
142             dbh => DBI->connect("dbi:SQLite:dbname=testdb","",""),
143             sql => 'SELECT * FROM names',
144             );
145              
146             =cut
147              
148             sub new {
149 1     1 1 1333895 my $type = shift;
150 1         5 my $self = {};
151 1         5 my ($filename, %params) = @_;
152 1         5 $self->{'filename'} = $filename;
153 1         18 $self->{'excel'} = Data::Tabular::Dumper->open (Excel => [ $self->{'filename'} ] );
154 1   50     23468 $self->{'dbh'} = $params{'dbh'} || undef; # DB handler
155 1   50     12 $self->{'sql'} = $params{'sql'} || undef; # SQL text
156 1         3 $self->{'worksheet'} = 0;
157 1         4 $self->{'worksheets'} = undef; # List of all crated worksheets
158 1         9 bless $self, $type;
159             }
160             # --------------------------------------------------------------------------------
161              
162              
163             =head2 dbh()
164              
165             $report->dbh(
166             DBI->connect("dbi:Pg:dbname=$dbname;host=$host;port=$port;options=$options",
167             $username,
168             $password,
169             {AutoCommit => 0, RaiseError => 1, PrintError => 0}
170             )
171             );
172              
173             Sets database handler if it was not set by new() method. It also gives
174             ability to change Db connection on the fly to query different
175             databases in one report.
176              
177             =cut
178              
179             sub dbh {
180 5     5 1 680 my $self = shift;
181 5 100       20 if (@_) { $self->{dbh} = shift }
  1         7  
182 5         22 return $self->{dbh};
183             }
184              
185             =head2 sql()
186              
187             Defines SQL query for the report. Can contain either single SQL
188             statement or multiple queris separated by semicolon followed by a new line
189             (C<;\s*\n>). Each separate query will produce its own workseet in multipage
190             Excel workbook.
191              
192             An example:
193              
194             $report->sql(
195             qq{
196             /*---
197             title: People Names
198             ---*/
199              
200             SELECT first_name as "First Name",
201             last_name as "Family name"
202             FROM people
203              
204             });
205              
206             =cut
207              
208             sub sql {
209 5     5 1 13 my $self = shift;
210 5 100       14 if (@_) { $self->{sql} = shift }
  1         4  
211 5         19 return $self->{sql};
212             }
213             # --------------------------------------------------------------------------------
214              
215             =head2 write()
216              
217             Creates and writes new Excel worksheet for each SQL query (or multiple
218             worksheets when several SQL queries, joined by C<;\n>).
219              
220             Can accept one optional parameter: SQL statement string.
221              
222             An example:
223              
224             $report->sql("SELECT first_name, last_name FROM people");
225             $report->write();
226              
227             $report->write("SELECT f_name, color from fruits");
228              
229             =cut
230              
231             sub write {
232 4     4 1 24 my $self = shift;
233              
234 4 50       15 carp::croak("Database handler is not defined. Can not run query.")
235             unless $self->dbh();
236              
237 4   66     17 $self->{'sql'} = shift || $self->{'sql'};
238              
239 4 50       15 carp::croak("Can not run query: you doid not give me any SQL query text.")
240             unless $self->sql();
241              
242             # Split SQL into separate statements
243 4         46 my $statements = SQL::Script->new( split_by => qr/;\s*\n/ );
244              
245 4         90 for my $sql ( @{$statements->split_sql(\$self->{'sql'})} ) {
  4         22  
246 5         311 $self->{'data'} = $self->__new_page($sql);
247             }
248              
249 4         487 return $self->{'data'};
250             }
251              
252             =head2 close()
253              
254             Cleanly close Excel file.
255              
256             =cut
257              
258             sub close {
259 1     1 1 5 my $self = shift;
260 1         7 $self->{'excel'}->close();
261             }
262             # --------------------------------------------------------------------------------
263              
264             =head1 PRIVATE METHODS
265              
266             =head3 __new_page()
267              
268             Adds new workseet to Excel workbook. This method is called for each
269             separate SQL SELECT statement by write(). If SQL script passed to
270             write(0 contains several SQL statements, then __new_page() is called
271             for each of them.
272              
273             =cut
274              
275             sub __new_page {
276 5     5   10 my $self = shift;
277 5         9 my ($sql) = shift;
278              
279 5         20 my $page_name = $self->__page_name(__parse_comments($sql));
280 5         62 $self->{'excel'}->page_start( $page_name );
281              
282 5         3541 $self->{'data'} = $self->{'dbh'}->selectall_arrayref($sql);
283              
284 5 50       1740 return unless scalar @{ $self->{ data } };
  5         24  
285              
286             # ----------------------------------------
287             # Define column names.
288 5         50 my $parser = SQL::Parser->new();
289 5         184862 $parser->parse($sql);
290              
291 5 100       6062 if ( scalar @{$parser->structure->{'column_defs'}} == scalar @{$self->{'data'}->[0]} ) {
  5         29  
  5         41  
292             # i.e. column number in SQL statement
293             # same as number of actually selected
294             # columns (it's not "SELECT *")
295 3         15 $self->{'excel'}->fields($parser->structure->{'org_col_names'});
296             } else { # i.e. SELECT *
297 2         7 $self->{'excel'}->fields( [map {"Column" . $_} (1..scalar @{$self->{'data'}->[0]})])
  4         36  
  2         7  
298             }
299              
300 5         1489 $self->{'excel'}->dump( $self->{'data'} );
301 5         3276 return $self->{'data'};
302             }
303             # --------------------------------------------------------------------------------
304              
305             =head3 __page_name()
306              
307             Get a worksheet name from SQL. Parses YAML structure embedded in SQL
308             comment block. If no such thig provided worksheet will have name
309             'Sheet+'.
310              
311             =cut
312              
313             sub __page_name {
314 5     5   30479 my $self = shift;
315 5         13 my ($yaml) = @_;
316              
317 5   66     36 my $page_name = $yaml->{'title'} || "Sheet" . $self->{'worksheet'};
318              
319 5 100       25 if ( $self->{'worksheets'} ) { # if worksheet with this name exists
320             # already, give new sheet generic
321             # name
322 10         105 $page_name = "Sheet" . $self->{'worksheet'}
323 4 50       10 if scalar (grep {/^$page_name$/} @{$self->{'worksheets'}}) == 1;
  4         13  
324             }
325              
326 5         13 $self->{'worksheet'}++;
327 5         8 push @{$self->{'worksheets'}}, $page_name;
  5         13  
328              
329 5         16 return $page_name;
330             }
331             # --------------------------------------------------------------------------------
332              
333             =head3 __parse_comments()
334              
335             Extract all comments from SQL statement and parses them with YAML
336             parser. Returns parsed hash.
337              
338             =cut
339              
340             sub __parse_comments {
341 5     5   34 my ($text) = @_;
342 5         38 my @lines = split /\n+/, $text;
343 5         10 my @comments;
344 5         10 my $comment_block = undef;
345             LINES:
346 5         10 for (@lines) {
347             # push @comments, $1, next LINES if /^\s*-- (.*)$/; # -- ... TODO
348 18 50       46 push @comments, $1, next LINES if /^\s*\/\* (.*)\*\/\s*$/; # /* ...*/
349 18 100       63 if (/\/\*/../\*\//) {
350 13         30 s/(\/\*+\s*)//;
351 13         28 s/(\s*\*+\/)//;
352 13         25 push @comments, $_;
353             }
354             }
355 5         14 push @comments, "\n\n";
356 5         34 return YAML::Load(join "\n", @comments);
357             }
358             # --------------------------------------------------------------------------------
359              
360             1;
361             __END__