1#!/usr/bin/perl -w 2 3############################################################################### 4# 5# A test for Spreadsheet::WriteExcelXML. 6# 7# Tests the print_area method. 8# 9# reverse('�'), November 2004, John McNamara, jmcnamara@cpan.org 10# 11 12 13use strict; 14use Spreadsheet::WriteExcelXML; 15use Test::More tests => 6; 16 17 18############################################################################## 19# 20# Create a new Excel XML file with different formats on each page. 21# 22my $test_file = "temp_test_file.xml"; 23my $workbook = Spreadsheet::WriteExcelXML->new($test_file); 24 25# Test with older cell limits. 26$workbook->use_lower_cell_limits(); 27 28# We use 'Sheet n' worksheet names so that they are single quoted in the 29# Excel XML output. 30 31my $worksheet1 = $workbook->add_worksheet('Sheet 1'); 32 $worksheet1->print_area("A1:A1"); 33 34my $worksheet2 = $workbook->add_worksheet('Sheet 2'); 35 $worksheet2->print_area("A1:A2"); 36 37my $worksheet3 = $workbook->add_worksheet('Sheet 3'); 38 $worksheet3->print_area("A1:B1"); 39 40my $worksheet4 = $workbook->add_worksheet('Sheet 4'); 41 $worksheet4->print_area("A1:B5"); 42 43my $worksheet5 = $workbook->add_worksheet('Sheet 5'); 44 $worksheet5->print_area("A:A"); 45 46my $worksheet6 = $workbook->add_worksheet('Sheet 6'); 47 $worksheet6->print_area(0,0,0,255); 48 49# Should be ignored 50my $worksheet7 = $workbook->add_worksheet('Sheet 7'); 51 $worksheet7->print_area("A1:IV65536"); 52 53# Should be ignored 54my $worksheet8 = $workbook->add_worksheet('Sheet 8'); 55 $worksheet8->print_area("A1"); 56 57 58$workbook->close(); 59 60 61############################################################################## 62# 63# Re-open and reread the Excel file. 64# 65open XML, $test_file or die "Couldn't open $test_file: $!\n"; 66my @swex_data = extract_names(*XML); 67close XML; 68unlink $test_file; 69 70 71############################################################################## 72# 73# Read the data from the Excel file in the __DATA__ section 74# 75my @test_data = extract_names(*DATA); 76 77 78 79############################################################################## 80# 81# Pad the SWEX and test data if necessary. 82# 83 84push @swex_data, ('') x (@test_data -@swex_data); 85push @test_data, ('') x (@swex_data -@test_data); 86 87 88############################################################################## 89# 90# Run the tests 91# 92for my $i (0 .. @test_data -1) { 93 is($swex_data[$i], $test_data[$i], "Testing print_area()"); 94 95} 96 97 98 99############################################################################## 100# 101# Extract <Name> sub-elements from a given filehandle. 102# 103sub extract_names { 104 105 my $fh = $_[0]; 106 my $in_elem = 0; 107 my $element = ''; 108 my @elements; 109 110 while (<$fh>) { 111 s/^\s+([<| ])/$1/; 112 s/\s+$//; 113 114 $in_elem = 1 if (m[^<Names] .. m[^</Names]); 115 116 $element .= $_ if $in_elem and not m[</?Names]; 117 118 if (m[^</Names>]) { 119 push @elements, $element; 120 $in_elem = 0; 121 $element = ''; 122 } 123 } 124 return @elements; 125} 126 127 128# The following data was generated by Excel. 129__DATA__ 130<?xml version="1.0"?> 131<?mso-application progid="Excel.Sheet"?> 132<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" 133 xmlns:o="urn:schemas-microsoft-com:office:office" 134 xmlns:x="urn:schemas-microsoft-com:office:excel" 135 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" 136 xmlns:html="http://www.w3.org/TR/REC-html40"> 137 <Styles> 138 <Style ss:ID="Default" ss:Name="Normal"> 139 <Alignment ss:Vertical="Bottom"/> 140 <Borders/> 141 <Font/> 142 <Interior/> 143 <NumberFormat/> 144 <Protection/> 145 </Style> 146 </Styles> 147 <Worksheet ss:Name="Sheet 1"> 148 <Names> 149 <NamedRange ss:Name="Print_Area" ss:RefersTo="='Sheet 1'!R1C1"/> 150 </Names> 151 <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> 152 <Print> 153 <ValidPrinterInfo/> 154 <PaperSizeIndex>9</PaperSizeIndex> 155 <HorizontalResolution>-3</HorizontalResolution> 156 <VerticalResolution>0</VerticalResolution> 157 </Print> 158 <Selected/> 159 <ProtectObjects>False</ProtectObjects> 160 <ProtectScenarios>False</ProtectScenarios> 161 </WorksheetOptions> 162 </Worksheet> 163 <Worksheet ss:Name="Sheet 2"> 164 <Names> 165 <NamedRange ss:Name="Print_Area" ss:RefersTo="='Sheet 2'!R1C1:R2C1"/> 166 </Names> 167 <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> 168 <Print> 169 <ValidPrinterInfo/> 170 <PaperSizeIndex>9</PaperSizeIndex> 171 <HorizontalResolution>-3</HorizontalResolution> 172 <VerticalResolution>0</VerticalResolution> 173 </Print> 174 <Panes> 175 <Pane> 176 <Number>3</Number> 177 <RangeSelection>R1C1:R2C1</RangeSelection> 178 </Pane> 179 </Panes> 180 <ProtectObjects>False</ProtectObjects> 181 <ProtectScenarios>False</ProtectScenarios> 182 </WorksheetOptions> 183 </Worksheet> 184 <Worksheet ss:Name="Sheet 3"> 185 <Names> 186 <NamedRange ss:Name="Print_Area" ss:RefersTo="='Sheet 3'!R1C1:R1C2"/> 187 </Names> 188 <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> 189 <Print> 190 <ValidPrinterInfo/> 191 <PaperSizeIndex>9</PaperSizeIndex> 192 <HorizontalResolution>-3</HorizontalResolution> 193 <VerticalResolution>0</VerticalResolution> 194 </Print> 195 <Panes> 196 <Pane> 197 <Number>3</Number> 198 <RangeSelection>R1C1:R1C2</RangeSelection> 199 </Pane> 200 </Panes> 201 <ProtectObjects>False</ProtectObjects> 202 <ProtectScenarios>False</ProtectScenarios> 203 </WorksheetOptions> 204 </Worksheet> 205 <Worksheet ss:Name="Sheet 4"> 206 <Names> 207 <NamedRange ss:Name="Print_Area" ss:RefersTo="='Sheet 4'!R1C1:R5C2"/> 208 </Names> 209 <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> 210 <Print> 211 <ValidPrinterInfo/> 212 <PaperSizeIndex>9</PaperSizeIndex> 213 <HorizontalResolution>-3</HorizontalResolution> 214 <VerticalResolution>0</VerticalResolution> 215 </Print> 216 <Panes> 217 <Pane> 218 <Number>3</Number> 219 <RangeSelection>R1C1:R5C2</RangeSelection> 220 </Pane> 221 </Panes> 222 <ProtectObjects>False</ProtectObjects> 223 <ProtectScenarios>False</ProtectScenarios> 224 </WorksheetOptions> 225 </Worksheet> 226 <Worksheet ss:Name="Sheet 5"> 227 <Names> 228 <NamedRange ss:Name="Print_Area" ss:RefersTo="='Sheet 5'!C1"/> 229 </Names> 230 <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> 231 <Print> 232 <ValidPrinterInfo/> 233 <PaperSizeIndex>9</PaperSizeIndex> 234 <HorizontalResolution>-3</HorizontalResolution> 235 <VerticalResolution>0</VerticalResolution> 236 </Print> 237 <Panes> 238 <Pane> 239 <Number>3</Number> 240 <ActiveRow>28</ActiveRow> 241 <ActiveCol>5</ActiveCol> 242 </Pane> 243 </Panes> 244 <ProtectObjects>False</ProtectObjects> 245 <ProtectScenarios>False</ProtectScenarios> 246 </WorksheetOptions> 247 </Worksheet> 248 <Worksheet ss:Name="Sheet 6"> 249 <Names> 250 <NamedRange ss:Name="Print_Area" ss:RefersTo="='Sheet 6'!R1"/> 251 </Names> 252 <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> 253 <Print> 254 <ValidPrinterInfo/> 255 <PaperSizeIndex>9</PaperSizeIndex> 256 <HorizontalResolution>-3</HorizontalResolution> 257 <VerticalResolution>0</VerticalResolution> 258 </Print> 259 <ProtectObjects>False</ProtectObjects> 260 <ProtectScenarios>False</ProtectScenarios> 261 </WorksheetOptions> 262 </Worksheet> 263 <Worksheet ss:Name="Sheet 7"> 264 <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> 265 <Print> 266 <ValidPrinterInfo/> 267 <PaperSizeIndex>9</PaperSizeIndex> 268 <HorizontalResolution>-3</HorizontalResolution> 269 <VerticalResolution>0</VerticalResolution> 270 </Print> 271 <ProtectObjects>False</ProtectObjects> 272 <ProtectScenarios>False</ProtectScenarios> 273 </WorksheetOptions> 274 </Worksheet> 275 <Worksheet ss:Name="Sheet 8"> 276 <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> 277 <ProtectObjects>False</ProtectObjects> 278 <ProtectScenarios>False</ProtectScenarios> 279 </WorksheetOptions> 280 </Worksheet> 281</Workbook> 282 283