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

24 thoughts on “Exporting to Excel with IronPython”

  1. Hi Julie. I tried to get the script inside the “Script to Export from the Web Player” section to work. However, I noticed that there are a few things inside the script that I noticed:

    1. The script exports the file on the WebPlayer server which is of no use to a regular user who doesn’t have access to the server so they can get the file

    2. The right extension seems to be “dxp” and not “xlsx” as we are using the “SaveAs” method of the Application class (see https://docs.tibco.com/pub/doc_remote/spotfire/7.6.0/doc/api/html/T_Spotfire_Dxp_Application_AnalysisApplication.htm)

    1. Thanks for checking out the script. I need to look at your comments in more detail, but… on no 1, for my use case, I used a shared drive location in my property control, so that I would still be able to get to the output. Any user can get to it. They don’t need access to the server. On no 2, I’ll have to look at that. The example I put together did work for xlsx and I didn’t have to export as a dxp.

    2. Okay, I looked closer at it. As long as you use a shared drive location, the user does not need access to the web player server. The export will be fine. You are right about the extension. When I tested this, I tested that it exported an Excel file, and it does. The problem crops up when you actually try to open that file. I’ll dig into your TIBCO link and see about a solution next week.

  2. Hi, thank you for your reply. I haven’t thought about using a shared drive option to solve our problem of getting the generated xlsx file down to the client, will think about that. I am assuming that your users are on the same network where the WebPlayer server is and everybody can access the shared folder (or drive)?

    As for the Application’s SaveAs method, I think the downloaded file from the script above cannot be opened as it’s actually the whole document (i.e. dxp, see the description of the SaveAs method) and we are just setting its file extension to “xlsx” which won’t open.

    1. Yes, users are all on the same network and everyone can access the shared folders/drive. It’s a fairly small company. 🙂
      You are right about the document. I started putting reworking it but haven’t finished it yet.

  3. Pingback: Copy and Paste, Then Filter with IronPython » The Analytics Corner

  4. Pingback: Set Multiple Scale Ranges with Document Properties » The Analytics Corner

  5. Why the use of “CashFlow”? Is there a more generic way to that? Trying the code with our Spotfire 10.3.3 gives:
    Could not perform action ‘Export Data’.
    Could not execute script ‘Export Data’: name ‘CashFlow’ is not defined

    1. First, here is the community post I was originally trying to link to (where I got the code). https://community.tibco.com/questions/need-learn-how-create-export-excel-button-function-my-dashboard.
      I think CashFlow might have been a table that I was exporting. You are getting the error because CashFlow isn’t defined as an object or a parameter. The community post discusses this in the first comment. I don’t have the DXP I created the blog post with the reference any further.

  6. Hi Julie,

    I am a newbie in the IronPython world. Is there a way to export the table to a specific worksheet in an existing file?, and even a a specific cell range?

    Thanks a bunch,

    AC

    1. I would expect that you could do this by modifying the line of code that saves it to Excel.

      SaveFile.Filter = “Excel Workbook (*.xls)|*.xls”

      Based on what I’ve done in other programs, I would think you might be able to add pipes and then specify the worksheet name…. maybe like this…

      SaveFile.Filter = “Excel Workbook (*.xls)|*.xls|||MyWorksheet”

  7. Gnanavel Divakaran

    Hi Julie,

    I am using the script to export into excel from web player shared by you. In Desktop I am getting the file as expected. But when I deploy in library and run from webserver I am not getting the file. I pointed the file path to my local drive

    1. The problem is that you are pointed to your local drive. Spotfire server cannot connect to your local drive. You need to use a networked file path.

  8. Gnanavel Divakaran

    Hi Julie,

    I am using the script for exporting into excel from web server. But when I try to run in web server I am not seeing the file getting downloaded in the desired path. The filepath I provided is my local drive.

  9. Hi Julie,

    I am using your base script to export Table and Cross Table visualizations from my large *.dxp file to Excel. I modified your script to loop through all pages and all visualizations and stop at any Table or Cross Table and export. The script is exporting the data to Excel. I can open the Excel file and see the data. However, I am getting a message when I open the file that it may be corrupt or not in the correct format. I was just wondering if you might know why.

    Thank you in advance,
    Thomas

    1. I swear I’ve gotten that error before. I think I just click past it and it’s fine. Unfortunately, I haven’t worked with Spotfire in 3 years.

Leave a Reply to Julie Sebby Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.