1*b1cdbd2cSJim Jagielski<?xml version="1.0" encoding="UTF-8"?> 2*b1cdbd2cSJim Jagielski<!DOCTYPE script:module PUBLIC "-//OpenOffice.org//DTD OfficeDocument 1.0//EN" "module.dtd"> 3*b1cdbd2cSJim Jagielski<script:module xmlns:script="http://openoffice.org/2000/script" script:name="sc_ScDataPilotTableObj" script:language="StarBasic"> 4*b1cdbd2cSJim Jagielski 5*b1cdbd2cSJim Jagielski 6*b1cdbd2cSJim Jagielski'************************************************************************* 7*b1cdbd2cSJim Jagielski' 8*b1cdbd2cSJim Jagielski' Licensed to the Apache Software Foundation (ASF) under one 9*b1cdbd2cSJim Jagielski' or more contributor license agreements. See the NOTICE file 10*b1cdbd2cSJim Jagielski' distributed with this work for additional information 11*b1cdbd2cSJim Jagielski' regarding copyright ownership. The ASF licenses this file 12*b1cdbd2cSJim Jagielski' to you under the Apache License, Version 2.0 (the 13*b1cdbd2cSJim Jagielski' "License"); you may not use this file except in compliance 14*b1cdbd2cSJim Jagielski' with the License. You may obtain a copy of the License at 15*b1cdbd2cSJim Jagielski' 16*b1cdbd2cSJim Jagielski' http://www.apache.org/licenses/LICENSE-2.0 17*b1cdbd2cSJim Jagielski' 18*b1cdbd2cSJim Jagielski' Unless required by applicable law or agreed to in writing, 19*b1cdbd2cSJim Jagielski' software distributed under the License is distributed on an 20*b1cdbd2cSJim Jagielski' "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 21*b1cdbd2cSJim Jagielski' KIND, either express or implied. See the License for the 22*b1cdbd2cSJim Jagielski' specific language governing permissions and limitations 23*b1cdbd2cSJim Jagielski' under the License. 24*b1cdbd2cSJim Jagielski' 25*b1cdbd2cSJim Jagielski'************************************************************************* 26*b1cdbd2cSJim Jagielski 27*b1cdbd2cSJim Jagielski 28*b1cdbd2cSJim Jagielski 29*b1cdbd2cSJim Jagielski 30*b1cdbd2cSJim Jagielski 31*b1cdbd2cSJim Jagielski' Be sure that all variables are dimensioned: 32*b1cdbd2cSJim Jagielskioption explicit 33*b1cdbd2cSJim Jagielski 34*b1cdbd2cSJim Jagielski 35*b1cdbd2cSJim Jagielski' REQUIRED VARIABLES for interface/service tests: 36*b1cdbd2cSJim Jagielski 37*b1cdbd2cSJim Jagielski' Requiered for com.sun.star.container.XNamed 38*b1cdbd2cSJim JagielskiGlobal cNameToSet As String ' "fixed" if name is fixed 39*b1cdbd2cSJim Jagielski 40*b1cdbd2cSJim Jagielski' Required for com.sun.star.sheet.XDataPilotTable 41*b1cdbd2cSJim JagielskiGlobal oOutputRange As Variant 42*b1cdbd2cSJim Jagielski 43*b1cdbd2cSJim Jagielski 44*b1cdbd2cSJim JagielskiSub CreateObj() 45*b1cdbd2cSJim Jagielski 46*b1cdbd2cSJim Jagielski'************************************************************************* 47*b1cdbd2cSJim Jagielski' COMPONENT: 48*b1cdbd2cSJim Jagielski' sc.ScDataPilotTableObj 49*b1cdbd2cSJim Jagielski'************************************************************************* 50*b1cdbd2cSJim JagielskiOn Error Goto ErrHndl 51*b1cdbd2cSJim Jagielski Dim oSheet As Object 52*b1cdbd2cSJim Jagielski Dim i, j As Integer 53*b1cdbd2cSJim Jagielski Dim oDataPilotTables As Object 54*b1cdbd2cSJim Jagielski Dim oDataPilotDescriptor As Object 55*b1cdbd2cSJim Jagielski Dim oFilterDescriptor As Object 56*b1cdbd2cSJim Jagielski Dim oDataPilotField As Object 57*b1cdbd2cSJim Jagielski 58*b1cdbd2cSJim Jagielski oDoc = utils.createDocument("scalc", cObjectName) 59*b1cdbd2cSJim Jagielski oSheet = oDoc.Sheets.getByIndex(0) 60*b1cdbd2cSJim Jagielski 61*b1cdbd2cSJim Jagielski for i = 1 to 5 62*b1cdbd2cSJim Jagielski oSheet.getCellByPosition(0, i).String = "Row" & i 63*b1cdbd2cSJim Jagielski oSheet.getCellByPosition(i, 0).String = "Col" & i 64*b1cdbd2cSJim Jagielski next i 65*b1cdbd2cSJim Jagielski 66*b1cdbd2cSJim Jagielski for i = 1 to 5 67*b1cdbd2cSJim Jagielski for j = 1 to 5 68*b1cdbd2cSJim Jagielski oSheet.getCellByPosition(i, j).Value = 2.5 * j + i 69*b1cdbd2cSJim Jagielski next j 70*b1cdbd2cSJim Jagielski next i 71*b1cdbd2cSJim Jagielski 72*b1cdbd2cSJim Jagielski Dim sCellRangeAddress As New com.sun.star.table.CellRangeAddress 73*b1cdbd2cSJim Jagielski sCellRangeAddress.Sheet = 0 74*b1cdbd2cSJim Jagielski sCellRangeAddress.StartColumn = 1 75*b1cdbd2cSJim Jagielski sCellRangeAddress.StartRow = 0 76*b1cdbd2cSJim Jagielski sCellRangeAddress.EndColumn = 1 77*b1cdbd2cSJim Jagielski sCellRangeAddress.EndRow = 5 78*b1cdbd2cSJim Jagielski 79*b1cdbd2cSJim Jagielski Dim sCellAddress As New com.sun.star.table.CellAddress 80*b1cdbd2cSJim Jagielski sCellAddress.Sheet = 0 81*b1cdbd2cSJim Jagielski sCellAddress.Column = 7 82*b1cdbd2cSJim Jagielski sCellAddress.Row = 8 83*b1cdbd2cSJim Jagielski 84*b1cdbd2cSJim Jagielski Dim FilterFields(1) As New com.sun.star.sheet.TableFilterField 85*b1cdbd2cSJim Jagielski FilterFields(0).Connection = com.sun.star.sheet.FilterConnection.AND 86*b1cdbd2cSJim Jagielski FilterFields(0).Field = 1 87*b1cdbd2cSJim Jagielski FilterFields(0).isNumeric = true 88*b1cdbd2cSJim Jagielski FilterFields(0).StringValue = "4" 89*b1cdbd2cSJim Jagielski FilterFields(0).Operator = com.sun.star.sheet.FilterOperator.GREATER 90*b1cdbd2cSJim Jagielski 91*b1cdbd2cSJim Jagielski FilterFields(1).Connection = com.sun.star.sheet.FilterConnection.AND 92*b1cdbd2cSJim Jagielski FilterFields(1).Field = 1 93*b1cdbd2cSJim Jagielski FilterFields(1).isNumeric = true 94*b1cdbd2cSJim Jagielski FilterFields(1).StringValue = "12" 95*b1cdbd2cSJim Jagielski FilterFields(1).Operator = com.sun.star.sheet.FilterOperator.LESS_EQUAL 96*b1cdbd2cSJim Jagielski 97*b1cdbd2cSJim Jagielski oDataPilotTables = oSheet.getDataPilotTables 98*b1cdbd2cSJim Jagielski oDataPilotDescriptor = oDataPilotTables.createDataPilotDescriptor() 99*b1cdbd2cSJim Jagielski oDataPilotDescriptor.setSourceRange(sCellRangeAddress) 100*b1cdbd2cSJim Jagielski oFilterDescriptor = oDataPilotDescriptor.getFilterDescriptor() 101*b1cdbd2cSJim Jagielski oFilterDescriptor.setFilterFields(FilterFields()) 102*b1cdbd2cSJim Jagielski 103*b1cdbd2cSJim Jagielski oDataPilotField = oDataPilotDescriptor.getDataPilotFields().getByIndex(0) 104*b1cdbd2cSJim Jagielski oDataPilotField.Function = com.sun.star.sheet.GeneralFunction.SUM 105*b1cdbd2cSJim Jagielski oDataPilotField.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.DATA 106*b1cdbd2cSJim Jagielski 107*b1cdbd2cSJim Jagielski oDataPilotTables.insertNewByName(cObjectName, sCellAddress, oDataPilotDescriptor) 108*b1cdbd2cSJim Jagielski 109*b1cdbd2cSJim Jagielski oObj = oDataPilotTables(0) 110*b1cdbd2cSJim Jagielski oOutputRange = sCellAddress 111*b1cdbd2cSJim Jagielski 112*b1cdbd2cSJim JagielskiExit Sub 113*b1cdbd2cSJim JagielskiErrHndl: 114*b1cdbd2cSJim Jagielski Test.Exception() 115*b1cdbd2cSJim JagielskiEnd Sub 116*b1cdbd2cSJim Jagielski 117*b1cdbd2cSJim Jagielski</script:module> 118