Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW

Can I Create Reports and Charts Through Perl?

by peacemaker1820 (Pilgrim)
on Feb 24, 2003 at 21:12 UTC ( #238235=perlquestion: print w/replies, xml ) Need Help??

peacemaker1820 has asked for the wisdom of the Perl Monks concerning the following question:

Hi all...
I was wondering if one is able to create reports and charts in Excel through Perl by using database to extract monthly/daily sales info and similar stuff?
Can it be done by creating a .xls extension document with comma delimiters? In addition, I would like to be able to insert formulas into the sheet.
Is there a template to do anything like this?

I am thirsty for some info...

Thanking all for any input
  • Comment on Can I Create Reports and Charts Through Perl?

Replies are listed 'Best First'.
Re: Can I Create Reports and Charts Through Perl?
by Corion (Pope) on Feb 24, 2003 at 21:19 UTC

    You can simply create .csv files, which are comma separated values. Excel will be able to read them, and if your country settings are the right settings, it will even be able to open up the files via double-clicking them from the explorer.

    The following should be an (untested) example that shows you what .csv can do (I assume US/english country settings) :

    A,B,C,D "This, my friend, is a string",1,2,3 42,23,17,5 =B2+C3,=A3+C3,=B3+C3,=SUM(A4:D4)

    The one thing you can't do this way is doing Excel charts. Here, Win32::OLE is your friend to remotely control Excel.

    If you are pulling your data out of the database, you will think about simply pulling it out via DBI and then writing the data into your .csv files via DBD::CSV.

    perl -MHTTP::Daemon -MHTTP::Response -MLWP::Simple -e ' ; # The $d = new HTTP::Daemon and fork and getprint $d->url and exit;#spider ($c = $d->accept())->get_request(); $c->send_response( new #in the HTTP::Response(200,$_,$_,qq(Just another Perl hacker\n))); ' # web
Re: Can I Create Reports and Charts Through Perl?
by Nkuvu (Priest) on Feb 24, 2003 at 21:27 UTC

    The short answer is Yes.

    The longer answer is that you can directly create the Excel spreadsheet, either via Win32::OLE (requires Office to be installed on the system) or the SpreadSheet::WriteExcel and SpreadSheet::ParseExcel modules from CPAN. Note that if you are using ActivePerl on Windows Win32::OLE is already installed (unless they've changed something since I last installed. ;) ).

    With OLE, you can do anything in Excel that has a similar VB control. Try perldoc Win32::OLE if you are on Windows. There's also a quick and dirty reference for Win32::OLE on this page that I have used frequently.

Re: Can I Create Reports and Charts Through Perl?
by Aristotle (Chancellor) on Feb 24, 2003 at 21:34 UTC

    A CPAN search on 'excel' reveals a wealth of code. Spreadsheet::WriteExcel may be worth a look.

    Using CSV files will be much easier if you don't need control over formatting, adding formulas and such, though.

    Makeshifts last the longest.

Re: Can I Create Reports and Charts Through Perl?
by dragonchild (Archbishop) on Feb 24, 2003 at 21:35 UTC
    Spreadsheet::ParseExcel and Spreadsheet::WriteExcel are also good places to start.

    We are the carpenters and bricklayers of the Information Age.

    Don't go borrowing trouble. For programmers, this means Worry only about what you need to implement.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://238235]
Approved by Corion
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (5)
As of 2020-12-03 20:25 GMT
Find Nodes?
    Voting Booth?
    How often do you use taint mode?

    Results (57 votes). Check out past polls.