What's the best way of writing back to a function's input arguments?

[earcam]

Hello,

I'm migrating an old C XLL (that included a JVM with JNI calls), to a
C# based addin (currently automation which is too slow).

The original addin wrote back to some of the functions' arguments and
allowed users to overwrite this data (which at a later stage was sent
off to a remote server), e.g.

fn(MODEL_ID, DATE_RANGE, INPUT_OUTPUT_RANGE)

So once the data is available (on a setup sheet) the fn function is
triggered and it writes to INPUT_OUTPUT_RANGE in another thread. When
this happens the function is invoked again (as it changed the contents
of INPUT_OUTPUT_RANGE), so it compares the i/o data and if it's the
same does nothing, if it's different it assumes the user has edited
the cell and updates the model.

Copying this behaviour from the original C addin into the automation
addin resulted in hideous performance and lots of contention causing
application busy exceptions. In a fit of desperation I converted all
the functions to be array functions and thought I could hook into
Excel and trap all edit/paste events, and simply write these back to
the model and mark relevant ranges as dirty and let Excel work
naturally - this has proved very painful, so I think I must resort to
the original behaviour of writing back to a function's own arguments.


So my question is what's the best way of implementing this with
ExcelDNA?
Is there an existing write-back-worker thread?
Also is there any way to mark cells as clean (to avoid the second
invocation)?



I wished I'd understood how poorly automation addins perform before I
posted this:
http://stackoverflow.com/questions/7879468/global-keyboard-hook-for-excel-automation-addin-not-vsto/7880073#7880073

Desperately hoping ExcelDNA can save me.

thanks,
Caspar

[Govert van Drimmelen]

Hi Caspar,

Yes, Excel-DNA can save you.

I'll lay out three approaches for you to consider:

1. Since your original code was a C XLL, easiest might be to just use
the C API from your C# add-in. In Excel-DNA this is very easy, since
you don't have to deal with XLOPERS or anything - all the marshaling
between XLOPERS and .NET types and the memory management is done
automatically. The C API is exposed through the
(ExcelDna.Integration.)XlCall.Excel method. This means you can port
the patterns that worked well in the old .xll to your new C# add-in,
and expect similar performance and stability.
However, you don't really describe how you implemented the "writes to
INPUT_OUTPUT_RANGE in another thread" in the old .xll. Since the C API
can only be called from the main Excel thread, you probably have some
ON.TIME handler that periodically checks shared data, but that causes
other problems. Whatever you do in the old .xll you'll be able to do
in the new C# add-in, probably more easily.

2. One way to initiate work on the main thread from another thread is
to run a macro via the COM interface, by calling Application.Run. If
you get COM to do the cross-apartment marshaling for your Application
interface, COM will also give the cross-thread marshaling you need to
safely have Application.Run running on the main thread, from which you
can change that cell and the rest follows. An example of using this
mechanism to update some cells from a separate thread is implemented
in the Distribution\Samples\ArrayResizer.dna sample. With Excel-DNA
you'll get a version of the Application interface that you can use
from a particular thread by calling the ExcelDnaUtil.Application
property from the thread where you want to use it. One major issue to
watch out for is that the cross-thread COM call can always fail since
the main Excel thread might be busy. So you need some error handling
with retry in the execution path running on the other thread - the
sample implements this too.

3. The best and most scalable approach, but also a bit more work and
with its own quirks, is to implement your notification as an RTD
server. This integrates very well into Excel's calculation
and dependency model, but there are still some issues to look out
for. For example, you have the same cross-thread problem when making
the call back to Excel. The typical way to deal with this is to set up
a _Windows_ timer on the main Excel thread which essentially polls for
updates, and notifies Excel periodically if there are changes. Excel
then takes care of calling the function again etc. Excel-DNA makes RTD
easier by (mostly) allowing you to deploy it without any COM
registration on the client, and also no administrator right needed to
deploy. All registration is done as-needed in the user's part of the
registry. The RTD servers can be wrapper in your own friendly-looking
functions, so you might even be able to get rid of the
INPUT_OUTPUT_RANGE, and just have a self-updating function.
There are some example RTD servers in the Distribution\Samples\RTD
directory, and the FinAnSu add-in (http://code.google.com/p/finansu/)
implements an RTD server to provide updating real-time web quotes.

I don't know of any way in Excel to mark cells as 'clean', so you
should implement some sort caching for any slow functions.

I hope this gives you some ideas, and I look forward to more detailed
questions and hearing how you approach it.

Regards,
Govert

> posted this:http://stackoverflow.com/questions/7879468/global-keyboard-hook-for-e...

[Govert van Drimmelen]

Hi Caspar,

On second thought, and as a completely independent response, I'd
suggest "Don't do it!"

Your question is:
"I have an old super-convoluted add-in that works in a way that is
totally contrary to how spreadsheets are designed to work. I'm
struggling to re-implement this weird behaviour. Can you help?"

You could probably un-ask the question by changing the add-in design
to something more straight-forward. Perhaps
* don't use _functions_ to update data to the back. Make some
templates sheets that users can fill in, and a nice Ribbon button that
reads and validates the data, then sends it to the back,
* use functions in a functional way. For functions that might change
given the same input values, make the function volatile or implement
as an RTD server. But don't try to write back to some arguments,
* for functions that talk to a server at the back and are very slow,
implement asynchronous functions using an RTD server,
* don't use functions as placeholders to indicate where data should be
placed by macros. Consider using a named range, and adding a button or
two to your ribbon to manage these 'target' ranges. If you really want
a function that 'returns' a variable size result, cautiously consider
the ArrayResizer sample as a way of making your array functions auto-
expand to the right size.

-Govert

[earcam]

Hi Govert,

Thanks for your speedy response and reading between the lines to
deduce what I actually meant - I'm completely with you on the "don't
do it!", it felt so wrong (like driving a car at 90mph in first gear)
and performed awfully.

The original version was a mashup of C/C++ (Steve Dalton's api), COM,
VBA, etc communicating with a JVM (started via C, using JNI) and had
stopped working in Excel 2007/2010 due a large number of bugs - I got
it working in 2007 but it's incredibly unstable and the code is a
maintenance nightmare.

I've two versions currently, both C# automation-addins that
communicate with a Java server via webservices for upload/download (as
a one time, user invoked operation, i.e. all necessary data is held in
C# after the user clicks download):

The first version mimicked the original with worker threads writing
back to the input ranges, it performs appallingly for the standard
model sizes (30+ sheets with some deep function dependency chains and
the functions take a load of configurable parameters).

The second uses array functions (so is clean and performs well), I
thought I'd be able to hook into edit validation events and simply
update the backing model, cancel the edit event(s) and mark the range
as dirty - but I can find no way to achieve this (the VB OnEntry hook
isn't invoked before Excel complains about attempts to edit part of an
array formula).

As the users spend hours tweaking various figures, their position is
that if it doesn't let them edit cells as if they were literals then
this solution is unacceptable. A concession was reached; I have hooks
for F2 and control+V, with a WPF form of a single input field overlaid
on the cell to be edited or a combobox for validation lists - visually
this works (except zooming; the overlay position is correct but I
can't scale the text box as the user won't see what they're typing in
the formula bar). But the behaviour isn't quite the same as Excel;
pasting is very limited, it does full/partial ranges, but doesn't
match the way Excel handles clipboard data being a different size from
the selected range. Also users won't be able to use the formula bar,
drag copy/fills, paste specials etc

I'm very worried that the users will find it too limiting when
editing. I'll demo the array formula version with WPF overlay forms
tomorrow/Friday and let them evaluate it, while that's happening I
think I'll migrate the inefficient write-back-to-args version with
ExcelDNA and Application.Run or a variation of the ArrayResizer
example.


Thanks again for your advice and from the little look I've had so far
at the samples ExcelDNA is infinitely more usable than the raw APIs.


thanks,
Caspar

[earcam]

Hi Govert,

I have migrated the functions to take the write-back range parameters
as string references (and plan to manage the edited cells later at
upload time), so

fn(MODEL_ID, RANGE1, RANGE2) simply becomes fn(MODEL_ID, "RANGE1",
"RANGE2")

When I began I was simply using:

Microsoft.Office.Interop.Excel.Range range =
((Microsoft.Office.Interop.Excel.Application)ExcelDnaUtil.Application).Range[rangeReference];
range.Value2 = value;

But this is failing with "HRESULT: 0x800A03EC" so I tried to implement
this using a background thread to write back but it's still failing
(see code below, note: ApplicationContext.Application is just a static
class hiding a Dictionary keyed by process ID with XL.Application set
in the IExcelAddin.AutoOpen)


Do you have any clues as to what I'm doing wrong?
thanks,
Caspar



using System;
using System.Threading;
using System.Collections.Concurrent;

using ExcelDna.Integration;

using XL = Microsoft.Office.Interop.Excel;

namespace x.y.z
{
public class CellWriter : IDisposable
{

internal BlockingCollection<CellWriteJob> workQueue = new
BlockingCollection<CellWriteJob>();
private volatile bool running = true;
private Thread thread;

public CellWriter()
{
thread = new Thread(() => {
while(running) {
CellWriteJob job = workQueue.Take();
job.Write(this);
}
});
thread.IsBackground = true;
thread.Start();
}


public void Stop()
{
ClearPending();
running = false;
thread.Interrupt();
thread.Abort();
}


public void Enqueue(string sheetName, string rangeId, object
value)
{
if(value != null) {
Enqueue(new CellWriteJob(ResolveRange(rangeId, sheetName),
value));
}
}


internal XL.Range ResolveRange(string range, string sheet =
"")
{
return ApplicationContext.Application.Range[sheet + "!" +
range];
}


public void Enqueue(string rangeId, object value)
{
if(value != null) {
Enqueue(new CellWriteJob(ResolveRange(rangeId), value));
}
}


internal void Enqueue(CellWriteJob job)
{
if(!(running || thread.IsAlive)) {
throw new Exception("CellWriter not running");
}
workQueue.Add(job);
}


public void ClearPending()
{
workQueue = new BlockingCollection<CellWriteJob>();
}


void IDisposable.Dispose()
{
Stop();
}
}


internal class CellWriteJob
{
private object data;
private XL.Range range;

public CellWriteJob(XL.Range range, object data)
{
this.range = range;
this.data = data;
}

public void Write(CellWriter writer)
{
try
{
if (data == null) {
return;
}
try {
XlCall.Excel(XlCall.xlcEcho, false);
range.set_Value(Type.Missing, data);
} finally {
XlCall.Excel(XlCall.xlcEcho, true);
}
}
catch (Exception e)
{
if(e.Message.Contains("RPC_E_SERVERCALL_RETRYLATER") ||
e.Message.Contains("HRESULT: 0x800AC472")) {
writer.Enqueue(this);
Thread.Sleep(250);
} else {
Logger.Log(e, "CellWriter failed, e: {0}", e.Message);

[Govert van Drimmelen]

Hi Caspar,

You won't be able to use the Application object you get in AutoOpen
from another thread. You have to get a fresh reference to the
Application in a call to ExcelDnaUtil.Application that you make from
the other thread.

And you can't call the C API from another thread, so you can do things
like XlCall.xlcEcho from the other thread.

If you use the COM interface from another thread you need to make sure
you Release everything properly too, else I think you risk Excel
crashing.

-Govert

On Nov 1, 1:18 pm, earcam <ear...@gmail.com> wrote:
> Hi Govert,
>

> I have migrated the functions to take the write-back range parameters
> as string references (and plan to manage the edited cells later at
> upload time), so
>
> fn(MODEL_ID, RANGE1, RANGE2) simply becomes fn(MODEL_ID, "RANGE1",
> "RANGE2")
>
> When I began I was simply using:
>
>                         Microsoft.Office.Interop.Excel.Range range =

> ((Microsoft.Office.Interop.Excel.Application)ExcelDnaUtil.Application).Rang e[rangeReference];