NAME

Catalyst::Action::Serialize::SimpleXLSX - Serialize to Microsoft Excel 2007 .xlsx files

SYNOPSIS

Serializes tabular data to an Excel file, with simple configuration options.

In your REST Controller:

  package MyApp::Controller::REST;

  use parent 'Catalyst::Controller::REST';
  use DBIx::Class::ResultClass::HashRefInflator;
  use POSIX 'strftime';

  __PACKAGE__->config->{map}{'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'} = 'SimpleXLSX';

  sub books : Local ActionClass('REST') {}

  sub books_GET {
    my ($self, $c) = @_;

    # Books (Sheet 1)
    my $books_rs = $c->model('MyDB::Book')->search();
    $books_rs->result_class('DBIx::Class::ResultClass::HashRefInflator');
    my @books = map { [ @{$_}{qw/author title/} ] } $books_rs->all;

    # Authors (Sheet 2)
    my $authors_rs = $c->model('MyDB::Author')->search();
    $authors_rs->result_class('DBIx::Class::ResultClass::HashRefInflator');
    my @authors = map { [ @{$_}{qw/first_name last_name/} ] } $authors_rs->all;

    my $entity = {
      sheets => [
        {
          name => 'Books',
          header => ['Author', 'Title'],
          rows => \@books,
        },
        {
          name => 'Authors',
          header => ['First Name', 'Last Name'],
          rows => \@authors,
        },
      ],
      # .xlsx suffix automatically appended
      filename => 'myapp-books-'.strftime('%m-%d-%Y', localtime)
    };

    $self->status_ok(
      $c,
      entity => $entity
    );
  }

In your jQuery webpage, to initiate a file download:

  <script>
  $(document).ready(function () {

  function export_to_excel() {
    $('<iframe ' + 'src="/item?content-type=application/vnd.openxmlformats-officedocument.spreadsheetml.sheet">').hide().appendTo('body');
  }
  $("#books").on("click", export_to_excel);

  });
  </script>

Note, the content-type query param is required if you're just linking to the action. It tells Catalyst::Controller::REST what you're serializing the data as.

DESCRIPTION

Your entity should be either:

• an array of arrays

• an array of arrays of arrays

• a hash with the keys as described below and in the "SYNOPSIS"

If entity is a hashref, keys should be:

sheets

An array of worksheets. Either sheets or a worksheet specification at the top level is required.

filename

Optional. The name of the file before .xlsx. Defaults to "data".

Each sheet should be an array of arrays, or a hashref with the following fields:

name

Optional. The name of the worksheet.

rows

Required. The array of arrays of rows.

header

Optional, an array for the first line of the sheet, which will be in bold.

column_widths

Optional, the widths in characters of the columns. Otherwise the widths are calculated automatically from the data and header.

If you only have one sheet, you can put it in the top level hash.

AUTHOR

Mike Baas <mbaas at cpan.org>

ORIGINAL AUTHOR

Rafael Kitover <rkitover at cpan.org>

ACKNOWLEDGEMENTS

This module is really nothing more than a tweak to Catalyst::Action::Serialize::SimpleExcel that drops in Excel::Writer::XLSX for compatibility with Excel 2007 and later. I just needed more rows!

SEE ALSO

Catalyst, Catalyst::Controller::REST, Catalyst::Action::REST, Catalyst::Action::Serialize::SimpleExcel, Excel::Writer::XLSX

REPOSITORY

https://github.com/initself/Catalyst-Action-Serialize-SimpleXLSX

COPYRIGHT & LICENSE

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.

cpanm Catalyst::Action::Serialize::SimpleXLSX

perl -MCPAN -e shell
install Catalyst::Action::Serialize::SimpleXLSX