#!/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>