Thanks. This is definitely helpful, but the one thing I find a bit
funny is the comments in the code... I think I recognize it as German, but
being a stupid American, English is the only language I actually speak. :)
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 01, 2002 11:14 AM
To: NIPP, SCOTT V (SBCSI)
Cc: '[EMAIL PROTECTED]'
Subject: Re: Database to Excel script...
Here we go:
>
> I have a MySQL database that I have a few Perl scripts working on.
> I am now looking into doing some reporting on these database tables. I
> would really like to find a script that queries the database and the uses
> the Spreadsheet::WriteExcel module to output the data directly to an Excel
> file.
use diagnostics;
use strict;
use DBI;
use Spreadsheet::WriteExcel;
my $filename ="abfrage.xls";
my $user = "root";
my $passwort = "";
my $dsn = "DBI:mysql:database=medialsoft;host=localhost";
unlink 'dbitrace.log' if -e 'dbitrace.log';
print "Content-type: application/vnd.ms-excel\n";
print "Content-Disposition: attachment; filename=$filename\n";
print "\n";
my ($model_id, $vorname, $name);
my @entries = ();
my %font = (
font => 'Arial',
size => 12,
color => 'black',
bold => 1,
);
my %shading = (
fg_color => 'red',
pattern => 1,
);
my $dbh = DBI->connect($dsn, $user, $passwort,{RaiseError
=> 1});
$dbh->trace( 2, "dbitrace.log" );
# trace verfolgung
my $sth1 = $dbh->prepare( "SELECT Count(*) FROM
modelle" );
$sth1->execute();
# alle Daten durchzählen, Gesamtzahl in
Variable $count schreiben
my $count = $sth1->fetchrow_array();
my $sth = $dbh->prepare( "SELECT model_id, vorname, name
FROM modelle" );
$sth->execute();
# alle Daten aus der Datenbank holen
while (my $ref = $sth->fetchrow_hashref ()) {
push @entries, [ $ref->{model_id}, $ref->{vorname},
$ref->{name} ];
# pack alles in den HASH
}
my $workbook = Spreadsheet::WriteExcel->new("-");
my $worksheet = $workbook->addworksheet("Adressen");
$worksheet->set_column(0, 0, 10);
$worksheet->set_column(1, 4, 20);
[..snip..]
my $format = $workbook->addformat();
my $format1 = $workbook->addformat(%font);
my $format2 = $workbook->addformat(%font, %shading);
my $format3 = $workbook->addformat();
$format3->set_text_wrap();
$format3->set_align('justify');
$format->set_bold();
# das war die Exceltabellenformatierung
# jetzt kommt das schleifen der Daten durch Zeilen und
Spalten
for my $col ( 0..7 ) {
for my $row ( 1..$count ) {
my $e = shift @entries;
# HASH entpacken
last unless $e;
# bis nichts mehr an Daten da ist
my ($id, $vor, $nach) = @$e;
$worksheet->write($id, 0, $id);
$worksheet->write($id, 1, $vor);
$worksheet->write($id, 2, $nach);
$worksheet->write(($count+2), 0,
"Programmiert als Open Source Software mit Perl5, DBD::MySQL,
Spreadsheet::WriteExcel by Andreas Schmitz 3. September 2002",
$format1);
# Varaiblen in Worksheets schreiben
}
}
for my $col ( 0..7 ) {
$worksheet->write(0, 0, "ID-Nummer", $format);
$worksheet->write(0, 1, "Vorname", $format1);
$worksheet->write(0, 2, "Name", $format1);
$worksheet->write(0, 34, "Gesamtanzahl",$format);
}
#Ueberschriften schreiben
--
Andreas Schmitz http://www.medialsoft.de
_ _ _ _ ° _ _ _ _ _
| | ||_ | \|| || _| (_)|- |
| | ||_ |_/||-||__
