Skip to content

Instantly share code, notes, and snippets.

@soh-i
Last active May 5, 2021 13:49
Show Gist options
  • Save soh-i/5866222 to your computer and use it in GitHub Desktop.
Save soh-i/5866222 to your computer and use it in GitHub Desktop.
Convert XLSX/XLS to CSV file
#!/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
@soh-i
Copy link
Author

soh-i commented Jun 29, 2013

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

@oskyar
Copy link

oskyar commented Apr 9, 2014

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)

@JBSTO
Copy link

JBSTO commented Sep 4, 2014

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.

@kvnx
Copy link

kvnx commented Jul 8, 2015

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

@kvnx
Copy link

kvnx commented Jul 8, 2015

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

@kvnx
Copy link

kvnx commented Jul 8, 2015

not work , error with separate fields!!!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment