Database Reference
In-Depth Information
# dump the table to the standard output in Excel format.
use
strict
;
use
warnings
;
use
DBI
;
use
Spreadsheet::ParseExcel::
Simple
;
use
Spreadsheet::WriteExcel::
FromDB
;
#
...
process
command
-
line
options
(
not
shown
)
...
#
...
to
get
$db_name
,
$tbl_name
...
#
...
connect
to
database
(
not
shown
)
...
my
$ss
=
Spreadsheet::WriteExcel::
FromDB
->
read
(
$dbh
,
$tbl_name
);
binmode
(
STDOUT
);
print
$ss
->
as_xls
();
Each utility writes to its standard output, which you can redirect to capture the results
in a file:
%
from_excel.pl data.xls > data.txt
%
to_excel.pl data.txt > data.xlsx
%
mysql_to_excel.pl cookbook profile > profile.xls
Note that
from_excel.pl
and
mysql_to_excel.pl
read and write
.xls
files, whereas
to_ex‐
cel.pl
writes
.xlsx
files.
See Also
On Windows, MySQL for Excel is an add-in that enables access to MySQL databases
directly from Excel. For information, visit the
“Download MySQL for Excel” page
on
the MySQL website.
11.9. Exporting Query Results as XML
Problem
You want to export the result of a query as an XML document.
Solution
mysql
can do that, or you can write your own exporter.
Discussion
The
mysql
client can produce XML-format output from a query result (see
Recipe 1.7
).
You can also write your own XML-export programs. One way to do this is to execute a
query and then write the result, adding the XML markup yourself. Another is to install
a few Perl modules and let them do the work: