NAME

Spreadsheet::WriteExcel::WebPivot - generates an MS Excel Web Pivot table for IE

makewebpivot

SYNPOSIS

use Spreadsheet::WriteExcel::WebPivot;

makewebpivot(db handle,query,query keys, summary type, file name, title);

DESCRIPTION

this method creates an MS Excel Web Pivot which when viewed on a Windows machine with MS Office XP and IE gives you a fully functional embedded spreadsheet object in the browser. This should be able to run and produce output anywhere perl is installed but the output will only be useful to those running Windows and IE. The pivot table gives a high level summary of the numbers that can be rearranged in different ways.

PARAMETERS

The parameters it takes are a database handle, a query string or array ref (see below), the query keys which is a reference to an array of the names of fields in the query listed in the order you want them diplayed in the web pivot, the summary type which is the type of summary to be used in the pivot, the file name to save the generated web pivot under and the last parameter is the title of the pivot table

Note the query parameter can also be a reference to an array of query strings. makewebpivot will detect whether query is a string or array of strings. If it is an array of strings it execute each query in order and add their results to the pivot. Note that if you use multiple queries, all queries must return the same results in the same format as the first query. This restriction is inherent in MS Excel pivot tables.

This module can also be used in the absense of a database connection by passing in a reference to an array of hash references. When called this way whatever query you pass in will be ignored and the module will instead present the data in the data structure you passed in. For a trivial example:

use Spreadsheet::WriteExcel::WebPivot;

my @array;

for(my $i=0; $i < 20; $i++) {

        push @array, { Name => "a$i", Number => $i };
}       

my @fields = qw(Name Number);

makewebpivot(\@array, '', \@fields, 'Count', 'exceltest', 'Test Pivot');

The above example uses the module without a database. Below is a trivial example using a database.

use Spreadsheet::WriteExcel::WebPivot; use DBI;

my $dbh = DBI->connect('dbi:your:database');

my @fields = qw(field1 field2 field3);

my $query = "select * from table";

makewebpivot($dbh,$query,\@fields,'Count', 'exceltest2','Test Pivot2');

Note: you will likely need to adjust the security settings of your IE browser to view the generated pages as pivot tables. Please inspect the generated pages if you are concerned about this.

AUTHOR

Nathan Lewis, <nathanlewis42@yahoo.co.uk>

COPYRIGHT AND LICENSE

Copyright (C) 2005 by Nathan Lewis

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.3 or, at your option, any later version of Perl 5 you may have available.