|
|
Below is Python script syntax that outputs each output pivot table into a separate Excel worksheet.
Would like to add another "if" that detects more than one pivot tables per output tree (example: all factor analysis pivot tables), then export that group of pivot tables into a separate Excel worksheet, while taking on the Header of the output tree (example: "Factor Analysis").
begin program.
SpssClient.StartClient()
OutputDoc = SpssClient.GetDesignatedOutputDoc()
OutputDoc.ClearSelection()
DOC = "C:/WORKBOOK_NAME.xls" # DESIGNATE WORKBOOK AND REPLACE BACK SLASHES WITH FWD SLASHES
OutputDoc.SetOutputOptions(SpssClient.DocExportOption.ExcelSheetNames,"mySheet") #NAMES WORKSHEETS
OutputDoc.SetOutputOptions(SpssClient.DocExportOption.ExcelStartingCell,"B6") #DESIGNATES STARTING CELL PER WORKSHEET
OutputDoc.SetOutputOptions(SpssClient.DocExportOption.ExcelLocationOptions,"OverwriteAtCellRef") #EXPORTS AND OVERWRITES TO STARTING CELL
#OutputDoc.SetOutputOptions(SpssClient.DocExportOption.ExcelLocationOptions,"AddRows") #ADDS TABLE AFTER THE LAST ROW OF EXISTING WORKSHEET
OutputDoc.SetOutputOptions(SpssClient.DocExportOption.ExcelOperationOptions,"CreateWorksheet") #OVERWRITES CONTENTS OF EXISTING WORKSHEET
#OutputDoc.SetOutputOptions(SpssClient.DocExportOption.ExcelOperationOptions,"ModifyWorksheet") #MODIFIES CONTENTS OF EXISTING WORKSHEET
OutputItems = OutputDoc.GetOutputItems()
i = 1 #NUMBER PREFIX PER WORKSHEET
for index in range(OutputItems.Size()):
OutputItem = OutputItems.GetItemAt(index)
if OutputItem.GetType() == SpssClient.OutputItemType.PIVOT: #GETS ALL TABLES
PivotTable=OutputItem.GetSpecificType()
OutputItem.SetSelected(True)
tableTitle=PivotTable.GetTitleText() #GETS TABLE TITLE
mySheet = ((str(i)+ " " + tableTitle)[:29]+"..") if len(str(i)+ " " + tableTitle)>31 else (str(i)+ " " + tableTitle) #WORKSHEET NAME
OutputDoc.SetOutputOptions(SpssClient.DocExportOption.ExcelSheetNames,mySheet)
OutputDoc.ExportDocument(SpssClient.SpssExportSubset.SpssSelected,DOC,SpssClient.DocExportFormat.SpssFormatXls)
OutputItem.SetSelected(False)
i = i + 1
DOC = None #CLEARS EXCEL FILE TO PREVENT UNINTENTIONAL OVERWRITING
end program.
|
Locked
