ExcelDNA observables dirty the sheet on each OnNext

[Corey Kosak]

This is possibly a dumb question, but my users are surprised that my observables dirty the sheet when they tick.

Put another way, my users are surprised by this workflow:

1. New sheet
2. Put =Tick() in a cell somewhere
3. Let it tick a few times
4. Save the worksheet
5. Let it tick a few more times
6. Close the sheet

Expected behavior: Sheet closes

Actual behavior: Excel prompts to save the sheet before closing

My users find this surprising because *they* didn't change anything between steps 4 and 6. The users feel that the formula they entered is the sheet's "actual" content; the fact that that formula is yielding changing dynamic array values over time is felt to be ephemeral and shouldn't be part of the nature of whether the sheet has changed and needs to be saved.

My silly little test program is below. Do you have an opinion on which of the below is closest to the truth

1. This is just the way things are.
2. There is an easy workaround
3. I am doing things the wrong way

Thank you,

Corey

using System.Diagnostics;
using ExcelDna.Integration;

namespace ExcelRepro;

public static class MyFunctions {
  [ExcelFunction(Description = "Ticking value")]
  public static object Tick() {
    return ExcelAsyncUtil.Observe("Tick", Array.Empty<object>(), () => new MyObservable());
  }
}

public class MyObservable : IObservable<object> {
  private readonly object _sync = new();
  private readonly HashSet<IObserver<object>> _observers = new();
  private int _value = 0;
  private CancellationTokenSource _cts = new();

  public IDisposable Subscribe(IObserver<object> observer) {
    lock (_sync) {
      _observers.Add(observer);
      if (_observers.Count == 1) {
        var token = _cts.Token;
        Task.Run(() => DoTick(token), token);
      }
    }

    return new ActionAsDisposable(() => {
      lock (_sync) {
        _observers.Remove(observer);
        if (_observers.Count == 0) {
          _cts.Cancel();
          _cts = new CancellationTokenSource();
        }
      }
    });
  }

  private async Task DoTick(CancellationToken token) {
    try {
      var integrationAssembly = typeof(ExcelDnaUtil).Assembly;
      var version = integrationAssembly.GetName().Version.ToString();
      while (true) {
        await Task.Delay(TimeSpan.FromSeconds(1), token);

        IObserver<object>[] copyOfObservers;
        int currentTick;
        lock (_sync) {
          currentTick = _value++;
          copyOfObservers = _observers.ToArray();
        }

        var result = new object[2, 2];
        result[0, 0] = "version =>";
        result[0, 1] = version;
        result[1, 0] = currentTick;
        result[1, 1] = "<= tick";

        foreach (var observer in copyOfObservers) {
          observer.OnNext(result);
        }
      }
    } catch (Exception e) {
      Debug.WriteLine($"Caught {e}");
    }
  }
}

public class ActionAsDisposable(Action action) : IDisposable {
  public void Dispose() {
    action();
  }
}

[Govert van Drimmelen]

Hi Corey,

 

Excel saves both the formulas and the cell values in the .xlsx file.

You can also recreate the behaviour using one of the built-in volatile functions like =NOW() or =RAND(), and pressing F9 to recalculate after saving.

The recalculation, which changes some cell values, also ‘dirties’ the workbook.

 

Your users have a reasonable model of how a spreadsheet-like calculation system might work - separating the internal formula-based model from the evaluated values (as a strictly presentation layer). But that is not how Excel works.

I would not suggest attempting to work around this, unless you’re building something like a version-control system for Excel models.

And even then, your users are probably better off updating their expectations of the out-of-the box behaviour.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/exceldna/203ede92-9270-4dcd-a873-2f5341eb1a66n%40googlegroups.com.

[Corey Kosak]

Ah, great, that makes sense. Thank you for the great explanation Govert.