editting pivot tables using SpssClient

[Jo-53]

I am trying to edit pivot tables using SpssClient (Python) with SPSS
v17.0.2. I can achieve most things with the data cells; I can colour them
red or left align them, but I cannot seem to be able to add borders. Why
does the code below not work?



data list free /1 q17.
begin data.
1
2
3
4
end data.

value labels q17 1 'yes' 2 'no' 3 'not sure' 4 'N/A'.

begin program.
import spss
import SpssClient

spss.Submit(r"""
CTABLES
  /VLABELS VARIABLES=q17 DISPLAY=DEFAULT
  /TABLE q17 [COUNT F40.0, COLPCT.COUNT PCT40.1]
  /CATEGORIES VARIABLES=q17 ORDER=A KEY=VALUE EMPTY=INCLUDE TOTAL=YES
POSITION=AFTER.""")

SpssClient.StartClient()

OutputDoc = SpssClient.GetDesignatedOutputDoc()
OutputItems = OutputDoc.GetOutputItems()
OutputItem = OutputItems.GetItemAt(OutputItems.Size()-1)

if OutputItem.GetType() == SpssClient.OutputItemType.PIVOT:
    objPivotTable=OutputItem.GetSpecificType()

    objDataCells = objPivotTable.DataCellArray()
    for i in range(objDataCells.GetNumRows()):
        for j in range(objDataCells.GetNumColumns()):
            try:
                objDataCells.SetBottomMarginAt(i,j,1)
            except:
                pass

OutputDoc.ClearSelection()

SpssClient.StopClient()

end program.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Peck, Jon]

The api you are using below, SetBottomMarginAt, does not add borders.  It sets the margin width.  As far as I can see, that works properly.  Borders cannot be set at the individual cell level.  They are a property of entire table areas and cannot be set directly with apis.

To set the characteristics of border lines, create a tableLook.  You can then apply that to all tables as a preference item (or specify specifically for particular Ctables output via syntax).  Alternatively, you can apply a tableLook programmatically with the SetTableLook api for a pivot table object.

One other tip: if you are doing cell by cell editing programmatically, it's a good idea to call
SetUpdateScreen(False)
before starting and
SetUpdateScreen(True)
afterwards for better performance and to avoid the flicker of constant redraws.

HTH,
Jon Peck



-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Jo
Sent: Thursday, October 08, 2009 4:22 AM
To: [hidden email]
Subject: [SPSSX-L] editting pivot tables using SpssClient

I am trying to edit pivot tables using SpssClient (Python) with SPSS
v17.0.2. I can achieve most things with the data cells; I can colour them
red or left align them, but I cannot seem to be able to add borders. Why
does the code below not work?



data list free /1 q17.
begin data.
1
2
3
4
end data.

value labels q17 1 'yes' 2 'no' 3 'not sure' 4 'N/A'.

begin program.
import spss
import SpssClient

spss.Submit(r"""
CTABLES
  /VLABELS VARIABLES=q17 DISPLAY=DEFAULT
  /TABLE q17 [COUNT F40.0, COLPCT.COUNT PCT40.1]
  /CATEGORIES VARIABLES=q17 ORDER=A KEY=VALUE EMPTY=INCLUDE TOTAL=YES
POSITION=AFTER.""")

SpssClient.StartClient()

OutputDoc = SpssClient.GetDesignatedOutputDoc()
OutputItems = OutputDoc.GetOutputItems()
OutputItem = OutputItems.GetItemAt(OutputItems.Size()-1)

if OutputItem.GetType() == SpssClient.OutputItemType.PIVOT:
    objPivotTable=OutputItem.GetSpecificType()

    objDataCells = objPivotTable.DataCellArray()
    for i in range(objDataCells.GetNumRows()):
        for j in range(objDataCells.GetNumColumns()):
            try:
                objDataCells.SetBottomMarginAt(i,j,1)
            except:
                pass

OutputDoc.ClearSelection()

SpssClient.StopClient()

end program.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD