Re: Two-dimensional array to "Excel" format

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]       );
[download]

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

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook
    xmlns:x="urn:schemas-microsoft-com:office:excel"
    xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
    <Styles>
        <Style ss:ID="s21">
            <Font ss:Bold="1"/>
        </Style>
    </Styles>
    <Worksheet ss:Name="Sheet1">
        <Table
            ss:ExpandedColumnCount="3"
            ss:ExpandedRowCount="9">
            <Row>
                <Cell>
                    <Data ss:Type="Number">2</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="Number">3</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="Number">5</Data>
                </Cell>
            </Row>
            <Row>
                <Cell ss:Index="1">
                    <Data ss:Type="Number">7</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="Number">11</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="Number">13</Data>
                </Cell>
            </Row>
            <Row>
                <Cell ss:Index="1">
                    <Data ss:Type="Number">17</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="Number">19</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="Number">23</Data>
                </Cell>
            </Row>
            <Row ss:Index="7">
                <Cell ss:Index="1" ss:StyleID="s21"/>
                <Cell ss:StyleID="s21">
                    <Data ss:Type="String">Height</Data>
                </Cell>
                <Cell ss:StyleID="s21">
                    <Data ss:Type="String">Siblings</Data>
                </Cell>
            </Row>
            <Row>
                <Cell ss:Index="1">
                    <Data ss:Type="String">Joe</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="Number">77</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="Number">1</Data>
                </Cell>
            </Row>
            <Row>
                <Cell ss:Index="1">
                    <Data ss:Type="String">Frank</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="Number">70</Data>
                </Cell>
                <Cell>
                    <Data ss:Type="Number">4</Data>
                </Cell>
            </Row>
        </Table>
    </Worksheet>
</Workbook>
[download]

--
John.

Comment on Re: Two-dimensional array to "Excel" format Select or Download Code