Convert XLSX/XLS to CSV file

gistfile1.pl

#!/usr/bin/env perl

use strict;
use warnings;
use Carp;
use Getopt::Long;
use Pod::Usage;
use File::Basename qw/fileparse/;
use File::Spec;
use Spreadsheet::ParseExcel;
use Spreadsheet::XLSX;

my %args = ();
my $help = undef;
GetOptions(
           \%args,
           'excel=s',
           'sheet=s',
           'man|help'=>\$help,
          ) or die pod2usage(1);

pod2usage(1) if $help;
pod2usage(-verbose=>2, exitstatus=>0, output=>\*STDERR) unless $args{excel} || $args{sheet};

if (_getSuffix($args{excel}) eq ".xls") {
    my $file = File::Spec->rel2abs($args{excel});

    if (-e $file) {
        print _XLS(file=>$file, sheet=>$args{sheet});
    } else {
        die "Error: can not find file";
    }
}
elsif (_getSuffix($args{excel}) eq ".xlsx") {
    my $file = File::Spec->rel2abs($args{excel});

    if (-e $file) {
        print _XLSX(file=>$file, sheet=>$args{sheet});
    } else {
        die "Error: Can not find file";
    }
}

sub _XLS {
    my %opts = (
                file   => undef,
                sheet  => undef,
                @_,
               );

    my $aggregated = ();
    my $parser     = Spreadsheet::ParseExcel->new();
    my $workbook   = $parser->parse($opts{file});

    if (!defined $workbook) {
        croak "Error: $parser->error()";
    }

    foreach my $worksheet ($workbook->worksheet($opts{sheet})) {
        my ($row_min, $row_max) = $worksheet->row_range();
        my ($col_min, $col_max) = $worksheet->col_range();

        foreach my $row ($row_min .. $row_max){
            foreach my $col ($col_min .. $col_max){
                my $cell = $worksheet->get_cell($row, $col);
                if ($cell) {
                    $aggregated .= $cell->value().',';
                } else {
                    $aggregated .= ',';
                }
            }
            $aggregated .= "\n";
        }
    }
    return $aggregated;
}

sub _XLSX {
    my %opts = (
                file   => undef,
                sheet  => undef,
                @_,
               );

    my $aggregated_x = ();
    my $excel = Spreadsheet::XLSX->new($opts{file});

    foreach my $sheet (@{ $excel->{Worksheet} }) {
        if ($sheet->{Name} eq $opts{sheet}) {
            $sheet->{MaxRow} ||= $sheet->{MinRow};

            foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {
                $sheet->{MaxCol} ||= $sheet->{MinCol};
                foreach my $col ($sheet->{MinCol} ..  $sheet->{MaxCol}) {
                    my $cell = $sheet->{Cells}->[$row]->[$col];
                    if ($cell) {
                        $aggregated_x .= $cell->{Val}.',';
                    }
                }
                $aggregated_x .= "\n";
            }
        }
    }
    return $aggregated_x;
}

sub _getSuffix {
    my $f = shift;
    my ($basename, $dirname, $ext) = fileparse($f, qr/\.[^\.]*$/);
    return $ext;
}


__END__

=head1 NAME

xls2csv - Converting XLS/XLSX file to CSV

=head1 SYNOPSIS

perl xls2csv --excel data.xls|.xlsx --sheet Sheet1

=head1 OPTIONS

 -e,  --excel     Given a XLS or XLSX file.         [Required]
 -s,  --sheet     Given a sheet name of the file.   [Required]
 -h,  --help      Show help messages.

=head1 DESCRIPTION

This program converts .xls and .xlsx file to csv.

=cut

perl gistfile1.pl test.xls Sheet1 > out.csv
perl gistfile1.pl test.xlsx Sheet2 > out.csv

Hi! I'm trying to use your script but I have a problem. The error is:
Can't call method "row_range" on an undefined value at gistfile1.pl line 60.

and this is the command:
perl gistfile1.pl -e file.xls -s Sheet1 > out.csv

What can be the error? (sorry for my english)

Hi, you have a bug in your code.

In the xlsx parsing portion the code fragment here:

if ($cell) {
$aggregated_x .= $cell->{Val}.',';
}
}
$aggregated_x .= "\n";

should actually read:

if ($cell) {
$aggregated_x .= $cell->{Val}.',';
}

else {
$aggregated_x .= ',';
}

}
$aggregated_x .= "\n";
}

Otherwise the xlsx parsing function will ignore blank cells, a huge problem for CSV files!

Kind regards,

JB.

where i must send my file pl and eccel in my cpanel root?

but to use this script i must install xls2csv in my server ??? how can i do in shared hosting with cpanel ?

not work , error with separate fields!!!