Functions to take a two dimensional array and output a simplified HTML/XML format that Excel 2000 and up can read. Output can be redirected to a .xls file, or to an .html file and opened with Excel manually.
#!/usr/bin/perl
use strict;
use warnings;

sub add_xl_header    {
    return<<EOT;
<html xmlns:x="urn:schemas-microsoft-com:office:excel">
<head>
<xml>
 <x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>
    <x:Name>Sheet1</x:Name>
    <x:WorksheetOptions>
     <x:Selected/>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
  </x:ExcelWorksheets>
 </x:ExcelWorkbook>
</xml>
</head>
<body>
EOT
}

sub add_xl_table    {
    defined(my $array_ref = shift) || return;
    my ($use_row_headers, $use_col_headers) = @_;
    my $table;
    my $max_width = 0;
    if ($use_row_headers)    {
        my $row = shift @$array_ref;
        $table .= "<tr>\n";
        $table .= "<th>$_</th>\n" for @$row;
        $table .= "</tr>\n";
    }
    for my $row (@$array_ref)    {
        $max_width = @$row if @$row > $max_width;
        $table .= "<tr>\n";
        $$row[0] = '<b>' . $$row[0] if $use_col_headers;
        $table .= "<td>$_</td>\n" for @$row;
        $table .= "</tr>\n";
    }
    $table = "<table>\n<col span=$max_width style='width:48pt'>\n$tabl
+e</table>\n";
    $table;
}

sub add_xl_separator    {
    my $sep_rows = shift || 1;
    return "<table><tr style='mso-xlrowspan:$sep_rows'></tr></table>\n
+";
}

sub add_xl_trailer    {
    return "</body>\n</html>\n";
}

## Misc
# Add a formula to a cell : <td x:num x:fmla="=B2+1"></td>
# Define a cell as Text   : <td style='mso-number-format:"\@"'>00123</
+td>
# Indent a row 2 columns  : <td colspan=2 style='mso-ignore:colspan'><
+/td>

my @test_array1 = ( [2, 3, 5] , [7, 11, 13] , [17, 19, 23] );
my @test_array2 = ( ['', 'Height', 'Siblings'], ['Joe', 77, 1], ['Fran
+k', 70, 4] );

print add_xl_header;
print add_xl_table \@test_array1;
print add_xl_separator 3;
print add_xl_table (\@test_array2, 1, 1);
print add_xl_trailer;

[download]

In reply to Two-dimensional array to "Excel" format by delirium

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.