How to import multiple Excel spreadsheets in Spotfire® Using IronPython Scripting

Introduction

When importing data from Excel, only the first spreadsheet is selected via the API. The script below shows an example of how to rotate the spreadsheets and import the data into Spotfire.

Code Sample

# Copyright © 2017. TIBCO Software Inc. Licensed under TIBCO BSD-style license.

from Spotfire.Dxp.Data import DataTableSaveSettings
import clr

clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')

from Microsoft.Office.Interop import Excel

# Assuming data tables in this analysis have the same name as the workbook spreadsheets.
dataTablesToUpdateFromExcel = ["sheet1", "sheet2", "sheet3", "sheet4"]

# Local path and name of the excel file.
filename= "C:TestDataTestData.xlsx"

# Microsoft Excel application
excelApp = Excel.ApplicationClass()   
excelApp.Visible = False
workbook = excelApp.Workbooks.Open(filename)

# Perform the data import 
for entry in dataTablesToUpdateFromExcel:

	# Rotate the spreadsheets to the beginning of the file      
	workbook.worksheets(entry).move(workbook.worksheets(1))
	workbook.save()

	# Find out if a data table already exist in the DXP file
	status, table = Document.Data.Tables.TryGetValue(entry)

	# Create data source
	ds = Document.Data.CreateFileDataSource(filename)
	ds.IsPromptingAllowed=False

	# Replace data table with new data if found
	if status:
		table.ReplaceData(ds)

		# Add a new data table, if corresponding table is not found
    	elif not status:
        	# Add table
        	myNewTable = Document.Data.Tables.Add(entry,ds)

        	# Keep the data embedded or linked
		# Embedded
		# settings = DataTableSaveSettings (myNewTable,False, False);
		# Linked
        	settings = DataTableSaveSettings (myNewTable,True, False);
        	Document.Data.SaveSettings.DataTableSettings.Add(settings);

# Exit MS Excel 
excelApp.Workbooks.close()
excelApp.quit()

References

• API Reference: CreateFileDataSource
• API Reference: DataTableSaveSettings
• API Reference: MS Excel

License:  TIBCO BSD-Style License