Create an Action Control to Export 2 Specific tables into Microsoft Excel using IronPython Script

[Milton Sestito]

Hi,

 

I have become fairly good at coding in TERR but now I am venturing into Iron Python since I don't believe R has the capability to export visualizations from Spotfire into an Excel file. I have 2 visualizations, both cross tables. See image attached for the dialogue box and script parameters.

To save myself time and heartache of coding a script from scratch in an unfamiliar programming language, I came across this script that I have tried to implement. I like this particular format because it gives the user the ability to define the path and file name by bringing up a dialog box rather than coding in a defined path in the script:

import System

import clr

import sys

clr.AddReference("System.Windows.Forms")

from sys import exit

from System.Windows.Forms import FolderBrowserDialog, MessageBox, MessageBoxButtons, DialogResult

from Spotfire.Dxp.Data.Export import DataWriterTypeIdentifiers

from System.IO import File, FileStream, FileMode

#This is a list of Data Tables and their Excel file names. You can see each referenced below as d[0] and d[1], respectively

#tableList = ["Table1"]

#Imports the location of the file so that there is a default place to put the exports

from Spotfire.Dxp.Application import DocumentMetadata

dmd = Application.DocumentMetadata # Get MetaData

path = str ( dmd.LoadedFromFileName ) #Get Path to file

savePath = '\'.join ( path.split ( '\' ) [ 0 :- 1 ]

dialogResult = MessageBox.Show ( "The files will be saved to "+savePath

+ ". Do you want to change the location"

, "Select the save location" , MessageBoxButtons.YesNo )

if ( dialogResult == DialogResult.Yes ) :

# Gets the file path from the user through a user prompt instead of a fixed file location

SaveFile = FolderBrowserDialog ( )

SaveFile.ShowDialog ( )

savePath = SaveFile.SelectedPath

#Message making sure that the user want to export the files

dialogResult = MessageBox.Show ( "Export Files."

+ "Export Files" , "Are you sure" , MessageBoxButtons.YesNo )

if ( dialogResult == DialogResult.Yes ) :

for d in tableList: #Cycles through the table list elements defined above

writer = Document.Data.CreateDataWriter ( DataWriterTypeIdentifiers.ExcelXlsDataWriter )

table = Document.Data.Tables [ "Table1" ] #d[0] is the Data Table name in the Spotfire project, defined above

filtered = Document.ActiveFilteringSelectionReference.GetSelection (table).AsIndexSet() #Or pass the filter

stream = File.OpenWrite ( savePath + '\' + "Table1" + ".xls" ) #d[1] is the Excel alias name. You could also use d.Name to export with the Data Table Name

names = [ ]

for col in table.Columns:

names.append(col.Name)

writer.Write ( stream , table , filtered , names )

stream.Close ( )

# If the user doesn't want to export a file

else:

dialogResult = MessageBox.Show ( "Ok" )

 

Unfortunately, when I run this, I get the following error:

File "", line 27

dialogResult = MessageBox.Show ( "The files will be saved to "+savePath

^

SyntaxError: unexpected token 'dialogResult'

 

Microsoft.Scripting.SyntaxErrorException: unexpected token 'dialogResult'

at Microsoft.Scripting.ErrorSink.Add(SourceUnit source, String message, SourceSpan span, Int32 errorCode, Severity severity)

at IronPython.Compiler.Parser.ReportSyntaxError(Int32 start, Int32 end, String message, Int32 errorCode)

at IronPython.Compiler.Parser.ReportSyntaxError(Token t, IndexSpan span, Int32 errorCode, Boolean allowIncomplete)

at IronPython.Compiler.Parser.ReportSyntaxError(TokenWithSpan t, Int32 errorCode)

.....

 

Just wondering if I could get some pointers in how to debug, beginning with what this error means and how to correct. My first thought is that maybe I need to define dialogResult somewhere, but again not really sure how to go about this. Any pointers would be greatly appreciated. Thank you in advance!

[David Boot-Olazabal]

Hi Milton,

You can find lots of iron python examples here:https://community.spotfire.com/wiki/ironpython-scripting-tibco-spotfire

And more specifically here for exporting examples to excel:https://community.spotfire.com/wiki/how-export-data-tibco-spotfire-using-ir...

Not sure what the reason for your error message is. The only thing I can think of, is that you have tried to run it in the web, which may not be working due to the MessageBox.

Did it already gave an error in the Analyst

Kind regards,

David

[Milton Sestito]

Hi David, Thanks for the response. I am actually running it on my desktop through an Analyst license as I've observed that my TERR scripts work in the web player version but not my Python scripts, hence I've leaned heavily towards focusing on coding with R whenver possible.I was able to work with a simpler script that works. In this example, I export the raw data from two cross-tables: Reserves and Economics into a single tab in Excel (not ideal but it works and is easy to manipulate as I know each table has 1000 rows and I can cut and paste to a new tab). Please see the script below.import System

from System.IO import FileStream, FileMode

from Spotfire.Dxp.Application.Visuals import TablePlot

from  Spotfire.Dxp.Data.Export import DataWriterTypeIdentifiers

import clr

clr.AddReference("System.Windows.Forms")from System.Windows.Forms import SaveFileDialog

SaveFile = SaveFileDialog()

SaveFile.Filter = "Excel Workbook (*.xls)|*.xls"

SaveFile.ShowDialog()

saveFilename = SaveFile.FileNamefrom Spotfire.Dxp.Application.Visuals import VisualContent,TablePlotColumnSortMode

from Spotfire.Dxp.Data.Export import DataWriterTypeIdentifiers 

from System.IO import File, StreamWriter

writer = Document.Data.CreateDataWriter(DataWriterTypeIdentifiers.ExcelXlsDataWriter)

stream = StreamWriter(saveFilename)

te = ReservesTable.As[VisualContent]()

je = EconomicTable.As[VisualContent] ()

te.ExportText(stream)

je.ExportText(stream)However, when I open the Excel file I get a warning message stating the file format and extension doesn't match and that the file could be corrupt. I elect to open it anyway and everything looks fine in Excel format. Just curious as to why this could be happening. Maybe I should try exporting as csv instead Anyhow, the script works it would just be nice if the compatibility warning would not appear every time as I provide templates for 3rd parties more often than not so I would like to eliminate anything that would cause concern on their end.  

[David Boot-Olazabal]

Hi Milton,

Thank you for your feedback.

Great that the 'simpler' code is working for you in the Analyst. Regarding the error message, does it make a difference when you replace .xls by .xlsx

I have found some other postings about possible solutions:https://community.spotfire.com/questions/exporting-excel-file andhttps://community.spotfire.com/questions/export-cross-table-excel-error-ope...

Also, for Python to be used in the web, you can install it as a service, just like TERR. Or if not yet on Spotfire 10, you could follow the guidelines to install Pythonhttps://community.spotfire.com/wiki/python-data-function-extension-tibco-sp....

Kind regards,

David