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,