Writing to Excel from Forest & Trees using Ole Automation

Products

• Cleverpath Reporter

Issue/Introduction

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.

 

Environment

Release:
Component: FTS

Resolution

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 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
objWorkBook.Add

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')
objRange.Select

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 )
        Switch colType 
            Case 'Integer'
            Case 'Double'
                intVal := View( 'GetValue', 'sample data', row, col )
                textVal := To_Char(intVal)
                Break
            Case 'Text'
                textVal := View( 'GetValue', 'sample data', row, col )
                Break
            Default
                textVal := ''
        EndSwitch

        rangeVal := Chr(Ascii('A') + col - 1) + To_Char(row)
        objRange := objExcel.Range(rangeVal)
        objRange.Select
        objExcel.ActiveCell.FormulaR1C1 := textVal
    Next col
Next row

Summary

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.