Displaying an Excel Spreadsheet/Graph on your Visual LANSA Form

Date:10 August 2006
Product/Release:Visual LANSA V11
Abstract:Embedding a Microsoft Office Spreadsheet ActiveX to show Excel tables directly on your Form
Submitted By:LANSA Technical Support

The Microsoft Excel ActiveX control is very useful for updating Excel spreadsheets, generating reports or even doing complex mathematic formulae. However it is a non-visual control; you cannot see the results of what you are doing in your VL form unless you query the ActiveX about the values of particular Cells.

If you want to show an Excel style graph or spreadsheet on your application you must embed another Microsoft ActiveX control: the Microsoft Office Spreadsheet control. This is a visual control that can be placed on your form to show an Excel worksheet that your users can interact with. The example source code below is based on Microsoft Office 2003. Earlier versions of the spreadsheet ActiveX may use different property/method names however the functionality should be the same.

Image of embedded spreadsheet

The ActiveX control:

The Office Spreadsheet ActiveX contains most (if not all) of the functionality of the Excel activeX, with a few more to allow it to be visualized on your form. 

The code for the ActiveX control in LANSA would be:

Begin_Com Role(*EXTENDS #PRIM_MCCO) Progid('OWC11.Spreadsheet.11') Typelibid('{0002E558-0000-0000-C000-000000000046}1.0,0')
End_Com

For other Office versions, you may need to use the ProgID Browser and find the Office Spreadsheet control under the Controls list.

Embedding it in your form:

When you first drag the activeX onto your form you will see all the toolbars, scroll bars and row/column headings. You can remove these at runtime or in design-time. For clarity and ease of use I chose to do this at runtime in my example. If you don’t want the user seeing the control as you are making the changes (i.e. on a slow PC), you can hide the grid until you are finished making the changes.

The following example shows how you can make a table with two sets of headings to display multi-dimensional data easily. To use this, create a new form and paste the code into your form. Note this code assumes you have named the ActiveX from above as #VX_SpdSht, you may need to make some changes if you use another name. The code is commented to explain what each section does, so it should be easy to modify for your own use:

Function Options(*DIRECT)
Begin_Com Role(*EXTENDS #PRIM_FORM) Clientheight(260) Clientwidth(535) Height(294) Layoutmanager(#ATLM_1) Left(384) Top(104) Width(543)
Define_Com Class(#VX_SpdSht.Spreadsheet) Name(#SpreadSheet) Displayposition(1) Height(260) Left(0) Parent(#COM_OWNER) Tabposition(1) Top(0) Visible(False) Width(535)
Define_Com Class(#VX_SpdSht._Range) Name(#CurrentRange) Reference(*Dynamic)
Define_Com Class(#PRIM_ATLM) Name(#ATLM_1)
Define_Com Class(#PRIM_ATLI) Name(#ATLI_1) Attachment(Center) Manage(#SpreadSheet) Parent(#ATLM_1)
Define_Com Class(#Prim_nmbr) Name(#TotalColumns)

Evtroutine Handling(#com_owner.Initialize)
Set Com(#com_owner) Caption(*component_desc)

* Create a range of Cells to work with
#CurrentRange <= #SpreadSheet.ActiveSheet.Cells
#Com_Owner.LoadData
#Com_Owner.FormatSpreadsheet
Endroutine

Mthroutine Name(FormatSpreadsheet) Help('Format Excel Sheet to look like a report')
Define_Com Class(#Prim_alph) Name(#RangeString)
Define_Com Class(#Prim_alph) Name(#StartCell)
Define_Com Class(#Prim_alph) Name(#PreviousCell)
Define_Com Class(#Prim_alph) Name(#CurrentCell)

* Set up spreadsheet to only show the columns
#SpreadSheet.DisplayColumnHeadings := False
#SpreadSheet.DisplayRowHeadings := False
#SpreadSheet.DisplayGridlines := False
#SpreadSheet.DisplayWorkbookTabs := False
#SpreadSheet.DisplayToolBar := False
* #SpreadSheet.DisplayHorizontalScrollBar := False
#SpreadSheet.DisplayVerticalScrollBar := False

* Static Text
* Setting the Value of the cells can be done with either of the following commands
#CurrentRange.Range<'A2'>.Value2 := 'Salary'
#CurrentRange.Item<3 1>.Value2 := 'Total'
#CurrentRange.Item<4 1>.Value2 := 'Average'
#CurrentRange.Item<5 1>.Value2 := 'Maximum'
#CurrentRange.Item<6 1>.Value2 := 'Minimum'

* Construct a string containing an Excel range
* that encompasses the index portion of this column
#RangeString := #Com_Owner.GetCellName( 3 1 ) + ':' + #Com_Owner.GetCellName( 6 1 )
#CurrentRange.Range<#RangeString.Value>.BorderAround Weight(1) Colorindex(-4105)

* Construct a string containing a typical Excel range - e.g. A1:H2
* In this case we want the two heading rows (A1 to the last column, row 2)
#RangeString := #Com_Owner.GetCellName( 1 1 ) + ':' + #Com_Owner.GetCellName( 2 #TotalColumns )

* Set the colour of the heading column cells (RRGGBB hexadecimal format)
#CurrentRange.Range<#RangeString.Value>.Interior.Color := (C0C0C0)

* Place a border around each heading column cell
#CurrentRange.Range<#RangeString.Value>.Cells.Borders.Weight := 1

* Go through each column; merge similar column headings and then
* set border 'around' the data in each column (i.e. not for each cell)
Begin_Loop Using(#ii) From(2) To(#TotalColumns)
#CurrentCell := #CurrentRange.Item<1 #ii>.Value2
If (#CurrentCell = #PreviousCell)

* Blank the text and merge with Previous Column
#CurrentRange.Item<1 #ii>.Value2 := ''

* Construct a string containing an Excel range
* from the start of the heading column to this column
#RangeString := #StartCell + ':' + #Com_Owner.GetCellName( 1 #ii )
#CurrentRange.Range<#RangeString.Value>.Merge
Else

* Start a new column
#StartCell := #Com_Owner.GetCellName( 1 #ii )
#PreviousCell := #CurrentCell
Endif

* Construct a string containing an Excel range
* that encompasses this data column
#RangeString := #Com_Owner.GetCellName( 3 #ii ) + ':' + #Com_Owner.GetCellName( 6 #ii )
#CurrentRange.Range<#RangeString.Value>.BorderAround Weight(1) Colorindex(-4105)
End_Loop

* Construct a string containing an Excel range
* that encompasses all columns/rows in this table
#RangeString := #Com_Owner.GetCellName( 1 1 ) + ':' + #Com_Owner.GetCellName( 6 #TotalColumns )
* Set this as the only cells viewable
#SpreadSheet.ViewableRange := #RangeString

* The following command locks the spreadsheet - use only after setting contents above
* #SpreadSheet.ActiveSheet.Protect

* All Changes made, show grid
#SpreadSheet.Visible := True
Endroutine

Mthroutine Name(LoadData) Help('Read details from database and populate Excel sheet')
Define_Com Class(#Prim_nmbr) Name(#EmpCount)
Define Field(#MaxSalary) Reffld(#Salary)
Define Field(#MinSalary) Reffld(#Salary)
Define Field(#TotSalary) Reffld(#Salary)
Define Field(#AvgSalary) Reffld(#Salary)

#TotalColumns := 1
Select Fields(#Deptment #Section) From_File(Sectab)

* Set up the headings
#TotalColumns += 1
#CurrentRange.Item<1 #TotalColumns.Value>.Value2 := #Deptment
#CurrentRange.Item<2 #TotalColumns.Value>.Value2 := #Section

#EmpCount := 0
#MaxSalary := 0
#MinSalary := 0
#TotSalary := 0
#AvgSalary := 0

Select Fields(#Salary) From_File(Pslmst1) With_Key(#Deptment #Section)
If ((#MinSalary = 0) Or (#MinSalary > #Salary))
#MinSalary := #Salary
Endif
If (#MaxSalary < #Salary)
#MaxSalary := #Salary
Endif
#TotSalary += #Salary
#EmpCount += 1
Endselect

If (#EmpCount > 0)
#AvgSalary := #TotSalary / #EmpCount
Endif

#CurrentRange.Item<3 #TotalColumns.Value>.Value2 := #TotSalary
#CurrentRange.Item<4 #TotalColumns.Value>.Value2 := #AvgSalary
#CurrentRange.Item<5 #TotalColumns.Value>.Value2 := #MaxSalary
#CurrentRange.Item<6 #TotalColumns.Value>.Value2 := #MinSalary
Endselect
Endroutine

Mthroutine Name(GetCellName) Help('Generate Excel Cell Name from a row index and column index')
Define_Map For(*Input) Class(#prim_nmbr) Name(#i_row)
Define_Map For(*Input) Class(#prim_nmbr) Name(#i_column)
Define_Map For(*Result) Class(#prim_alph) Name(#result)
Define Field(#WrkField1) Type(*Packed) Length(3) Decimals(0)
Define Field(#WrkField2) Type(*Packed) Length(3) Decimals(0)

If (#i_column > 26)
* Column is past the single letter columns, find both letters

If (#i_column > 256)
* Excel Spreadsheet only goes up to column IV, all others ignored
#result := 'IV' + #i_row.AsString
Return
Endif

#WrkField1 := #i_column / 26
#WrkField2 := #i_column - (#WrkField1 * 26)
#result := #Com_owner.NumToLetter( #WrkField1 ) + #Com_owner.NumToLetter( #WrkField2 ) + #i_row.AsString
Else
#result := #result + #Com_owner.NumToLetter( #i_column ) + #i_row.AsString
Endif
Endroutine

Mthroutine Name(NumToLetter) Help('Convert a number from 1-26 into the corresponding alphabet letter')
Define_Map For(*Input) Class(#Std_Count) Name(#i_Num)
Define_Map For(*Result) Class(#Std_Flag) Name(#o_Letter)

If (#i_Num < 1)
#o_Letter := 'A'
Return
Endif

If (#i_Num > 26)
#o_Letter := 'Z'
Return
Endif

* Convert to Ascii letter
#o_Letter := (64 + #i_Num).AsChar
Endroutine