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)<=6,FLOOR(A1,1)<=TODAY())</formula> 174 </cfRule> 175 <cfRule type="timePeriod" priority="5" timePeriod="lastWeek"> 176 <formula>AND(TODAY()-ROUNDDOWN(A1,0)>=(WEEKDAY(TODAY())),TODAY()-ROUNDDOWN(A1,0)<(WEEKDAY(TODAY())+7))</formula> 177 </cfRule> 178 <cfRule type="timePeriod" priority="6" timePeriod="thisWeek"> 179 <formula>AND(TODAY()-ROUNDDOWN(A1,0)<=WEEKDAY(TODAY())-1,ROUNDDOWN(A1,0)-TODAY()<=7-WEEKDAY(TODAY()))</formula> 180 </cfRule> 181 <cfRule type="timePeriod" priority="7" timePeriod="nextWeek"> 182 <formula>AND(ROUNDDOWN(A1,0)-TODAY()>(7-WEEKDAY(TODAY())),ROUNDDOWN(A1,0)-TODAY()<(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