1 'encoding UTF-8 Do not remove or change this line!
2 '**************************************************************************
3 '* DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
5 '* Copyright 2008 by Sun Microsystems, Inc.
7 '* OpenOffice.org - a multi-platform office productivity suite
9 '* $RCSfile: c_getpivotdata.inc,v $
13 '* last change: $Author: jsi $ $Date: 2008-06-16 08:05:50 $
15 '* This file is part of OpenOffice.org.
17 '* OpenOffice.org is free software: you can redistribute it and/or modify
18 '* it under the terms of the GNU Lesser General Public License version 3
19 '* only, as published by the Free Software Foundation.
21 '* OpenOffice.org is distributed in the hope that it will be useful,
22 '* but WITHOUT ANY WARRANTY; without even the implied warranty of
23 '* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
24 '* GNU Lesser General Public License version 3 for more details
25 '* (a copy is included in the LICENSE file that accompanied this code).
27 '* You should have received a copy of the GNU Lesser General Public License
28 '* version 3 along with OpenOffice.org. If not, see
29 '* <http://www.openoffice.org/license.html>
30 '* for a copy of the LGPLv3 License.
32 '/***********************************************************************
34 '* owner : oliver.craemer@sun.com
36 '* short description : level 2 test for the getpivotdata function
38 '************************************************************************
40 ' #1 tgetpivotdata_function_wizard ' Testcase for manually input of the function
41 ' #1 tgetpivotdata_xls_filter ' Testcase for import/export of the function
43 '\***********************************************************************
45 testcase tgetpivotdata_function_wizard
47 dim stestdocument as string
48 dim slocalfile as string
49 dim sfunctionstring as string
50 dim sfunctionname as string
51 dim sfunctionwithparameter as string
53 stestdocument = Convertpath (gTesttoolPath + "spreadsheet\optional\input\datapilot.ods" )
54 slocalfile = Convertpath ( gOfficePath + "user\work\datapilot.ods" )
57 '/// Get localised string for function GETPIVOTDATA by fFunctionname
58 printlog "Get localised string for function GETPIVOTDATA by fFunctionname"
59 sfunctionname = fFunctionname("GETPIVOTDATA")
61 '/// Load testdocument <i>gTestToolPath</i>/spreadsheet/optional/input/datapilot.ods
62 printlog "Load testdocument datapilot.ods"
63 call hFileOpen ( stestdocument )
65 '/// If the file is not editable, click the edit button on standardbar
66 printlog "If the file is not editable, click the edit button on standardbar"
67 sMakeReadOnlyDocumentEditable()
69 '/// Select cell I8 and open function wizard by INSERT FUNCTION
70 printlog "Select cell I8 and open function wizard by INSERT FUNCTION"
71 call fGotoCell ( "I8" )
73 kontext ( "FunctionWizard" )
74 '/// Select category SPREADSHEET (11th category in listbox)
75 printlog "Select category SPREADSHEET (11th category in listbox)"
77 '/// Select the function GETPIVOTDATA in the now filtered function list
78 printlog "Select the function GETPIVOTDATA in the now filtered function list"
80 FunctionLB.select sfunctionname
81 printlog " Function " & sfunctionname & " selected in category SPREADSHEET"
83 warnlog "The function is not available under category SPREADSHEET"
87 FunctionLB.select sfunctionname
89 warnlog "The function is generally not available"
94 '/// Press NEXT button
95 printlog "Press NEXT button"
97 '/// Enter 2000 for parameter Data Field
98 printlog "Enter 2000 for parameter Data Field"
99 kontext "FunctionWizard"
100 Editfield1.settext "2000"
101 '/// Enter C29 for parameter DataPilot
102 printlog "Enter C29 for parameter DataPilot"
103 Editfield2.settext "C29"
104 '/// Enter C3 for parameter Field Name / Item 1
105 printlog "Enter C3 for parameter Field Name / Item 1"
106 Editfield3.settext "C3"
107 '/// Enter C16 for parameter Field Name / Item 2
108 printlog "Enter C16 for parameter Field Name / Item 2"
109 Editfield4.settext "C16"
110 '/// Scroll down to enable the next field
111 printlog "Scroll down to enable the next field"
112 Editfield4.TypeKeys "<DOWN>"
113 '/// Enter C26 for parameter Field Name / Item 3
114 printlog "Enter C26 for parameter Field Name / Item 3"
115 Editfield4.settext "C26"
116 '/// Scroll down to enable the next field
117 printlog "Scroll down to enable the next field"
118 Editfield4.TypeKeys "<DOWN>"
119 '/// Enter F27 for parameter Field Name / Item 4
120 printlog "Enter F27 for parameter Field Name / Item 4"
121 Editfield4.settext "F27"
122 '/// Close functionwizard by pressing OK button
123 printlog "Close functionwizard by pressing OK button"
125 '/// Memorize cellcontent
126 kontext ( "RechenleisteCalc" )
127 EingabeZeileCalc.TypeKeys ("<f2><mod1 a>")
129 sfunctionwithparameter = GetClipboardText ()
130 '/// Press twice <ESCAPE> to leave the cell
131 printlog "Press twice <ESCAPE> to leave the cell"
132 kontext ( "DocumentCalc" )
133 DocumentCalc.TypeKeys "<ESCAPE>" , 2
134 '/// Verify that the function result is 100000
135 printlog "Verify that the function result is 100000"
136 call fCalcCompareCellValue ( "I8",100000 )
137 '/// Save the document again
138 printlog "Save the document again"
139 if NOT hFileSaveAsKill (slocalfile) then
140 warnlog "Saving test document localy failed -> Aborting"
144 printlog " File saved successfully."
146 '/// Close the file with File/close
147 printlog "Close the file with File/close"
150 '/// Load localy saved document
151 printlog "Load localy saved document"
152 call hFileOpen ( slocalfile )
153 '/// Verify that cell I8 still shows 100000
154 printlog "Verify that cell I8 still shows 100000"
155 call fCalcCompareCellValue ( "I8",100000 )
156 '/// Verify that the function in cell I8 is still =GETPIVOTDATA("2000";C29;C3;C16;C26;F27)
157 printlog "Verify that the function in cell I8 is still " & sfunctionwithparameter
158 kontext ( "RechenleisteCalc" )
159 EingabeZeileCalc.TypeKeys ("<f2><mod1 a>")
161 if GetClipboardText () = sfunctionwithparameter then
162 printlog " The function was successfully saved and reloaded"
163 else warnlog "The function is not " & sfunctionwithparameter & ", it is " & GetClipboardText ()
165 '/// Press twice <ESCAPE> to leave the cell
166 printlog "Press twice <ESCAPE> to leave the chart object"
167 kontext "DocumentCalc"
168 DocumentCalc.TypeKeys "<ESCAPE>" , 2
170 printlog "Close document"
176 '---------------------------------------------------------------------------
178 testcase tgetpivotdata_xls_filter
180 dim stestdocument as string
181 dim slocalfile as string
182 dim sfunctionparameter as string
183 dim sfunctionname as string
184 dim sfunctionwithparameter as string
185 dim sdecimalseperator as string
187 stestdocument = Convertpath (gTesttoolPath + "spreadsheet\optional\input\getpivotdata2.xls" )
188 'stestdocument = Convertpath ( gOfficePath + "user\work\test.ods" )
189 slocalfile = Convertpath ( gOfficePath + "user\work\getpivotdata2.xls" )
190 sdecimalseperator = GetDecimalSeperator
192 sfunctionname = fFunctionname("GETPIVOTDATA")
194 '/// Load testdocument <i>gTestToolPath</i>/spreadsheet/optional/input/getpivotdata2.xls
195 printlog "Load testdocument getpivotdata2.xls"
196 call hFileOpen ( stestdocument )
198 '/// If the file is not editable, click the edit button on standardbar
199 printlog "If the file is not editable, click the edit button on standardbar"
200 call sMakeReadOnlyDocumentEditable
201 '/// Verify that Cell G12 shows 2.5
202 printlog "Verify that Cell G12 shows 2" & sdecimalseperator & "5"
203 call fCalcCompareCellValue ("G12","2" & sdecimalseperator & "5")
204 '/// Verify that the function is =GETPIVOTDATA("val";$F$5;"col1";"a";"col2";"x")
205 printlog "Verify that the function is =GETPIVOTDATA(""val"";$F$5;""col1"";""a"";""col2"";""x"")"
206 kontext ( "RechenleisteCalc" )
207 EingabeZeileCalc.TypeKeys ("<f2><mod1 a>")
209 sfunctionwithparameter = GetClipboardText ()
210 '/// Press twice <ESCAPE> to leave the cell
211 'printlog "Press twice <ESCAPE> to leave the cell"
212 kontext ( "DocumentCalc" )
213 DocumentCalc.TypeKeys "<ESCAPE>" , 2
214 if sfunctionwithparameter = "=" & sfunctionname & "(""val"";$F$5;""col1"";""a"";""col2"";""x"")" then
215 printlog " The function is correct"
217 warnlog "The function is " & sfunctionwithparameter & " instead of =GETPIVOTDATA(""val"";$F$5;""col1"";""a"";""col2"";""x"")"
220 '/// Verify that Cell G27 shows 0.05
221 printlog "Verify that Cell G27 shows 0" & sdecimalseperator & "05"
222 call fCalcCompareCellValue ("G27","0" & sdecimalseperator & "05")
223 '/// Verify that the function is =GETPIVOTDATA("val";$F$19;"col1";"a";"col2";"y")
224 printlog "Verify that the function is =GETPIVOTDATA(""val"";$F$19;""col1"";""a"";""col2"";""y"")"
225 kontext ( "RechenleisteCalc" )
226 EingabeZeileCalc.TypeKeys ("<f2><mod1 a>")
228 sfunctionwithparameter = GetClipboardText ()
229 '/// Press twice <ESCAPE> to leave the cell
230 'printlog "Press twice <ESCAPE> to leave the cell"
231 kontext ( "DocumentCalc" )
232 DocumentCalc.TypeKeys "<ESCAPE>" , 2
233 if sfunctionwithparameter = "=" & sfunctionname & "(""val"";$F$19;""col1"";""a"";""col2"";""y"")" then
234 printlog " The function is correct"
236 warnlog "The function is " & sfunctionwithparameter & " instead of =GETPIVOTDATA(""val"";$F$19;""col1"";""a"";""col2"";""y"")"
239 '/// Verify that Cell G43 shows 3
240 printlog "Verify that Cell G43 shows 3"
241 call fCalcCompareCellValue ("G43","3")
242 '/// Verify that the function is =GETPIVOTDATA("val";$F$33;"col1";"a";"col2";"z";"col3";"p"))
243 printlog "Verify that the function is =GETPIVOTDATA(""val"";$F$33;""col1"";""a"";""col2"";""z"";""col3"";""p"")"
244 kontext ( "RechenleisteCalc" )
245 EingabeZeileCalc.TypeKeys ("<f2><mod1 a>")
247 sfunctionwithparameter = GetClipboardText ()
248 '/// Press twice <ESCAPE> to leave the cell
249 'printlog "Press twice <ESCAPE> to leave the cell"
250 kontext ( "DocumentCalc" )
251 DocumentCalc.TypeKeys "<ESCAPE>" , 2
252 if sfunctionwithparameter = "=" & sfunctionname & "(""val"";$F$33;""col1"";""a"";""col2"";""z"";""col3"";""p"")" then
253 printlog " The function is correct"
255 warnlog "The function is " & sfunctionwithparameter & " instead of =GETPIVOTDATA(""val"";$F$33;""col1"";""a"";""col2"";""z"";""col3"";""p"")"
258 '/// Save document back to xls locally
259 printlog "Save document back to xls locally"
260 call hFileSaveAsKill ( slocalfile )
263 printlog "Close document"
267 printlog "Load Document"
268 call hFileOpen ( slocalfile )
271 '/// Verify that Cell G12 shows 2.5
272 printlog "Verify that Cell G12 shows 2" & sdecimalseperator & "5"
273 call fCalcCompareCellValue ("G12","2" & sdecimalseperator & "5")
274 '/// Verify that the function is =GETPIVOTDATA("val";$F$5;"col1";"a";"col2";"x")
275 printlog "Verify that the function is =GETPIVOTDATA(""val"";$F$5;""col1"";""a"";""col2"";""x"")"
276 kontext ( "RechenleisteCalc" )
277 EingabeZeileCalc.TypeKeys ("<f2><mod1 a>")
279 sfunctionwithparameter = GetClipboardText ()
280 '/// Press twice <ESCAPE> to leave the cell
281 'printlog " Press twice <ESCAPE> to leave the cell"
282 kontext ( "DocumentCalc" )
283 DocumentCalc.TypeKeys "<ESCAPE>" , 2
284 if sfunctionwithparameter = "=" & sfunctionname & "(""val"";$F$5;""col1"";""a"";""col2"";""x"")" then
285 printlog " The function is correct"
287 warnlog "The function is " & sfunctionwithparameter & " instead of =GETPIVOTDATA(""val"";$F$5;""col1"";""a"";""col2"";""x"")"
290 '/// Verify that Cell G27 shows 0.05
291 printlog "Verify that Cell G27 shows 0" & sdecimalseperator & "05"
292 call fCalcCompareCellValue ("G27","0" & sdecimalseperator & "05")
293 '/// Verify that the function is =GETPIVOTDATA("val";$F$19;"col1";"a";"col2";"y")
294 printlog "Verify that the function is =GETPIVOTDATA(""val"";$F$19;""col1"";""a"";""col2"";""y"")"
295 kontext ( "RechenleisteCalc" )
296 EingabeZeileCalc.TypeKeys ("<f2><mod1 a>")
298 sfunctionwithparameter = GetClipboardText ()
299 '/// Press twice <ESCAPE> to leave the cell
300 'printlog " Press twice <ESCAPE> to leave the cell"
301 kontext ( "DocumentCalc" )
302 DocumentCalc.TypeKeys "<ESCAPE>" , 2
303 if sfunctionwithparameter = "=" & sfunctionname & "(""val"";$F$19;""col1"";""a"";""col2"";""y"")" then
304 printlog " The function is correct"
306 warnlog "The function is " & sfunctionwithparameter & " instead of =GETPIVOTDATA(""val"";$F$19;""col1"";""a"";""col2"";""y"")"
309 '/// Verify that Cell G43 shows 3
310 printlog "Verify that Cell G43 shows 3"
311 call fCalcCompareCellValue ("G43","3")
312 '/// Verify that the function is =GETPIVOTDATA("val";$F$33;"col1";"a";"col2";"z";"col3";"p"))
313 printlog "Verify that the function is =GETPIVOTDATA(""val"";$F$33;""col1"";""a"";""col2"";""z"";""col3"";""p"")"
314 kontext ( "RechenleisteCalc" )
315 EingabeZeileCalc.TypeKeys ("<f2><mod1 a>")
317 sfunctionwithparameter = GetClipboardText ()
318 '/// Press twice <ESCAPE> to leave the cell
319 'printlog " Press twice <ESCAPE> to leave the cell"
320 kontext ( "DocumentCalc" )
321 DocumentCalc.TypeKeys "<ESCAPE>" , 2
322 if sfunctionwithparameter = "=" & sfunctionname & "(""val"";$F$33;""col1"";""a"";""col2"";""z"";""col3"";""p"")" then
323 printlog "The function is correct"
325 warnlog "The function is " & sfunctionwithparameter & " instead of =GETPIVOTDATA(""val"";$F$33;""col1"";""a"";""col2"";""z"";""col3"";""p"")"
329 printlog "Close document"