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
@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