Differences between version 3 and predecessor to the previous major change of Excel2CSV.
Other diffs: Previous Revision, Previous Author, or view the Annotated Edit History
| Newer page: | version 3 | Last edited on Saturday, February 21, 2004 7:06:08 am | by AristotlePagaltzis | Revert |
| Older page: | version 2 | Last edited on Friday, February 20, 2004 2:06:15 pm | by DrewBroadley | Revert |
@@ -1,59 +1,127 @@
[Perl] script to convert [Microsoft] Excel Spreadsheets to [CSV] (Command Seperated Values)
-Requires [Perl] Modules:
+Requires the following
[Perl] Modules:
-#Spreadsheet::!
ParseExcel
-#Getopt::Long
+# Spreadsheet::~
ParseExcel
+# Getopt::Long
-See Also
: [http://search.cpan.org/~tmtm
/Spreadsheet-ParseExcel-Simple-1.01
/] for Simple Excel Parsing.
+See also
: [http://search.cpan.org/dist
/Spreadsheet-ParseExcel-Simple/]
----
- use strict;
- use Spreadsheet::!ParseExcel;
- use Getopt::Long;
- my $oExcel = new Spreadsheet::
!ParseExcel;
- my $excelfile ;
- my %options;
+<verbatim>
+#
!/usr/bin/perl
+=head1 NAME
- $options{'suffix'} = ".csv";
- $options{'sep'} = ";";
+excel2csv -- dump all worksheets from an Excel file to CSV files
- Getopt::Long::!GetOptions( \%options, 'help','quiet', 'suffix
=s', 'sep=s') or exit;
+=head1 SYNOPSIS
- if (@ARGV == 0 or defined $options{'help'}) {
- die
<<'EOF';
- Usage: excel2csv [[ options ] files
- where the options specify
-
--help this helpful help
- --quiet no progress information given
- --suffix output file suffix (default ".csv")
- --sep output field separator (default ";")
- EOF
- }
+F<excel2csv>
+S
<B
<-h>>
+F<excel2csv>
+S<B<--man>>
- #1
.1 Normal Excel97
- foreach $excelfile
(@ARGV
)
- {
- my $oBook
= $oExcel
->Parse
($excelfile
);
- my
($iR
, $iC
, $oWkS
, $oWkC
, $file
);
-
for(my $iSheet
=iSheet <
$oBook
->{!
SheetCount} ; $iSheet++) {
-
$oWkS =
$oBook
->{Worksheet}[[
$iSheet
];
- open
$file, "> " . $oWkS
->{Name
} .
$options
{'suffix'
} or die "Can not open file for writing!"
;
-
print $oWkS
->{Name} . "\n" if not defined
$options{'quiet'}
;
- for(
my $iR
= $oWkS
->{!MinRow
} ;
- defined
$oWkS
->{!MaxRow
} &&
$iR <= $oWkS
->{!
MaxRow} ; $iR++) {
- for(
my $iC
= $oWkS
->{!
MinCol} ;
- defined
$oWkS
->{!MaxCol} && $iC <= $oWkS->{!
MaxCol} ; $iC++)
{
- $oWkC =
$oWkS
->{!
Cells}[[$iR
][[$iC];
- print $file $oWkC->Value if($oWkC
);
-
print $file
$options{'
sep'}
;
- }
-
print $file
"\n";
-
}
- close $file;
-
}
-
}
-----
+F<rename>
+S<B<[ -s ]>>
+S<B<[ -S ]>>
+S<B<[ -v ]>>
+S<B<file
.xls [ F<file2.xls> F<file3.xls> ... ]>>
+
+=head1 DESCRIPTION
+
+C<excel2csv> takes any number of Excel files on the command line, reads them, and dumps each worksheet therein to a separate CSV file, named after the worksheet.
+
+=head1 ARGUMENTS
+
+=over 4
+
+=item B<-h>, B<--help>
+
+See a synopsis.
+
+=item B<--man>
+
+Browse the manpage.
+
+=back
+
+=head1 OPTIONS
+
+=over 4
+
+=item B<-s>, B<--suffix>
+
+The suffix for the CSV files generated. Default is C<.csv>.
+
+=item B<-S>, B<--separator>
+
+The field separator used in the generated CSV files. Default is C<;>.
+
+=item B<-v>, B<--verbose>
+
+Print additional information about the operations
(not
) executed.
+
+=back
+
+=head1 BUGS
+
+CSV generation is fugded
-- no quoting issues are taken into consideration. CSV generation should rely on one of the modules available for the purpose instead.
+
+=head1 AUTHORS
+
+Drew Broadley, with contributions from Aristotle Pagaltzis
+
+=head1 COPYRIGHT
+
+FIXME FIXME FIXME FIXME FIXME FIXME FIXME FIXME
+
+=cut
+
+use strict;
+use warnings;
+
+use Spreadsheet::ParseExcel;
+use Getopt::Long 2.24, qw
(:config bundling no_ignore_case no_auto_abbrev
);
+use Pod::Usage;
+
+Getopt::Long::GetOptions
(
+ 'h|help' => sub { pod2usage( -verbose => 1 ) }
,
+ 'man' => sub { pod2usage( -verbose => 2 ) },
+ 's|suffix=s' => \(my
$opt_suffix = '.csv')
,
+ 'S|sep=s' => \(my
$opt_separator = ';')
,
+ 'v|verbose' => \(my
$opt_verbose)
,
+) or pod2usage();
+
+#1.1 Normal Excel97
+my
$excel = Spreadsheet::ParseExcel->new(
);
+for (@ARGV) {
+
my $book
= $excel->Parse($_)
;
+
+ my
$last_sheet =
$book
->{SheetCount} - 1
;
+
+ for my
$worksheet ( @{
$book
->{Worksheet}
}[ 0 ..
$last_sheet
] ) {
+
+ next
+ if not defined
$worksheet
->{MaxRow
}
+ or not defined
$worksheet->
{MaxCol
};
+
+
print $worksheet
->{Name} . "\n" if $opt_verbose
;
+
+
my $filename
= $worksheet
->{Name
} . $opt_suffix
;
+ open my $fh, ">", $filename
+ or die "Can open $filename to write: $!\n";
+
+ my @row =
$worksheet
->{MinRow
} ..
$worksheet
->{MaxRow};
+
my @col
= $worksheet
->{MinCol} ..
$worksheet
->{MaxCol};
+
+ for my
$row ( @
{ $worksheet
->{Cells}
}[ @row
] ) {
+
print $fh join
$opt_
sep, @$row[ @col ]
;
+
print $fh
"\n";
+
}
+
+
}
+}
+</verbatim>
