#!/usr/bin/perl -w

###############################################################################
#
# A test for Spreadsheet::WriteExcelXML.
#
# Tests cell comments.
#
# reverse('©'), April 2005, John McNamara, jmcnamara@cpan.org
#


use strict;
use Spreadsheet::WriteExcelXML;
use Test::More tests => 9;



##############################################################################
#
# 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();


my $merge_format = $workbook->add_format();
my $date_format  = $workbook->add_format(num_format => 'dd/mm/yyyy');

$worksheet->set_column('B:C', 18);

$worksheet->write            ('B2',     12345);
$worksheet->write            ('B3',     'Hello');
$worksheet->write_date_time  ('B4',     '2005-05-01T', $date_format);
$worksheet->write            ('B5',     '=1+2+3');
$worksheet->write            ('B6',     'http://www.perl.com');
$worksheet->write_html_string('B7',     'Some <B>bold</B>');
$worksheet->write            ('B8',     '');
$worksheet->merge_range      ('B9:C9',  'Merge', $merge_format);


$worksheet->write_comment    ('B2',     'Number');
$worksheet->write_comment    ('B3',     'String');
$worksheet->write_comment    ('B4',     'Date');
$worksheet->write_comment    ('B5',     'Formula');
$worksheet->write_comment    ('B6',     'Hyperlink');
$worksheet->write_comment    ('B7',     'Html string');
$worksheet->write_comment    ('B8',     'Blank');
$worksheet->write_comment    ('B9',     'Merge');



$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);

my $cell = "B2";

for my $i (0 .. @test_data -1) {
    is($swex_data[$i],$test_data[$i], " \tTesting cell comments: " . $cell++);

}



##############################################################################
#
# Extract <Comment> elements from a given filehandle.
#
sub extract_cells {

    my $fh          = $_[0];
    my $in_comment  = 0;
    my $comment     = '';
    my @comments;

    while (<$fh>) {
        s/^\s+([<| ])/$1/;
        s/\s+$//;

        if (m/<Comment/) {
            $in_comment = 1;
            $comment    = '';
        }

        $comment .= $_ if $in_comment;

        if (m[/Comment>]) {
            $in_comment = 0;
            $comment    =~ s{\s+}{ }g;
            $comment    =~ s{.*?<Comment[^>]+>}{<Comment>};
            $comment    =~ s{</Comment>.*}{};
            $comment    =~ s{<Font[^>]+>}{}g;
            $comment    =~ s{</Font>}{}g;
            push @comments, $comment;
            $comment    = '';
        }
    }

    return @comments;
}


# 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>
  <Style ss:ID="s23" ss:Name="Hyperlink">
   <Font ss:Color="#0000FF" ss:Underline="Single"/>
  </Style>
  <Style ss:ID="s29">
   <Alignment ss:Vertical="Bottom"/>
  </Style>
  <Style ss:ID="s30">
   <Font x:Family="Swiss"/>
   <Interior/>
  </Style>
  <Style ss:ID="s31">
   <NumberFormat ss:Format="dd/mm/yyyy;@"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="9" x:FullColumns="1"
   x:FullRows="1">
   <Column ss:Index="2" ss:AutoFitWidth="0" ss:Width="93.75" ss:Span="1"/>
   <Row ss:Index="2">
    <Cell ss:Index="2"><Data ss:Type="Number">12345</Data><Comment
      ss:Author="WriteExcel"><ss:Data xmlns="http://www.w3.org/TR/REC-html40"><Font
        html:Face="Tahoma" x:Family="Swiss" html:Size="8" html:Color="#000000">Number</Font></ss:Data></Comment></Cell>
   </Row>
   <Row>
    <Cell ss:Index="2"><Data ss:Type="String">Hello</Data><Comment
      ss:Author="WriteExcel"><ss:Data xmlns="http://www.w3.org/TR/REC-html40"><Font
        html:Face="Tahoma" x:Family="Swiss" html:Size="8" html:Color="#000000">String</Font></ss:Data></Comment></Cell>
   </Row>
   <Row>
    <Cell ss:Index="2" ss:StyleID="s31"><Data ss:Type="DateTime">2005-05-01T00:00:00.000</Data><Comment
      ss:Author="WriteExcel"><ss:Data xmlns="http://www.w3.org/TR/REC-html40"><Font
        html:Face="Tahoma" x:Family="Swiss" html:Size="8" html:Color="#000000">Date</Font></ss:Data></Comment></Cell>
   </Row>
   <Row>
    <Cell ss:Index="2" ss:Formula="=1+2+3"><Data ss:Type="Number">6</Data><Comment
      ss:Author="WriteExcel"><ss:Data xmlns="http://www.w3.org/TR/REC-html40"><Font
        html:Face="Tahoma" x:Family="Swiss" html:Size="8" html:Color="#000000">Formula</Font></ss:Data></Comment></Cell>
   </Row>
   <Row>
    <Cell ss:Index="2" ss:StyleID="s23" ss:HRef="http://www.perl.com/"><Data
      ss:Type="String">http://www.perl.com</Data><Comment ss:Author="WriteExcel"><ss:Data
       xmlns="http://www.w3.org/TR/REC-html40"><Font html:Face="Tahoma"
        x:Family="Swiss" html:Size="8" html:Color="#000000">Hyperlink</Font></ss:Data></Comment></Cell>
   </Row>
   <Row>
    <Cell ss:Index="2"><ss:Data ss:Type="String"
      xmlns="http://www.w3.org/TR/REC-html40">Some <B>bold</B></ss:Data><Comment
      ss:Author="WriteExcel"><ss:Data xmlns="http://www.w3.org/TR/REC-html40"><Font
        html:Face="Tahoma" x:Family="Swiss" html:Size="8" html:Color="#000000">Html string</Font></ss:Data></Comment></Cell>
   </Row>
   <Row>
    <Cell ss:Index="2" ss:StyleID="s30"><Comment ss:Author="WriteExcel"><ss:Data
       xmlns="http://www.w3.org/TR/REC-html40"><Font html:Face="Tahoma"
        x:Family="Swiss" html:Size="8" html:Color="#000000">Blank</Font></ss:Data></Comment></Cell>
   </Row>
   <Row>
    <Cell ss:Index="2" ss:MergeAcross="1" ss:StyleID="s29"><Data ss:Type="String">Merge</Data><Comment
      ss:Author="WriteExcel"><ss:Data xmlns="http://www.w3.org/TR/REC-html40"><Font
        html:Face="Tahoma" x:Family="Swiss" html:Size="8" html:Color="#000000">Merge</Font></ss:Data></Comment></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Print>
    <ValidPrinterInfo/>
    <HorizontalResolution>600</HorizontalResolution>
    <VerticalResolution>600</VerticalResolution>
   </Print>
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>13</ActiveRow>
     <ActiveCol>1</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>