Writing to Excel in F#

Question

Has anyone got experience of writing values to an Excel spreadsheet from F# using Microsoft.Office.Interop? I can read values fine, but when I try to change the value of a cell I always get an error. I have seen posts with this error where column or row are 0, but in my case those values are always positive. I am getting my Excel Application object from excel-dna.

    let getCell (sheet: Excel.Worksheet) (row: int, col:int) =
        let rng = sheet.Cells.Item(row, col) :?> Excel.Range
        match isNull rng with 
        | true ->
            None
        | false ->
            match isNull rng.Value2 with 
            | true -> None
            | false  -> 
                let s = "Hello"
                let o = s :> obj // passing in unboxed string does not help
                rng.Value2 <- o // this line throws an error Exception from HRESULT: 0x800A03EC Error
                Some rng.Value2

Answer 1

I use NetOffice (https://netoffice.io/ - be careful to grab the right Nuget packages).

For example, this code works when called from a ribbon handler after opening a new, blank workbook:

open NetOffice.ExcelApi

...

use app = new Application (null, ExcelDnaUtil.Application)
let wb = app.ActiveWorkbook
let sheet = wb.Sheets.["Sheet1"] :?> Worksheet
let range = sheet.Cells.[1, 1]
range.Value2 <- "Hello"

Answer 2

I found the answer to my question here

My error was to try and update a cell from inside a function that had been exported to Excel via an add-in. UDFs are not allowed to have side-effects. The solution is to add a lambda to a queue of stuff to be done once the current function completes (ExcelAsyncUtil.QueueAsMacro).

Code here is C# as I wanted to see if F# was the problem, which was not the case - though I have yet to try passing in a lambda function from F#

using ExcelDna.Integration;
using Microsoft.Office.Interop.Excel;
using System;

public static class MyFunctions
{
    [ExcelFunction(Description = "My first .NET function")]
    public static string HelloDna(string name)
    {
        name = "Hello " + name + ".  No error occured";
        Application xlApp = (Application) ExcelDnaUtil.Application;
        Worksheet ws = (Worksheet) xlApp.ActiveSheet;
        Range r = (Range) ws.Cells[3, 3];
        try
        {
            r.Value2 = "Hello"; //throws error because I am updating a cell in code that is calculating a value
        }
        catch (Exception e) {            
            name = e.Message;
        }
        return name;
    }

    [ExcelFunction(Description = "My second .NET function")]
    public static string HelloDna2(string name)
    {
        ExcelAsyncUtil.QueueAsMacro(() =>
        {
            var refB1 = new ExcelReference(0, 0, 1, 1, "Sheet1");
            refB1.SetValue("Done!");
        });

        return "Hello " + name;
    }
}

F#

    let putCellAsync (sheet: Excel.Worksheet) (s:string, row: int, col:int) =
        let f = ExcelAction(fun () ->
            let refB1: ExcelReference = new ExcelReference(row, row, col, col, "Sheet1")
            ignore <| refB1.SetValue(s)
        )
        ExcelAsyncUtil.QueueAsMacro(f)

Answer 3

Another option is SharpCells. This add-in I am developing to give a better user experience than the Microsoft.Office.Interop APIs particularly when using F#. You would set a cell imperatively like this:

[<UDF(MacroType = XLMacroType.CommandMacro)>]
let MyFirstCommand (xlm: XLMacro) =
    // Sets cell A1 on the Active Worksheet to "hello"
    xlm.Set(XLValue12.Of("hello"), 0, 0)
    // Return 1 to indicate macro success
    1

A CommandMacro can be bound to a button on the worksheet and called like a VBA subroutine.