#!/usr/bin/perl -w
###############################################################################
#
# A test for Spreadsheet::WriteExcelXML.
#
# Tests array formulas.
#
# reverse('©'), July 2004, John McNamara, jmcnamara@cpan.org
#
use strict;
use Spreadsheet::WriteExcelXML;
use Test::More tests => 6;
##############################################################################
#
# Create a new Excel XML file with row data set.
#
my $test_file = "temp_test_file.xml";
my $workbook = Spreadsheet::WriteExcelXML->new($test_file);
my $worksheet = $workbook->add_worksheet();
$worksheet->write('A1', [[500, 10], [300, 15]]);
$worksheet->write('A3', '{=SUM(A1:B1*A2:B2)}');
$worksheet->write_array_formula('A4:A4', '{=SUM(A1:B1*A2:B2)}');
$worksheet->write('A6', [[1, 2, 3], [20234, 21003, 10000]]);
$worksheet->write_array_formula('C6:C8', '{=TREND(B6:B8,A6:A8)}');
$worksheet->write_array_formula('D6:D8', '=TREND(B6:B8,A6:A8)' );
$worksheet->write_array_formula('E6:E8', 'TREND(B6:B8,A6:A8)' );
$workbook->close();
##############################################################################
#
# Re-open and reread the Excel file.
#
open XML, $test_file or die "Couldn't open $test_file: $!\n";
my @swex_data = extract_cells(*XML);
close XML;
unlink $test_file;
##############################################################################
#
# Read the data from the Excel file in the __DATA__ section
#
my @test_data = extract_cells(*DATA);
##############################################################################
#
# Check for the same number of elements.
#
is(@swex_data, @test_data, " \tCheck for data size");
##############################################################################
#
# Test that the SWEX elements and Excel are the same.
#
# Pad the SWEX data if necessary.
push @swex_data, ('') x (@test_data -@swex_data);
for my $i (0 .. @test_data -1) {
is($swex_data[$i],$test_data[$i], " \tTesting ss:ArrayRange attribute");
}
##############################################################################
#
# Extract <Cell> elements from a given filehandle.
#
sub extract_cells {
my $fh = $_[0];
my $in_cell = 0;
my $cell = '';
my @cells;
while (<$fh>) {
s/^\s+([<| ])/$1/;
s/\s+$//;
if (m/<Cell/) {
$in_cell = 1;
$cell = '';
}
$cell .= $_ if $in_cell;
if (m[/]) {
$in_cell = 0;
next unless $cell =~ /ArrayRange/;
$cell =~ s{>.*}{>};
$cell =~ s{/>$}{>};
push @cells, $cell;
$cell = '';
}
}
return @cells;
}
# The following data was generated by Excel.
__DATA__
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="8" x:FullColumns="1"
x:FullRows="1">
<Row>
<Cell><Data ss:Type="Number">500</Data></Cell>
<Cell><Data ss:Type="Number">300</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number">10</Data></Cell>
<Cell><Data ss:Type="Number">15</Data></Cell>
</Row>
<Row>
<Cell ss:ArrayRange="RC" ss:Formula="=SUM(R[-2]C:R[-2]C[1]*R[-1]C:R[-1]C[1])"><Data
ss:Type="Number">9500</Data></Cell>
</Row>
<Row>
<Cell ss:ArrayRange="RC" ss:Formula="=SUM(R[-3]C:R[-3]C[1]*R[-2]C:R[-2]C[1])"><Data
ss:Type="Number">9500</Data></Cell>
</Row>
<Row ss:Index="6">
<Cell><Data ss:Type="Number">1</Data></Cell>
<Cell><Data ss:Type="Number">20234</Data></Cell>
<Cell ss:ArrayRange="RC:R[2]C"
ss:Formula="=TREND(RC[-1]:R[2]C[-1],RC[-2]:R[2]C[-2])"><Data ss:Type="Number">22196</Data></Cell>
<Cell ss:ArrayRange="RC:R[2]C"
ss:Formula="=TREND(RC[-2]:R[2]C[-2],RC[-3]:R[2]C[-3])"><Data ss:Type="Number">22196</Data></Cell>
<Cell ss:ArrayRange="RC:R[2]C"
ss:Formula="=TREND(RC[-3]:R[2]C[-3],RC[-4]:R[2]C[-4])"><Data ss:Type="Number">22196</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number">2</Data></Cell>
<Cell><Data ss:Type="Number">21003</Data></Cell>
<Cell><Data ss:Type="Number">17079</Data></Cell>
<Cell><Data ss:Type="Number">17079</Data></Cell>
<Cell><Data ss:Type="Number">17079</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number">3</Data></Cell>
<Cell><Data ss:Type="Number">10000</Data></Cell>
<Cell><Data ss:Type="Number">11961.999999999996</Data></Cell>
<Cell><Data ss:Type="Number">11961.999999999996</Data></Cell>
<Cell><Data ss:Type="Number">11961.999999999996</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>7</ActiveRow>
<ActiveCol>4</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>