Laserfiche WebLink
TSTool Documentation Spreadsheet Integration <br /> Spreadsheet Integration - 5 <br /> <br />A more robust way to manipulate Excel files is to utilize a software package or library that has been <br />written specifically to manipulate Excel files through an application programmer interface (API). The <br />following options are available for integrating TSTool an d Excel and an appropriate option should be <br />chosen for the specific environment, problem, and user : <br /> <br />• Use built -in TSTool features to interact directly with Excel *.xls , *.xlsx file s : <br />o The TSTool ReadTableFromExcel() command will read a table from Excel and the <br />table can be processed by other table commands. For example, use the <br />TableToTimeSeries() command to convert data read from Excel into time series <br />that can be further processed with TSTool commands. <br />o There currently is no TSTool command implemented to w rite to an Excel workbook , <br />although this may be implemented in the future . <br />o There currently is no TSTool command implemented to read time series directly from an <br />Excel workbook (without using the TableToTimeSeries() command), although <br />this may be implemente d in the future. <br />o There currently is no TSTool command implemented to write time series directly to an <br />Excel workbook, although this may be implemented in the future. <br />o The Apache POI package (http://poi.apache.org) is used for integration with Excel files. <br />F unctionality that is envisioned for future TSTool enhancements can be implemented <br />using this package. <br />• Use built -in TSTool features to interact directly with Excel *.csv file s , which can be edited by <br />Excel and do not include formatting, formulas, etc.: <br />o Use the ReadDelimitedFile() command to read time series from a delimited file <br />that was exported from Excel as a *.csv file . <br />o Use the ReadTableFromDelimitedFile() command to read a table from a <br />delimited file that was exported from Excel as a *.csv file . <br />o Use the WriteDateValue() command to write a DateValue format file. The bottom <br />part of the file can be read i nto Excel as a delimited file. A command may be <br />implemented in the future to write time series to a delimited file (with no extra <br />information). <br />o Use the WriteTableToDelimitedFile() command to write a TSTool table to a <br />delimited (*.csv ) file that can be opened in E xcel. <br />• Use built -in TSTool features to interact with an Exc el file as if it is a database : <br />o Define an ODBC DSN for the Excel file and use a GenericDatabaseDataStore (see <br />Generic Database DataStore appendix). Use DatabaseEngine=”Excel” and <br />specify the OdbcName property in the datastore configuration file. <br />o Use the ReadTableFromDataStore() command: <br /> The Excel worksheet must be relatively simple with column headings in the first <br />row. <br /> N eed a good reference for EXCEL SQL statements, in particular showing <br />complex queries and how to write to the sheet ? What are the limitations on SQL <br />when processing E xcel files ? <br />o Optionally use the TableToTimeSeries() command to convert the table to time <br />series. <br />o There currently is no command to read a datastore table into a time series (without using <br />the TableToTimeSeries() com mand); however, this capability may be added in <br />the future. <br />• Use Python software to read/write Excel files and create files that can be processed with TSTool <br />commands, such as a *.csv file and the ReadTableFromDelimitedFile() command . The <br />133