#!/usr/bin/perl

use strict;
use warnings;

BEGIN { delete @ENV{qw( LANG LC_ALL LC_DATE )}; }

use Test::More tests => 151;

use_ok ("Spreadsheet::ParseExcel");

my $bk;
ok ($bk = Spreadsheet::ParseExcel::Workbook->Parse ("t/excel_files/Dates.xls"),
    "Open/parse Dates.xls");

ok (my @ws = @{$bk->{Worksheet}},	"Book has sheets");
is (scalar @ws, 1,			"But just one");

my $ws = $ws[0];
ok (my $cells = $ws->{Cells},		"Worksheet has cells");
is ($ws->{Name}, "DateTest",		"Worksheet label");
is ($ws->{MinRow}, 0,			"Row start");
is ($ws->{MinCol}, 0,			"Col start");
is ($ws->{MaxRow}, 6,			"Row count");
is ($ws->{MaxCol}, 4,			"Col count");

my @expect = (# Date   Date      Date          Date          Text
    [ 39668,  "8-Aug", 20080808, "2008-08-08", "08/08/2008", "08 Aug 2008" ],
    [ 39672, "12-Aug", 20080812, "2008-08-12", "08/12/2008", "12 Aug 2008" ],
    [ 39790,  "8-Dec", 20081208, "2008-12-08", "12/08/2008", "08 Dec 2008" ],
    [ 39673, "13-Aug", 20080813, "2008-08-13", "08/13/2008", "13 Aug 2008" ],
    );
#            non given, ISO,     type 14,      US broken,    Text-default
my @format = (undef,  "yyyymmdd", undef,       "mm/dd/yyyy", undef);
my @col = ("A".."E");

foreach my $row (0 .. 3) {
    foreach my $col (0 .. 4) {
	my $R = $row + 1;
	my $C = $col + 1;
	my $cell = ("A".."E")[$col].$R;
	ok (my $wc = $ws->{Cells}[$row][$col], "Cell $cell");
	is ($wc->{Val}, $expect[$row][$col == 4 ? 5 : 0], "Base value for $cell");
	is ($wc->Value, $expect[$row][$C], "Formatted value for $cell");

	is ($wc->{Type}, $col == 4 ? "Text" : "Date", "Cell $cell Type");
	my $fmt    = $wc->{Format};
	my $fmtstr = $bk->{FormatStr}{$fmt->{FmtIdx}};
	$fmtstr and $fmtstr =~ s/\\//g;
	is ($fmtstr, $format[$col], "Format string");
	}
    }

# Additional allignment tests
is ($ws->{Cells}[0][$_]{Format}{AlignV}, 3, "$col[$_]1 v-aligned justified") for 1..3;
is ($ws->{Cells}[1][$_]{Format}{AlignV}, 0, "$col[$_]2 v-aligned top")       for 1..3;
is ($ws->{Cells}[2][$_]{Format}{AlignV}, 1, "$col[$_]3 v-aligned center")    for 1..3;
is ($ws->{Cells}[3][$_]{Format}{AlignV}, 2, "$col[$_]4 v-aligned bottom")    for 1..3;

is ($ws->{Cells}[$_][0]{Format}{AlignH}, 0, "A$_ h-aligned -")       for 1..3;
is ($ws->{Cells}[$_][1]{Format}{AlignH}, 1, "B$_ h-aligned left")    for 1..3;
is ($ws->{Cells}[$_][2]{Format}{AlignH}, 2, "C$_ h-aligned center")  for 1..3;
is ($ws->{Cells}[$_][3]{Format}{AlignH}, 3, "D$_ h-aligned right")   for 1..3;

# Additional color tests
is ($ws->{Cells}[0][$_]{Format}{Font}{Color}, 32767, "$col[$_]1 font color") for 0..3;
is ($ws->{Cells}[0][ 4]{Format}{Font}{Color},    18, "E1 font color");

my %expect = (
    B7 => [ 1, 6, 39673, "ddd, dd mmm yyyy", "Wed, 13 Aug 2008" ],
    C7 => [ 2, 6, 39673, "m-d-yy",           "8-13-08"          ],
    );
# Test ddd, dd MM yyyy
foreach my $cell ("B7", "C7") {
    my ($c, $r) = @{$expect{$cell}}[0,1];
    ok (my $wc     = $ws->{Cells}[$r][$c],             "Cell   $cell");
    ok (my $fmt    = $wc->{Format},                    "Format $cell");
    ok (my $fmtstr = $bk->{FormatStr}{$fmt->{FmtIdx}}, "FmtStr $cell");
	   $fmtstr and $fmtstr =~ s{\\}{}g;	# Unescape for test
	   $fmtstr and $fmtstr =~ s{/}{-}g and
	       $expect{$cell}[4] =~ s{-}{/}g;	# System locale's deate-sep is used :(

    is ($wc->{Val}, $expect{$cell}[2], "$cell value");
    is ($fmtstr,    $expect{$cell}[3], "$cell Format string");
    is ($wc->Value, $expect{$cell}[4], "$cell formatted value");
    }