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
Recommended Comments
There are no comments to display.