The Perl and Raku Conference 2025: Greenville, South Carolina - June 27-29 Learn more

 / 
Spreadsheet-Template-0.05
River stage one • 1 direct dependent • 1 total dependent
/ Spreadsheet::Template

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 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.

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, or date_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 than none is chosen.

fg_color

The foreground color for the cell. Only has meaning if a pattern other than none or solid 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 or date_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

Excel::Template

SUPPORT

You can find this documentation for this module with the perldoc command.


            

              
              
perldoc Spreadsheet::Template

You can also look for information at:

SPONSORS

Parts of this code were paid for by

Socialflow http://socialflow.com

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