Virtual Spring Cleaning (part 2 of XX) - in which I implement fun parts of Excel
I don't mind working with Spreadsheets. Much of my work consists of creating Spreadsheets from SQL queries. Sometimes, the resulting spreadsheet should be a pivot table, listing some values across the spreadsheet. For most of my Spreadsheet-generation needs, Querylet is sufficient, but it cannot create pivot tables.
To remedy the need to manually create pivot tables, I wrote DBIx::PivotQuery, which makes creating a pivot table from an SQL query very easy.
use DBIx::PivotQuery 'pivot_by'; my $rows = pivot_by( dbh => $dbh, columns => ['month'], rows => ['region'], aggregate => ['sum(amount) as amount'], sql => <<'SQL'); select month(date) as report_month , region , amount from mytable SQL
The above code returns a data structure roughly like
[ ['region','1','2',...,'11','12'], ['East', 0, 0 ,..., 10, 20 ], ['North', 0, 1 ,..., 10, 20 ], ['South', 0, 3 ,..., 10, 5 ], ['West', 0, 6 ,..., 8, 20 ], ]
The code was fairly easy to write once I realized that I should leave the
aggregation to SQL instead of (re)implementing
count and all the other
interesting aggregates in Perl. As the code simply wraps your SQL with another
SQL statement, it cannot handle
ORDER BY clauses well, but I've been somewhat
OK with the order that the module chooses.
Speaking of the fun parts of Excel, one application idea I have is to write an Excel clone for the terminal with vi keybindings. Most of the constituent parts are already there, like Spreadsheet::Read and Tickit. But Tickit doesn't work under Windows and I haven't found the time to port it there.
Leave a comment