Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
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 avoiding work at the Monastery: (5)
As of 2014-12-28 18:56 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (182 votes), past polls