NAME
Clearbuilt::ExcelErator - Write XLSX files in a Clearbuilt-standard way
VERSION
version 2.0001
SYNOPSIS
my
%spreadsheet
= (
'title'
=>
'Summary'
,
'col_widths'
=> {
'1-3'
=> 12
},
'rows'
=> [
[ {
value
=>
'The Report Title'
,
format
=> [
font
=>
'bold'
],
nowidth
=> 1 } ],
[],
[
''
,
{
value
=>
'Qty'
,
format
=> [
bb
=> 2,
font
=>
'bold'
,
halign
=>
'center'
] },
{
value
=>
'Cost'
,
format
=> [
bb
=> 2,
font
=>
'bold'
,
halign
=>
'center'
] },
{
value
=>
'Total Cost'
,
format
=> [
bb
=> 2,
font
=>
'bold'
,
halign
=>
'center'
] },
],
[
'Widget 1'
,
{
value
=>
$qty_of_widget_1
,
format
=> [
halign
=>
'right'
,
type
=>
'dec1comma'
] },
{
value
=>
$cost_of_widget_1
,
format
=> [
halign
=>
'right'
,
type
=>
'currencyacct'
] },
{
value
=>
$qty_of_widget_1
*
$cost_of_widget_1
,
format
=> [
halign
=>
'right'
,
type
=>
'currencyacct'
] },
],
[
'Widget 2'
,
{
value
=>
$qty_of_widget_2
,
format
=> [
halign
=>
'right'
,
type
=>
'dec1comma'
] },
{
value
=>
$cost_of_widget_2
,
format
=> [
halign
=>
'right'
,
type
=>
'currencyacct'
] },
{
value
=>
$qty_of_widget_2
*
$cost_of_widget_2
,
format
=> [
halign
=>
'right'
,
type
=>
'currencyacct'
] },
],
[
'Totals'
,
{
value
=>
"=sum(B3:B4)"
,
format
=> [
tb
=> 2,
halign
=>
'right'
,
type
=>
'dec1comma'
] },
{
value
=>
"=sum(C3:C4)"
,
format
=> [
tb
=> 2,
halign
=>
'right'
,
type
=>
'currencyacct'
] },
{
value
=>
"=sum(D3:D4)"
,
format
=> [
tb
=> 2,
halign
=>
'right'
,
type
=>
'currencyacct'
] },
],
],
);
my
$workbook
= Clearbuilt::ExcelErator->new( {
filename
=>
'my_workbook.xlsx'
} );
$workbook
->write_the_book( [\
%spreadsheet
] );
DESCRIPTION
Clearbuilt::Excelerator is a wrapper around Excel::Writer::XLSX that simplifies and standardizes its usage. You create a hash defining your spradsheet, and it does the rest for you!
More documentation of the hash will be added later, but the "SYNOPSIS" above shows a simple and common usage, with frequently-used options. A more-extensive example can be found in the package, in examples/create_test_excel_sheet
.
THE WORKBOOK ARRAY
The workbook is an array of hashes, each of which is a worksheet.
Note that the hash for this simple example is sent as an arrayref-to-the-hash. The implication of that it is, of course, that you could create multiple hashes, push them into an array in the order you want, and send a reference to that array to write_the_book
and get a multi-sheet workbook.
THE WORKSHEET HASH
There are only three valid elements in this hash:
title
: The title of the spreadsheet, which will show up in the tabs at the bottom.col_widths
: A hashref of column widths. The key is the column number (beginning with 1), and the value is the desired width.rows
: The array of rows for the sheet.
THE WORKSHEET ROWS ARRAY
The rows
array is an array of arrayrefs; each of those is an arrayref of cells. The cell can be a scalar, in which case it is displayed with default formatting, or a hashref with a value
and optionally a format
. If you do not specify a format
, you get the default for that cell.
EXPORTED METHODS
new({ filename => <filespec>})>
Opens the desired file for writing. At this time, filename
is the only parameter, which is passed verbatim into Excel::Writer::XLSX; there may be other options in the future.
write_the_book(\%spreadsheet);
Writes the file, and closes it. Easy-peasy!
REQUIRES
ROADMAP
Add other formatting functions
Default column formatting
More documentation
A robust unit test for
write_the_book
AUTHOR
D Ruth Holloway <ruthh@clearbuilt.com>
COPYRIGHT AND LICENSE
This software is copyright (c) 2022 by Clearbuilt.
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.