http://www.perlmonks.org?node_id=322343

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;

Replies are listed 'Best First'.
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: