Spreadsheet::Template - generate spreadsheets from a template
version 0.05
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;
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.
spreadsheet_to_template
Name of the Spreadsheet::Template::Processor class to use to preprocess the template. Defaults to Spreadsheet::Template::Processor::Xslate.
Arguments to pass to the processor_class constructor.
processor_class
Name of the Spreadsheet::Template::Writer class to use to preprocess the template. Defaults to Spreadsheet::Template::Writer::XLSX.
Arguments to pass to the writer_class constructor.
writer_class
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.
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.
process
$template
$vars
write
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" } ] } ] }
The entire JSON document describes a workbook to be produced. The document should be a JSON object with these keys:
The (zero-based) index of the worksheet to be initially selected when the spreadsheet is opened.
An array of worksheet objects.
Each element of the worksheets array in the workbook object should be a JSON object with these keys:
worksheets
The name of the worksheet.
An array of numbers corresponding to the widths of the columns in the spreadsheet.
An array of numbers corresponding to the heights of the rows in the spreadsheet.
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.
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.
An array of arrays of cell objects. Each innermost array represents a row, containing all of the cell data for that row.
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.
range
Each element of the two-dimensional cells array in a worksheet object should be a JSON object with these keys:
cells
The unformatted contents of the cell. For cells with a type of string, this should be a string, for cells with a type of number, this should be a number, and for cells with a type of date_time, this should be a string containing the ISO8601 representation of the date and time.
type
string
number
date_time
The format object describing how the cell's contents should be formatted.
The type of the data in the cell. Can be either string, number, or date_time.
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.
contents
Each cell object contains a format key whose value should be a JSON object with these (all optional) keys:
format
The font size for the cell contents.
The font color for the cell contents.
True if the cell contents are bold.
True if the cell contents are italic.
The background pattern for the cell. Can have any of these values (with none being the default if nothing is specified):
none
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
The background color for the cell. Only has meaning if a pattern other than none is chosen.
pattern
The foreground color for the cell. Only has meaning if a pattern other than none or solid is chosen.
solid
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
The border color for the cell. This should be an array with four elements, corresponding to the left, right, top, and bottom borders.
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
The vertical alignment for the cell contents. Can have any of these values, with bottom being the default:
bottom
top vcenter bottom vjustify
True if the contents of the cell should be text-wrapped.
The numeric format for the cell. Only meaningful if the cell's type is number or date_time. This is the string representation of the format as understood by Excel itself.
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.
Excel::Template
You can find this documentation for this module with the perldoc command.
perldoc Spreadsheet::Template
You can also look for information at:
MetaCPAN
https://metacpan.org/release/Spreadsheet-Template
Github
https://github.com/doy/spreadsheet-template
RT: CPAN's request tracker
http://rt.cpan.org/NoAuth/Bugs.html?Dist=Spreadsheet-Template
CPAN Ratings
http://cpanratings.perl.org/d/Spreadsheet-Template
Parts of this code were paid for by
Jesse Luehrs <doy@tozt.net>
This software is Copyright (c) 2015 by Jesse Luehrs.
This is free software, licensed under:
The MIT (X11) License
To install Spreadsheet::Template, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Spreadsheet::Template
CPAN shell
perl -MCPAN -e shell install Spreadsheet::Template
For more information on module installation, please visit the detailed CPAN module installation guide.