'encoding UTF-8 Do not remove or change this line!
'*************************************************************************
'
' Licensed to the Apache Software Foundation (ASF) under one
' or more contributor license agreements. See the NOTICE file
' distributed with this work for additional information
' regarding copyright ownership. The ASF licenses this file
' to you under the Apache License, Version 2.0 (the
' "License"); you may not use this file except in compliance
' with the License. You may obtain a copy of the License at
'
' http://www.apache.org/licenses/LICENSE-2.0
'
' Unless required by applicable law or agreed to in writing,
' software distributed under the License is distributed on an
' "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
' KIND, either express or implied. See the License for the
' specific language governing permissions and limitations
' under the License.
'
'*************************************************************************
'*
'* short description : level 2 test for the getpivotdata function
'*
'************************************************************************
'*
' #1 tgetpivotdata_function_wizard ' Testcase for manually input of the function
' #1 tgetpivotdata_xls_filter ' Testcase for import/export of the function
'*
'\***********************************************************************
testcase tgetpivotdata_function_wizard
dim stestdocument as string
dim slocalfile as string
dim sfunctionstring as string
dim sfunctionname as string
dim sfunctionwithparameter as string
stestdocument = Convertpath (gTesttoolPath + "spreadsheet\optional\input\datapilot.ods" )
slocalfile = Convertpath ( gOfficePath + "user\work\datapilot.ods" )
setclipboard = ""
'/// Get localised string for function GETPIVOTDATA by fFunctionname
printlog "Get localised string for function GETPIVOTDATA by fFunctionname"
sfunctionname = fFunctionname("GETPIVOTDATA")
'/// Load testdocument gTestToolPath/spreadsheet/optional/input/datapilot.ods
printlog "Load testdocument datapilot.ods"
call hFileOpenLocally( stestdocument )
'/// Select cell I8 and open function wizard by INSERT FUNCTION
printlog "Select cell I8 and open function wizard by INSERT FUNCTION"
call fGotoCell ( "I8" )
InsertFunction
kontext ( "FunctionWizard" )
'/// Select category SPREADSHEET (11th category in listbox)
printlog "Select category SPREADSHEET (11th category in listbox)"
CategoryLB.select 11
'/// Select the function GETPIVOTDATA in the now filtered function list
printlog "Select the function GETPIVOTDATA in the now filtered function list"
try
FunctionLB.select sfunctionname
printlog " Function " & sfunctionname & " selected in category SPREADSHEET"
catch
warnlog "The function is not available under category SPREADSHEET"
endcatch
CategoryLB.select 2
try
FunctionLB.select sfunctionname
catch
warnlog "The function is generally not available"
FunctionWizard.OK
call hCloseDocument
goto endsub
endcatch
'/// Press NEXT button
printlog "Press NEXT button"
Forward.click
'/// Enter 2000 for parameter Data Field
printlog "Enter 2000 for parameter Data Field"
kontext "FunctionWizard"
Editfield1.settext "2000"
'/// Enter C29 for parameter DataPilot
printlog "Enter C29 for parameter DataPilot"
Editfield2.settext "C29"
'/// Enter C3 for parameter Field Name / Item 1
printlog "Enter C3 for parameter Field Name / Item 1"
Editfield3.settext "C3"
'/// Enter C16 for parameter Field Name / Item 2
printlog "Enter C16 for parameter Field Name / Item 2"
Editfield4.settext "C16"
'/// Scroll down to enable the next field
printlog "Scroll down to enable the next field"
Editfield4.TypeKeys ""
'/// Enter C26 for parameter Field Name / Item 3
printlog "Enter C26 for parameter Field Name / Item 3"
Editfield4.settext "C26"
'/// Scroll down to enable the next field
printlog "Scroll down to enable the next field"
Editfield4.TypeKeys ""
'/// Enter F27 for parameter Field Name / Item 4
printlog "Enter F27 for parameter Field Name / Item 4"
Editfield4.settext "F27"
'/// Close functionwizard by pressing OK button
printlog "Close functionwizard by pressing OK button"
FunctionWizard.OK
'/// Memorize cellcontent
kontext ( "RechenleisteCalc" )
EingabeZeileCalc.TypeKeys ("")
editcopy
sfunctionwithparameter = GetClipboardText ()
'/// Press twice to leave the cell
printlog "Press twice to leave the cell"
kontext ( "DocumentCalc" )
DocumentCalc.TypeKeys "" , 2
'/// Verify that the function result is 100000
printlog "Verify that the function result is 100000"
call fCalcCompareCellValue ( "I8",100000 )
'/// Save the document again
printlog "Save the document again"
if NOT hFileSaveAsKill (slocalfile) then
warnlog "Saving test document localy failed -> Aborting"
call hCloseDocument
goto endsub
else
printlog " File saved successfully."
end if
'/// Close the file with File/close
printlog "Close the file with File/close"
call hCloseDocument
sleep(3)
'/// Load localy saved document
printlog "Load localy saved document"
call hFileOpen ( slocalfile )
'/// Verify that cell I8 still shows 100000
printlog "Verify that cell I8 still shows 100000"
call fCalcCompareCellValue ( "I8",100000 )
'/// Verify that the function in cell I8 is still =GETPIVOTDATA("2000";C29;C3;C16;C26;F27)
printlog "Verify that the function in cell I8 is still " & sfunctionwithparameter
kontext ( "RechenleisteCalc" )
EingabeZeileCalc.TypeKeys ("")
editcopy
if GetClipboardText () = sfunctionwithparameter then
printlog " The function was successfully saved and reloaded"
else warnlog "The function is not " & sfunctionwithparameter & ", it is " & GetClipboardText ()
end if
'/// Press twice to leave the cell
printlog "Press twice to leave the chart object"
kontext "DocumentCalc"
DocumentCalc.TypeKeys "" , 2
'/// Close document
printlog "Close document"
call hCloseDocument
sleep(3)
'///
endcase
'
'---------------------------------------------------------------------------
'
testcase tgetpivotdata_xls_filter
dim stestdocument as string
dim slocalfile as string
dim sfunctionparameter as string
dim sfunctionname as string
dim sfunctionwithparameter as string
dim sdecimalseperator as string
stestdocument = Convertpath (gTesttoolPath + "spreadsheet\optional\input\getpivotdata2.xls" )
'stestdocument = Convertpath ( gOfficePath + "user\work\test.ods" )
slocalfile = Convertpath ( gOfficePath + "user\work\getpivotdata2.xls" )
sdecimalseperator = GetDecimalSeperator
setclipboard = ""
sfunctionname = fFunctionname("GETPIVOTDATA")
'/// Load testdocument gTestToolPath/spreadsheet/optional/input/getpivotdata2.xls
printlog "Load testdocument getpivotdata2.xls"
call hFileOpenLocally( stestdocument )
'/// Verify that Cell G12 shows 2.5
printlog "Verify that Cell G12 shows 2" & sdecimalseperator & "5"
call fCalcCompareCellValue ("G12","2" & sdecimalseperator & "5")
'/// Verify that the function is =GETPIVOTDATA("val";$F$5;"col1";"a";"col2";"x")
printlog "Verify that the function is =GETPIVOTDATA(""val"";$F$5;""col1"";""a"";""col2"";""x"")"
kontext ( "RechenleisteCalc" )
EingabeZeileCalc.TypeKeys ("")
editcopy
sfunctionwithparameter = GetClipboardText ()
'/// Press twice to leave the cell
'printlog "Press twice to leave the cell"
kontext ( "DocumentCalc" )
DocumentCalc.TypeKeys "" , 2
if sfunctionwithparameter = "=" & sfunctionname & "(""val"";$F$5;""col1"";""a"";""col2"";""x"")" then
printlog " The function is correct"
else
warnlog "The function is " & sfunctionwithparameter & " instead of =GETPIVOTDATA(""val"";$F$5;""col1"";""a"";""col2"";""x"")"
end if
'/// Verify that Cell G27 shows 0.05
printlog "Verify that Cell G27 shows 0" & sdecimalseperator & "05"
call fCalcCompareCellValue ("G27","0" & sdecimalseperator & "05")
'/// Verify that the function is =GETPIVOTDATA("val";$F$19;"col1";"a";"col2";"y")
printlog "Verify that the function is =GETPIVOTDATA(""val"";$F$19;""col1"";""a"";""col2"";""y"")"
kontext ( "RechenleisteCalc" )
EingabeZeileCalc.TypeKeys ("")
editcopy
sfunctionwithparameter = GetClipboardText ()
'/// Press twice to leave the cell
'printlog "Press twice to leave the cell"
kontext ( "DocumentCalc" )
DocumentCalc.TypeKeys "" , 2
if sfunctionwithparameter = "=" & sfunctionname & "(""val"";$F$19;""col1"";""a"";""col2"";""y"")" then
printlog " The function is correct"
else
warnlog "The function is " & sfunctionwithparameter & " instead of =GETPIVOTDATA(""val"";$F$19;""col1"";""a"";""col2"";""y"")"
end if
'/// Verify that Cell G43 shows 3
printlog "Verify that Cell G43 shows 3"
call fCalcCompareCellValue ("G43","3")
'/// Verify that the function is =GETPIVOTDATA("val";$F$33;"col1";"a";"col2";"z";"col3";"p"))
printlog "Verify that the function is =GETPIVOTDATA(""val"";$F$33;""col1"";""a"";""col2"";""z"";""col3"";""p"")"
kontext ( "RechenleisteCalc" )
EingabeZeileCalc.TypeKeys ("")
editcopy
sfunctionwithparameter = GetClipboardText ()
'/// Press twice to leave the cell
'printlog "Press twice to leave the cell"
kontext ( "DocumentCalc" )
DocumentCalc.TypeKeys "" , 2
if sfunctionwithparameter = "=" & sfunctionname & "(""val"";$F$33;""col1"";""a"";""col2"";""z"";""col3"";""p"")" then
printlog " The function is correct"
else
warnlog "The function is " & sfunctionwithparameter & " instead of =GETPIVOTDATA(""val"";$F$33;""col1"";""a"";""col2"";""z"";""col3"";""p"")"
end if
'/// Save document back to xls locally
printlog "Save document back to xls locally"
call hFileSaveAsKill ( slocalfile )
'/// Close document
printlog "Close document"
call hCloseDocument
'/// Load Document
printlog "Load Document"
call hFileOpen ( slocalfile )
sleep (2)
'/// Verify that Cell G12 shows 2.5
printlog "Verify that Cell G12 shows 2" & sdecimalseperator & "5"
call fCalcCompareCellValue ("G12","2" & sdecimalseperator & "5")
'/// Verify that the function is =GETPIVOTDATA("val";$F$5;"col1";"a";"col2";"x")
printlog "Verify that the function is =GETPIVOTDATA(""val"";$F$5;""col1"";""a"";""col2"";""x"")"
kontext ( "RechenleisteCalc" )
EingabeZeileCalc.TypeKeys ("")
editcopy
sfunctionwithparameter = GetClipboardText ()
'/// Press twice to leave the cell
'printlog " Press twice to leave the cell"
kontext ( "DocumentCalc" )
DocumentCalc.TypeKeys "" , 2
if sfunctionwithparameter = "=" & sfunctionname & "(""val"";$F$5;""col1"";""a"";""col2"";""x"")" then
printlog " The function is correct"
else
warnlog "The function is " & sfunctionwithparameter & " instead of =GETPIVOTDATA(""val"";$F$5;""col1"";""a"";""col2"";""x"")"
end if
'/// Verify that Cell G27 shows 0.05
printlog "Verify that Cell G27 shows 0" & sdecimalseperator & "05"
call fCalcCompareCellValue ("G27","0" & sdecimalseperator & "05")
'/// Verify that the function is =GETPIVOTDATA("val";$F$19;"col1";"a";"col2";"y")
printlog "Verify that the function is =GETPIVOTDATA(""val"";$F$19;""col1"";""a"";""col2"";""y"")"
kontext ( "RechenleisteCalc" )
EingabeZeileCalc.TypeKeys ("")
editcopy
sfunctionwithparameter = GetClipboardText ()
'/// Press twice to leave the cell
'printlog " Press twice to leave the cell"
kontext ( "DocumentCalc" )
DocumentCalc.TypeKeys "" , 2
if sfunctionwithparameter = "=" & sfunctionname & "(""val"";$F$19;""col1"";""a"";""col2"";""y"")" then
printlog " The function is correct"
else
warnlog "The function is " & sfunctionwithparameter & " instead of =GETPIVOTDATA(""val"";$F$19;""col1"";""a"";""col2"";""y"")"
end if
'/// Verify that Cell G43 shows 3
printlog "Verify that Cell G43 shows 3"
call fCalcCompareCellValue ("G43","3")
'/// Verify that the function is =GETPIVOTDATA("val";$F$33;"col1";"a";"col2";"z";"col3";"p"))
printlog "Verify that the function is =GETPIVOTDATA(""val"";$F$33;""col1"";""a"";""col2"";""z"";""col3"";""p"")"
kontext ( "RechenleisteCalc" )
EingabeZeileCalc.TypeKeys ("")
editcopy
sfunctionwithparameter = GetClipboardText ()
'/// Press twice to leave the cell
'printlog " Press twice to leave the cell"
kontext ( "DocumentCalc" )
DocumentCalc.TypeKeys "" , 2
if sfunctionwithparameter = "=" & sfunctionname & "(""val"";$F$33;""col1"";""a"";""col2"";""z"";""col3"";""p"")" then
printlog "The function is correct"
else
warnlog "The function is " & sfunctionwithparameter & " instead of =GETPIVOTDATA(""val"";$F$33;""col1"";""a"";""col2"";""z"";""col3"";""p"")"
end if
'/// Close document
printlog "Close document"
call hCloseDocument
sleep(3)
endcase