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


    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.

    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

    License:  TIBCO BSD-Style License

     

     


    User Feedback

    Recommended Comments

    There are no comments to display.


×
×
  • Create New...