#!/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 bold');
$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 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/]) {
$in_comment = 0;
$comment =~ s{\s+}{ }g;
$comment =~ s{.*?]+>}{};
$comment =~ s{.*}{};
$comment =~ s{]+>}{}g;
$comment =~ s{}{}g;
push @comments, $comment;
$comment = '';
}
}
return @comments;
}
# The following data was generated by Excel.
__DATA__
| 12345Number |
| HelloString |
| 2005-05-01T00:00:00.000Date |
| 6Formula |
| http://www.perl.comHyperlink |
| Some boldHtml string |
| Blank |
| MergeMerge |
600
600
3
13
1
False
False