1###############################################################################
2#
3# Tests for Excel::Writer::XLSX::Worksheet methods.
4#
5# Copyright 2000-2021, John McNamara, jmcnamara@cpan.org
6#
7
8use lib 't/lib';
9use TestFunctions qw(_expected_to_aref _got_to_aref _is_deep_diff _new_worksheet);
10use strict;
11use warnings;
12
13use Test::More tests => 1;
14
15###############################################################################
16#
17# Tests setup.
18#
19my $expected;
20my $got;
21my $caption;
22my $worksheet;
23
24
25###############################################################################
26#
27# Test the _assemble_xml_file() method.
28#
29# Test conditional formats.
30#
31$caption = " \tWorksheet: _assemble_xml_file()";
32
33$worksheet = _new_worksheet(\$got);
34
35$worksheet->select();
36
37# Start test code.
38$worksheet->write( 'A1', 10 );
39$worksheet->write( 'A2', 20 );
40$worksheet->write( 'A3', 30 );
41$worksheet->write( 'A4', 40 );
42
43$worksheet->conditional_formatting( 'A1:A4',
44    {
45        type     => 'time_period',
46        criteria => 'yesterday',
47        format   => undef,
48    }
49);
50
51$worksheet->conditional_formatting( 'A1:A4',
52    {
53        type     => 'time_period',
54        criteria => 'today',
55        format   => undef,
56    }
57);
58
59$worksheet->conditional_formatting( 'A1:A4',
60    {
61        type     => 'time_period',
62        criteria => 'tomorrow',
63        format   => undef,
64    }
65);
66
67$worksheet->conditional_formatting( 'A1:A4',
68    {
69        type     => 'time_period',
70        criteria => 'last 7 days',
71        format   => undef,
72    }
73);
74
75$worksheet->conditional_formatting( 'A1:A4',
76    {
77        type     => 'time_period',
78        criteria => 'last week',
79        format   => undef,
80    }
81);
82
83$worksheet->conditional_formatting( 'A1:A4',
84    {
85        type     => 'time_period',
86        criteria => 'this week',
87        format   => undef,
88    }
89);
90
91$worksheet->conditional_formatting( 'A1:A4',
92    {
93        type     => 'time_period',
94        criteria => 'next week',
95        format   => undef,
96    }
97);
98
99$worksheet->conditional_formatting( 'A1:A4',
100    {
101        type     => 'time_period',
102        criteria => 'last month',
103        format   => undef,
104    }
105);
106
107$worksheet->conditional_formatting( 'A1:A4',
108    {
109        type     => 'time_period',
110        criteria => 'this month',
111        format   => undef,
112    }
113);
114
115$worksheet->conditional_formatting( 'A1:A4',
116    {
117        type     => 'time_period',
118        criteria => 'next month',
119        format   => undef,
120    }
121);
122
123# End test code.
124
125$worksheet->_assemble_xml_file();
126
127$expected = _expected_to_aref();
128$got      = _got_to_aref( $got );
129
130_is_deep_diff( $got, $expected, $caption );
131
132__DATA__
133<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
134<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
135  <dimension ref="A1:A4"/>
136  <sheetViews>
137    <sheetView tabSelected="1" workbookViewId="0"/>
138  </sheetViews>
139  <sheetFormatPr defaultRowHeight="15"/>
140  <sheetData>
141    <row r="1" spans="1:1">
142      <c r="A1">
143        <v>10</v>
144      </c>
145    </row>
146    <row r="2" spans="1:1">
147      <c r="A2">
148        <v>20</v>
149      </c>
150    </row>
151    <row r="3" spans="1:1">
152      <c r="A3">
153        <v>30</v>
154      </c>
155    </row>
156    <row r="4" spans="1:1">
157      <c r="A4">
158        <v>40</v>
159      </c>
160    </row>
161  </sheetData>
162  <conditionalFormatting sqref="A1:A4">
163    <cfRule type="timePeriod" priority="1" timePeriod="yesterday">
164      <formula>FLOOR(A1,1)=TODAY()-1</formula>
165    </cfRule>
166    <cfRule type="timePeriod" priority="2" timePeriod="today">
167      <formula>FLOOR(A1,1)=TODAY()</formula>
168    </cfRule>
169    <cfRule type="timePeriod" priority="3" timePeriod="tomorrow">
170      <formula>FLOOR(A1,1)=TODAY()+1</formula>
171    </cfRule>
172    <cfRule type="timePeriod" priority="4" timePeriod="last7Days">
173      <formula>AND(TODAY()-FLOOR(A1,1)&lt;=6,FLOOR(A1,1)&lt;=TODAY())</formula>
174    </cfRule>
175    <cfRule type="timePeriod" priority="5" timePeriod="lastWeek">
176      <formula>AND(TODAY()-ROUNDDOWN(A1,0)&gt;=(WEEKDAY(TODAY())),TODAY()-ROUNDDOWN(A1,0)&lt;(WEEKDAY(TODAY())+7))</formula>
177    </cfRule>
178    <cfRule type="timePeriod" priority="6" timePeriod="thisWeek">
179      <formula>AND(TODAY()-ROUNDDOWN(A1,0)&lt;=WEEKDAY(TODAY())-1,ROUNDDOWN(A1,0)-TODAY()&lt;=7-WEEKDAY(TODAY()))</formula>
180    </cfRule>
181    <cfRule type="timePeriod" priority="7" timePeriod="nextWeek">
182      <formula>AND(ROUNDDOWN(A1,0)-TODAY()&gt;(7-WEEKDAY(TODAY())),ROUNDDOWN(A1,0)-TODAY()&lt;(15-WEEKDAY(TODAY())))</formula>
183    </cfRule>
184    <cfRule type="timePeriod" priority="8" timePeriod="lastMonth">
185      <formula>AND(MONTH(A1)=MONTH(TODAY())-1,OR(YEAR(A1)=YEAR(TODAY()),AND(MONTH(A1)=1,YEAR(A1)=YEAR(TODAY())-1)))</formula>
186    </cfRule>
187    <cfRule type="timePeriod" priority="9" timePeriod="thisMonth">
188      <formula>AND(MONTH(A1)=MONTH(TODAY()),YEAR(A1)=YEAR(TODAY()))</formula>
189    </cfRule>
190    <cfRule type="timePeriod" priority="10" timePeriod="nextMonth">
191      <formula>AND(MONTH(A1)=MONTH(TODAY())+1,OR(YEAR(A1)=YEAR(TODAY()),AND(MONTH(A1)=12,YEAR(A1)=YEAR(TODAY())+1)))</formula>
192    </cfRule>
193  </conditionalFormatting>
194  <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
195</worksheet>
196