in reply to Generating an Excel Report

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)');

[download]

-Mark

Replies are listed 'Best First'.
Re: Re: Generating an Excel Report
by dragonchild (Archbishop) on Mar 02, 2004 at 12:39 UTC
    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>

    [download]

    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.

[reply]
[d/l]
      Hi.. Im sorry but could you teach me how do I insert this code on my perl program??
[reply]
        It's just like any templating system. You have two files - the Perl script and the template file. The Perl script has the Perl code in it and the template file has the XML in it.

        ------
        We are the carpenters and bricklayers of the Information Age.

        Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

[reply]

In Section Seekers of Perl Wisdom