dnl PSPP - a program for statistical analysis.
dnl Copyright (C) 2017 Free Software Foundation, Inc.
dnl
dnl This program is free software: you can redistribute it and/or modify
dnl it under the terms of the GNU General Public License as published by
dnl the Free Software Foundation, either version 3 of the License, or
dnl (at your option) any later version.
dnl
dnl This program is distributed in the hope that it will be useful,
dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
dnl GNU General Public License for more details.
dnl
dnl You should have received a copy of the GNU General Public License
dnl along with this program.  If not, see <http://www.gnu.org/licenses/>.
dnl
m4_define([SPREADSHEET_TEST_PREP],[dnl
 AT_KEYWORDS([spreadsheet])
 m4_if($1,[GNM],[dnl
    AT_CHECK([gzip -c $top_srcdir/tests/language/data-io/Book1.gnm.unzipped > Book1.gnumeric])dnl
    m4_define([testsheet],[Book1.gnumeric])dnl
    ]) dnl
 m4_if($1,[ODS],[dnl
    AT_CHECK([cp $top_srcdir/tests/language/data-io/test.ods test.ods])dnl
    m4_define([testsheet],[test.ods])dnl
    ])dnl
])

m4_define([CHECK_SPREADSHEET_READER],
 [dnl
AT_SETUP([GET DATA /TYPE=$1 with CELLRANGE])
SPREADSHEET_TEST_PREP($1)
AT_DATA([get-data.sps], [dnl
GET DATA /TYPE=$1 /FILE='testsheet'  /READNAMES=off /SHEET=name 'This' /CELLRANGE=range 'g9:i13' .
DISPLAY VARIABLES.
LIST.
])
AT_CHECK([pspp -o pspp.csv get-data.sps])
AT_CHECK([cat pspp.csv], [0], [dnl
Table: Variables
Name,Position,Print Format,Write Format
VAR001,1,F8.2,F8.2
VAR002,2,A8,A8
VAR003,3,F8.2,F8.2

Table: Data List
VAR001,VAR002,VAR003
.00,fred,20.00
1.00,11,21.00
2.00,twelve,22.00
3.00,13,23.00
4.00,14,24.00
])
AT_CLEANUP

AT_SETUP([GET DATA /TYPE=$1 with CELLRANGE and READNAMES])
SPREADSHEET_TEST_PREP($1)
AT_DATA([get-data.sps], [dnl
GET DATA /TYPE=$1 /FILE='testsheet'  /READNAMES=on /SHEET=name 'This' /CELLRANGE=range 'g8:i13' .
DISPLAY VARIABLES.
LIST.
])
AT_CHECK([pspp -o pspp.csv get-data.sps])
AT_CHECK([cat pspp.csv], [0], [dnl
Table: Variables
Name,Position,Print Format,Write Format
V1,1,F8.2,F8.2
V2,2,A8,A8
VAR001,3,F8.2,F8.2

Table: Data List
V1,V2,VAR001
.00,fred,20.00
1.00,11,21.00
2.00,twelve,22.00
3.00,13,23.00
4.00,14,24.00
])
AT_CLEANUP

AT_SETUP([GET DATA /TYPE=$1 without CELLRANGE])
SPREADSHEET_TEST_PREP($1)
AT_DATA([get-data.sps], [dnl
GET DATA /TYPE=$1 /FILE='testsheet' /SHEET=index 3.
DISPLAY VARIABLES.
LIST.
])
AT_CHECK([pspp -O format=csv get-data.sps], [0], [dnl
Table: Variables
Name,Position,Print Format,Write Format
name,1,A8,A8
id,2,F8.2,F8.2
height,3,F8.2,F8.2

warning: Cannot convert the value in the spreadsheet cell C4 to format (F8.2): Field contents are not numeric.

Table: Data List
name,id,height
fred,.00,23.40
bert,1.00,.56
charlie,2.00,.  @&t@
dick,3.00,-34.09
])
AT_CLEANUP

AT_SETUP([GET DATA /TYPE=$1 with missing data])
SPREADSHEET_TEST_PREP($1)
AT_DATA([get-data.sps], [dnl
* This sheet has no data in one of its variables
GET DATA /TYPE=$1 /FILE='testsheet' /READNAMES=on /SHEET=index 5.
DISPLAY VARIABLES.
LIST.
])
AT_CHECK([pspp -o pspp.csv get-data.sps])
AT_CHECK([cat pspp.csv], [0], [dnl
Table: Variables
Name,Position,Print Format,Write Format
vone,1,F8.2,F8.2
vtwo,2,F8.2,F8.2
vthree,3,A8,A8
v4,4,F8.2,F8.2

Table: Data List
vone,vtwo,vthree,v4
1.00,3.00,,5.00
2.00,4.00,,6.00
])
AT_CLEANUP

dnl This syntax doesn't do anything particularly useful.
dnl It has been seen to cause a few crashes, so we check here that it
dnl doesn't do anthing bad.
AT_SETUP([GET DATA /TYPE=$1 with no options])
SPREADSHEET_TEST_PREP($1)
AT_DATA([get-data.sps], [dnl
* This sheet is empty
GET DATA /TYPE=$1 /FILE='testsheet'.
DISPLAY DICTIONARY.
LIST.
])
AT_CHECK([pspp -o pspp.csv get-data.sps], [0], [ignore])
AT_CLEANUP



AT_SETUP([GET DATA /TYPE=$1 with empty sheet])
SPREADSHEET_TEST_PREP($1)
AT_DATA([get-data.sps], [dnl
* This sheet is empty
GET DATA /TYPE=$1 /FILE='testsheet' /SHEET=name 'Empty'.
])
AT_CHECK([pspp -o pspp.csv get-data.sps], [0], [dnl
warning: Selected sheet or range of spreadsheet `testsheet' is empty.
])
AT_CLEANUP

AT_SETUP([GET DATA /TYPE=$1 with nonexistent sheet])
SPREADSHEET_TEST_PREP($1)
AT_DATA([get-data.sps], [dnl
* This sheet doesnt exist.
GET DATA /TYPE=$1 /FILE='testsheet' /SHEET=name 'foobarxx'.
])
AT_CHECK([pspp -o pspp.csv get-data.sps], [0], [dnl
warning: Selected sheet or range of spreadsheet `testsheet' is empty.
])
AT_CLEANUP
])


AT_BANNER([GET DATA Spreadsheet /TYPE=GNM])

CHECK_SPREADSHEET_READER([GNM])

dnl Check for a bug where gnumeric files were interpreted incorrectly
AT_SETUP([GET DATA /TYPE=GNM sheet index bug])
AT_KEYWORDS([spreadsheet])
AT_DATA([minimal3.gnumeric],[dnl
<?xml version="1.0" encoding="UTF-8"?>
<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">
  <gnm:Version Epoch="1" Major="10" Minor="8" Full="1.10.8"/>
  <gnm:SheetNameIndex>
    <gnm:SheetName gnm:Cols="256" gnm:Rows="65536">Sheet1</gnm:SheetName>
    <gnm:SheetName gnm:Cols="256" gnm:Rows="65536">Sheet2</gnm:SheetName>
    <gnm:SheetName gnm:Cols="256" gnm:Rows="65536">Sheet3</gnm:SheetName>
  </gnm:SheetNameIndex>
  <gnm:Sheets>
    <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">
      <gnm:Name>Sheet1</gnm:Name>
      <gnm:MaxCol>2</gnm:MaxCol>
      <gnm:MaxRow>3</gnm:MaxRow>
      <gnm:Names>
        <gnm:Name>
          <gnm:name>Print_Area</gnm:name>
          <gnm:value>#REF!</gnm:value>
          <gnm:position>A1</gnm:position>
        </gnm:Name>
        <gnm:Name>
          <gnm:name>Sheet_Title</gnm:name>
          <gnm:value>&quot;Sheet1&quot;</gnm:value>
          <gnm:position>A1</gnm:position>
        </gnm:Name>
      </gnm:Names>
      <gnm:Cols DefaultSizePts="48">
        <gnm:ColInfo No="0" Unit="94.5" HardSize="1"/>
        <gnm:ColInfo No="1" Unit="48" Count="2"/>
      </gnm:Cols>
      <gnm:Rows DefaultSizePts="12.75">
        <gnm:RowInfo No="0" Unit="13.5" Count="4"/>
      </gnm:Rows>
      <gnm:Cells>
        <gnm:Cell Row="0" Col="0" ValueType="60">Name</gnm:Cell>
        <gnm:Cell Row="0" Col="1" ValueType="60">x</gnm:Cell>
        <gnm:Cell Row="0" Col="2" ValueType="60">y</gnm:Cell>
        <gnm:Cell Row="1" Col="0" ValueType="60">Sheet One</gnm:Cell>
        <gnm:Cell Row="1" Col="1" ValueType="40">1</gnm:Cell>
        <gnm:Cell Row="1" Col="2" ValueType="40">2</gnm:Cell>
        <gnm:Cell Row="2" Col="0" ValueType="60">foo</gnm:Cell>
        <gnm:Cell Row="2" Col="1" ValueType="40">3</gnm:Cell>
        <gnm:Cell Row="2" Col="2" ValueType="40">4</gnm:Cell>
        <gnm:Cell Row="3" Col="0" ValueType="60">bar</gnm:Cell>
        <gnm:Cell Row="3" Col="1" ValueType="40">5</gnm:Cell>
        <gnm:Cell Row="3" Col="2" ValueType="40">6</gnm:Cell>
      </gnm:Cells>
    </gnm:Sheet>
    <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">
      <gnm:Name>Sheet2</gnm:Name>
      <gnm:MaxCol>2</gnm:MaxCol>
      <gnm:MaxRow>2</gnm:MaxRow>
      <gnm:Names>
        <gnm:Name>
          <gnm:name>Print_Area</gnm:name>
          <gnm:value>#REF!</gnm:value>
          <gnm:position>A1</gnm:position>
        </gnm:Name>
        <gnm:Name>
          <gnm:name>Sheet_Title</gnm:name>
          <gnm:value>&quot;Sheet2&quot;</gnm:value>
          <gnm:position>A1</gnm:position>
        </gnm:Name>
      </gnm:Names>
      <gnm:Cols DefaultSizePts="48">
        <gnm:ColInfo No="0" Unit="48"/>
        <gnm:ColInfo No="1" Unit="57.75"/>
        <gnm:ColInfo No="2" Unit="54.75"/>
      </gnm:Cols>
      <gnm:Rows DefaultSizePts="12.75">
        <gnm:RowInfo No="0" Unit="13.5" Count="3"/>
      </gnm:Rows>
      <gnm:Cells>
        <gnm:Cell Row="0" Col="0" ValueType="60">Comment</gnm:Cell>
        <gnm:Cell Row="0" Col="1" ValueType="60">DOB</gnm:Cell>
        <gnm:Cell Row="0" Col="2" ValueType="60">wealth</gnm:Cell>
        <gnm:Cell Row="1" Col="0" ValueType="60">Sheet Two</gnm:Cell>
        <gnm:Cell Row="1" Col="1" ValueType="60">24/5/1966</gnm:Cell>
        <gnm:Cell Row="1" Col="2" ValueType="40" ValueFormat="_($* 0.00_);_($* (0.00);_($* &quot;-&quot;??_);_(@_)">0.02</gnm:Cell>
        <gnm:Cell Row="2" Col="0" ValueType="60">wee</gnm:Cell>
        <gnm:Cell Row="2" Col="1" ValueType="40" ValueFormat="dd/mm/yyyy">37145</gnm:Cell>
        <gnm:Cell Row="2" Col="2" ValueType="40" ValueFormat="_($* 0.00_);_($* (0.00);_($* &quot;-&quot;??_);_(@_)">3000</gnm:Cell>
      </gnm:Cells>
    </gnm:Sheet>
    <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">
      <gnm:Name>Sheet3</gnm:Name>
      <gnm:MaxCol>2</gnm:MaxCol>
      <gnm:MaxRow>2</gnm:MaxRow>
      <gnm:Names>
        <gnm:Name>
          <gnm:name>Print_Area</gnm:name>
          <gnm:value>#REF!</gnm:value>
          <gnm:position>A1</gnm:position>
        </gnm:Name>
        <gnm:Name>
          <gnm:name>Sheet_Title</gnm:name>
          <gnm:value>&quot;Sheet3&quot;</gnm:value>
          <gnm:position>A1</gnm:position>
        </gnm:Name>
      </gnm:Names>
      <gnm:Cols DefaultSizePts="48">
        <gnm:ColInfo No="0" Unit="48" Count="3"/>
      </gnm:Cols>
      <gnm:Rows DefaultSizePts="12.75">
        <gnm:RowInfo No="0" Unit="13.5"/>
        <gnm:RowInfo No="1" Unit="12.75" Count="2"/>
      </gnm:Rows>
      <gnm:Cells>
        <gnm:Cell Row="0" Col="0" ValueType="40">3</gnm:Cell>
        <gnm:Cell Row="0" Col="1" ValueType="40">4</gnm:Cell>
        <gnm:Cell Row="0" Col="2" ValueType="40">5</gnm:Cell>
        <gnm:Cell Row="1" Col="0" ValueType="40">6</gnm:Cell>
        <gnm:Cell Row="1" Col="1" ValueType="40">7</gnm:Cell>
        <gnm:Cell Row="1" Col="2" ValueType="40">8</gnm:Cell>
        <gnm:Cell Row="2" Col="0" ValueType="40">9</gnm:Cell>
        <gnm:Cell Row="2" Col="1" ValueType="40">10</gnm:Cell>
        <gnm:Cell Row="2" Col="2" ValueType="40">11</gnm:Cell>
      </gnm:Cells>
    </gnm:Sheet>
  </gnm:Sheets>
</gnm:Workbook>
])

AT_DATA([gnum.sps], [dnl
GET DATA
	/TYPE=GNM
        /FILE='minimal3.gnumeric'
	/SHEET=index 3
	/READNAMES=off
	.

LIST.
])

AT_CHECK([pspp -O format=csv gnum.sps], [0], [dnl
Table: Data List
VAR001,VAR002,VAR003
3,4.00,5.00
6,7.00,8.00
9,10.00,11.00
])


AT_CLEANUP


dnl Check for a bug where certain gnumeric files failed an assertion
AT_SETUP([GET DATA /TYPE=GNM assert-fail])
AT_KEYWORDS([spreadsheet])
AT_DATA([read.sps],[dnl
GET DATA
	/TYPE=GNM
	/FILE='crash.gnumeric'
	.
list.
])


AT_DATA([crash.gnumeric],[dnl
<?xml version="1.0" encoding="UTF-8"?>
<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">
  <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">
  </office:document-meta>
  <gnm:SheetNameIndex>
    <gnm:SheetName gnm:Cols="256" gnm:Rows="65536">Sheet1</gnm:SheetName>
  </gnm:SheetNameIndex>
  <gnm:Sheets>
    <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">
      <gnm:Name>Sheet1</gnm:Name>
      <gnm:MaxCol>2</gnm:MaxCol>
      <gnm:MaxRow>4</gnm:MaxRow>
      <gnm:Styles>
        <gnm:StyleRegion startCol="0" startRow="0" endCol="255" endRow="65535">
          <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">
          </gnm:Style>
        </gnm:StyleRegion>
      </gnm:Styles>
      <gnm:Cells>
        <gnm:Cell Row="1" Col="1" ValueType="60">one</gnm:Cell>
        <gnm:Cell Row="1" Col="2" ValueType="60">two</gnm:Cell>
        <gnm:Cell Row="2" Col="1" ValueType="40">1</gnm:Cell>
        <gnm:Cell Row="2" Col="2" ValueType="40">2</gnm:Cell>
        <gnm:Cell Row="3" Col="1" ValueType="40">1</gnm:Cell>
        <gnm:Cell Row="3" Col="2" ValueType="40">2</gnm:Cell>
        <gnm:Cell Row="4" Col="1" ValueType="40">1</gnm:Cell>
        <gnm:Cell Row="4" Col="2" ValueType="40">2</gnm:Cell>
      </gnm:Cells>
    </gnm:Sheet>
  </gnm:Sheets>
</gnm:Workbook>
])

AT_CHECK([pspp -O format=csv read.sps], [0], [ignore])


AT_CLEANUP



AT_BANNER([GET DATA Spreadsheet /TYPE=ODS])

CHECK_SPREADSHEET_READER([ODS])


AT_SETUP([GET DATA /TYPE=ODS crash])
AT_KEYWORDS([spreadsheet])


AT_CHECK([cp $top_srcdir/tests/language/data-io/newone.ods this.ods])dnl

AT_DATA([crash.sps],[dnl
GET DATA /TYPE=ODS /FILE='this.ods' /CELLRANGE=RANGE 'A1:C8'  /READNAMES=ON
LIST.
])

AT_CHECK([pspp -O format=csv crash.sps], [0], [ignore])

AT_CLEANUP


AT_SETUP([GET DATA /TYPE=ODS readnames])
AT_KEYWORDS([spreadsheet])

dnl Check for a bug where in the ODS reader /READNAMES incorrectly
dnl dealt with repeated names.
AT_CHECK([cp $top_srcdir/tests/language/data-io/readnames.ods this.ods])dnl

AT_DATA([readnames.sps],[dnl
GET DATA /TYPE=ODS /FILE='this.ods' /CELLRANGE=RANGE 'A1:H8' /READNAMES=ON
DISPLAY DICTIONARY.
LIST.
])


AT_CHECK([pspp -O format=csv readnames.sps], [0], [dnl
Table: Variables
Name,Position,Measurement Level,Role,Width,Alignment,Print Format,Write Format
freda,1,Scale,Input,8,Right,F8.2,F8.2
fred,2,Scale,Input,8,Right,F8.2,F8.2
fred_A,3,Scale,Input,8,Right,F8.2,F8.2
fred_B,4,Scale,Input,8,Right,F8.2,F8.2
fred_C,5,Scale,Input,8,Right,F8.2,F8.2
fred_D,6,Scale,Input,8,Right,F8.2,F8.2
fred_E,7,Scale,Input,8,Right,F8.2,F8.2

Table: Data List
freda,fred,fred_A,fred_B,fred_C,fred_D,fred_E
1.00,2.00,3.00,4.00,5.00,6.00,7.00
8.00,9.00,10.00,11.00,12.00,13.00,14.00
])

AT_CLEANUP