Exporting to Excel with IronPython

Exporting to Excel with IronPython from Spotfire desktop is pretty simple. I found a code snippet on TIBCO community that worked. However, it gets complicated quickly if you want the same functionality from the web player. You’ll find the same code doesn’t work. But, there is a workaround! Thus, I have put together this post explaining exporting to Excel from both Spotfire desktop and web player.

Script to Export from the Desktop App

When working in the desktop application, attach the script below to an action control button in a text area to export to Excel. After clicking the button, Spotfire prompts users to select a location for the export, and they are done. Easy.

Here is the code snippet for easy copy and paste.

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.FileName

from 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 = CashFlow.As[VisualContent]()
te.ExportText(stream)

Attaching a Script to an Action Control Button

If you’ve never attached an IronPython script to an action control button, follow the simple steps below.

1. Right-click on a text area and select Edit HTML.
2. Click the add action control button
3. In the text box, give the button a name.
4. Make sure the control type is button.
5. Then, click the Script button.
6. Click the New button.
7. Name the script.
8. Copy and paste or type out your script.
9. Click Run to make sure there are no errors.
10. Click Ok and Ok to exit.

Unfortunately, this script will not work in the web player. It will generate the following error.

Why Does the Code Fail?

As explained in this community post, the code is using Windows forms, which aren’t supported in web player. Just to be clear, that’s this part of the code….

from System.Windows.Forms import SaveFileDialog

So, let me show you a workaround.

Script to Export from the Web Player

The only workaround for this limitation is to remove the reference to Windows forms and use property controls instead. Here is my modified code. As you can see, I have created objects called “folderName” and “fileName” and connected them to two document properties.

This code connects to my two input property controls also named “folderName” and “fileName”.

This code is a combination of code from 2 community posts. The first one is here. The second is at this community post. I made modifications to use the “ExcelXlsxDataWriter” and to incorporate my property controls. Also note, in my first code snippet above I used the ExcelXlsDataWriter. This code snippet uses the ExcelXlsxDataWriter. Make sure to use the correct one for the file extension you want.

from Spotfire.Dxp.Data.Export import DataWriterTypeIdentifiers 
from System.IO import File, Directory

#Set the DataTable you want to use.
table = Document.ActiveDataTableReference 
writer = Document.Data.CreateDataWriter(DataWriterTypeIdentifiers.ExcelXlsxDataWriter)
#Set a filtering or use a active one.
filtering = Document.ActiveFilteringSelectionReference.GetSelection(table).AsIndexSet()


fullPath = Document.Properties["fullPath"]
folderName = Document.Properties["folderName"]


# directory
if not Directory.Exists(folderName):
    Directory.CreateDirectory(folderName)


stream = File.OpenWrite(fullPath)

names = []
for col in table.Columns:
  names.append(col.Name)

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

stream.Close()

Wrap Up

In conclusion, exporting to Excel with IronPython is possible. But, you must implement the solution differently depending on which platform users launch from.

Spotfire Version

Content created with Spotfire 10.2.

If You Enjoyed This, Check Out Other Posts

• Changing Checkbox Filters with IronPython
• Commenting Out Multiple Lines of IronPython
• How to Reset Markings When Changing Pages