Writing or exporting data directly to an excel file via IronPython and ODBC.
Introduction
Writing or exporting data directly to an excel file can be done with the ODBC method. This requires to have the Microsoft Access Database Engine Driver and setup the System DSN by using the ODBC Data Source Administrator.
.gif.bbae5e75dc715d9e93f10d41f9fb6a69.gif)
Setup System DSN ODBC Excel Data Source
.png.91c3d365009301aacef5bc641b2dcf04.png)
.png.a6f6e0a2cc1b82590a28ea94f7c25674.png)
.png.67b8412a410c64c26292d023d1b40893.png)
.png.fdae5b8dc7e14eaba49b850b163e8301.png)
Make sure to select the correct version and excel file and unset the Read Only checkbox
Code Sample
from Spotfire.Dxp.Data.Import import DatabaseDataSource, DatabaseDataSourceSettings from Spotfire.Dxp.Data import DataSourcePromptMode import datetime #set parameters to pass to sql tstamp = datetime.datetime.now().strftime("%Y-%m-%d %H:%m:%S") comment = Document.Properties[dp] score = Document.Properties["rating"] #prepare sql sql = "INSERT INTO [sheet1$] ([date],[comment],[rating]) VALUES ('{0}','{1}',{2})" sql = sql.format(tstamp,comment,score) #setup data source dbSettings=DatabaseDataSourceSettings("System.Data.Odbc","DSN=system_dsn_excel",sql) ds=DatabaseDataSource(dbSettings) #Add a temporary datasource data table to execute query and dispose tbl = "ODBC_Table_excel" #temporal spotfire table that holds no data, but inserts data to sql database Document.Data.Tables.Add(tbl,ds) Document.Data.Tables[tbl].Remove()
Recommended Comments
There are no comments to display.