tests/language/commands/get-data-spreadsheet.at

   1 dnl PSPP - a program for statistical analysis.
   2 dnl Copyright (C) 2017 Free Software Foundation, Inc.
   3 dnl
   4 dnl This program is free software: you can redistribute it and/or modify
   5 dnl it under the terms of the GNU General Public License as published by
   6 dnl the Free Software Foundation, either version 3 of the License, or
   7 dnl (at your option) any later version.
   8 dnl
   9 dnl This program is distributed in the hope that it will be useful,
  10 dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
  11 dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12 dnl GNU General Public License for more details.
  13 dnl
  14 dnl You should have received a copy of the GNU General Public License
  15 dnl along with this program.  If not, see <http://www.gnu.org/licenses/>.
  16 dnl
  17 m4_define([SPREADSHEET_TEST_PREP],[dnl
  18  AT_KEYWORDS([spreadsheet])
  19  m4_if($1,[GNM],[dnl
  20     AT_KEYWORDS([gnumeric])
  21     AT_CHECK([gzip -c $top_srcdir/tests/language/commands/Book1.gnm.unzipped > Book1.gnumeric])dnl
  22     m4_define([testsheet],[Book1.gnumeric])dnl
  23     ]) dnl
  24  m4_if($1,[ODS],[dnl
  25     AT_KEYWORDS([ods])
  26     AT_CHECK([cp $top_srcdir/tests/language/commands/test.ods test.ods])dnl
  27     m4_define([testsheet],[test.ods])dnl
  28     ])dnl
  29 ])
  30
  31 m4_define([CHECK_SPREADSHEET_READER],
  32  [dnl
  33 AT_SETUP([GET DATA /TYPE=$1 with CELLRANGE])
  34 SPREADSHEET_TEST_PREP($1)
  35 AT_DATA([get-data.sps], [dnl
  36 GET DATA /TYPE=$1 /FILE='testsheet'  /READNAMES=off /SHEET=name 'This' /CELLRANGE=range 'g9:i13' .
  37 DISPLAY VARIABLES.
  38 LIST.
  39 ])
  40 AT_CHECK([pspp -o pspp.csv get-data.sps])
  41 AT_CHECK([cat pspp.csv], [0], [dnl
  42 Table: Variables
  43 Name,Position,Print Format,Write Format
  44 VAR001,1,F8.2,F8.2
  45 VAR002,2,A8,A8
  46 VAR003,3,F8.2,F8.2
  47
  48 Table: Data List
  49 VAR001,VAR002,VAR003
  50 .00,fred,20.00
  51 1.00,11,21.00
  52 2.00,twelve,22.00
  53 3.00,13,23.00
  54 4.00,14,24.00
  55 ])
  56 AT_CLEANUP
  57
  58 AT_SETUP([GET DATA /TYPE=$1 with CELLRANGE and READNAMES])
  59 SPREADSHEET_TEST_PREP($1)
  60 AT_DATA([get-data.sps], [dnl
  61 GET DATA /TYPE=$1 /FILE='testsheet'  /READNAMES=on /SHEET=name 'This' /CELLRANGE=range 'g8:i13' .
  62 DISPLAY VARIABLES.
  63 LIST.
  64 ])
  65 AT_CHECK([pspp -o pspp.csv get-data.sps])
  66 AT_CHECK([cat pspp.csv], [0], [dnl
  67 Table: Variables
  68 Name,Position,Print Format,Write Format
  69 V1,1,F8.2,F8.2
  70 V2,2,A8,A8
  71 VAR001,3,F8.2,F8.2
  72
  73 Table: Data List
  74 V1,V2,VAR001
  75 .00,fred,20.00
  76 1.00,11,21.00
  77 2.00,twelve,22.00
  78 3.00,13,23.00
  79 4.00,14,24.00
  80 ])
  81 AT_CLEANUP
  82
  83 AT_SETUP([GET DATA /TYPE=$1 without CELLRANGE])
  84 SPREADSHEET_TEST_PREP($1)
  85 AT_DATA([get-data.sps], [dnl
  86 GET DATA /TYPE=$1 /FILE='testsheet' /SHEET=index 3.
  87 DISPLAY VARIABLES.
  88 LIST.
  89 ])
  90 AT_CHECK([pspp -O format=csv get-data.sps], [0], [dnl
  91 Table: Variables
  92 Name,Position,Print Format,Write Format
  93 name,1,A8,A8
  94 id,2,F8.2,F8.2
  95 height,3,F8.2,F8.2
  96
  97 warning: Cannot convert the value in the spreadsheet cell C4 to format (F8.2): Field contents are not numeric.
  98
  99 Table: Data List
 100 name,id,height
 101 fred,.00,23.40
 102 bert,1.00,.56
 103 charlie,2.00,.  @&t@
 104 dick,3.00,-34.09
 105 ])
 106 AT_CLEANUP
 107
 108 AT_SETUP([GET DATA /TYPE=$1 with missing data])
 109 SPREADSHEET_TEST_PREP($1)
 110 AT_DATA([get-data.sps], [dnl
 111 * This sheet has no data in one of its variables
 112 GET DATA /TYPE=$1 /FILE='testsheet' /READNAMES=on /SHEET=index 5.
 113 DISPLAY VARIABLES.
 114 LIST.
 115 ])
 116 AT_CHECK([pspp -o pspp.csv get-data.sps])
 117 AT_CHECK([cat pspp.csv], [0], [dnl
 118 Table: Variables
 119 Name,Position,Print Format,Write Format
 120 vone,1,F8.2,F8.2
 121 vtwo,2,F8.2,F8.2
 122 vthree,3,A8,A8
 123 v4,4,F8.2,F8.2
 124
 125 Table: Data List
 126 vone,vtwo,vthree,v4
 127 1.00,3.00,,5.00
 128 2.00,4.00,,6.00
 129 ])
 130 AT_CLEANUP
 131
 132 dnl This syntax doesn't do anything particularly useful.
 133 dnl It has been seen to cause a few crashes, so we check here that it
 134 dnl doesn't do anthing bad.
 135 AT_SETUP([GET DATA /TYPE=$1 with no options])
 136 SPREADSHEET_TEST_PREP($1)
 137 AT_DATA([get-data.sps], [dnl
 138 * This sheet is empty
 139 GET DATA /TYPE=$1 /FILE='testsheet'.
 140 DISPLAY DICTIONARY.
 141 LIST.
 142 ])
 143 AT_CHECK([pspp -o pspp.csv get-data.sps], [0], [ignore])
 144 AT_CLEANUP
 145
 146
 147
 148 AT_SETUP([GET DATA /TYPE=$1 with empty sheet])
 149 SPREADSHEET_TEST_PREP($1)
 150 AT_DATA([get-data.sps], [dnl
 151 * This sheet is empty
 152 GET DATA /TYPE=$1 /FILE='testsheet' /SHEET=name 'Empty'.
 153 ])
 154 AT_CHECK([pspp -o pspp.csv get-data.sps], [0], [dnl
 155 warning: Selected sheet or range of spreadsheet `testsheet' is empty.
 156 ])
 157 AT_CLEANUP
 158
 159 AT_SETUP([GET DATA /TYPE=$1 with nonexistent sheet])
 160 SPREADSHEET_TEST_PREP($1)
 161 AT_DATA([get-data.sps], [dnl
 162 * This sheet doesnt exist.
 163 GET DATA /TYPE=$1 /FILE='testsheet' /SHEET=name 'foobarxx'.
 164 ])
 165 AT_CHECK([pspp -o pspp.csv get-data.sps], [0], [dnl
 166 warning: Selected sheet or range of spreadsheet `testsheet' is empty.
 167 ])
 168 AT_CLEANUP
 169 ])
 170
 171
 172 AT_BANNER([GET DATA Spreadsheet /TYPE=GNM])
 173
 174 CHECK_SPREADSHEET_READER([GNM])
 175
 176 dnl Check for a bug where gnumeric files were interpreted incorrectly
 177 AT_SETUP([GET DATA /TYPE=GNM sheet index bug])
 178 AT_KEYWORDS([spreadsheet])
 179 AT_DATA([minimal3.gnumeric],[dnl
 180 <?xml version="1.0" encoding="UTF-8"?>
 181 <gnm:Workbook xmlns:gnm="http://www.gnumeric.org/v10.dtd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.gnumeric.org/v9.xsd">
 182   <gnm:Version Epoch="1" Major="10" Minor="8" Full="1.10.8"/>
 183   <gnm:SheetNameIndex>
 184     <gnm:SheetName gnm:Cols="256" gnm:Rows="65536">Sheet1</gnm:SheetName>
 185     <gnm:SheetName gnm:Cols="256" gnm:Rows="65536">Sheet2</gnm:SheetName>
 186     <gnm:SheetName gnm:Cols="256" gnm:Rows="65536">Sheet3</gnm:SheetName>
 187   </gnm:SheetNameIndex>
 188   <gnm:Sheets>
 189     <gnm:Sheet DisplayFormulas="0" HideZero="0" HideGrid="0" HideColHeader="0" HideRowHeader="0" DisplayOutlines="1" OutlineSymbolsBelow="1" OutlineSymbolsRight="1" Visibility="GNM_SHEET_VISIBILITY_VISIBLE" GridColor="0:0:0">
 190       <gnm:Name>Sheet1</gnm:Name>
 191       <gnm:MaxCol>2</gnm:MaxCol>
 192       <gnm:MaxRow>3</gnm:MaxRow>
 193       <gnm:Names>
 194         <gnm:Name>
 195           <gnm:name>Print_Area</gnm:name>
 196           <gnm:value>#REF!</gnm:value>
 197           <gnm:position>A1</gnm:position>
 198         </gnm:Name>
 199         <gnm:Name>
 200           <gnm:name>Sheet_Title</gnm:name>
 201           <gnm:value>&quot;Sheet1&quot;</gnm:value>
 202           <gnm:position>A1</gnm:position>
 203         </gnm:Name>
 204       </gnm:Names>
 205       <gnm:Cols DefaultSizePts="48">
 206         <gnm:ColInfo No="0" Unit="94.5" HardSize="1"/>
 207         <gnm:ColInfo No="1" Unit="48" Count="2"/>
 208       </gnm:Cols>
 209       <gnm:Rows DefaultSizePts="12.75">
 210         <gnm:RowInfo No="0" Unit="13.5" Count="4"/>
 211       </gnm:Rows>
 212       <gnm:Cells>
 213         <gnm:Cell Row="0" Col="0" ValueType="60">Name</gnm:Cell>
 214         <gnm:Cell Row="0" Col="1" ValueType="60">x</gnm:Cell>
 215         <gnm:Cell Row="0" Col="2" ValueType="60">y</gnm:Cell>
 216         <gnm:Cell Row="1" Col="0" ValueType="60">Sheet One</gnm:Cell>
 217         <gnm:Cell Row="1" Col="1" ValueType="40">1</gnm:Cell>
 218         <gnm:Cell Row="1" Col="2" ValueType="40">2</gnm:Cell>
 219         <gnm:Cell Row="2" Col="0" ValueType="60">foo</gnm:Cell>
 220         <gnm:Cell Row="2" Col="1" ValueType="40">3</gnm:Cell>
 221         <gnm:Cell Row="2" Col="2" ValueType="40">4</gnm:Cell>
 222         <gnm:Cell Row="3" Col="0" ValueType="60">bar</gnm:Cell>
 223         <gnm:Cell Row="3" Col="1" ValueType="40">5</gnm:Cell>
 224         <gnm:Cell Row="3" Col="2" ValueType="40">6</gnm:Cell>
 225       </gnm:Cells>
 226     </gnm:Sheet>
 227     <gnm:Sheet DisplayFormulas="0" HideZero="0" HideGrid="0" HideColHeader="0" HideRowHeader="0" DisplayOutlines="1" OutlineSymbolsBelow="1" OutlineSymbolsRight="1" Visibility="GNM_SHEET_VISIBILITY_VISIBLE" GridColor="0:0:0">
 228       <gnm:Name>Sheet2</gnm:Name>
 229       <gnm:MaxCol>2</gnm:MaxCol>
 230       <gnm:MaxRow>2</gnm:MaxRow>
 231       <gnm:Names>
 232         <gnm:Name>
 233           <gnm:name>Print_Area</gnm:name>
 234           <gnm:value>#REF!</gnm:value>
 235           <gnm:position>A1</gnm:position>
 236         </gnm:Name>
 237         <gnm:Name>
 238           <gnm:name>Sheet_Title</gnm:name>
 239           <gnm:value>&quot;Sheet2&quot;</gnm:value>
 240           <gnm:position>A1</gnm:position>
 241         </gnm:Name>
 242       </gnm:Names>
 243       <gnm:Cols DefaultSizePts="48">
 244         <gnm:ColInfo No="0" Unit="48"/>
 245         <gnm:ColInfo No="1" Unit="57.75"/>
 246         <gnm:ColInfo No="2" Unit="54.75"/>
 247       </gnm:Cols>
 248       <gnm:Rows DefaultSizePts="12.75">
 249         <gnm:RowInfo No="0" Unit="13.5" Count="3"/>
 250       </gnm:Rows>
 251       <gnm:Cells>
 252         <gnm:Cell Row="0" Col="0" ValueType="60">Comment</gnm:Cell>
 253         <gnm:Cell Row="0" Col="1" ValueType="60">DOB</gnm:Cell>
 254         <gnm:Cell Row="0" Col="2" ValueType="60">wealth</gnm:Cell>
 255         <gnm:Cell Row="1" Col="0" ValueType="60">Sheet Two</gnm:Cell>
 256         <gnm:Cell Row="1" Col="1" ValueType="60">24/5/1966</gnm:Cell>
 257         <gnm:Cell Row="1" Col="2" ValueType="40" ValueFormat="_($* 0.00_);_($* (0.00);_($* &quot;-&quot;??_);_(@_)">0.02</gnm:Cell>
 258         <gnm:Cell Row="2" Col="0" ValueType="60">wee</gnm:Cell>
 259         <gnm:Cell Row="2" Col="1" ValueType="40" ValueFormat="dd/mm/yyyy">37145</gnm:Cell>
 260         <gnm:Cell Row="2" Col="2" ValueType="40" ValueFormat="_($* 0.00_);_($* (0.00);_($* &quot;-&quot;??_);_(@_)">3000</gnm:Cell>
 261       </gnm:Cells>
 262     </gnm:Sheet>
 263     <gnm:Sheet DisplayFormulas="0" HideZero="0" HideGrid="0" HideColHeader="0" HideRowHeader="0" DisplayOutlines="1" OutlineSymbolsBelow="1" OutlineSymbolsRight="1" Visibility="GNM_SHEET_VISIBILITY_VISIBLE" GridColor="0:0:0">
 264       <gnm:Name>Sheet3</gnm:Name>
 265       <gnm:MaxCol>2</gnm:MaxCol>
 266       <gnm:MaxRow>2</gnm:MaxRow>
 267       <gnm:Names>
 268         <gnm:Name>
 269           <gnm:name>Print_Area</gnm:name>
 270           <gnm:value>#REF!</gnm:value>
 271           <gnm:position>A1</gnm:position>
 272         </gnm:Name>
 273         <gnm:Name>
 274           <gnm:name>Sheet_Title</gnm:name>
 275           <gnm:value>&quot;Sheet3&quot;</gnm:value>
 276           <gnm:position>A1</gnm:position>
 277         </gnm:Name>
 278       </gnm:Names>
 279       <gnm:Cols DefaultSizePts="48">
 280         <gnm:ColInfo No="0" Unit="48" Count="3"/>
 281       </gnm:Cols>
 282       <gnm:Rows DefaultSizePts="12.75">
 283         <gnm:RowInfo No="0" Unit="13.5"/>
 284         <gnm:RowInfo No="1" Unit="12.75" Count="2"/>
 285       </gnm:Rows>
 286       <gnm:Cells>
 287         <gnm:Cell Row="0" Col="0" ValueType="40">3</gnm:Cell>
 288         <gnm:Cell Row="0" Col="1" ValueType="40">4</gnm:Cell>
 289         <gnm:Cell Row="0" Col="2" ValueType="40">5</gnm:Cell>
 290         <gnm:Cell Row="1" Col="0" ValueType="40">6</gnm:Cell>
 291         <gnm:Cell Row="1" Col="1" ValueType="40">7</gnm:Cell>
 292         <gnm:Cell Row="1" Col="2" ValueType="40">8</gnm:Cell>
 293         <gnm:Cell Row="2" Col="0" ValueType="40">9</gnm:Cell>
 294         <gnm:Cell Row="2" Col="1" ValueType="40">10</gnm:Cell>
 295         <gnm:Cell Row="2" Col="2" ValueType="40">11</gnm:Cell>
 296       </gnm:Cells>
 297     </gnm:Sheet>
 298   </gnm:Sheets>
 299 </gnm:Workbook>
 300 ])
 301
 302 AT_DATA([gnum.sps], [dnl
 303 GET DATA
 304         /TYPE=GNM
 305         /FILE='minimal3.gnumeric'
 306         /SHEET=index 3
 307         /READNAMES=off
 308         .
 309
 310 LIST.
 311 ])
 312
 313 AT_CHECK([pspp -O format=csv gnum.sps], [0], [dnl
 314 Table: Data List
 315 VAR001,VAR002,VAR003
 316 3,4.00,5.00
 317 6,7.00,8.00
 318 9,10.00,11.00
 319 ])
 320
 321
 322 AT_CLEANUP
 323
 324
 325 dnl Check for a bug where certain gnumeric files failed an assertion
 326 AT_SETUP([GET DATA /TYPE=GNM assert-fail])
 327 AT_KEYWORDS([spreadsheet])
 328 AT_DATA([read.sps],[dnl
 329 GET DATA
 330         /TYPE=GNM
 331         /FILE='crash.gnumeric'
 332         .
 333 list.
 334 ])
 335
 336
 337 AT_DATA([crash.gnumeric],[dnl
 338 <?xml version="1.0" encoding="UTF-8"?>
 339 <gnm:Workbook xmlns:gnm="http://www.gnumeric.org/v10.dtd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.gnumeric.org/v9.xsd">
 340   <office:document-meta xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:meta="urn:oasis:names:tc:opendocument:xmlns:meta:1.0" xmlns:ooo="http://openoffice.org/2004/office" office:version="1.1">
 341   </office:document-meta>
 342   <gnm:SheetNameIndex>
 343     <gnm:SheetName gnm:Cols="256" gnm:Rows="65536">Sheet1</gnm:SheetName>
 344   </gnm:SheetNameIndex>
 345   <gnm:Sheets>
 346     <gnm:Sheet DisplayFormulas="0" HideZero="0" HideGrid="0" HideColHeader="0" HideRowHeader="0" DisplayOutlines="1" OutlineSymbolsBelow="1" OutlineSymbolsRight="1" Visibility="GNM_SHEET_VISIBILITY_VISIBLE" GridColor="0:0:0">
 347       <gnm:Name>Sheet1</gnm:Name>
 348       <gnm:MaxCol>2</gnm:MaxCol>
 349       <gnm:MaxRow>4</gnm:MaxRow>
 350       <gnm:Styles>
 351         <gnm:StyleRegion startCol="0" startRow="0" endCol="255" endRow="65535">
 352           <gnm:Style HAlign="1" VAlign="2" WrapText="0" ShrinkToFit="0" Rotation="0" Shade="0" Indent="0" Locked="1" Hidden="0" Fore="0:0:0" Back="FFFF:FFFF:FFFF" PatternColor="0:0:0" Format="General">
 353           </gnm:Style>
 354         </gnm:StyleRegion>
 355       </gnm:Styles>
 356       <gnm:Cells>
 357         <gnm:Cell Row="1" Col="1" ValueType="60">one</gnm:Cell>
 358         <gnm:Cell Row="1" Col="2" ValueType="60">two</gnm:Cell>
 359         <gnm:Cell Row="2" Col="1" ValueType="40">1</gnm:Cell>
 360         <gnm:Cell Row="2" Col="2" ValueType="40">2</gnm:Cell>
 361         <gnm:Cell Row="3" Col="1" ValueType="40">1</gnm:Cell>
 362         <gnm:Cell Row="3" Col="2" ValueType="40">2</gnm:Cell>
 363         <gnm:Cell Row="4" Col="1" ValueType="40">1</gnm:Cell>
 364         <gnm:Cell Row="4" Col="2" ValueType="40">2</gnm:Cell>
 365       </gnm:Cells>
 366     </gnm:Sheet>
 367   </gnm:Sheets>
 368 </gnm:Workbook>
 369 ])
 370
 371 AT_CHECK([pspp -O format=csv read.sps], [0], [ignore])
 372
 373
 374 AT_CLEANUP
 375
 376
 377
 378 AT_BANNER([GET DATA Spreadsheet /TYPE=ODS])
 379
 380 CHECK_SPREADSHEET_READER([ODS])
 381
 382
 383 AT_SETUP([GET DATA /TYPE=ODS crash])
 384 AT_KEYWORDS([spreadsheet])
 385
 386
 387 AT_CHECK([cp $top_srcdir/tests/language/commands/newone.ods this.ods])dnl
 388
 389 AT_DATA([crash.sps],[dnl
 390 GET DATA /TYPE=ODS /FILE='this.ods' /CELLRANGE=RANGE 'A1:C8'  /READNAMES=ON
 391 LIST.
 392 ])
 393
 394 AT_CHECK([pspp -O format=csv crash.sps], [0], [ignore])
 395
 396 AT_CLEANUP
 397
 398
 399 AT_SETUP([GET DATA /TYPE=ODS readnames])
 400 AT_KEYWORDS([spreadsheet])
 401
 402 dnl Check for a bug where in the ODS reader /READNAMES incorrectly
 403 dnl dealt with repeated names.
 404 AT_CHECK([cp $top_srcdir/tests/language/commands/readnames.ods this.ods])dnl
 405
 406 AT_DATA([readnames.sps],[dnl
 407 GET DATA /TYPE=ODS /FILE='this.ods' /CELLRANGE=RANGE 'A1:H8' /READNAMES=ON
 408 EXECUTE.
 409 DISPLAY DICTIONARY.
 410 LIST.
 411 ])
 412
 413
 414 AT_CHECK([pspp -O format=csv readnames.sps], [0], [dnl
 415 Table: Variables
 416 Name,Position,Measurement Level,Role,Width,Alignment,Print Format,Write Format
 417 freda,1,Nominal,Input,8,Right,F8.2,F8.2
 418 fred,2,Nominal,Input,8,Right,F8.2,F8.2
 419 fred_A,3,Nominal,Input,8,Right,F8.2,F8.2
 420 fred_B,4,Nominal,Input,8,Right,F8.2,F8.2
 421 fred_C,5,Nominal,Input,8,Right,F8.2,F8.2
 422 fred_D,6,Nominal,Input,8,Right,F8.2,F8.2
 423 fred_E,7,Nominal,Input,8,Right,F8.2,F8.2
 424
 425 Table: Data List
 426 freda,fred,fred_A,fred_B,fred_C,fred_D,fred_E
 427 1.00,2.00,3.00,4.00,5.00,6.00,7.00
 428 8.00,9.00,10.00,11.00,12.00,13.00,14.00
 429 ])
 430
 431 AT_CLEANUP
 432