Create your blog and photo album with postbit
Create your blog and photo album

Create new post

Content:

Upload a picture:
Tags (keywords separated by comma)

Save Cancel
perl:   Followers: 2 ; Following: 2

Explore perl's photo albums:
Perl images (1)
Photos from posts (2)

How to read Excel files using Perl


There are two CPAN modules that you should install so you can use Perl to read Excel files for both formats ".xls extension" and ".xlsx extension.".
 
This is a sample script of how to parse xls and xlsx files using Perl:
 
#!/usr/bin/perl
 
# Script to test Spreadsheet::ParseXLSX and Spreadsheet::ParseExcel
# by Rodrigo Siqueira
# Created: 20/nov/2015
 
use strict;
 
$|=1;
 
use Data::Dumper;
use feature 'say';
 
use Spreadsheet::ParseXLSX;  # Only .xlsx files
use Spreadsheet::ParseExcel; # Only .xls  files
 
use Insite::String;
 
my $string = new String();
 
 
my $file = 'test.xlsx';
$file = 'test.xls';
 
if (!-e $file) {
  die("ERROR: Can not read file ($file): $!\n");
} else {
  say "Reading file: $file ...\n";
}
 
 
my $workbook;
 
if ($file =~ /\.xlsx/i) {
  my $parser = Spreadsheet::ParseXLSX->new;
  $workbook = $parser->parse($file);
} elsif ($file =~ /\.xls$/i) {
  $workbook = Spreadsheet::ParseExcel::Workbook->Parse($file);
}
 
say "Number of tabs: ".scalar( @{$workbook->{Worksheet}} );
 
#print Dumper($workbook);
 
foreach my $tab (@{$workbook->{Worksheet}}) {
 
  print "SHEET NAME:", $tab->{Name}, "\n";
 
  say "Number of rows: $tab->{MaxRow}";
  say "Number of columns: $tab->{MaxCol}";
  say "";
 
  for (my $iR = $tab->{MinRow} ; defined $tab->{MaxRow} && $iR <= $tab->{MaxRow} ; $iR++) { # Rows
    my $line_number = $iR;
    print "Line $line_number: ";
    for (my $iC = $tab->{MinCol} ; defined $tab->{MaxCol} && $iC <= $tab->{MaxCol} ; $iC++) { # Columns
      my $oWkC = $tab->{Cells}[$iR][$iC];
 
      if ($oWkC) {
        my $value = $oWkC->Value;
        $value = $string->iso8859_utf8($value);
 
        $value =~ s/;/\\;/gs; # Escape ';' as '\;' ??
        print "$value;"; # Column value
      }
 
      # print "($iR, $iC) = $value\n" if($oWkC);
 
    } # /Columns
 
    print "\n";
 
    # Only find 10 lines in this test, will exit
    last if $line_number==10;
 
  } # /Rows
 
}
 
 
### END ###
 

Post by perl (2015-11-20 17:02)

Post your comment:

Name: Email: Site:




| Explore users | New posts | Create your blog | Create your photo album |
| About Postbit | Our blog | Terms of use | Contact Postbit |


Copyright © 2017 - postbit.com