File Coverage

blib/lib/Data/Table/Excel.pm

Criterion	Covered	Total	%
statement	14	16	87.5
branch	1	2	50.0
condition			n/a
subroutine	6	6	100.0
pod			n/a
total	21	24	87.5

package Data::Table::Excel;
BEGIN { die "Your perl version is old, see README for instructions" if $] < 5.005; }

use strict;
use Data::Table;
use Spreadsheet::WriteExcel;
use Spreadsheet::ParseExcel;
#use Spreadsheet::XLSX;
use Spreadsheet::ParseXLSX;
use Excel::Writer::XLSX;
use vars qw($VERSION @ISA @EXPORT @EXPORT_OK);
use Carp;
use Exporter 'import';

@ISA = qw(Exporter AutoLoader);
# Items to export into callers namespace by default. Note: do not export
# names by default without a very good reason. Use EXPORT_OK instead.
# Do not simply export all your public functions/methods/constants.
@EXPORT = ();
@EXPORT_OK = qw(
  tables2xls xls2tables tables2xlsx xlsx2tables xls2xlsx xlsx2xls is_xlsx excelFileToTable
);
$VERSION = '0.5';

sub xls2tables {
  my ($fileName, $sheetNames, $sheetIndices) = @_;
  return excelFileToTable($fileName, $sheetNames, $sheetIndices, '2003');
}

sub xlsx2tables {
  my ($fileName, $sheetNames, $sheetIndices) = @_;
  return excelFileToTable($fileName, $sheetNames, $sheetIndices, '2007');
}

sub is_xlsx {
    my $filename=shift;
    open(IN, $filename) or die "Cannot open $filename!";
    binmode IN;
    my $buffer;
    read(IN, $buffer, 2, 0);
    close(IN);
    return uc($buffer) eq 'PK';
}

sub excelFileToTable {
  my ($fileName, $sheetNames, $sheetIndices, $excelFormat) = @_;
  my %sheetsName = ();
  my %sheetsIndex = ();
  if (defined($sheetNames)) {
    $sheetNames=[$sheetNames] if (ref($sheetNames) eq 'SCALAR');
    if (ref($sheetNames) eq 'ARRAY') {
      foreach my $name (@$sheetNames) {
        $sheetsName{$name} = 1;
      }
    }
  } elsif (defined($sheetIndices)) {
    $sheetIndices=[$sheetIndices] if (ref($sheetIndices) eq 'SCALAR');
    if (ref($sheetIndices) eq 'ARRAY') {
      foreach my $idx (@$sheetIndices) {
        $sheetsIndex{$idx} = 1;
      }
    }
  }
  my $excel = undef;
  if (!defined($excelFormat)) {
    $excelFormat=is_xlsx($fileName)?'2007':'2003';
  }
  if ($excelFormat eq '2003') {
    $excel = Spreadsheet::ParseExcel::Workbook->Parse($fileName);
  } elsif ($excelFormat eq '2007') {
    #$excel = Spreadsheet::XLSX->new($fileName);
    my $parser=Spreadsheet::ParseXLSX->new;
    $excel = $parser->parse($fileName);
  } else {
    croak "Unrecognized Excel format, must be either 2003 or 2007!";
  }
  my @tables = ();
  my @sheets = ();
  my @column_headers = ();
  my $num = 0;
  foreach my $sheet (@{$excel->{Worksheet}}) {
    $num++;
    next if ((scalar keys %sheetsName) && !defined($sheetsName{$sheet->{Name}}));
    next if ((scalar keys %sheetsIndex) && !defined($sheetsIndex{$num}));
    next unless defined($sheet->{MinRow}) && defined($sheet->{MaxRow}) && defined($sheet->{MinCol}) && defined($sheet->{MaxRow});
    push @sheets, $sheet->{Name};
    #printf("Sheet: %s\n", $sheet->{Name});
    $sheet->{MaxRow} ||= $sheet->{MinRow};
    $sheet->{MaxCol} ||= $sheet->{MinCol};
    my @header = ();
    foreach my $col ($sheet->{MinCol} ..  $sheet->{MaxCol}) {
      my $cel=$sheet->{Cells}[$sheet->{MinRow}][$col];
      push @header, defined($cel)?$cel->{Val}:undef;
    }
    my $s = join($Data::Table::DEFAULTS{CSV_DELIMITER}, map {Data::Table::csvEscape($_)} @header);
    my $t = undef;
    my $hasColumnHeader=Data::Table::fromFileIsHeader($s, $Data::Table::DEFAULTS{CSV_DELIMITER});
    push @column_headers, $hasColumnHeader;
    if ($hasColumnHeader) {
      $t = new Data::Table([], \@header, 0);
    } else {
      my @newHeader =  map {"col$_"} (1..($sheet->{MaxCol}-$sheet->{MinCol}+1));
      # name each column as col1, col2, .. etc
      $t = new Data::Table([\@header], \@newHeader, 0);
    }
    foreach my $row (($sheet->{MinRow}+1) .. $sheet->{MaxRow}) {
      my @one = ();
      foreach my $col ($sheet->{MinCol} ..  $sheet->{MaxCol}) {
        my $cel=$sheet->{Cells}[$row][$col];
        push @one, defined($cel)?$cel->{Val}:undef;
      }
      $t->addRow(\@one);
    }
    push @tables, $t;
  }
  return (\@tables, \@sheets, \@column_headers);
}

# color palette is defined in
# http://search.cpan.org/src/JMCNAMARA/Spreadsheet-WriteExcel-2.20/doc/palette.html
sub oneTable2Worksheet {
  my ($workbook, $t, $name, $colors, $portrait, $column_header) = @_;
  $column_header = 0 unless defined($column_header);
  # Add a worksheet
  my $worksheet = $workbook->add_worksheet($name);
  $portrait=1 unless defined($portrait);
  #my @BG_COLOR=(26,47,44);
  my @BG_COLOR=(44, 9, 30);
  @BG_COLOR=@$colors if ((ref($colors) eq "ARRAY") && (scalar @$colors==3));
  my $fmt_header= $workbook->add_format();
  $fmt_header->set_bg_color($BG_COLOR[2]);
  $fmt_header->set_bold();
  $fmt_header->set_color('white');
  my $fmt_odd= $workbook->add_format();
  $fmt_odd->set_bg_color($BG_COLOR[0]);
  my $fmt_even= $workbook->add_format();
  $fmt_even->set_bg_color($BG_COLOR[1]);
  my @FORMAT = ($fmt_odd, $fmt_even);

  my @header=$t->header;
  my $offset=($column_header)? 1:0;
  if ($portrait) {
    if ($column_header) {
      for (my $i=0; $i<@header; $i++) {
        $worksheet->write(0, $i, $header[$i], $fmt_header);
      }
    }
    for (my $i=0; $i<$t->nofRow; $i++) {
      for (my $j=0; $j<$t->nofCol; $j++) {
        $worksheet->write($i+$offset, $j, $t->elm($i,$j), $FORMAT[$i%2]);
      }
    }
  } else {
    if ($column_header) {
      for (my $i=0; $i<@header; $i++) {
        $worksheet->write($i, 0, $header[$i], $fmt_header);
      }
    }
    for (my $i=0; $i<$t->nofRow; $i++) {
      for (my $j=0; $j<$t->nofCol; $j++) {
        $worksheet->write($j, $i+$offset, $t->elm($i,$j), $FORMAT[$i%2]);
      }
    }
  }
}

sub tables2excelFile {
  my ($fileName, $tables, $names, $colors, $portrait, $excelFormat, $column_headers) = @_;
  confess("No table is specified!\n") unless (defined($tables)&&(scalar @$tables));
  $names =[] unless defined($names);
  $colors=[] unless defined($colors);
  $portrait=[] unless defined($portrait);
  $column_headers=[1] unless defined($column_headers);
  my $workbook = undef;
  $excelFormat='2007' unless defined($excelFormat);
  if ($excelFormat eq '2003') {
    $workbook = Spreadsheet::WriteExcel->new($fileName);
  } elsif ($excelFormat eq '2007') {
    $workbook = Excel::Writer::XLSX->new($fileName);
  } else {
    croak "Unrecognized Excel format, must be either 2003 or 2007!";
  }
  $portrait=[] unless defined($portrait);
  my ($prevColors, $prevPortrait, $prevColumnHeader) = (undef, undef, undef);
  for (my $i=0; $i<@$tables; $i++) {
    my $myColor=$colors->[$i];
    $myColor=$prevColors if (!defined($myColor) && defined($prevColors));
    $prevColors=$myColor;
    my $myPortrait=$portrait->[$i];
    $myPortrait=$prevPortrait if (!defined($myPortrait) && defined($prevPortrait));
    $prevPortrait=$myPortrait;
	my $mySheet = $names->[$i] ? $names->[$i]:"Sheet".($i+1);
    my $myColumnHeader = $column_headers->[$i];
    $myColumnHeader = $prevColumnHeader if (!defined($myColumnHeader) && defined($prevColumnHeader));
    $prevColumnHeader=$myColumnHeader;
    oneTable2Worksheet($workbook, $tables->[$i], $mySheet, $myColor, $myPortrait, $myColumnHeader);
  }
}

sub tables2xls {
  my ($fileName, $tables, $names, $colors, $portrait, $column_headers) = @_;
  tables2excelFile($fileName, $tables, $names, $colors, $portrait, '2003', $column_headers);
}

sub tables2xlsx {
  my ($fileName, $tables, $names, $colors, $portrait, $column_headers) = @_;
  tables2excelFile($fileName, $tables, $names, $colors, $portrait, '2007', $column_headers);
}

sub xls2xlsx {
  my ($xlsFile, $xlsxFile) = @_;
  unless (defined($xlsxFile)) {
    $xlsxFile = $xlsFile;
    $xlsxFile =~ s/\.xls$/\.xlsx/i;
  }
  my ($tables, $table_names, $column_headers) = xls2tables($xlsFile);
  tables2xlsx($xlsxFile, $tables, $table_names, undef, undef, $column_headers);
}

sub xlsx2xls {
  my ($xlsxFile, $xlsFile) = @_;
  unless (defined($xlsFile)) {
    $xlsFile = $xlsxFile;
    $xlsFile =~ s/\.xlsx$/\.xls/i;
  }
  my ($tables, $table_names, $column_headers) = xlsx2tables($xlsxFile);
  tables2xls($xlsFile, $tables, $table_names, undef, undef, $column_headers);
}

1;

__END__