#!/usr/bin/perl -wl
##############################################################################
#
# A test for Spreadsheet::WriteExcelXML.
#
# Regression test the ouput from a typical program against the stored output
# from a previous version.
#
# reverse('©'), April 2005, John McNamara, jmcnamara@cpan.org
#
use strict;
use Spreadsheet::WriteExcelXML;
use Test::More tests => 1;
##############################################################################
#
# Create a new typical WriteExcelXML file.
#
my $test_file = "temp_test_file.xml";
my $workbook = Spreadsheet::WriteExcelXML->new($test_file);
my $worksheet = $workbook->add_worksheet('Demo');
my $worksheet2 = $workbook->add_worksheet('Another sheet');
my $worksheet3 = $workbook->add_worksheet('And another');
my $bold = $workbook->add_format(bold => 1);
# Write a general heading
$worksheet->set_column('A:A', 48, $bold);
$worksheet->set_column('B:B', 20 );
$worksheet->set_row (0, 40 );
my $heading = $workbook->add_format(
bold => 1,
color => 'blue',
size => 16,
merge => 1,
align => 'vcenter',
);
my @headings = ('Features of Spreadsheet::WriteExcelXML', '');
$worksheet->write_row('A1', \@headings, $heading);
# Some text examples
my $text_format = $workbook->add_format(
bold => 1,
italic => 1,
color => 'red',
size => 18,
font =>'Lucida Calligraphy'
);
$worksheet->write('A2', "Text");
$worksheet->write('B2', "Hello Excel");
$worksheet->write('A3', "Formatted text");
$worksheet->write('B3', "Hello Excel", $text_format);
# Some numeric examples
my $num1_format = $workbook->add_format(num_format => '$#,##0.00');
my $num2_format = $workbook->add_format(num_format => ' d mmmm yyy');
$worksheet->write('A4', "Numbers");
$worksheet->write('B4', 1234.56);
$worksheet->write('A5', "Formatted numbers");
$worksheet->write('B5', 1234.56, $num1_format);
$worksheet->write('A6', "Formatted numbers");
$worksheet->write('B6', 37257, $num2_format);
# Formulae
$worksheet->set_selection('B7');
$worksheet->write('A7', 'Formulas and functions, "=SIN(PI()/4)"');
$worksheet->write('B7', '=SIN(PI()/4)');
# Hyperlinks
my $url_format = $workbook->add_format(
underline => 1,
color => 'blue',
);
$worksheet->write('A8', "Hyperlinks");
$worksheet->write('B8', 'http://www.perl.com/', $url_format);
# Misc
$worksheet->write('A17', "Page/printer setup");
$worksheet->write('A18', "Multiple worksheets");
$workbook->close();
##############################################################################
#
# Re-open and reread the Excel file.
#
open XML, $test_file or die "Couldn't open $test_file: $!\n";
my $swex_data = do {local $/; <XML>};
$swex_data =~ s/[\r\n]+/ /g;
close XML;
unlink $test_file;
##############################################################################
#
# Read the data from the Excel file in the __DATA__ section
#
my $test_data = do {local $/; <DATA>};
$test_data =~ s/[\r\n]+/ /g;
##############################################################################
#
# Run the tests
#
is($swex_data, $test_data, "Regression test");
__DATA__
<?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>
<Style ss:ID="s22">
<Alignment
ss:Horizontal="CenterAcrossSelection"
ss:Vertical="Center"/>
<Font
ss:Size="16"
ss:Color="#0000FF"
ss:Bold="1"/>
</Style>
<Style ss:ID="s23">
<Font
ss:FontName="Lucida Calligraphy"
ss:Size="18"
ss:Color="#FF0000"
ss:Bold="1"
ss:Italic="1"/>
</Style>
<Style ss:ID="s24">
<NumberFormat ss:Format="$#,##0.00"/>
</Style>
<Style ss:ID="s25">
<NumberFormat ss:Format=" d mmmm yyy"/>
</Style>
<Style ss:ID="s26">
<Font
ss:Color="#0000FF"
ss:Underline="Single"/>
</Style>
</Styles>
<Worksheet ss:Name="Demo">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="18">
<Column ss:StyleID="s21" ss:AutoFitWidth="0" ss:Width="255.75"/>
<Column ss:AutoFitWidth="0" ss:Width="108.75"/>
<Row ss:AutoFitHeight="0" ss:Height="39.75">
<Cell ss:StyleID="s22">
<Data ss:Type="String">Features of Spreadsheet::WriteExcelXML</Data>
</Cell>
<Cell ss:StyleID="s22"/>
</Row>
<Row>
<Cell>
<Data ss:Type="String">Text</Data>
</Cell>
<Cell>
<Data ss:Type="String">Hello Excel</Data>
</Cell>
</Row>
<Row>
<Cell>
<Data ss:Type="String">Formatted text</Data>
</Cell>
<Cell ss:StyleID="s23">
<Data ss:Type="String">Hello Excel</Data>
</Cell>
</Row>
<Row>
<Cell>
<Data ss:Type="String">Numbers</Data>
</Cell>
<Cell>
<Data ss:Type="Number">1234.56</Data>
</Cell>
</Row>
<Row>
<Cell>
<Data ss:Type="String">Formatted numbers</Data>
</Cell>
<Cell ss:StyleID="s24">
<Data ss:Type="Number">1234.56</Data>
</Cell>
</Row>
<Row>
<Cell>
<Data ss:Type="String">Formatted numbers</Data>
</Cell>
<Cell ss:StyleID="s25">
<Data ss:Type="Number">37257</Data>
</Cell>
</Row>
<Row>
<Cell>
<Data ss:Type="String">Formulas and functions, "=SIN(PI()/4)"</Data>
</Cell>
<Cell ss:Formula="=SIN(PI()/4)"/>
</Row>
<Row>
<Cell>
<Data ss:Type="String">Hyperlinks</Data>
</Cell>
<Cell ss:StyleID="s26" ss:HRef="http://www.perl.com/">
<Data ss:Type="String">http://www.perl.com/</Data>
</Cell>
</Row>
<Row ss:Index="17">
<Cell>
<Data ss:Type="String">Page/printer setup</Data>
</Cell>
</Row>
<Row>
<Cell>
<Data ss:Type="String">Multiple worksheets</Data>
</Cell>
</Row>
</Table>
</Worksheet>
<Worksheet ss:Name="Another sheet">
</Worksheet>
<Worksheet ss:Name="And another">
</Worksheet>
</Workbook>