Excel output from Perl script

In an earlier post, I had explained how to output some tabular data from a PHP script into an Excel sheet using PHPExcel. Today, I shall explain the Perl equivalent, using a module named Spreadsheet::WriteExcel. This module can be easily installed using PPM. By looking at the documentation and the code examples, we can quickly rustle up our own Perl code to create an Excel sheet containing the songs data from our famous music database.

    # Create a new Excel workbook
    my $workbook = Spreadsheet::WriteExcel->new('music_list.xls');

    

    # Add a worksheet

    my $worksheet = $workbook->add_worksheet();

    

    #  Add and define a format for title row

    my $format = $workbook->add_format(); # Add a format

    $format->set_bold();

    $format->set_color('black');

    my $slate_grey = $workbook->set_custom_color(40, '#6D7B8D'); # slate grey color

    $format->set_bg_color($slate_grey);

    $format->set_align('center');

    

    # Write the Excel title row using the new format

    $worksheet->write(0, 0, "Song Title", $format);

    $worksheet->write(0, 1, "Artist Name", $format);

    $worksheet->write(0, 2, "Album Title", $format);

    $worksheet->write(0, 3, "Year Title", $format);

    

    # Increase the column width for clarity

    $worksheet->set_column('A:D', 25);

    # Define the format for the cell borders

    my $cell_border_format = $workbook->add_format(border => 1);

    if ($row_count == 0) {

        $worksheet->write(1, 0, "No records");

    } else {

        for (my $i=0; $i < $row_count; $i++) {

            # Write the actual table contents and also set the cell borders

            $worksheet->write($i+1, 0, $excel_content->{"a" . $i}, $cell_border_format);

            $worksheet->write($i+1, 1, $excel_content->{"b" . $i}, $cell_border_format);

            $worksheet->write($i+1, 2, $excel_content->{"c" . $i}, $cell_border_format);

            $worksheet->write($i+1, 3, $excel_content->{"d" . $i}, $cell_border_format);

        }

    }

One catch is that the Spreadsheet::WriteExcel module cannot overwrite an existing Excel file (by design, to avoid accidental data loss). So let us put in a bit of code to delete the existing file, if any.

    # Check if Excel file already exists

    if (-e 'music_list.xls') {

        # Try and delete old Excel file

        if (unlink('music_list.xls') == 0) {

            # Show error message if delete fails

            print "Old Excel file could not be deleted";

            # Show the HTML footers and exit

            &tail($dbh);

            exit(0);
        }
    }