Contribute: Plugins/ExportPluginsExcel

Export Plugin Tutorial 4: Excel

In this tutorial and the next one we'll look at exporting files in non-text formats. Here we will explore exporting metadata in Excel format (pre Microsoft Office 2007 which uses an XML based format).

To prepare for this tutorial you should install the Spreadsheet::Excel module. The following command as root, or using sudo should work.

cpan Spreadsheet::Excel

Excel.pm

package EPrints::Plugin::Export::MyPlugins::Excel;

@ISA = ("EPrints::Plugin::Export");

use strict;
use Spreadsheet::WriteExcel;

sub new
{
  my ($class, %opts) = @_;
  my $self = $class->SUPER::new(%opts);

  $self->{name} = "Excel";
  $self->{accept} = ['list/eprint'];
  $self->{visible} = "all";
  $self->{suffix} = ".xls";
  $self->{mimetype} = "application/vnd.ms-excel";

  return $self;
}

sub output_list
{
  my ($plugin, %opts) = @_;
  my $workbook;

  my $output;
  open(my $FH,'>',\$output);

  if (defined $opts{"fh"})
  {
    $workbook = Spreadsheet::WriteExcel->new(\*{$opts{"fh"}});
    die("Unable to create spreadsheet: $!")unless defined $workbook;
  }
  else
  {
    $workbook = Spreadsheet::WriteExcel->new($FH);
    die("Unable to create spreadsheet: $!")unless defined $workbook;
  }

  my $worksheet = $workbook->add_worksheet();

  my $i = 0;
  my @fields =
  $plugin->{session}->get_repository->get_dataset("archive")->get_fields;

  foreach my $field (@fields)
  {
    $worksheet->write(0, $i, $field->get_name);
    $i++;
  }

  $i = 1;
  foreach my $dataobj ($opts{list}->get_records)
  {
    my $j = 0;
    foreach my $field (@fields)
    {
      if ($dataobj->exists_and_set($field->get_name))
      {
        if ($field->get_property("multiple"))
        {
          if ($field->{type} eq "name")
          {
            my $namelist = '';
            foreach my $name (@{$dataobj->get_value_raw($field->get_name)})
            {
              $namelist .= $name->{family} . "," . $name->{given} . ";";
            }
            $worksheet->write($i, $j, $namelist);
          }
          elsif ($field->{type} eq "compound")
          {
            $worksheet->write($i, $j, "COMPOUND");
          }
          else
          {
            $worksheet->write($i, $j,
                        join(';',@{$dataobj->get_value($field->get_name)}));
          }
        }
        else {
          $worksheet->write($i, $j, $dataobj->get_value($field->get_name));
        }
      }
      $j++;
    }
    $i++;
  }

  $workbook->close;

  if (defined $opts{"fh"})
  {
    return undef;
  }

  return $output;
}

1;

In More Detail

Modules

We need to import a module for creating Excel files.

use Spreadsheet::WriteExcel;

Constructor

For the sake of simplicity this plugin will only deal with lists of eprints. This avoids some code duplication, and it would be fairly easy to modify the plugin to deal with both individual eprints and lists of eprints sensibly.

  
  $self->{accept} = ['list/eprint'];

The file extension and MIME type are set to values appropriate for Excel files.

  $self->{suffix} = ".xls";
  $self->{mimetype} = "application/vnd.ms-excel";

List Handling

Setting Up a Workbook

Here we create a new Excel workbook. We start by creating a file handle using a scalar rather than a filename, this creates an in-memory file. Then depending on if a file handle has been supplied or not we create a workbook object that will be written to that file handle or the scalar file handle we just created.

  my $workbook;

  my $output;
  open(my $FH,'>',\$output);

  if (defined $opts{"fh"})
  {
    $workbook = Spreadsheet::WriteExcel->new(\*{$opts{"fh"}});
    die("Unable to create spreadsheet: $!")unless defined $workbook;
  }
  else
  {
    $workbook = Spreadsheet::WriteExcel->new($FH);
    die("Unable to create spreadsheet: $!")unless defined $workbook;
  }

Handling DataObjs

  my $worksheet = $workbook->add_worksheet();

  my $i = 0;
  my @fields =
  $plugin->{session}->get_repository->get_dataset("archive")->get_fields;

  foreach my $field (@fields)
  {
    $worksheet->write(0, $i, $field->get_name);
    $i++;
  }

  $i = 1;
  foreach my $dataobj ($opts{list}->get_records)
  {
    my $j = 0;
    foreach my $field (@fields)
    {
      if ($dataobj->exists_and_set($field->get_name))
      {
        if ($field->get_property("multiple"))
        {
          if ($field->{type} eq "name")
          {
            my $namelist = '';
            foreach my $name (@{$dataobj->get_value_raw($field->get_name)})
            {
              $namelist .= $name->{family} . "," . $name->{given} . ";";
            }
            $worksheet->write($i, $j, $namelist);
          }
          elsif ($field->{type} eq "compound")
          {
            $worksheet->write($i, $j, "COMPOUND");
          }
          else
          {
            $worksheet->write($i, $j,
                        join(';',@{$dataobj->get_value($field->get_name)}));
          }
        }
        else {
          $worksheet->write($i, $j, $dataobj->get_value($field->get_name));
        }
      }
      $j++;
    }
    $i++;
  }

Finishing Up

  $workbook->close;

  if (defined $opts{"fh"})
  {
    return undef;
  }

  return $output;

Testing Your Plugin

Restart your webserver and test the plugin as in the previous tutorial.