Why not adopt me?
NAME
Spreadsheet::Template - generate spreadsheets from a template
VERSION
version 0.05
SYNOPSIS
use Spreadsheet::Template;
my $template = Spreadsheet::Template->new;
my $in = do { local $/; <> };
my $out = $template->render($in);
open my $fh, '>', 'out.xlsx';
binmode $fh;
$fh->print($out);
$fh->close;
DESCRIPTION
This module is used to render spreadsheets from JSON files which describe the desired content and formatting. These JSON files can be preprocessed with a template engine such as Text::Xslate in order to customize the spreadsheet contents before generation, in a similar way to how HTML pages can be rendered with templates.
The typical workflow for using this module is to create a sample spreadsheet in Excel with the desired layout and formatting, and use Spreadsheet::Template::Generator (or the included spreadsheet_to_template
script) to generate a base template. That base template can then be edited to add in template declarations, and then this module can be used to generate new spreadsheets based on the template.
ATTRIBUTES
processor_class
Name of the Spreadsheet::Template::Processor class to use to preprocess the template. Defaults to Spreadsheet::Template::Processor::Xslate.
processor_options
Arguments to pass to the processor_class
constructor.
writer_class
Name of the Spreadsheet::Template::Writer class to use to preprocess the template. Defaults to Spreadsheet::Template::Writer::XLSX.
writer_options
Arguments to pass to the writer_class
constructor.
json
Instance of a JSON class that will handle decoding. Defaults to an instance of JSON. Passing in a JSON obj with ->relaxed(1) set will allow for trailing commas in your templates.
METHODS
render($template, $vars)
Calls process
on the Spreadsheet::Template::Processor instance with $template
and $vars
as arguments, decodes the result as JSON, and returns the result of passing that data to the write
method of the Spreadsheet::Template::Writer instance.
DATA FORMAT
The intermediate data format that should be produced after the template has been preprocessed is a JSON file, with a structure that looks like this:
{
"selection" : 0,
"worksheets" : [
{
"column_widths" : [ 10, 10, 10 ],
"name" : "Sheet1",
"row_heights" : [ 18, 18, 18 ],
"selection" : [ 0, 0 ],
"autofilter" : [
[ [0, 0], [0, 2] ]
],
"cells" : [
[
{
"contents" : "This is cell A1",
"format" : {
"color" : "#000000",
"size" : 14,
"text_wrap" : true,
"valign" : "vcenter"
},
"type" : "string"
},
{
"contents" : "3.25",
"format" : {
"color" : "#000000",
"num_format" : "\"$\"#,##0.00_);[Red]\\(\"$\"#,##0.00\\)",
"size" : 14
},
"type" : "number"
}
],
[
{
"contents" : "2013-03-20T00:00:00",
"format" : {
"color" : "#000000",
"align" : "center",
"num_format" : "d-mmm",
"size" : 14,
"border_color" : [
"#000000",
"#000000",
"#000000",
"#000000"
],
"border" : [
"thin",
"thin",
"thin",
"thin"
]
},
"type" : "date_time"
},
{
"contents" : "3.25",
"formula" : "SUM(B1:B1)",
"format" : {
"bg_color" : "#d8d8d8",
"bold" : true,
"color" : "#000000",
"num_format" : "\"$\"#,##0.00_);[Red]\\(\"$\"#,##0.00\\)",
"pattern" : "solid",
"size" : 14
},
"type" : "string"
}
]
],
"merge" : [
{
"range" : [ [1, 0], [1, 2] ],
"contents" : "Merged Contents",
"format" : {
"color" : "#000000"
},
"type" : "string"
}
]
}
]
}
workbook
The entire JSON document describes a workbook to be produced. The document should be a JSON object with these keys:
- selection
-
The (zero-based) index of the worksheet to be initially selected when the spreadsheet is opened.
- worksheets
-
An array of worksheet objects.
worksheet
Each element of the worksheets
array in the workbook object should be a JSON object with these keys:
- name
-
The name of the worksheet.
- column_widths
-
An array of numbers corresponding to the widths of the columns in the spreadsheet.
- row_heights
-
An array of numbers corresponding to the heights of the rows in the spreadsheet.
- selection
-
An array of two numbers corresponding to the (zero-based) row and column of the cell that should be selected when the worksheet is first displayed.
- autofilter
-
Enables autofilter behavior for each range of cells listed. Cell ranges are specified by an array of two arrays of two numbers, corresponding to the row and column of the top left and bottom right cell of the autofiltered range.
- cells
-
An array of arrays of cell objects. Each innermost array represents a row, containing all of the cell data for that row.
- merge
-
An array of merge objects. Merge objects are identical to cell objects, except that they contain an additional
range
key, which has a value of an array of two arrays of two numbers, corresponding to the row and column of the top left and bottom right cell of the range to be merged.
cell
Each element of the two-dimensional cells
array in a worksheet object should be a JSON object with these keys:
- contents
-
The unformatted contents of the cell. For cells with a
type
ofstring
, this should be a string, for cells with atype
ofnumber
, this should be a number, and for cells with atype
ofdate_time
, this should be a string containing the ISO8601 representation of the date and time. - format
-
The format object describing how the cell's contents should be formatted.
- type
-
The type of the data in the cell. Can be either
string
,number
, ordate_time
. - formula
-
The formula used to calculate the cell contents. This field is optional. If you want the generated spreadsheet to be able to be read by programs other than full spreadsheet applications (such as by Spreadsheet::Template::Generator, then you should ensure that you include an accurate value for
contents
as well, since most simple spreadsheet parsers don't include a full formula calculation engine.
format
Each cell object contains a format
key whose value should be a JSON object with these (all optional) keys:
- size
-
The font size for the cell contents.
- color
-
The font color for the cell contents.
- bold
-
True if the cell contents are bold.
- italic
-
True if the cell contents are italic.
- pattern
-
The background pattern for the cell. Can have any of these values (with
none
being the default if nothing is specified):none solid medium_gray dark_gray light_gray dark_horizontal dark_vertical dark_down dark_up dark_grid dark_trellis light_horizontal light_vertical light_down light_up light_grid light_trellis gray_125 gray_0625
- bg_color
-
The background color for the cell. Only has meaning if a
pattern
other thannone
is chosen. - fg_color
-
The foreground color for the cell. Only has meaning if a
pattern
other thannone
orsolid
is chosen. - border
-
The border style for the cell. This should be an array with four elements, corresponding to the left, right, top, and bottom borders. Each element can have any of these values (with
none
being the default if nothing is specified):none thin medium dashed dotted thick double hair medium_dashed dash_dot medium_dash_dot dash_dot_dot medium_dash_dot_dot slant_dash_dot
- border_color
-
The border color for the cell. This should be an array with four elements, corresponding to the left, right, top, and bottom borders.
- align
-
The horizontal alignment for the cell contents. Can have any of these values, with
none
being the default:none left center right fill justify center_across
- valign
-
The vertical alignment for the cell contents. Can have any of these values, with
bottom
being the default:top vcenter bottom vjustify
- text_wrap
-
True if the contents of the cell should be text-wrapped.
- num_format
-
The numeric format for the cell. Only meaningful if the cell's type is
number
ordate_time
. This is the string representation of the format as understood by Excel itself.
BUGS
Default values aren't handled properly - spreadsheets can set defaults for things like font sizes, but this isn't actually handled, so cells that are supposed to use the default may get an incorrect value.
Please report any bugs to GitHub Issues at https://github.com/doy/spreadsheet-template/issues.
SEE ALSO
SUPPORT
You can find this documentation for this module with the perldoc command.
perldoc Spreadsheet::Template
You can also look for information at:
MetaCPAN
Github
RT: CPAN's request tracker
http://rt.cpan.org/NoAuth/Bugs.html?Dist=Spreadsheet-Template
CPAN Ratings
SPONSORS
Parts of this code were paid for by
AUTHOR
Jesse Luehrs <doy@tozt.net>
COPYRIGHT AND LICENSE
This software is Copyright (c) 2015 by Jesse Luehrs.
This is free software, licensed under:
The MIT (X11) License