An alternative method of exporting data to an Excel spreadsheet from a Forest and Trees application is to programmatically use Ole Automation. This article will give a concise description as to how this can be achieved.
To use the Ole automation features of excel with F&T, any installation of Excel is required. A small sample application will give a little illustration of the functionality.
The files that are used in this demonstration can be downloaded from here:
The application demonstrates how to write data from a F&T view into any Excel worksheet. Here follows an explanation of how this is achieved.
All OleObject variable objects should be declared as global. In the sample F&T application the global object variables used are shown below.
Creating and opening a new worksheet
Within your FTV you must instantiate the Excel Ole Automation server object. This is achieved like this
objExcel := New OleObject( 'Excel.Application' )
To open and add a new workbook,
objWorkbook := objExcel.Workbooks
To display the workbook
objExcel.visible := TRUE
Writing Data to the worksheet
To move to any cell a 'Range' must be declared and selected for example to move to cell G10,
objRange := objExcel.Range('G10')
To insert the value 'Forest and Trees' into the selected cell do,
objExcel.ActiveCell.FormulaR1C1 := 'Forest and Trees'
All we need to do within the application is to select each cell and insert each value into its own cell. The following F&T function will extract the data from the sample data view and insert into the excel spreadsheet.
Dim row, col, nCols, nRows, intVal As Integer
Dim colType, textVal, rangeVal As Text
nCols := View( 'GetNumColumns', 'sample data' )
nRows := View( 'GetNumRows', 'sample data' )
For row = 1 To nRows
For col = 1 To nCols
colType := View( 'GetColumnType', 'sample data', col )
intVal := View( 'GetValue', 'sample data', row, col )
textVal := To_Char(intVal)
textVal := View( 'GetValue', 'sample data', row, col )
textVal := ''
rangeVal := Chr(Ascii('A') + col - 1) + To_Char(row)
objRange := objExcel.Range(rangeVal)
objExcel.ActiveCell.FormulaR1C1 := textVal
It is possible to use almost any Excel function from F&T. The scope of this article cannot cover the use of all of the functions but it should give a general guide.