Database Reference
In-Depth Information
use warnings ;
use Spreadsheet::ParseExcel:: Simple ;
@ARGV or die "Usage: $0 excel-file\n" ;
my $xls = Spreadsheet::ParseExcel:: Simple -> read ( $ARGV [ 0 ]);
foreach my $sheet ( $xls -> sheets ())
{
while ( $sheet -> has_data ())
{
my @data = $sheet -> next_row ();
print join ( "\t" , @data ) . "\n" ;
}
}
The to_excel.pl script performs the converse operation of reading a tab-delimited file
and writing it in Excel format:
#!/usr/bin/perl
# to_excel.pl: Read tab-delimited, linefeed-terminated input, write
# Excel-format output to the standard output.
use strict ;
use warnings ;
use Excel::Writer:: XLSX ;
binmode ( STDOUT );
my $ss = Excel::Writer:: XLSX -> new ( \* STDOUT );
my $ws = $ss -> add_worksheet ();
my $row = 0 ;
while ( <> ) # read each row of input
{
chomp ;
my @data = split ( /\t/ , $_ , 10000 ); # split, preserving all fields
my $col = 0 ;
foreach my $val ( @data ) # write row to the worksheet
{
$ws -> write ( $row , $col , $val );
$col ++ ;
}
$row ++ ;
}
to_excel.pl assumes input in tab-delimited, linefeed-terminated format. Use it in con‐
junction with cvt_file.pl (see Recipe 11.6 ) to work with files not in that format.
Another Excel-related Perl module, Spreadsheet::WriteExcel::FromDB, reads data from
a table using a DBI connection and writes it in Excel format. Here's a script that exports
a MySQL table as an Excel spreadsheet:
#!/usr/bin/perl
# mysql_to_excel.pl: Given a database and table name,
Search WWH ::




Custom Search