Export output to excel using Python (v17)

[Arthur-59]

Hi list,

I am attempting to export a large number of custom tables to Excel using
code from Programming and Data Management for SPSS® Statistics 17.0, p. 432
(exact code below).

This works fine if I run one table; however, when I attempt to run this for
the entire output file the Excel file does not retain the associated table
looks and has the following error messages:

Excel recovered your formulas and cell values, but some data may have been
lost.

  <?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
- <recoveryLog
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <logFileName>error042440_04.xml</logFileName>
  <summary>Errors were detected in file 'S:\Mscience\Python\Example\ADSK
Channel Engagement Banner1 v1_0.xls'</summary>
- <additionalInfo>
  <info>Excel recovered your formulas and cell values, but some data may
have been lost.</info>
  </additionalInfo>
  </recoveryLog>

Any ideas why this is occurring?

Thanks.
Arthur

* EXPORT OUTPUT TO EXCEL .

BEGIN PROGRAM .
import spss, SpssClient, sys
SpssClient.StartClient()
OutputDoc = SpssClient.GetDesignatedOutputDoc()
OutputDoc.ClearSelection()

OutputDoc.SetOutputOptions
(SpssClient.DocExportOption.ExcelOperationOptions,"CreateWorkbook")
OutputDoc.SetOutputOptions
(SpssClient.DocExportOption.ExcelSheetNames,"Test")
OutputItems = OutputDoc.GetOutputItems()
for index in range(OutputItems.Size()):
 OutputItem = OutputItems.GetItemAt(index)
 if (OutputItem.GetType() ==SpssClient.OutputItemType.HEAD and
OutputItem.GetDescription() == "Table 1"): OutputItem.SetSelected(True)
try:
 OutputDoc.ExportDocument
(SpssClient.SpssExportSubset.SpssSelected, "C:/temp1.xls",
SpssClient.DocExportFormat.SpssFormatXls)
except:
 print sys.exc_info()[1]
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

[SPSS Support]

Hi Arthur,

You may want to check out our Developer Central site's forums at http://www.spss.com/fusetalk/forum/index.cfm?forumid=2. There's a forum on programmability that includes Python as a specific subcategory (at http://www.spss.com/fusetalk/forum/categories.cfm?catid=9&entercat=y).

David Nichols

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Arthur
Sent: Wednesday, February 25, 2009 4:05 PM
To: [hidden email]
Subject: [SPSSX-L] Export output to excel using Python (v17)

Hi list,

I am attempting to export a large number of custom tables to Excel using code from Programming and Data Management for SPSS® Statistics 17.0, p. 432 (exact code below).

This works fine if I run one table; however, when I attempt to run this for the entire output file the Excel file does not retain the associated table looks and has the following error messages:

Excel recovered your formulas and cell values, but some data may have been lost.

  <?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
- <recoveryLog
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <logFileName>error042440_04.xml</logFileName>
  <summary>Errors were detected in file 'S:\Mscience\Python\Example\ADSK Channel Engagement Banner1 v1_0.xls'</summary>
- <additionalInfo>
  <info>Excel recovered your formulas and cell values, but some data may have been lost.</info>
  </additionalInfo>
  </recoveryLog>

Any ideas why this is occurring?

Thanks.
Arthur

* EXPORT OUTPUT TO EXCEL .

BEGIN PROGRAM .
import spss, SpssClient, sys
SpssClient.StartClient()
OutputDoc = SpssClient.GetDesignatedOutputDoc()
OutputDoc.ClearSelection()

OutputDoc.SetOutputOptions
(SpssClient.DocExportOption.ExcelOperationOptions,"CreateWorkbook")
OutputDoc.SetOutputOptions
(SpssClient.DocExportOption.ExcelSheetNames,"Test")
OutputItems = OutputDoc.GetOutputItems() for index in range(OutputItems.Size()):
 OutputItem = OutputItems.GetItemAt(index)  if (OutputItem.GetType() ==SpssClient.OutputItemType.HEAD and
OutputItem.GetDescription() == "Table 1"): OutputItem.SetSelected(True)
try:
 OutputDoc.ExportDocument
(SpssClient.SpssExportSubset.SpssSelected, "C:/temp1.xls",
SpssClient.DocExportFormat.SpssFormatXls)
except:
 print sys.exc_info()[1]
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