Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Two-dimensional array to "Excel" format

by delirium (Chaplain)
on Jan 19, 2004 at 13:54 UTC ( #322343=snippet: print w/ replies, xml ) Need Help??

Description: 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;

Comment on Two-dimensional array to "Excel" format
Download Code
Re: Two-dimensional array to "Excel" format
by jmcnamara (Monsignor) on Apr 25, 2004 at 15:14 UTC

    Nice. ++

    Here is another way to do it using the new Spreadsheet::WriteExcelXML module.

    It doesn't support all of the features of Spreadsheet::WriteExcel but they will be added in time.

    #!/usr/bin/perl -w use strict; use Spreadsheet::WriteExcelXML; my $workbook = Spreadsheet::WriteExcelXML->new("demo.xml"); my $worksheet = $workbook->add_worksheet(); my $bold = $workbook->add_format(bold => 1); my @test_array1 = ( [2, 3, 5], [7, 11, 13], [17, 19, 23], ); my @test_array2 = ( ['', 'Height', 'Siblings'], ['Joe', 77, 1 ], ['Frank', 70, 4 ], ); $worksheet->write_col('A1', \@test_array1); $worksheet->write('A7', $test_array2[0], $bold); $worksheet->write('A8', $test_array2[1] ); $worksheet->write('A9', $test_array2[2] );

    The output which can be read by Excel 2002 and 2003 looks like this:

Back to Snippets Section

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: snippet [id://322343]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (5)
As of 2015-07-04 19:19 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (60 votes), past polls