#!/usr/bin/perl -w
###############################################################################
#
# A test for Spreadsheet::WriteExcel.
#
# Tests for the internal methods used to write the AUTOFILTER record.
#
# reverse('©'), August 2007, John McNamara, jmcnamara@cpan.org
#
use strict;
use Spreadsheet::WriteExcel;
use Test::More tests => 29;
###############################################################################
#
# Tests setup
#
my $test_file = "temp_test_file.xls";
my $workbook = Spreadsheet::WriteExcel->new($test_file);
my $worksheet = $workbook->add_worksheet();
###############################################################################
#
# Test cases.
#
my @tests = (
{
'column' => 0,
'expression' => 'x = Blanks',
'data' => [qw(
9E 00 18 00 00 00 84 32 0C 02 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00
)],
},
{
'column' => 1,
'expression' => 'x = Nonblanks',
'data' => [qw(
9E 00 18 00 01 00 84 32 0E 05 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00
)],
},
{
'column' => 2,
'expression' => 'x > 1.001',
'data' => [qw(
9E 00 18 00 02 00 80 32 04 04 6A BC 74 93 18 04
F0 3F 00 00 00 00 00 00 00 00 00 00
)],
},
{
'column' => 3,
'expression' => 'x >= 1.001',
'data' => [qw(
9E 00 18 00 03 00 80 32 04 06 6A BC 74 93 18 04
F0 3F 00 00 00 00 00 00 00 00 00 00
)],
},
{
'column' => 4,
'expression' => 'x < 1.001',
'data' => [qw(
9E 00 18 00 04 00 80 32 04 01 6A BC 74 93 18 04
F0 3F 00 00 00 00 00 00 00 00 00 00
)],
},
{
'column' => 5,
'expression' => 'x <= 1.001',
'data' => [qw(
9E 00 18 00 05 00 80 32 04 03 6A BC 74 93 18 04
F0 3F 00 00 00 00 00 00 00 00 00 00
)],
},
{
'column' => 6,
'expression' => 'x > 1.001 and x <= 5.001',
'data' => [qw(
9E 00 18 00 06 00 80 32 04 04 6A BC 74 93 18 04
F0 3F 04 03 1B 2F DD 24 06 01 14 40
)],
},
{
'column' => 7,
'expression' => 'x > 1.001 or x <= 5.001',
'data' => [qw(
9E 00 18 00 07 00 81 32 04 04 6A BC 74 93 18 04
F0 3F 04 03 1B 2F DD 24 06 01 14 40
)],
},
{
'column' => 8,
'expression' => 'x <> 2.001',
'data' => [qw(
9E 00 18 00 08 00 80 32 04 05 35 5E BA 49 0C 02
00 40 00 00 00 00 00 00 00 00 00 00
)],
},
{
'column' => 9,
'expression' => 'x = 1.001',
'data' => [qw(
9E 00 1E 00 09 00 84 32 06 02 00 00 00 00 05 00
00 00 00 00 00 00 00 00 00 00 00 00 00 31 2E 30
30 31
)],
},
{
'column' => 10,
'expression' => 'x = West',
'data' => [qw(
9E 00 1D 00 0A 00 84 32 06 02 00 00 00 00 04 00
00 00 00 00 00 00 00 00 00 00 00 00 00 57 65 73
74
)],
},
{
'column' => 11,
'expression' => 'x = East',
'data' => [qw(
9E 00 1D 00 0B 00 84 32 06 02 00 00 00 00 04 00
00 00 00 00 00 00 00 00 00 00 00 00 00 45 61 73
74
)],
},
{
'column' => 12,
'expression' => 'x <> West',
'data' => [qw(
9E 00 1D 00 0C 00 80 32 06 05 00 00 00 00 04 00
00 00 00 00 00 00 00 00 00 00 00 00 00 57 65 73
74
)],
},
{
'column' => 13,
'expression' => 'x =~ b*',
'data' => [qw(
9E 00 1B 00 0D 00 80 32 06 02 00 00 00 00 02 00
00 00 00 00 00 00 00 00 00 00 00 00 00 62 2A
)],
},
{
'column' => 14,
'expression' => 'x !~ b*',
'data' => [qw(
9E 00 1B 00 0E 00 80 32 06 05 00 00 00 00 02 00
00 00 00 00 00 00 00 00 00 00 00 00 00 62 2A
)],
},
{
'column' => 15,
'expression' => 'x =~ *b',
'data' => [qw(
9E 00 1B 00 0F 00 80 32 06 02 00 00 00 00 02 00
00 00 00 00 00 00 00 00 00 00 00 00 00 2A 62
)],
},
{
'column' => 16,
'expression' => 'x !~ *b',
'data' => [qw(
9E 00 1B 00 10 00 80 32 06 05 00 00 00 00 02 00
00 00 00 00 00 00 00 00 00 00 00 00 00 2A 62
)],
},
{
'column' => 17,
'expression' => 'x =~ *b*',
'data' => [qw(
9E 00 1C 00 11 00 80 32 06 02 00 00 00 00 03 00
00 00 00 00 00 00 00 00 00 00 00 00 00 2A 62 2A
)],
},
{
'column' => 18,
'expression' => 'x !~ *b*',
'data' => [qw(
9E 00 1C 00 12 00 80 32 06 05 00 00 00 00 03 00
00 00 00 00 00 00 00 00 00 00 00 00 00 2A 62 2A
)],
},
{
'column' => 19,
'expression' => 'x = fo?',
'data' => [qw(
9E 00 1C 00 13 00 80 32 06 02 00 00 00 00 03 00
00 00 00 00 00 00 00 00 00 00 00 00 00 66 6F 3F
)],
},
{
'column' => 20,
'expression' => 'x = fo~?',
'data' => [qw(
9E 00 1D 00 14 00 80 32 06 02 00 00 00 00 04 00
00 00 00 00 00 00 00 00 00 00 00 00 00 66 6F 7E
3F
)],
},
{
'column' => 21,
'expression' => 'x = East and x = West',
'data' => [qw(
9E 00 22 00 15 00 8C 32 06 02 00 00 00 00 04 00
00 00 06 02 00 00 00 00 04 00 00 00 00 45 61 73
74 00 57 65 73 74
)],
},
{
'column' => 22,
'expression' => 'top 10 items',
'data' => [qw(
9E 00 18 00 16 00 30 05 04 06 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00
)],
},
{
'column' => 23,
'expression' => 'top 10 %',
'data' => [qw(
9E 00 18 00 17 00 70 05 04 06 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00
)],
},
{
'column' => 24,
'expression' => 'bottom 10 items',
'data' => [qw(
9E 00 18 00 18 00 10 05 04 03 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00
)],
},
{
'column' => 25,
'expression' => 'bottom 10 %',
'data' => [qw(
9E 00 18 00 19 00 50 05 04 03 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00
)],
},
{
'column' => 26,
'expression' => 'top 5 items',
'data' => [qw(
9E 00 18 00 1A 00 B0 02 04 06 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00
)],
},
{
'column' => 27,
'expression' => 'top 100 items',
'data' => [qw(
9E 00 18 00 1B 00 30 32 04 06 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00
)],
},
{
'column' => 28,
'expression' => 'top 101 items',
'data' => [qw(
9E 00 18 00 1C 00 B0 32 04 06 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00
)],
},
);
###############################################################################
#
# Run tests.
#
for my $test (@tests) {
my $column = $test->{column};
my $expression = $test->{expression};
my @tokens = $worksheet->_extract_filter_tokens($expression);
@tokens = $worksheet->_parse_filter_expression($expression, @tokens);
my $result = $worksheet->_store_autofilter($column , @tokens);
my $target = join " ", @{$test->{data}};
my $caption = " \tfilter_column($column, '$expression')";
$result = unpack_record($result);
is($result, $target, $caption);
}
#
# Helper functions.
#
###############################################################################
#
# Unpack the binary data into a format suitable for printing in tests.
#
sub unpack_record {
return join ' ', map {sprintf "%02X", $_} unpack "C*", $_[0];
}
# Cleanup
$workbook->close();
unlink $test_file;
__END__