Tuesday, 28 January 2014

Mortality Manager - Progress and NetOffice


Since the last post, a bit of progress has been made:
  • Mortality tables can be selected from the SoA site 
  • These can be downloaded and stored locally
  • The files are handled as F# Records
  • The files are persisted as Json using Json.NET
  • A local index of these files is created
  • Facilities are provided to update the index and remove unwanted files
The users interface has been slightly expanded and now appears like this:

Interacting with Excel

Code is now being added to update the workbooks and worksheets running in the active session of Excel.

This is surprisingly quite troublesome to achieve. The standard facilities provided are to use Primary Interop Assemblies provided with Office. This is however problematic, as this typically only allows you to develop code that runs with a single version of Office.


Thankfully, there is an option that addresses this version limitation - NetOffice.

This is reasonably simple to set up, but even so the code you need to write is quite "clumsy". (Note that this is not a limitation of NetOffice - you would need to use the same code with the Primary Interop Assemblies).

As an example:

namespace MortMgr

open NetOffice
open NetOffice.ExcelApi
open NetOffice.ExcelApi.Enums

module Xint = 
    /// set cell value on Active Sheet
    let CellValue (r, c) vl = 
        let xlapp = Application.GetActiveInstance(true)
        let ws = xlapp.ActiveSheet :?> Worksheet
        ws.Cells.[r, c].Value2 <- vl

All this code does is write a single value to the ActiveSheet in Excel at row r and column c. The code as you can see is not pretty and requires casting (using of :?> in F#) to make it work.

Hopefully, I can find a better way to do this or, if not, I might write some F# utility code to simplify this aspect.