Sunday, 12 January 2014

Mortality Manager

Introduction

In the next few posts I will develop a manager for mortality tables. This will use the data loaded in earlier posts and develop a simple management utility for this data in Excel.

The main tool I will use for this is FCell.

The code I will be developing will be hosted on BitBucket at:
https://bitbucket.org/pb_bwfc/mortality-manager

Setting Up Visual Studio

To set up working with an Excel UI with FCell requires first creating a suitable F# Library. In this case, I wanted to use WPF to create the dialogs, which requires a bit more effort than using WinForms.

Using Visual Studio, after creating an empty project, just add these references:
  • PresentationCore
  • PresentationFramework
  • System.Drawing
  • System.Windows.Forms
  • System.Xaml
  • UIAutomationTypes
  • WindowsBase
  • WindowsFormsIntegration
From FCell, you then need to add a reference to:
  •    FCell.Ribbon

 

Adding Skeleton Code

To get the basic UI in Excel you create code similar to the following:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
27: 
28: 
29: 
30: 
31: 
32: 
33: 
34: 
35: 
36: 
37: 
38: 
39: 
40: 
41: 
42: 
43: 
44: 
45: 
46: 
47: 
48: 
49: 
50: 
51: 
52: 
53: 
54: 
55: 
56: 
57: 
58: 
59: 
60: 
61: 
62: 
63: 
64: 
65: 
66: 
67: 
68: 
69: 
70: 
71: 
72: 
73: 
74: 
75: 
76: 
77: 
78: 
79: 
80: 
81: 
82: 
83: 
84: 
85: 
namespace MortMgr

open FCell.Ribbon
open System.Reflection
open System.Windows
open System.Windows.Controls
open System.Windows.Forms.Integration
open System.Runtime.InteropServices

module ExcelUI = 
    //force a new assembly version on each build
    [<AssemblyVersion("1.0.0.*")>]
    do ()
    
    type SettingsDlg() as this = 
        inherit Window()
        do 
            this.Title <- "Settings"
            this.Width <- 350.
            this.Height <- 200.
            this.WindowStartupLocation <- WindowStartupLocation.CenterScreen
            let svb = Button(Content = "Save", Width = 75.)
            let cnb = Button(Content = "Cancel", Width = 75.)
            let ftb = TextBox(Name = "FolTxBox", Text = "C:\Mort", Width = 275.)
            let dp = DockPanel()
            let sp = StackPanel()
            let sph1 = StackPanel(Orientation = Orientation.Horizontal)
            let sph2 = StackPanel(Orientation = Orientation.Horizontal)
            sph1.Children.Add(Label(Content = "Folder:")) |> ignore
            sph1.Children.Add(ftb) |> ignore
            sph2.Children.Add(svb) |> ignore
            sph2.Children.Add(cnb) |> ignore
            sp.Children.Add(sph1) |> ignore
            sp.Children.Add(sph2) |> ignore
            dp.Children.Add(sp) |> ignore
            this.Content <- dp
    
    type MortCTP() as this = 
        inherit UserControl()
        do 
            let b = Button(Content = "Click Me", Width = 75.)
            let dp = DockPanel()
            let sp = StackPanel()
            sp.Children.Add(Label(Content = "Mortality Manager")) |> ignore
            sp.Children.Add(b) |> ignore
            dp.Children.Add(sp) |> ignore
            this.Content <- dp
            b.Click.Add(fun _ -> System.Windows.Forms.MessageBox.Show("Test Button Clicked") |> ignore)
    
    //implement your Custom Task Pane as Windows UserControl:
    [<ComVisible(true)>]
    type MortTaskPane() as this = 
        inherit System.Windows.Forms.UserControl()
        do 
            let wpfElementHost = new ElementHost(Dock = System.Windows.Forms.DockStyle.Fill)
            do this.Controls.Add(wpfElementHost)
            let mctp = new MortCTP()
            wpfElementHost.HostContainer.Children.Add(mctp) |> ignore
    
    [<ComVisible(true)>]
    type MyFRibbon() = 
        inherit XlRibbon()
        //define your Ribbon customization as xml, see Office documentation for details
        override this.GetCustomUI(ribbonID : string) = @"
            <customUI xmlns='http://schemas.microsoft.com/office/2006/01/customui' onLoad='OnLoad' loadImage='GetImage'>
                <ribbon>
                <tabs>
                    <tab id='MyTabId' label='Mort'>
                    <group id='MyGroupId' label='Admin'>
                        <button id='MyButtonId' imageMso='PageMenu' label='Settings'
                        size='large' onAction='OnButtonClick' />
                    </group>
                    </tab>
                </tabs>
                </ribbon>
            </customUI>"
        
        member this.OnButtonClick(control : IRibbonControl) = 
            let dlg = new SettingsDlg()
            dlg.ShowDialog() |> ignore
        
        //create Custom Task Pane
        override this.OnCTPFactoryAvailable() = 
            let ctp = this.CreateCustomTaskPane(typeof<MortTaskPane>, "MortTaskPane")
            ctp.Visible <- true

Setting up and Using in FCell

To use this code in FCell - simply open up Excel and add the location of the compiled .DLL to the .NET Search Folders in this dialog:




The .DLL will then be loaded and an extra tab will be added to the Ribbon and a Custom Task Pane will be visible:




No comments:

Post a Comment