@@ -1,3 +1,33 @@
+0.54 - 30 Jan 2014, H.Merijn Brand
+ * Optional features required versions now builtin
+
+0.53 - 29 Jan 2014, H.Merijn Brand
+ * Updated copyright to 2014
+ * Prefer Data::Peek over Data::Dumper
+ * Support (and prefer) Spreadsheet::ParseXLSX for .xlsx
+
+0.52 - 05 Dec 2013, H.Merijn Brand
+ * Store CSV parse error - if any - in $ss->[0]{error}
+
+0.51 - 30 Oct 2013, H.Merijn Brand
+ * Link xlsgrep during install
+
+0.50 - 24 Oct 2013, H.Merijn Brand
+ * Added xlsgrep to examples
+ * Allow -C B,D for xlscat
+ * More prominent reference to xlscat in docs
+
+0.49 - 11 May 2013, H.Merijn Brand
+ * /dev/null cannot be used for tests on Windows
+
+0.48 - 14 Mar 2013, H.Merijn Brand
+ * Improve documentation consistency (RT#80409)
+ * Updated copyright to 2013
+ * Force old(er) tar format (ustar) - assumes GNU tar on release box
+ * Fix yml/json optional_features
+ * Install utilities from example by default
+ * Several minor changes (for maint)
+
0.47 - 30 Jun 2012, H.Merijn Brand
* Note that empty sheets are skipped when clip is true (RT#75277)
* Allow undef as valid value for the options (Max Maischein)
@@ -1,89 +1,131 @@
{
- "resources" : {
- "repository" : "http://repo.or.cz/w/Spreadsheet-Read.git",
- "license" : "http://dev.perl.org/licenses/"
- },
"meta-spec" : {
- "version" : 2,
- "url" : "https://metacpan.org/module/CPAN::Meta::Spec?#meta-spec"
+ "version" : "2",
+ "url" : "http://search.cpan.org/perldoc?CPAN::Meta::Spec"
},
- "test_requires" : {
- "Test::More" : "0.88",
- "Test::Harness" : "0",
- "Test::NoWarnings" : "0"
+ "release_status" : "stable",
+ "dynamic_config" : 1,
+ "resources" : {
+ "repository" : {
+ "web" : "http://repo.or.cz/w/Spreadsheet-Read.git",
+ "type" : "git",
+ "url" : "http://repo.or.cz/r/Spreadsheet-Read.git"
+ },
+ "license" : [
+ "http://dev.perl.org/licenses/"
+ ]
},
- "distribution_type" : "module",
- "generated_by" : "Author",
- "version" : "0.47",
"name" : "Spreadsheet-Read",
- "author" : [
- "H.Merijn Brand <h.m.brand@xs4all.nl>"
- ],
- "license" : "perl",
"provides" : {
"Spreadsheet::Read" : {
- "version" : "0.47",
- "file" : "Read.pm"
+ "file" : "Read.pm",
+ "version" : "0.54"
}
},
+ "version" : "0.54",
+ "license" : [
+ "perl_5"
+ ],
"optional_features" : {
- "opt_tools" : {
- "recommends" : {
- "Tk::TableMatrix::Spreadsheet" : "0",
- "Tk::NoteBook" : "0",
- "Tk" : "0"
+ "opt_csv" : {
+ "prereqs" : {
+ "runtime" : {
+ "recommends" : {
+ "Text::CSV" : "1.32",
+ "Text::CSV_PP" : "1.31",
+ "Text::CSV_XS" : "1.04"
+ },
+ "requires" : {
+ "Text::CSV_XS" : "0.71"
+ }
+ }
+ },
+ "description" : "Provides parsing of CSV streams"
+ },
+ "opt_oo" : {
+ "prereqs" : {
+ "runtime" : {
+ "requires" : {
+ "Spreadsheet::ReadSXC" : "0.20"
+ }
+ }
},
- "description" : "Spreadsheet tools"
+ "description" : "Provides parsing of OpenOffice spreadsheets"
},
"opt_excelx" : {
- "requires" : {
- "Spreadsheet::XLSX" : "0.13",
- "Spreadsheet::XLSX::Fmt2007" : "0"
+ "prereqs" : {
+ "runtime" : {
+ "requires" : {
+ "Spreadsheet::ParseExcel::FmtDefault" : "0",
+ "Spreadsheet::ParseXLSX" : "0.13"
+ }
+ }
},
"description" : "Provides parsing of Microsoft Excel 2007 files"
},
- "opt_csv" : {
- "requires" : {
- "Text::CSV_XS" : "0.69"
+ "opt_tools" : {
+ "description" : "Spreadsheet tools",
+ "prereqs" : {
+ "runtime" : {
+ "recommends" : {
+ "Tk::NoteBook" : "0",
+ "Tk" : "804.032",
+ "Tk::TableMatrix::Spreadsheet" : "0"
+ }
+ }
+ }
+ },
+ "opt_excel" : {
+ "prereqs" : {
+ "runtime" : {
+ "recommends" : {
+ "Spreadsheet::ParseExcel" : "0.59"
+ },
+ "requires" : {
+ "Spreadsheet::ParseExcel" : "0.34",
+ "Spreadsheet::ParseExcel::FmtDefault" : "0"
+ }
+ }
},
+ "description" : "Provides parsing of Microsoft Excel files"
+ }
+ },
+ "generated_by" : "Author",
+ "author" : [
+ "H.Merijn Brand <h.m.brand@xs4all.nl>"
+ ],
+ "prereqs" : {
+ "runtime" : {
"recommends" : {
- "Text::CSV_PP" : "1.29",
- "Text::CSV_XS" : "0.90",
- "Text::CSV" : "1.21"
+ "Data::Peek" : "0.39",
+ "File::Temp" : "0.2304",
+ "IO::Scalar" : "0",
+ "perl" : "5.018001"
},
- "description" : "Provides parsing of CSV streams"
- },
- "opt_oo" : {
"requires" : {
- "Spreadsheet::ReadSXC" : "0.20"
- },
- "description" : "Provides parsing of OpenOffice spreadsheets"
+ "Carp" : "0",
+ "Data::Dumper" : "0",
+ "File::Temp" : "0.22",
+ "Exporter" : "0",
+ "Data::Peek" : "0",
+ "perl" : "5.006"
+ }
},
- "opt_excel" : {
- "requires" : {
- "Spreadsheet::ParseExcel" : "0.26",
- "Spreadsheet::ParseExcel::FmtDefault" : "0"
- },
+ "test" : {
"recommends" : {
- "Spreadsheet::ParseExcel" : "0.59"
+ "Test::More" : "1.001002"
},
- "description" : "Provides parsing of Microsoft Excel files"
+ "requires" : {
+ "Test::Harness" : "0",
+ "Test::NoWarnings" : "0",
+ "Test::More" : "0.88"
+ }
+ },
+ "configure" : {
+ "requires" : {
+ "ExtUtils::MakeMaker" : "0"
+ }
}
},
- "requires" : {
- "perl" : "5.006",
- "File::Temp" : "0.22",
- "Data::Dumper" : "0",
- "Exporter" : "0",
- "Carp" : "0"
- },
- "recommends" : {
- "Test::More" : "0.98",
- "perl" : "5.016000",
- "IO::Scalar" : "0"
- },
- "abstract" : "Meta-Wrapper for reading spreadsheet data",
- "configure_requires" : {
- "ExtUtils::MakeMaker" : "0"
- }
+ "abstract" : "Meta-Wrapper for reading spreadsheet data"
}
@@ -1,66 +1,58 @@
---- #YAML:1.0
-name: Spreadsheet-Read
-version: 0.47
-abstract: Meta-Wrapper for reading spreadsheet data
-license: perl
-author:
+---
+abstract: Meta-Wrapper for reading spreadsheet data
+author:
- H.Merijn Brand <h.m.brand@xs4all.nl>
-generated_by: Author
-distribution_type: module
-provides:
- Spreadsheet::Read:
- file: Read.pm
- version: 0.47
-requires:
- perl: 5.006
- Exporter: 0
- Carp: 0
- Data::Dumper: 0
- File::Temp: 0.22
-configure_requires:
- ExtUtils::MakeMaker: 0
-test_requires:
- Test::Harness: 0
- Test::More: 0.88
- Test::NoWarnings: 0
-recommends:
- perl: 5.016000
- IO::Scalar: 0
- Test::More: 0.98
-resources:
- license: http://dev.perl.org/licenses/
- repository: http://repo.or.cz/w/Spreadsheet-Read.git
-meta-spec:
- version: 1.4
- url: http://module-build.sourceforge.net/META-spec-v1.4.html
-optional_features:
- opt_csv:
- description: Provides parsing of CSV streams
- requires:
- Text::CSV_XS: 0.69
- recommends:
- Text::CSV: 1.21
- Text::CSV_PP: 1.29
- Text::CSV_XS: 0.90
- opt_excel:
- description: Provides parsing of Microsoft Excel files
- requires:
- Spreadsheet::ParseExcel: 0.26
+configure_requires:
+ ExtUtils::MakeMaker: 0
+dynamic_config: 1
+generated_by: Author, CPAN::Meta::Converter version 2.133380
+license: perl
+meta-spec:
+ url: http://module-build.sourceforge.net/META-spec-v1.4.html
+ version: '1.4'
+name: Spreadsheet-Read
+optional_features:
+ opt_csv:
+ description: Provides parsing of CSV streams
+ requires:
+ Text::CSV_XS: '0.71'
+ opt_excel:
+ description: Provides parsing of Microsoft Excel files
+ requires:
+ Spreadsheet::ParseExcel: '0.34'
Spreadsheet::ParseExcel::FmtDefault: 0
- recommends:
- Spreadsheet::ParseExcel: 0.59
- opt_excelx:
- description: Provides parsing of Microsoft Excel 2007 files
- requires:
- Spreadsheet::XLSX: 0.13
- Spreadsheet::XLSX::Fmt2007: 0
- opt_oo:
- description: Provides parsing of OpenOffice spreadsheets
- requires:
- Spreadsheet::ReadSXC: 0.20
- opt_tools:
- description: Spreadsheet tools
- recommends:
- Tk: 0
- Tk::NoteBook: 0
- Tk::TableMatrix::Spreadsheet: 0
+ opt_excelx:
+ description: Provides parsing of Microsoft Excel 2007 files
+ requires:
+ Spreadsheet::ParseExcel::FmtDefault: 0
+ Spreadsheet::ParseXLSX: '0.13'
+ opt_oo:
+ description: Provides parsing of OpenOffice spreadsheets
+ requires:
+ Spreadsheet::ReadSXC: '0.20'
+ opt_tools:
+ description: Spreadsheet tools
+provides:
+ Spreadsheet::Read:
+ file: Read.pm
+ version: '0.54'
+recommends:
+ Data::Peek: '0.39'
+ File::Temp: '0.2304'
+ IO::Scalar: 0
+ Test::More: '1.001002'
+ perl: '5.018001'
+requires:
+ Carp: 0
+ Data::Dumper: 0
+ Data::Peek: 0
+ Exporter: 0
+ File::Temp: '0.22'
+ Test::Harness: 0
+ Test::More: '0.88'
+ Test::NoWarnings: 0
+ perl: '5.006'
+resources:
+ license: http://dev.perl.org/licenses/
+ repository: http://repo.or.cz/r/Spreadsheet-Read.git
+version: '0.54'
@@ -6,11 +6,13 @@ use ExtUtils::MakeMaker;
my @exe;
unless (exists $ENV{AUTOMATED_TESTING} and $ENV{AUTOMATED_TESTING} == 1) {
+ -f "examples/xlsgrep" or link "examples/xlscat", "examples/xlsgrep";
for ( [ "xlscat", "Convert Spreadsheet to plain text or CSV" ],
+ [ "xlsgrep", "Grep pattern from Spreadsheet" ],
[ "ss2tk", "Show a Spreadsheet in Perl/Tk" ],
[ "xls2csv", "Wrapper around xlscat for easy XLS => CSV" ],
) {
- prompt ("Do you want to install '$_->[0]' ($_->[1]) ? ", "n") =~ m/[Yy]/ and
+ prompt ("Do you want to install '$_->[0]' ($_->[1]) ? ", "y") =~ m/[Yy]/ and
push @exe, "examples/$_->[0]";
}
}
@@ -31,12 +33,15 @@ my %wm = (
"IO::Scalar" => 0, # Optional
# Backend parsers, all optional
-# "Text::CSV" => 1.15,
-# "Text::CSV_XS" => 0.69,
-# "Text::CSV_PP" => 1.23,
+ # Versions also need to be declared inside Read.pm !
+# "Text::CSV" => 1.32,
+# "Text::CSV_PP" => 1.31,
+# "Text::CSV_XS" => 1.04,
# "Spreadsheet::ReadSXC" => 0.20,
-# "Spreadsheet::ParseExcel" => 0.26,
+# "Spreadsheet::ParseExcel" => 0.34,
# "Spreadsheet::ParseExcel::FmtDefault" => 0,
+# "Spreadsheet::ParseXLSX" => 0.13,
+# "Spreadsheet::XLSX" => 0.13,
# "Spreadsheet::Perl" => 0, # Not yet
# For testing
@@ -44,10 +49,11 @@ my %wm = (
"Test::NoWarnings" => 0,
# for ss2tk
-# "Tk" => 804.027,
+# "Tk" => 804.032,
# "Tk::NoteBook" => 4.009,
# "Tk::TableMatrix::Spreadsheet" => 1.2,
},
+ macro => { TARFLAGS => "--format=ustar -c -v -f", },
);
$ExtUtils::MakeMaker::VERSION > 6.30 and $wm{LICENSE} = "perl";
@@ -59,7 +59,7 @@ H.Merijn Brand, <h.m.brand@xs4all.nl>
=head1 COPYRIGHT AND LICENSE
-Copyright (C) 2005-2012 H.Merijn Brand
+Copyright (C) 2005-2014 H.Merijn Brand
This library is free software; you can redistribute it and/or modify
it under the same terms as Perl itself.
@@ -9,21 +9,23 @@ package Spreadsheet::Read;
=head1 SYNOPSIS
use Spreadsheet::Read;
- my $ref = ReadData ("test.csv", sep => ";");
- my $ref = ReadData ("test.sxc");
- my $ref = ReadData ("test.ods");
- my $ref = ReadData ("test.xls");
- my $ref = ReadData ("test.xlsx");
- my $ref = ReadData ($fh, parser => "xls");
+ my $book = ReadData ("test.csv", sep => ";");
+ my $book = ReadData ("test.sxc");
+ my $book = ReadData ("test.ods");
+ my $book = ReadData ("test.xls");
+ my $book = ReadData ("test.xlsx");
+ my $book = ReadData ($fh, parser => "xls");
- my $a3 = $ref->[1]{A3}, "\n"; # content of field A3 of sheet 1
+ my $sheet = $book->[1]; # first datasheet
+ my $cell = $book->[1]{A3}; # content of field A3 of sheet 1
+ my $cell = $book->[1]{cell}[1][3]; # same, unformatted
=cut
use strict;
use warnings;
-our $VERSION = "0.47";
+our $VERSION = "0.54";
sub Version { $VERSION }
use Carp;
@@ -36,23 +38,25 @@ use File::Temp qw( );
use Data::Dumper;
my @parsers = (
- [ csv => "Text::CSV_XS" ],
- [ csv => "Text::CSV_PP" ], # Version 1.05 and up
- [ csv => "Text::CSV" ], # Version 1.00 and up
- [ ods => "Spreadsheet::ReadSXC" ],
- [ sxc => "Spreadsheet::ReadSXC" ],
- [ xls => "Spreadsheet::ParseExcel" ],
- [ xlsx => "Spreadsheet::XLSX" ],
- [ prl => "Spreadsheet::Perl" ],
+ [ csv => "Text::CSV_XS", "0.71" ],
+ [ csv => "Text::CSV_PP", "1.17" ],
+ [ csv => "Text::CSV", "1.17" ],
+ [ ods => "Spreadsheet::ReadSXC", "0.20" ],
+ [ sxc => "Spreadsheet::ReadSXC", "0.20" ],
+ [ xls => "Spreadsheet::ParseExcel", "0.34" ],
+ [ xlsx => "Spreadsheet::ParseXLSX", "0.13" ],
+ [ xlsx => "Spreadsheet::XLSX", "0.13" ],
+ [ prl => "Spreadsheet::Perl", "" ],
# Helper modules
- [ ios => "IO::Scalar" ],
+ [ ios => "IO::Scalar", "" ],
+ [ dmp => "Data::Peek", "" ],
);
my %can = map { $_->[0] => 0 } @parsers;
for (@parsers) {
- my ($flag, $mod) = @$_;
+ my ($flag, $mod, $vsn) = @$_;
$can{$flag} and next;
- eval "require $mod; \$can{\$flag} = '$mod'";
+ eval "require $mod; $vsn and ${mod}->VERSION ($vsn); \$can{\$flag} = '$mod'";
}
$can{sc} = __PACKAGE__; # SquirelCalc is built-in
@@ -90,6 +94,17 @@ my @def_attr = (
# Helper functions
+sub _dump
+{
+ my ($label, $ref) = @_;
+ if ($can{dmp}) {
+ print STDERR Data::Peek::DDumper ({ $label => $ref });
+ }
+ else {
+ print STDERR Data::Dumper->Dump ([$ref], [$label]);
+ }
+ } # _dump
+
sub _parser
{
my $type = shift or return "";
@@ -100,6 +115,7 @@ sub _parser
$type eq "oo" and return "sxc";
$type eq "ods" and return "sxc";
$type eq "openoffice" and return "sxc";
+ $type eq "libreoffice" and return "sxc";
$type eq "perl" and return "prl";
$type eq "squirelcalc" and return "sc";
return exists $can{$type} ? $type : "";
@@ -139,7 +155,7 @@ sub cell2cr
($c, $r);
} # cell2cr
-# my @row = cellrow ($ss->[1], 1);
+# my @row = cellrow ($book->[1], 1);
sub cellrow
{
my $sheet = shift or return;
@@ -151,7 +167,7 @@ sub cellrow
map { $s->[$_][$row] } 1..$sheet->{maxcol};
} # cellrow
-# my @row = row ($ss->[1], 1);
+# my @row = row ($book->[1], 1);
sub row
{
my $sheet = shift or return;
@@ -163,7 +179,7 @@ sub row
} # row
# Convert {cell}'s [column][row] to a [row][column] list
-# my @rows = rows ($ss->[1]);
+# my @rows = rows ($book->[1]);
sub rows
{
my $sheet = shift or return;
@@ -177,7 +193,7 @@ sub rows
} 1..$sheet->{maxrow};
} # rows
-# If option "clip" is set, remove the trailing lines and
+# If option "clip" is set, remove the trailing rows and
# columns in each sheet that contain no visible data
sub _clipsheets
{
@@ -216,7 +232,7 @@ sub _clipsheets
}
$ss->{maxcol} or $ss->{maxrow} = 0;
- # Remove trailing empty lines
+ # Remove trailing empty rows
while ($ss->{maxrow} and not (
grep { defined && m/\S/ }
map { $ss->{cell}[$_][$ss->{maxrow}] }
@@ -231,16 +247,29 @@ sub _clipsheets
$ref;
} # _clipsheets
-sub _xls_color {
- my ($clr, @clr) = @_;
- defined $clr or return undef;
- @clr == 0 && $clr == 32767 and return undef; # Default fg color
- @clr == 2 && $clr == 0 and return undef; # No fill bg color
- @clr == 2 && $clr == 1 and ($clr, @clr) = ($clr[0]);
- @clr and return undef; # Don't know what to do with this
- "#" . lc Spreadsheet::ParseExcel->ColorIdxToRGB ($clr);
+# Convert a single color (index) to a color
+sub _xls_color
+{
+ my $clr = shift;
+ defined $clr or return undef;
+ $clr eq "#000000" and return undef;
+ $clr =~ m/^#[0-9a-fA-F]+$/ and return lc $clr;
+ $clr == 0 || $clr == 32767 and return undef; # Default fg color
+ return "#" . lc Spreadsheet::ParseExcel->ColorIdxToRGB ($clr);
} # _xls_color
+# Convert a fill [ $pattern, $front_color, $back_color ] to a single background
+sub _xls_fill
+{
+ my ($p, $fg, $bg) = @_;
+ defined $p or return undef;
+ $p == 32767 and return undef; # Default fg color
+ $p == 0 && !defined $bg and return undef; # No fill bg color
+ $p == 1 and return _xls_color ($fg);
+ $bg < 8 || $bg > 63 and return undef; # see Workbook.pm#106
+ return _xls_color ($bg);
+ } # _xls_fill
+
sub ReadData
{
my $txt = shift or return;
@@ -260,7 +289,7 @@ sub ReadData
# $debug = $opt{debug} // 0;
$debug = defined $opt{debug} ? $opt{debug} : $def_opts{debug};
- $debug > 4 and print STDERR Data::Dumper->Dump ([\%opt],["Options"]);
+ $debug > 4 and _dump (Options => \%opt);
my %parser_opts = map { $_ => $opt{$_} }
grep { !exists $def_opts{$_} }
@@ -281,11 +310,11 @@ sub ReadData
$debug and print STDERR "Opening CSV $label\n";
- my $csv;
my @data = (
{ type => "csv",
parser => $can{csv},
version => $can{csv}->VERSION,
+ error => undef,
quote => '"',
sepchar => ',',
sheets => 1,
@@ -328,13 +357,14 @@ sub ReadData
$in = $txt; # Now pray ...
}
$debug > 1 and print STDERR "CSV sep_char '$sep', quote_char '$quo'\n";
- $csv = $can{csv}->new ({
+ my $csv = $can{csv}->new ({
%parser_opts,
sep_char => ($data[0]{sepchar} = $sep),
quote_char => ($data[0]{quote} = $quo),
keep_meta_info => 1,
binary => 1,
+ auto_diag => 1,
}) or croak "Cannot create a csv ('$sep', '$quo') parser!";
while (my $row = $csv->getline ($in)) {
@@ -350,7 +380,7 @@ sub ReadData
$opt{attr} and $data[1]{attr}[$c + 1][$r] = { @def_attr };
}
}
- $csv->eof () or $csv->error_diag;
+ $csv->eof () or $data[0]{error} = [ $csv->error_diag ];
close $in;
for (@{$data[1]{cell}}) {
@@ -388,21 +418,28 @@ sub ReadData
$debug and print STDERR "Opening $parse_type \$txt\n";
if ($io_ref) {
$oBook = $parse_type eq "XLSX"
+ ? $can{xlsx} =~ m/::XLSX/
? Spreadsheet::XLSX->new ($io_ref)
+ : Spreadsheet::ParseXLSX->new (%parser_opts)->parse ($io_ref)
: Spreadsheet::ParseExcel->new (%parser_opts)->Parse ($io_ref);
}
else {
$oBook = $parse_type eq "XLSX"
+ ? $can{xlsx} =~ m/::XLSX/
? Spreadsheet::XLSX->new ($txt)
+ : Spreadsheet::ParseXLSX->new (%parser_opts)->parse ($txt)
: Spreadsheet::ParseExcel->new (%parser_opts)->Parse ($txt);
}
- $debug > 8 and print STDERR Data::Dumper->Dump ([$oBook],["oBook"]);
+ $debug > 8 and _dump (oBook => $oBook);
my @data = ( {
type => lc $parse_type,
parser => $can{lc $parse_type},
version => $parse_type eq "XLSX"
+ ? $can{xlsx} =~ m/::XLSX/
? $Spreadsheet::XLSX::VERSION
+ : $Spreadsheet::ParseXLSX::VERSION
: $Spreadsheet::ParseExcel::VERSION,
+ error => undef,
sheets => $oBook->{SheetCount} || 0,
sheet => {},
} );
@@ -415,7 +452,9 @@ sub ReadData
);
$oBook->{FormatStr}{$_} = $def_fmt{$_} for keys %def_fmt;
my $oFmt = $parse_type eq "XLSX"
+ ? $can{xlsx} =~ m/::XLSX/
? Spreadsheet::XLSX::Fmt2007->new
+ : Spreadsheet::ParseExcel::FmtDefault->new
: Spreadsheet::ParseExcel::FmtDefault->new;
$debug and print STDERR "\t$data[0]{sheets} sheets\n";
@@ -496,8 +535,8 @@ sub ReadData
type => lc $oWkC->{Type},
enc => $oWkC->{Code},
merged => $oWkC->{Merged} || 0,
- hidden => $FmT->{Hidden},
- locked => $FmT->{Lock},
+ hidden => $FmT->{Hidden} || 0,
+ locked => $FmT->{Lock} || 0,
format => $fmi,
halign => [ undef, qw( left center right
fill justify ), undef,
@@ -511,7 +550,7 @@ sub ReadData
italic => $FnT->{Italic},
uline => $FnT->{Underline},
fgcolor => _xls_color ($FnT->{Color}),
- bgcolor => _xls_color (@{$FmT->{Fill}}),
+ bgcolor => _xls_fill (@{$FmT->{Fill}}),
};
}
}
@@ -552,6 +591,7 @@ sub ReadData
{ type => "sc",
parser => "Spreadsheet::Read",
version => $VERSION,
+ error => undef,
sheets => 1,
sheet => { sheet => 1 },
},
@@ -610,12 +650,13 @@ sub ReadData
}
!$sxc && $txt =~ m/^<\?xml/i and
$sxc = Spreadsheet::ReadSXC::read_xml_string ($txt, $sxc_options);
- $debug > 8 and print STDERR Data::Dumper->Dump ([$sxc],["sxc"]);
+ $debug > 8 and _dump (sxc => $sxc);
if ($sxc) {
my @data = ( {
type => "sxc",
parser => "Spreadsheet::ReadSXC",
version => $Spreadsheet::ReadSXC::VERSION,
+ error => undef,
sheets => 0,
sheet => {},
} );
@@ -672,10 +713,10 @@ Spreadsheet::Read tries to transparently read *any* spreadsheet and
return its content in a universal manner independent of the parsing
module that does the actual spreadsheet scanning.
-For OpenOffice this module uses Spreadsheet::ReadSXC
+For OpenOffice and/or LibreOffice this module uses Spreadsheet::ReadSXC
-For Microsoft Excel this module uses Spreadsheet::ParseExcel or
-Spreadsheet::XLSX
+For Microsoft Excel this module uses Spreadsheet::ParseExcel,
+Spreadsheet::ParseXLSX, or Spreadsheet::XLSX.
For CSV this module uses Text::CSV_XS or Text::CSV_PP.
@@ -685,7 +726,7 @@ For SquirrelCalc there is a very simplistic built-in parser
The data is returned as an array reference:
- $ref = [
+ $book = [
# Entry 0 is the overall control hash
{ sheets => 2,
sheet => {
@@ -694,7 +735,8 @@ The data is returned as an array reference:
},
type => "xls",
parser => "Spreadsheet::ParseExcel",
- version => 0.26,
+ version => 0.59,
+ error => undef,
},
# Entry 1 is the first sheet
{ label => "Sheet 1",
@@ -724,27 +766,27 @@ The control hash (the first entry in the returned array ref), contains
some spreadsheet meta-data. The entry C<sheet> is there to be able to find
the sheets when accessing them by name:
- my %sheet2 = %{$ref->[$ref->[0]{sheet}{"Sheet 2"}]};
+ my %sheet2 = %{$book->[$book->[0]{sheet}{"Sheet 2"}]};
=head2 Functions
=over 2
-=item my $ref = ReadData ($source [, option => value [, ... ]]);
+=item my $book = ReadData ($source [, option => value [, ... ]]);
-=item my $ref = ReadData ("file.csv", sep => ',', quote => '"');
+=item my $book = ReadData ("file.csv", sep => ',', quote => '"');
-=item my $ref = ReadData ("file.xls", dtfmt => "yyyy-mm-dd");
+=item my $book = ReadData ("file.xls", dtfmt => "yyyy-mm-dd");
-=item my $ref = ReadData ("file.ods");
+=item my $book = ReadData ("file.ods");
-=item my $ref = ReadData ("file.sxc");
+=item my $book = ReadData ("file.sxc");
-=item my $ref = ReadData ("content.xml");
+=item my $book = ReadData ("content.xml");
-=item my $ref = ReadData ($content);
+=item my $book = ReadData ($content);
-=item my $ref = ReadData ($fh, parser => "xls");
+=item my $book = ReadData ($fh, parser => "xls");
Tries to convert the given file, string, or stream to the data
structure described above.
@@ -762,7 +804,8 @@ Currently supported options are:
Force the data to be parsed by a specific format. Possible values are
C<csv>, C<prl> (or C<perl>), C<sc> (or C<squirelcalc>), C<sxc> (or C<oo>,
-C<ods>, C<openoffice>) C<xls> (or C<excel>), and C<xlsx> (or C<excel2007>).
+C<ods>, C<openoffice>, C<libreoffice>) C<xls> (or C<excel>), and C<xlsx>
+(or C<excel2007>).
When parsing streams, instead of files, it is highly recommended to pass
this option.
@@ -782,7 +825,7 @@ See L<Cell Attributes> below.
=item clip
-If set, C<ReadData ()> will remove all trailing lines and columns per
+If set, C<ReadData ()> will remove all trailing rows and columns per
sheet that have no visual data. If a sheet has no data at all, the
sheet will be skipped entirely when this attribute is true.
@@ -826,7 +869,7 @@ happened to be at the time the user saved the file.
Enable some diagnostic messages to STDERR.
-The value determines how much diagnostics are dumped (using Data::Dumper).
+The value determines how much diagnostics are dumped (using Data::Peek).
A value of 9 and higher will dump the entire structure from the back-end
parser.
@@ -847,6 +890,13 @@ Text::CSV_PP) is able to automatically detect and use C<\r> line endings).
CSV can parse streams too, but be sure to pass C<sep> and/or C<quote> if
these do not match the default C<,> and C<">.
+When an error is found in the CSV, it is automatically reported (to STDERR).
+The structure will store the error in C<< $ss->[0]{error} >> as anonymous
+list returned by C<< $csv->error_diag >>. See Text::CSV_XS for documentation.
+
+ my $ss = ReadData ("bad.csv");
+ $ss->[0]{error} and say $ss->[0]{error}[1];
+
=head2 Functions
=over 4
@@ -867,31 +917,31 @@ pair (1 based):
my ($col, $row) = cell2cr ("D14"); # returns ( 4, 14)
my ($col, $row) = cell2cr ("AB4"); # returns (28, 4)
-=item my @row = row ($ref, $row)
+=item my @row = row ($sheet, $row)
-=item my @row = Spreadsheet::Read::row ($ss->[1], 3)
+=item my @row = Spreadsheet::Read::row ($book->[1], 3)
-Get full row of formatted values (like C<< $ss->{A3} .. $ss->{G3} >>)
+Get full row of formatted values (like C<< $sheet->{A3} .. $sheet->{G3} >>)
Note that the indexes in the returned list are 0-based.
C<row ()> is not imported by default, so either specify it in the
use argument list, or call it fully qualified.
-=item my @row = cellrow ($ref, $row)
+=item my @row = cellrow ($book, $row)
-=item my @row = Spreadsheet::Read::cellrow ($ss->[1], 3)
+=item my @row = Spreadsheet::Read::cellrow ($book->[1], 3)
-Get full row of unformatted values (like C<< $ss->{cell}[1][3] .. $ss->{cell}[7][3] >>)
+Get full row of unformatted values (like C<< $sheet->{cell}[1][3] .. $sheet->{cell}[7][3] >>)
Note that the indexes in the returned list are 0-based.
C<cellrow ()> is not imported by default, so either specify it in the
use argument list, or call it fully qualified.
-=item my @rows = rows ($ref)
+=item my @rows = rows ($book)
-=item my @rows = Spreadsheet::Read::rows ($ss->[1])
+=item my @rows = Spreadsheet::Read::rows ($book->[1])
Convert C<{cell}>'s C<[column][row]> to a C<[row][column]> list.
@@ -978,6 +1028,109 @@ is made to analyze and store field attributes like this:
This has now been partially implemented, mainly for Excel, as the other
parsers do not (yet) support all of that. YMMV.
+=head1 TOOLS
+
+This modules comes with a few tools that perform tasks from the FAQ, like
+"How do I select only column D through F from sheet 2 into a CSV file?"
+
+If the module was installed without the tools, you can find them here:
+ http://repo.or.cz/w/Spreadsheet-Read.git/tree/HEAD:/examples
+
+=head2 C<xlscat>
+
+Show (parts of) a spreadsheet in plain text, CSV, or HTML
+
+ usage: xlscat [-s <sep>] [-L] [-n] [-A] [-u] [Selection] file.xls
+ [-c | -m] [-u] [Selection] file.xls
+ -i [-S sheets] file.xls
+ Generic options:
+ -v[#] Set verbose level (xlscat/xlsgrep)
+ -d[#] Set debug level (Spreadsheet::Read)
+ -u Use unformatted values
+ --noclip Do not strip empty sheets and
+ trailing empty rows and columns
+ -e <enc> Set encoding for input and output
+ -b <enc> Set encoding for input
+ -a <enc> Set encoding for output
+ Input CSV:
+ --in-sep=c Set input sep_char for CSV
+ Input XLS:
+ --dtfmt=fmt Specify the default date format to replace 'm-d-yy'
+ the default replacement is 'yyyy-mm-dd'
+ Output Text (default):
+ -s <sep> Use separator <sep>. Default '|', \n allowed
+ -L Line up the columns
+ -n Number lines (prefix with column number)
+ -A Show field attributes in ANSI escapes
+ Output Index only:
+ -i Show sheet names and size only
+ Output CSV:
+ -c Output CSV, separator = ','
+ -m Output CSV, separator = ';'
+ Output HTML:
+ -H Output HTML
+ Selection:
+ -S <sheets> Only print sheets <sheets>. 'all' is a valid set
+ Default only prints the first sheet
+ -R <rows> Only print rows <rows>. Default is 'all'
+ -C <cols> Only print columns <cols>. Default is 'all'
+ -F <flds> Only fields <flds> e.g. -FA3,B16
+
+=head2 C<xlsgrep>
+
+Show (parts of) a spreadsheet that match a pattern in plain text, CSV, or HTML
+
+ usage: xlsgrep [-s <sep>] [-L] [-n] [-A] [-u] [Selection] pattern file.xls
+ [-c | -m] [-u] [Selection] pattern file.xls
+ -i [-S sheets] pattern file.xls
+ Generic options:
+ -v[#] Set verbose level (xlscat/xlsgrep)
+ -d[#] Set debug level (Spreadsheet::Read)
+ -u Use unformatted values
+ --noclip Do not strip empty sheets and
+ trailing empty rows and columns
+ -e <enc> Set encoding for input and output
+ -b <enc> Set encoding for input
+ -a <enc> Set encoding for output
+ Input CSV:
+ --in-sep=c Set input sep_char for CSV
+ Input XLS:
+ --dtfmt=fmt Specify the default date format to replace 'm-d-yy'
+ the default replacement is 'yyyy-mm-dd'
+ Output Text (default):
+ -s <sep> Use separator <sep>. Default '|', \n allowed
+ -L Line up the columns
+ -n Number lines (prefix with column number)
+ -A Show field attributes in ANSI escapes
+ Grep options:
+ -i Ignore case
+ -w Match whole words only
+ -h[#] Show # header lines
+ Output CSV:
+ -c Output CSV, separator = ','
+ -m Output CSV, separator = ';'
+ Output HTML:
+ -H Output HTML
+ Selection:
+ -S <sheets> Only print sheets <sheets>. 'all' is a valid set
+ Default only prints the first sheet
+ -R <rows> Only print rows <rows>. Default is 'all'
+ -C <cols> Only print columns <cols>. Default is 'all'
+ -F <flds> Only fields <flds> e.g. -FA3,B16
+
+=head2 C<ss2tk>
+
+Show a spreadsheet in a perl/Tk spreadsheet widget
+
+ usage: ss2tk [-w <width>] [X11 options] file.xls [<pattern>]
+ -w <width> use <width> as default column width (4)
+
+=head2 C<xls2csv>
+
+Convert a spreadsheet to CSV. This is just a small wrapper over C<xlscat>.
+
+ usage: xls2csv [ -o file.csv ] file.xls
+
=head1 TODO
=over 4
@@ -1019,45 +1172,49 @@ OO interface.
=item Text::CSV_XS, Text::CSV_PP
-http://search.cpan.org/dist/Text-CSV_XS ,
-http://search.cpan.org/dist/Text-CSV_PP , and
-http://search.cpan.org/dist/Text-CSV .
+http://metacpan.org/release/Text-CSV_XS ,
+http://metacpan.org/release/Text-CSV_PP , and
+http://metacpan.org/release/Text-CSV .
Text::CSV is a wrapper over Text::CSV_XS (the fast XS version) and/or
Text::CSV_PP (the pure perl version)
=item Spreadsheet::ParseExcel
-http://search.cpan.org/dist/Spreadsheet-ParseExcel
+http://metacpan.org/release/Spreadsheet-ParseExcel
+
+=item Spreadsheet::ParseXLSX
+
+http://metacpan.org/release/Spreadsheet-ParseXLSX
=item Spreadsheet::XLSX
-http://search.cpan.org/dist/Spreadsheet-XLSX
+http://metacpan.org/release/Spreadsheet-XLSX
=item Spreadsheet::ReadSXC
-http://search.cpan.org/dist/Spreadsheet-ReadSXC
+http://metacpan.org/release/Spreadsheet-ReadSXC
=item Spreadsheet::BasicRead
-http://search.cpan.org/dist/Spreadsheet-BasicRead
+http://metacpan.org/release/Spreadsheet-BasicRead
for xlscat likewise functionality (Excel only)
=item Spreadsheet::ConvertAA
-http://search.cpan.org/dist/Spreadsheet-ConvertAA
+http://metacpan.org/release/Spreadsheet-ConvertAA
for an alternative set of cell2cr () / cr2cell () pair
=item Spreadsheet::Perl
-http://search.cpan.org/dist/Spreadsheet-Perl
+http://metacpan.org/release/Spreadsheet-Perl
offers a Pure Perl implementation of a spreadsheet engine. Users that want
this format to be supported in Spreadsheet::Read are hereby motivated to
offer patches. It's not high on my TODO-list.
=item xls2csv
-http://search.cpan.org/dist/xls2csv offers an alternative for my C<xlscat -c>,
+http://metacpan.org/release/xls2csv offers an alternative for my C<xlscat -c>,
in the xls2csv tool, but this tool focuses on character encoding
transparency, and requires some other modules.
@@ -1069,7 +1226,7 @@ H.Merijn Brand, <h.m.brand@xs4all.nl>
=head1 COPYRIGHT AND LICENSE
-Copyright (C) 2005-2012 H.Merijn Brand
+Copyright (C) 2005-2014 H.Merijn Brand
This library is free software; you can redistribute it and/or modify
it under the same terms as Perl itself.
@@ -1,7 +1,7 @@
#!/pro/bin/perl
# ss-dup-tk.pl: Find dups in spreadsheet
-# (m)'09 [23-01-2009] Copyright H.M.Brand 2005-2012
+# (m)'09 [23-01-2009] Copyright H.M.Brand 2005-2014
use strict;
use warnings;
@@ -1,7 +1,7 @@
#!/pro/bin/perl
# ss2tk: show SpreadSheet file in Tk::TableMatrix::Spreadsheet (*)
-# (m)'07 [26-06-2007] Copyright H.M.Brand 2005-2012
+# (m)'07 [26-06-2007] Copyright H.M.Brand 2005-2014
use strict;
use warnings;
@@ -1,7 +1,7 @@
#!/pro/bin/perl
# xls2csv: Convert Microsoft Excel spreadsheet to CSV
-# (m)'08 [10-03-2008] Copyright H.M.Brand 2008-2012
+# (m)'13 [24-10-2013] Copyright H.M.Brand 2008-2014
use strict;
use warnings;
@@ -18,7 +18,7 @@ use Getopt::Long qw( :config bundling nopermute passthrough );
my $csv;
my $opt_f;
GetOptions (
- "help|?" => sub { usage 1; },
+ "help|?" => sub { usage 0; },
"o|c=s" => \$csv,
"f" => \$opt_f,
) or usage 1;
@@ -1,29 +1,34 @@
#!/pro/bin/perl
-# xls2cat: show XLS/SXC file as Text
-# (m)'11 [03-02-2011] Copyright H.M.Brand 2005-2012
+# xlscat: show XLS/SXC file as Text
+# xlsgrep: grep pattern
+# (m)'13 [24-10-2013] Copyright H.M.Brand 2005-2014
use strict;
use warnings;
-our $VERSION = "2.1";
+our $VERSION = "2.5";
+
+my $is_grep = $0 =~ m/grep$/;
sub usage
{
my $err = shift and select STDERR;
+ (my $scrpt = $0) =~ s{.*[\/]}{};
+ my $p = $is_grep ? " pattern" : "";
print
- "usage: xlscat [-s <sep>] [-L] [-n] [-A] [-u] [ Selection ] file.xls\n",
- " [-c | -m] [-u] [ Selection ] file.xls\n",
- " -i [ -S sheets ] file.xls\n",
+ "usage: $scrpt\t[-s <sep>] [-L] [-n] [-A] [-u] [Selection]$p file.xls\n",
+ " \t[-c | -m] [-u] [Selection]$p file.xls\n",
+ " \t -i [-S sheets]$p file.xls\n",
" Generic options:\n",
- " -v[#] Set verbose level (xlscat)\n",
+ " -v[#] Set verbose level (xlscat/xlsgrep)\n",
" -d[#] Set debug level (Spreadsheet::Read)\n",
" -u Use unformatted values\n",
" --noclip Do not strip empty sheets and\n",
" trailing empty rows and columns\n",
- " -e <enc> Set encoding for input and output\n",
- " -b <enc> Set encoding for input\n",
- " -a <enc> Set encoding for output\n",
+ " -e <enc> Set encoding for input and output\n",
+ " -b <enc> Set encoding for input\n",
+ " -a <enc> Set encoding for output\n",
" Input CSV:\n",
" --in-sep=c Set input sep_char for CSV\n",
" Input XLS:\n",
@@ -34,8 +39,13 @@ sub usage
" -L Line up the columns\n",
" -n Number lines (prefix with column number)\n",
" -A Show field attributes in ANSI escapes\n",
+ $is_grep ? (
+ " Grep options:\n",
+ " -i Ignore case\n",
+ " -w Match whole words only\n",
+ " -h[#] Show # header lines\n") : (
" Output Index only:\n",
- " -i Show sheet names and size only\n",
+ " -i Show sheet names and size only\n"),
" Output CSV:\n",
" -c Output CSV, separator = ','\n",
" -m Output CSV, separator = ';'\n",
@@ -51,7 +61,7 @@ sub usage
exit $err;
} # usage
-use Getopt::Long qw(:config bundling nopermute noignorecase);
+use Getopt::Long qw(:config bundling noignorecase);
my $opt_c; # Generate CSV
my $opt_s; # Text separator
my $opt_S; # Sheets to print
@@ -59,7 +69,7 @@ my $opt_R; # Rows to print
my $opt_C; # Columns to print
my $dtfmt; # Default date-format for Excel
my $opt_F = ""; # Fields to print
-my $opt_i = 0; # Index
+my $opt_i = 0; # Index (cat) | ignore_case (grep)
my $opt_L = 0; # Auto-size/align columns
my $opt_n = 0; # Prefix lines with column number
my $opt_u = 0; # Show unformatted values
@@ -67,6 +77,8 @@ my $opt_v = 0; # Verbosity for xlscat
my $opt_d = 0; # Debug level for Spreadsheet::Read
my $opt_A = 0; # Show field colors in ANSI escapes
my $opt_H = 0; # Output in HTML
+my $opt_h = 0; # Number of header lines for grep
+my $opt_w = 0; # Grep words
my $clip = 1;
my $enc_i; # Input encoding
my $enc_o; # Output encoding
@@ -95,21 +107,27 @@ GetOptions (
"R|rows=s" => \$opt_R,
"C|columns=s" => \$opt_C,
"F|fields=s" => \$opt_F,
- "L|fit|align" => \$opt_L,
- "n|number" => \$opt_n,
- "A|ansi" => \$opt_A,
- "u|unformatted" => \$opt_u,
+ "L|fit|align!" => \$opt_L,
+ "n|number!" => \$opt_n,
+ "A|ansi|color!" => \$opt_A,
+ "u|unformatted!" => \$opt_u,
"v|verbose:1" => \$opt_v,
"d|debug:1" => \$opt_d,
"H|html:1" => \$opt_H,
- "noclip" => sub { $clip = 0 },
+ "noclip" => sub { $clip = 0 },
# Encoding
"e|encoding=s" => sub { $enc_i = $enc_o = $_[1] },
"b|encoding-in=s" => \$enc_i,
"a|encoding-out=s" => \$enc_o,
+
+ # Grep
+ "i|ignore-case!" => \$opt_i,
+ "w|word!" => \$opt_w,
+ "h|header:1" => \$opt_h,
) or usage 1, "GetOpt: $@";
+unless ($is_grep) {
$opt_i && $opt_L and usage 1, "Options i and L are mutually exclusive";
$opt_i && $opt_s and usage 1, "Options i and s are mutually exclusive";
$opt_i && $opt_c and usage 1, "Options i and c are mutually exclusive";
@@ -119,13 +137,14 @@ $opt_i && $opt_R and usage 1, "Options i and R are mutually exclusive";
$opt_i && $opt_C and usage 1, "Options i and C are mutually exclusive";
$opt_i && $opt_F and usage 1, "Options i and F are mutually exclusive";
$opt_i && $opt_H and usage 1, "Options i and H are mutually exclusive";
+}
$opt_c && $opt_s and usage 1, "Options c and s are mutually exclusive";
$opt_c && $opt_H and usage 1, "Options c and H are mutually exclusive";
$opt_s && $opt_H and usage 1, "Options s and H are mutually exclusive";
defined $opt_s or $opt_s = "|"; eval "\$opt_s = qq{$opt_s}";
-defined $opt_S or $opt_S = $opt_i ? "all" : "1";
-$opt_i && $opt_v < 1 and $opt_v = 1;
+defined $opt_S or $opt_S = $opt_i || $is_grep ? "all" : "1";
+$opt_i && !$is_grep && $opt_v < 1 and $opt_v = 1;
if ($opt_c) {
$opt_L = 0; # Cannot align CSV
@@ -151,9 +170,18 @@ if ($opt_c) {
} # ddumper
}
+my $pattern;
+if ($is_grep) {
+ $pattern = shift or usage 1;
+ $opt_w and $pattern = "\\b$pattern\\b";
+ $opt_i and $pattern = "(?i:$pattern)";
+ $pattern = qr{$pattern};
+ $opt_v > 1 and warn "Matching on $pattern\n";
+ }
+
@ARGV or usage 1;
my $file = shift;
--f $file or usage 1, "the first argument is not a regular file";
+-f $file or usage 1, "the file argument is not a regular file";
-s $file or usage 1, "the file is empty";
use Encode qw( encode decode );
@@ -178,11 +206,11 @@ my @RDarg = (debug => $opt_d, clip => $clip);
$opt_A and push @RDarg, "attr" => 1;
defined $sep and push @RDarg, "sep" => $sep, parser => "csv";
defined $dtfmt and push @RDarg, "dtfmt" => $dtfmt;
-$opt_v > 4 and print STDERR "ReadData ($file, @RDarg);\n";
+$opt_v > 4 and warn "ReadData ($file, @RDarg);\n";
my $xls = ReadData ($file, @RDarg) or die "cannot read $file\n";
$opt_v > 7 and ddumper ($xls);
my $sc = $xls->[0]{sheets} or die "No sheets in $file\n";
-$opt_v > 1 and print STDERR "Opened $file with $sc sheets\n";
+$opt_v > 1 and warn "Opened $file with $sc sheets\n";
$opt_S eq "all" and $opt_S = "1..$sc"; # all
$opt_S =~ s/-$/-$sc/; # 3,6-
@@ -214,7 +242,7 @@ sub ansi_color
{
my ($fg, $bg, $bold, $ul) = @_;
- #print STDERR "$fg on $bg $bold $ul\n";
+ # warn "$fg on $bg $bold $ul\n";
my $attr = join ";", 0, grep { /\S/ }
$bold ? 1 : "",
$ul ? 4 : "",
@@ -283,7 +311,7 @@ my @opt_F = split m/[^A-Z\d]+/ => $opt_F;
foreach my $si (1 .. $sc) {
my @data;
exists $print{sheet}{$si} or next;
- $opt_v > 1 and print STDERR "Opening sheet $si ...\n";
+ $opt_v > 1 and warn "Opening sheet $si ...\n";
my $s = $xls->[$si] or next;
$opt_v > 5 and ddumper ($s);
my @r = (1, $s->{maxrow});
@@ -291,10 +319,11 @@ foreach my $si (1 .. $sc) {
my ($sn, $nr, $nc) = ($s->{label}, $r[-1], $c[-1]);
$opt_v and printf STDERR "%s - %02d: [ %-*s ] %3d Cols, %5d Rows\n",
$file, $si, $name_len, $sn, $nc, $nr;
- $opt_i and next;
+ $opt_i && !$is_grep and next;
if (@opt_F) {
foreach my $fld (@opt_F) {
+ $is_grep && defined $s->{$fld} && $s->{$fld} !~ $pattern and next;
print "$fld:",$s->{$fld},"\n";
}
next;
@@ -308,6 +337,13 @@ foreach my $si (1 .. $sc) {
}
if (my $cols = $opt_C) {
$cols eq "all" and $cols = "1..$nc"; # all
+ if ($cols =~ m/[A-Za-z]/) { # -C B,D => -C 2,4
+ my %ct = map {
+ my ($cc, $rr) = cell2cr (uc "$_".1);
+ ($_ => $cc)
+ } ($cols =~ m/([a-zA-Z]+)/g);
+ $cols =~ s/([A-Za-z]+)/$ct{$1}/g;
+ }
$cols =~ s/-$/-$nc/; # 3,6-
$cols =~ s/-/../g;
eval "\$print{col} = [ map { \$_ - 1 } $cols ]";
@@ -327,12 +363,14 @@ foreach my $si (1 .. $sc) {
my ($uval, $fval) = map {
defined $_ ? $enc_i ? decode ($enc_i, $_) : $_ : $undef
} $s->{cell}[$_][$r], $s->{$cell};
- $opt_v > 2 and print STDERR "$_:$r '$uval' / '$fval'\n";
+ $opt_v > 2 and warn "$_:$r '$uval' / '$fval'\n";
$opt_A and
push @att, [ @{$s->{attr}[$_][$r]}{qw( fgcolor bgcolor bold uline halign )} ];
defined $s->{cell}[$_][$r] ? $opt_u ? $uval : $fval : "";
} $c[0] .. $c[1];
exists $print{col} and @row = @row[@{$print{col}}];
+ $is_grep && $r > $opt_h &&
+ ! grep { defined $_ && $_ =~ $pattern } @row and next;
if ($opt_L) {
foreach my $c (0 .. $#row) {
my $l = length $row[$c];
@@ -374,7 +412,7 @@ foreach my $si (1 .. $sc) {
}
print join ($opt_s => @row), "\n";
} continue {
- ++$h % 100 or printf STDERR $v_fmt, $nc, $h, "\r";
+ ++$h % 100 == 0 && $opt_v and printf STDERR $v_fmt, $nc, $h, "\r";
}
$opt_H and print " </table>\n\n";
printf STDERR $v_fmt, $nc, $h, "\n";
@@ -89,10 +89,13 @@ for (undef, "", " ", 0, 1, [], {}) {
my $arg = defined $_ ? $_ : "-- undef --";
is ($ref, undef, "Illegal ReadData ([ $arg ])");
}
-for (undef, "", " ", 0, 1, [], {}) {
- my $ref = ReadData ("/dev/null", separator => $_);
- my $arg = defined $_ ? $_ : "-- undef --";
- is ($ref, undef, "Illegal ReadData ({ $arg })");
+SKIP: {
+ -c "/dev/null" or skip "/dev/null cannot be used for tests", 7;
+ for (undef, "", " ", 0, 1, [], {}) {
+ my $ref = ReadData ("/dev/null", separator => $_);
+ my $arg = defined $_ ? $_ : "-- undef --";
+ is ($ref, undef, "Illegal ReadData ({ $arg })");
+ }
}
for (undef, "", " ", 0, 1, [], {}) {
my $ref;
@@ -3,7 +3,7 @@
use strict;
use warnings;
-my $tests = 69;
+my $tests = 103;
use Test::More;
require Test::NoWarnings;
@@ -14,10 +14,40 @@ Spreadsheet::Read::parses ("xls") or
BEGIN { delete @ENV{qw( LANG LC_ALL LC_DATE )}; }
my $xls;
-ok ($xls = ReadData ("files/Dates.xls", attr => 1, dtfmt => "yyyy-mm-dd"), "Excel Date testcase");
+ok ($xls = ReadData ("files/Dates.xls",
+ attr => 1, dtfmt => "yyyy-mm-dd"), "Excel Date testcase");
-my $ss = $xls->[1];
-my $attr = $ss->{attr};
+my %fmt = (
+ A1 => [ "8-Aug", undef ],
+ A2 => [ "12-Aug", undef ],
+ A3 => [ "8-Dec", undef ],
+ A4 => [ "13-Aug", undef ],
+ A6 => [ "Short: dd-MM-yyyy", undef ],
+ A7 => [ "2008-08-13", "yyyy-mm-dd" ],
+ B1 => [ 20080808, "yyyymmdd" ],
+ B2 => [ 20080812, "yyyymmdd" ],
+ B3 => [ 20081208, "yyyymmdd" ],
+ B4 => [ 20080813, "yyyymmdd" ],
+ B6 => [ "Long: ddd, dd MMM yyyy", undef ],
+ B7 => [ "Wed, 13 Aug 2008", "ddd, dd mmm yyyy" ],
+ C1 => [ "2008-08-08", "yyyy-mm-dd" ],
+ C2 => [ "2008-08-12", "yyyy-mm-dd" ],
+ C3 => [ "2008-12-08", "yyyy-mm-dd" ],
+ C4 => [ "2008-08-13", "yyyy-mm-dd" ],
+ C6 => [ "Default format 0x0E", undef ],
+ C7 => [ "8/13/08", "m/d/yy" ],
+ D1 => [ "08/08/2008", "mm/dd/yyyy" ],
+ D2 => [ "08/12/2008", "mm/dd/yyyy" ],
+ D3 => [ "12/08/2008", "mm/dd/yyyy" ],
+ D4 => [ "08/13/2008", "mm/dd/yyyy" ],
+ E1 => [ "08 Aug 2008", undef ],
+ E2 => [ "12 Aug 2008", undef ],
+ E3 => [ "08 Dec 2008", undef ],
+ E4 => [ "13 Aug 2008", undef ],
+ );
+
+ok (my $ss = $xls->[1], "sheet");
+ok (my $attr = $ss->{attr}, "attr");
my @date = (undef, 39668, 39672, 39790, 39673);
my @fmt = (undef, undef, "yyyymmdd", "yyyy-mm-dd", "mm/dd/yyyy");
@@ -28,42 +58,15 @@ foreach my $r (1 .. 4) {
is ($attr->[$_][$r]{format}, $fmt[$_], "Date format row $r col $_") for 1 .. 4;
}
-is ($ss->{A1}, "8-Aug", "Cell content A1");
-is ($ss->{A2}, "12-Aug", "Cell content A2");
-is ($ss->{A3}, "8-Dec", "Cell content A3");
-is ($ss->{A4}, "13-Aug", "Cell content A4");
-
-is ($ss->{B1}, 20080808, "Cell content B1");
-is ($ss->{B2}, 20080812, "Cell content B2");
-is ($ss->{B3}, 20081208, "Cell content B3");
-is ($ss->{B4}, 20080813, "Cell content B4");
-
-is ($ss->{C1}, "2008-08-08", "Cell content C1");
-is ($ss->{C2}, "2008-08-12", "Cell content C2");
-is ($ss->{C3}, "2008-12-08", "Cell content C3");
-is ($ss->{C4}, "2008-08-13", "Cell content C4");
-
-is ($ss->{D1}, "08/08/2008", "Cell content D1");
-is ($ss->{D2}, "08/12/2008", "Cell content D2");
-is ($ss->{D3}, "12/08/2008", "Cell content D3");
-is ($ss->{D4}, "08/13/2008", "Cell content D4");
-
-is ($ss->{E1}, "08 Aug 2008", "Cell content E1");
-is ($ss->{E2}, "12 Aug 2008", "Cell content E2");
-is ($ss->{E3}, "08 Dec 2008", "Cell content E3");
-is ($ss->{E4}, "13 Aug 2008", "Cell content E4");
-
-# Below can only be checked when SS::PE 0.34 is out
-#use DDumper;
-#foreach my $r (1..4,6..7) {
-# foreach my $c (1..5) {
-# my $cell = cr2cell ($c, $r);
-# my $fmt = $ss->{attr}[$c][$r]{format};
-# defined $ss->{$cell} or next;
-# printf STDERR "# attr %s: %-22s %s\n",
-# $cell, $ss->{$cell}, defined $fmt ? "'$fmt'" : "<undef>";
-# }
-# }
+foreach my $r (1..4,6..7) {
+ foreach my $c (1..5) {
+ my $cell = cr2cell ($c, $r);
+ my $fmt = $ss->{attr}[$c][$r]{format};
+ defined $ss->{$cell} or next;
+ is ($ss->{$cell}, $fmt{$cell}[0], "$cell content");
+ is ($fmt, $fmt{$cell}[1], "$cell format");
+ }
+ }
unless ($ENV{AUTOMATED_TESTING}) {
Test::NoWarnings::had_no_warnings ();
@@ -14,16 +14,22 @@ Spreadsheet::Read::parses ("xlsx") or
my $xls;
ok ($xls = ReadData ("files/attr.xlsx", attr => 1), "Excel Attributes testcase");
+my $parser = $xls->[0]{parser};
+
SKIP: {
ok (my $fmt = $xls->[$xls->[0]{sheet}{Format}], "format");
$fmt->{attr}[2][2]{merged} or
- skip "$xls->[0]{parser} $xls->[0]{version} does not reliably support attributes yet", 38;
+ skip "$parser $xls->[0]{version} does not reliably support attributes yet", 38;
+
+ # The return value for the invisible part of merged cells differs for
+ # the available parsers
+ my $mcrv = $parser =~ m/::XLSX/ ? undef : "";
is ($fmt->{B2}, "merged", "Merged cell left formatted");
- is ($fmt->{C2}, undef, "Merged cell right formatted");
+ is ($fmt->{C2}, $mcrv, "Merged cell right formatted");
is ($fmt->{cell}[2][2], "merged", "Merged cell left unformatted");
- is ($fmt->{cell}[3][2], undef, "Merged cell right unformatted");
+ is ($fmt->{cell}[3][2], $mcrv, "Merged cell right unformatted");
is ($fmt->{attr}[2][2]{merged}, 1, "Merged cell left merged");
is ($fmt->{attr}[3][2]{merged}, 1, "Merged cell right merged");
@@ -3,7 +3,7 @@
use strict;
use warnings;
-my $tests = 71;
+my $tests = 103;
use Test::More;
require Test::NoWarnings;
@@ -14,48 +14,63 @@ Spreadsheet::Read::parses ("xlsx") or
BEGIN { delete @ENV{qw( LANG LC_ALL LC_DATE )}; }
my $xls;
-ok ($xls = ReadData ("files/Dates.xlsx", attr => 1, dtfmt => "yyyy-mm-dd"), "Excel Date testcase");
+ok ($xls = ReadData ("files/Dates.xlsx",
+ attr => 1, dtfmt => "yyyy-mm-dd"), "Excel Date testcase");
+
+my %fmt = (
+ A1 => [ "8-Aug", "d-mmm" ],
+ A2 => [ "12-Aug", "d-mmm" ],
+ A3 => [ "8-Dec", "d-mmm" ],
+ A4 => [ "13-Aug", "d-mmm" ],
+ A6 => [ "Short: dd-MM-yyyy", undef ],
+ A7 => [ "2008-08-13", "yyyy-mm-dd" ],
+ B1 => [ 20080808, "yyyymmdd" ],
+ B2 => [ 20080812, "yyyymmdd" ],
+ B3 => [ 20081208, "yyyymmdd" ],
+ B4 => [ 20080813, "yyyymmdd" ],
+ B6 => [ "Long: ddd, dd MMM yyyy", undef ],
+ B7 => [ "Wed, 13 Aug 2008", "ddd, dd mmm yyyy" ],
+ C1 => [ "2008-08-08", "yyyy-mm-dd" ],
+ C2 => [ "2008-08-12", "yyyy-mm-dd" ],
+ C3 => [ "2008-12-08", "yyyy-mm-dd" ],
+ C4 => [ "2008-08-13", "yyyy-mm-dd" ],
+ C6 => [ "Default format 0x0E", undef ],
+ C7 => [ "8/13/08", "m/d/yy" ],
+ D1 => [ "08/08/2008", "mm/dd/yyyy" ],
+ D2 => [ "08/12/2008", "mm/dd/yyyy" ],
+ D3 => [ "12/08/2008", "mm/dd/yyyy" ],
+ D4 => [ "08/13/2008", "mm/dd/yyyy" ],
+ E1 => [ "08 Aug 2008", undef ],
+ E2 => [ "12 Aug 2008", undef ],
+ E3 => [ "08 Dec 2008", undef ],
+ E4 => [ "13 Aug 2008", undef ],
+ );
SKIP: {
ok (my $ss = $xls->[1], "sheet");
ok (my $attr = $ss->{attr}, "attr");
defined $attr->[2][1]{format} or
- skip "$xls->[0]{parser} $xls->[0]{version} does not reliably support formats", 68;
+ skip "$xls->[0]{parser} $xls->[0]{version} does not reliably support formats", 100;
- my @date = (undef, 39668, 39672, 39790, 39673);
- my @fmt = (undef, undef, "yyyymmdd", "yyyy-mm-dd", "mm/dd/yyyy");
+ my @date = (undef, 39668, 39672, 39790, 39673);
+ my @fmt = (undef, "d-mmm", "yyyymmdd", "yyyy-mm-dd", "mm/dd/yyyy");
foreach my $r (1 .. 4) {
- is ($ss->{cell}[$_][$r], $date[$r], "Date value row $r col $_") for 1 .. 4;
+ is ($ss->{cell}[$_][$r], $date[$r], "Date value row $r col $_") for 1 .. 4;
is ($attr->[$_][$r]{type}, "date", "Date type row $r col $_") for 1 .. 4;
is ($attr->[$_][$r]{format}, $fmt[$_], "Date format row $r col $_") for 1 .. 4;
}
- is ($ss->{A1}, "8-Aug", "Cell content A1");
- is ($ss->{A2}, "12-Aug", "Cell content A2");
- is ($ss->{A3}, "8-Dec", "Cell content A3");
- is ($ss->{A4}, "13-Aug", "Cell content A4");
-
- is ($ss->{B1}, 20080808, "Cell content B1");
- is ($ss->{B2}, 20080812, "Cell content B2");
- is ($ss->{B3}, 20081208, "Cell content B3");
- is ($ss->{B4}, 20080813, "Cell content B4");
-
- is ($ss->{C1}, "2008-08-08", "Cell content C1");
- is ($ss->{C2}, "2008-08-12", "Cell content C2");
- is ($ss->{C3}, "2008-12-08", "Cell content C3");
- is ($ss->{C4}, "2008-08-13", "Cell content C4");
-
- is ($ss->{D1}, "08/08/2008", "Cell content D1");
- is ($ss->{D2}, "08/12/2008", "Cell content D2");
- is ($ss->{D3}, "12/08/2008", "Cell content D3");
- is ($ss->{D4}, "08/13/2008", "Cell content D4");
-
- is ($ss->{E1}, "08 Aug 2008", "Cell content E1");
- is ($ss->{E2}, "12 Aug 2008", "Cell content E2");
- is ($ss->{E3}, "08 Dec 2008", "Cell content E3");
- is ($ss->{E4}, "13 Aug 2008", "Cell content E4");
+ foreach my $r (1..4,6..7) {
+ foreach my $c (1..5) {
+ my $cell = cr2cell ($c, $r);
+ my $fmt = $ss->{attr}[$c][$r]{format};
+ defined $ss->{$cell} or next;
+ is ($ss->{$cell}, $fmt{$cell}[0], "$cell content");
+ is ($fmt, $fmt{$cell}[1], "$cell format");
+ }
+ }
}
unless ($ENV{AUTOMATED_TESTING}) {
@@ -26,7 +26,10 @@ foreach my $row (1 .. 19) {
$ss->{B18} =~ m/[.]/ and
skip "$xls->[0]{parser} $xls->[0]{version} has format problems", 1;
my $i = int $ss->{"A$row"};
- is ($ss->{"B$row"}, "$i%", "Formatted values for row $row\n");
+ # Allow edge case. rounding .5 will be different in -Duselongdouble perl
+ my $f = $ss->{"B$row"};
+ $row == 11 && $f eq "1%" and $i = 1;
+ is ($f, "$i%", "Formatted values for row $row\n");
}
}