Home

Search this site:
 

Horshack’s
very
own site

 

This is Bavaria, Germany!
You will find links to Perl,
bootdisks for system administration,
a lot of Linux,
Windows administration
and some personal belongings.
Creating Excel files with Perl

Creating Excel-files with Perl

Create Excelfile

You don't need Win32 to create Excel-files because the format is standardized. So it's possible to create excel-files in Linux. I use it to regularly create financial reports from a database.

This is how you create the Spreadsheet:

use strict;
use warnings;
use Date::Calc;
use Spreadsheet::WriteExcel;
use Spreadsheet::WriteExcel::Utility;

our $workbook = Spreadsheet::WriteExcel->new("perl2.xls"); 

our %format = &get_excel_cell_formats;

Defining display formats

&get_excel_cell_formats defines the different kinds how your text and your numbers get displayed:

sub get_excel_cell_formats {
    my $format;

    # bold
    $format{bold} = $workbook->add_format();
    $format{bold}->set_bold();

    # german date format
    $format{date_ger} = $workbook->add_format();
    $format{date_ger}->set_num_format('dd.mm.yyyy');

    # Euro-cells: 1.234,56 E
    $format{currency_euro} = $workbook->add_format();
    $format{currency_euro}->set_num_format( sprintf '0,000.00 "%s"', chr(128));

    # Euro-cells, with red values below zero
    $format{currency_euro_red} = $workbook->add_format();
    $format{currency_euro_red}->set_num_format(
            sprintf '0,000.00 "%s";[Red]-0,000.00 "%s"', chr(128), chr(128));
    return %format;
}

Writing Text

excel_text.gif

sub write_text {
        my $worksheet = shift;
        my ($x,$y)=(0,0);
        # Simple text
        $worksheet->write($y++, $x, "Hi Excel!");
        # Simple text bold
        $worksheet->write($y++, $x, "Hi Excel!", $format{bold});
}

Writing Dates

excel_dates.gif

sub write_dates {
    my $worksheet = shift;
    my $y         = 0;

    # Set width of column
    $worksheet->set_column( 0, 0, 20 );
    $worksheet->set_column( 1, 1, 15 );

    # German date: 30.01.2002
    my ( $year, $month, $day ) = Date::Calc::Today;
    my $date = xl_date_list( $year, $month, $day );
    $worksheet->write( $y, 0, "Today" );
    $worksheet->write( $y, 1, $date, $format{date_ger} );
    $y++;

...

Writing Money

excel_money.gif

sub write_money {
    my $worksheet = shift;
    my ( $x, $y ) = ( 1, 0 );

    # some currency values in a row and sum it up
    # remember first and last cell to sum it up later
    # SUM works only with A1 not 0/0

    my $first = xl_rowcol_to_cell( $y, $x );
    my $last;

    foreach ( 1 .. 10 ) {
        $worksheet->write(
            $y, $x, rand() * 90000 + 1 - 40000,
            $format{currency_euro}
        );

        # remember last row
        $last = xl_rowcol_to_cell( $y, $x );
        $y++;
    }

    # Sum it up
    $worksheet->write( $y, $x - 1, "Sum" );
    $worksheet->write(
        $y, $x, "=SUM($first:$last)",
        $format{currency_euro}
    );
}


Last modified:
30.12.2005 08:13
Impressum
Wozu Witze ausdenken? Das Leben schreibt sie selbst:
Das haben wir jetzt 40 Jahre so gemacht, warum sollten wir das ändern?
Why inventing jokes? Real life does a better job:
We are doing it this way for 40 years, why should we change it?