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

use Spreadsheet::WriteExcel;

Constructor

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

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

List Handling

Setting Up a Workbook

  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.