Database to Excel script...
Scott V Nipp
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. Does anyone out there have something like this that I could
cannibalize? Thanks in advance.
Scott Nipp
Phone: (214) 858-1289
E-mail: sn4...@sbc.com
Web: http:\\ldsa.sbcld.sbc.com
Andreas Schmitz
>
> 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
_ _ _ _ ° _ _ _ _ _
| | ||_ | \|| || _| (_)|- |
| | ||_ |_/||-||__
Ilya Sterin
with .csv extension which will open in excel as you want.
Ilya
-----Original Message-----
From: NIPP, SCOTT V (SBCSI)
To: 'dbi-...@perl.org'
Sent: 10/1/02 9:04 AM
Subject: Database to Excel script...
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
Jeff Zucker
> You can easily accomplish that by dumpin into a CSV (comma separated file)
> with .csv extension which will open in excel as you want.
Here's one for the FAQ:
How do I use DBI with Excel?
With Kawai Takanori's DBD::Excel (on CPAN). It provides a DBI interface
on top of Spreadsheet::ParseExcel, SpreadSheet::WriteExcel and
SQL::Statement.
--
Jeff
Scott V Nipp
looking to create a cron job that will run a monthly report and e-mail the
resulting Excel file to a couple of individuals. I am able to do all of
this manually at this time, but I would really like to automate the process
if possible. This is also a great learning opportunity and I think that I
will have more requests in the future for generating Excel reports of this
nature.
-----Original Message-----
From: Sterin, Ilya [mailto:Ist...@ciber.com]
Sent: Tuesday, October 01, 2002 10:11 AM
To: NIPP, SCOTT V (SBCSI); ''dbi-...@perl.org' '
Subject: RE: Database to Excel script...
You can easily accomplish that by dumpin into a CSV (comma separated file)
with .csv extension which will open in excel as you want.
Ilya
-----Original Message-----
From: NIPP, SCOTT V (SBCSI)
To: 'dbi-...@perl.org'
Sent: 10/1/02 9:04 AM
Subject: Database to Excel script...
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. Does anyone out there have something like this that I could
cannibalize? Thanks in advance.
Scott Nipp
Phone: (214) 858-1289
E-mail: sn4...@sbc.com
Web: http:\\ldsa.sbcld.sbc.com <http:\\ldsa.sbcld.sbc.com>
Scott V Nipp
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: Andreas...@t-online.de [mailto:Andreas...@t-online.de]
Sent: Tuesday, October 01, 2002 11:14 AM
To: NIPP, SCOTT V (SBCSI)
Cc: 'dbi-...@perl.org'
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;
Ilya Sterin
Ilya
-----Original Message-----
From: Jeff Zucker [mailto:je...@vpservices.com]
Sent: Tuesday, October 01, 2002 11:37 AM
To: dbi-users
Subject: Re: Database to Excel script...
Sterin, Ilya wrote:
> You can easily accomplish that by dumpin into a CSV (comma separated file)
> with .csv extension which will open in excel as you want.
Ilya Sterin
https://pause.perl.org/pub/PAUSE/authors/id/I/IS/ISTERIN/DBIx-Dump-0.01.tar
Ilya
-----Original Message-----
From: NIPP, SCOTT V (SBCSI)
To: 'Andreas...@t-online.de'
Cc: 'dbi-...@perl.org'
Sent: 10/1/02 9:39 AM
Subject: RE: Database to Excel script...
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: Andreas...@t-online.de [mailto:Andreas...@t-online.de]
Sent: Tuesday, October 01, 2002 11:14 AM
To: NIPP, SCOTT V (SBCSI)
Cc: 'dbi-...@perl.org'
Subject: Re: Database to Excel script...
John McNamara
> 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.
The Spreadsheet::WriteExcel::FromDB module has a MySQL subclass:
http://search.cpan.org/author/TMTM/Spreadsheet-WriteExcel-FromDB/
John.
--
# Sum the numbers in the first column of a file
perl -lpe '$,+=$_}{$_=+$,' file
Ilya Sterin
but that does not pupulat the excel spreadsheet with data from a select
query, rather you have to select and loop while do()ing it.
There is lot of overhead for certain things, I just though a simple mod
would help to just dump data, and nothing else.
I'll also look into the AnyData, it's probably redundant, but I was looking
into supporting format that say can't be queried, but can be dumped into.
Ilya
-----Original Message-----
From: Jeff Zucker
To: Sterin, Ilya (I.)
Cc: 'dbi-...@perl.org'; Kawai Takanori (Hippo2000)
Sent: 10/1/02 3:41 PM
Subject: Re: Database to Excel script...
Ilya Sterin wrote:
>
>Jeff Zucker wrote
>>
>>With Kawai Takanori's DBD::Excel (on CPAN).
>
>Yes, but DBD::Excel allows you to query Excel files, not dump them.
Here's a somewhat simple minded patch to DBD::Excel that allows one to
create and populate an Excel file with standard SQL CREATE and INSERT
statements.
=item xl_mode
If xl_mode is set to "write" in the connect() statement, the CREATE and
INSERT statements will be written to file when disconnect() is
explicitly called. For example. this creates a new .xls file and
inserts rows into it:
my $hDb = DBI->connect("dbi:Excel:file=$file;xl_mode=write");
$hDb->do("CREATE TABLE $table (id INTEGER,phrase CHAR(40))");
$hDb->do("INSERT INTO $table VALUES (1,'JAPH')");
$hDb->do("INSERT INTO $table VALUES (2,'Hello World')");
$hDb->disconnect;
--
Jeff
<<excel.dif>>
Takanori Kawai
I will (and should) update DBD::Excel.
----- Original Message -----
From: "Jeff Zucker" <je...@vpservices.com>
To: "Sterin, Ilya (I.)" <ist...@ford.com>
Cc: <dbi-...@perl.org>; " Kawai Takanori (Hippo2000)" <kwi...@cpan.org>
Sent: Wednesday, October 02, 2002 6:41 AM
Subject: Re: Database to Excel script...
> Ilya Sterin wrote:
> >
> >Jeff Zucker wrote
> >>
>
> >>With Kawai Takanori's DBD::Excel (on CPAN).
> >
> >Yes, but DBD::Excel allows you to query Excel files, not dump them.
>
>
> Here's a somewhat simple minded patch to DBD::Excel that allows one to
> create and populate an Excel file with standard SQL CREATE and INSERT
> statements.
(snip)
==============================================
Kawai, Takanori(Hippo2000)
Mail: GCD0...@nifty.ne.jp kwi...@cpan.org
http://member.nifty.ne.jp/hippo2000/index_e.htm
http://www.hippo2000.info/cgi-bin/KbWikiE/KbWiki.pl
==============================================
Takanori Kawai
From: "NIPP, SCOTT V (SBCSI)" <sn4...@sbc.com>
To: <dbi-...@perl.org>
Sent: Wednesday, October 02, 2002 12:04 AM
Subject: Database to Excel script...
> 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. Does anyone out there have something like this that I could
> cannibalize? Thanks in advance.
Well, it has been already solved this problem,.
[yet another answer...]
use strict;
use DBI;
my $hDbM = DBI->connect('DBI:mysql:test', undef, undef,
{RaiseError=>1, AutoCommit=>1});
my $hDbE = DBI->connect
('DBI:Excel:file=dummy.xls', undef, undef,
{RaiseError=>1, AutoCommit=>1});
my $hStMt = $hDbM->prepare('SHOW TABLES');
$hStMt->execute();
my $raMt;
while($raMt=$hStMt->fetchrow_arrayref()) {
my $sTbl = $raMt->[0];
print "-- $sTbl\n";
my $hStMs = $hDbM->prepare("SELECT * FROM $sTbl");
$hStMs->execute();
my ($hStEi, $raMs);
$hDbE->do(
"CREATE TABLE $sTbl (".
join(",\n", map { $_ . ' text' } @{$hStMs->{NAME}}) .
')');
$hStEi = $hDbE->prepare(
"INSERT INTO $sTbl VALUES(" .
join(',', ('?') x $hStMs->{NUM_OF_FIELDS}) .
')');
while($raMs = $hStMs->fetchrow_arrayref()) {
$hStEi->execute(@$raMs);
}
$hStMs->finish();
}
$hDbM->disconnect;
$hDbE->func('dump.xls', 'save');
$hDbE->disconnect;
Jeff Zucker
> Jeff, that's helpful:-)
>
> but that does not pupulat the excel spreadsheet with data from a select
> query, rather you have to select and loop while do()ing it.
>
> There is lot of overhead for certain things, I just though a simple mod
> would help to just dump data, and nothing else.
How much simpler can you get than this (requires only AnyData, not
DBD::AnyData or any of the SQL* modules):
use DBI;
use AnyData;
my $dbh = DBI->connect($mysql_or_other_dsn);
my $sth = $dbh->prepare($query);
for my $new_format(qw(CSV XML Ini Tab Pipe Fixed HTMLTable)) {
adConvert( 'DBI', $sth, $new_format, "newfile.$new_format");
}
__END__
--
Jeff
Steve Sapovits
Date::Manip is a good in-between date translator.
----
Steve Sapovits
GSI Commerce
sapo...@gsicommerce.com
> -----Original Message-----
> From: NIPP, SCOTT V (SBCSI) [SMTP:sn4...@sbc.com]
> Sent: Wednesday, October 02, 2002 4:46 PM
> To: 'Jeff Zucker'; Sterin, Ilya; dbi-users
> Subject: RE: Database to Excel script...
>
> OK... I am stuck once again. I am to pull the data from the
> database and export it directly to an Excel file. Now, I am trying to
> figure out the formatting... The first problem I am running into is
> reformatting dates from the output of MySQL to the desired Excel format.
> Here is a same of what I am attempting:
>
> MySQL date: 2002-09-02 13:13:00
>
> Desired Excel date: 9/2/2002 13:13
>
> I know that there is a set_num_format function in the WriteExcel
> module, but this works with epoch numbers. I am not sure about
> transforming
> one date format to another date format. Any help would be greatly
> appreciated.
>
> -----Original Message-----
> From: Jeff Zucker [mailto:je...@vpservices.com]
> Sent: Tuesday, October 01, 2002 6:51 PM
> To: Sterin, Ilya; dbi-users
> Subject: Re: Database to Excel script...
>
>
Crown David T.
approach is just a mad mess of splits and swapping.
David Crown, MCSE, MCP+I, CCNA
Information Resources Management
State of Delaware,
Department of Natural Resources and Environmental Control
dcr...@state.de.us
Voice: (302)739-4409 Fax: (302)739-6242
www.dnrec.state.de.us/
Cp
> Subject: RE: Database to Excel script...
>
> OK... I am stuck once again. I am to pull the data from the
> database and export it directly to an Excel file. Now, I am trying to
> figure out the formatting... The first problem I am running into is
> reformatting dates from the output of MySQL to the desired Excel format.
> Here is a same of what I am attempting:
>
> MySQL date: 2002-09-02 13:13:00
>
> Desired Excel date: 9/2/2002 13:13
>
> I know that there is a set_num_format function in the WriteExcel
> module, but this works with epoch numbers. I am not sure about transforming
> one date format to another date format. Any help would be greatly
> appreciated.
Actually, you should read the docs again. set_num_format works on any kind
of numbers. Dates should be in the internal excel format, which are the
decimal equivalent of days since the Excel epoch (0 January 1900 or 1
January 1904 depending on the OS). You should have a read of the docs which
show several concise examples, including:
$format->set_num_format('dd/mm/yyyy hh:mm AM/PM');
$worksheet->write($row, $col, $timestr, $format);
There are helper routines in the Spreadsheet::WriteExcel::Utility. Their use
is also documented in perldoc Spreadsheet::WriteExcel (In the section Dates
in Excel).
An example given:
$timestr = xl_date_list(2002, 1, 1);
Since these routines are built on-top of Date::Calc and Date::Manip, you
might look into those options as well.
There are plenty of MySQL solutions as well, such as:
SELECT DATE_FORMAT('2002-01-01', '%d/%m/%Y');
These are less portable but still viable. DATE_FORMAT and other date
manipulation functions are documented in the MySQL Reference Manual. Start
with Chapter 7 Functions for Use in SELECT and WHERE Clauses.
You might consider posting questions on Perl modules that are outside of the
scope of the DBI to the appropriate newsgroup: comp.lang.perl.modules.
cp
--
Ilya Sterin
you to assign an even handler to handle transformations.
Give me until tomorrow noon.
Ilya
-----Original Message-----
From: NIPP, SCOTT V (SBCSI)
To: 'Jeff Zucker'; Sterin, Ilya; dbi-users
Sent: 10/2/02 2:45 PM
Subject: RE: Database to Excel script...
OK... I am stuck once again. I am to pull the data from the
database and export it directly to an Excel file. Now, I am trying to
figure out the formatting... The first problem I am running into is
reformatting dates from the output of MySQL to the desired Excel format.
Here is a same of what I am attempting:
MySQL date: 2002-09-02 13:13:00
Desired Excel date: 9/2/2002 13:13
I know that there is a set_num_format function in the WriteExcel
module, but this works with epoch numbers. I am not sure about
transforming
one date format to another date format. Any help would be greatly
appreciated.
-----Original Message-----
Scott V Nipp
message:
Can't locate object method "set_column" via package
"Spreadsheet::WriteExcel" at
./oncall_report.pl line 23.
Here is the line of the script...
$xls->set_column(6, 7, 50);
I am not sure what is wrong with this... I pretty much copied this code
from one of the examples, but I am definitely having a problem. Thanks
again.
-----Original Message-----
From: Sterin, Ilya [mailto:Ist...@ciber.com]
Ilya Sterin
Ilya Sterin
person, or on comp.lang.perl.misc
Thanks.
John McNamara
> I am trying to do the cell formatting now and I am running into an error
> message:
>
> Can't locate object method "set_column" via package
> "Spreadsheet::WriteExcel" at ./oncall_report.pl line 23.
>
> Here is the line of the script...
>
> $xls->set_column(6, 7, 50);
It looks like you might be calling the set_column() method on a
workbook object instead of a worksheet object.
John.
--
Andreas Schmitz
> $xls->set_column(6, 7, 50);
please read the docs:
Perl/site/lib/Spreadsheet/WriteExcel/doc/WriteExcel.html#set_column_first_col_last_col