Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid

Generating an Excel Report

by kasmot (Novice)
on Mar 02, 2004 at 07:45 UTC ( #333188=perlquestion: print w/replies, xml ) Need Help??

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

Hi, Is it possible to create an excel report using perl. If its possible, I would deeply appreciate if someone could give me some code on how to do that. Lots of thanks! - kasmot

Replies are listed 'Best First'.
Re: Generating an Excel Report
by kvale (Monsignor) on Mar 02, 2004 at 07:56 UTC
    There exists a module expressly for the purpose of creating an Excel file: Spreadsheet::WriteExcel. Here is the general idea from the synopsis:
    use Spreadsheet::WriteExcel; # Create a new Excel workbook my $workbook = Spreadsheet::WriteExcel->new("perl.xls"); # Add a worksheet $worksheet = $workbook->add_worksheet(); # Add and define a format $format = $workbook->add_format(); # Add a format $format->set_bold(); $format->set_color('red'); $format->set_align('center'); # Write a formatted and unformatted string, row and column notatio +n. $col = $row = 0; $worksheet->write($row, $col, "Hi Excel!", $format); $worksheet->write(1, $col, "Hi Excel!"); # Write a number and a formula using A1 notation $worksheet->write('A3', 1.2345); $worksheet->write('A4', '=SIN(PI()/4)');


      Alternately, you could use Excel::Template, which uses Spreadsheet::WriteExcel.
      use Excel::Template; my $template = Excel::Template->new( filename => 'template.xml', ); $template->param( hello => 'Hi Excel!', value => 1.2345, formula => '=SIN(PI()/4)', ); $template->write_file('perl.xls'); -------- template.xml -------- <workbook> <worksheet> <row> <bold> <cell><var name="hello" /></cell> </bold> <cell col="+1" text="$value" /> <formula><var name="formula" /> </row> <cell row="1" col="0"><var name="hello" /></cell> </worksheet> </workbook>

      I don't have all the formatting options built in, yet, but the rest of it works. It takes the same data structure as HTML::Template, so it's really good for building reports. (My current job has me using HTML::Template, Excel::Template, PDF::Template, and Graph::Template for this very purpose.)

      We are the carpenters and bricklayers of the Information Age.

      Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

        Hi.. Im sorry but could you teach me how do I insert this code on my perl program??
Re: Generating an Excel Report
by Trimbach (Curate) on Mar 02, 2004 at 07:59 UTC

    Or, if you're feeling lazy and your needs are modest, you can just output a tab-delimited text file with a ".xls" extension and Excel will happily open it without complaint. You don't even if to go through the "import" process in Excel; double-clicking the file opens it right up, at least in Excel versions from the last couple of years.

    Gary Blackburn
    Trained Killer

      The naming a file .xls trick has more uses, too.

      You can mark up an HTML table and apply font and centering options, and also formulas, then save it as .xls, and Excel won't complain. See this snippet for a simple example.

      Formulas look like this:

      <td x:num x:fmla="=B2+1"></td>

        If you're on a Win32 platform you can also use Win32::OLE I don't know if it is bulkier or less efficient than Spreadsheet::WriteExcel, but it's what I have used, in the past (well yesterday really).

        This script runs some queries against a SQL Server Database to create the report that is then created and formatted via Win32::OLE. This is really more than you need, but its an example of a report that uses a decent amount of features and gives you an idea of what kind of code is needed to properly format something. Writing your own is more rewarding than gutting this one and making it fit your needs, so please do so if you find this one useful.

Re: Generating an Excel Report
by Mr. Muskrat (Canon) on Mar 03, 2004 at 05:04 UTC

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (4)
As of 2020-12-03 10:50 GMT
Find Nodes?
    Voting Booth?
    How often do you use taint mode?

    Results (55 votes). Check out past polls.