[ExcelFunction("Obtains historical market data from Yahoo")]
    public static object GetHistoricalDataFromYahooAsyncResize(
          [ExcelArgument("Yahoo Ticker")] string ticker,
          [ExcelArgument("From Date")] DateTime fromDate,
          [ExcelArgument("To Date")] DateTime toDate)
    {
     Â
      var caller = Excel(xlfCaller) as ExcelReference;
      Debug.Print(string.Format("{0}, called from GetHistoricalDataFromYahooAsyncResize", caller.ToString()));
      var result = ExcelAsyncUtil.Observe("GetHistoricalDataFromYahooAsyncResize", new object[] { ticker, fromDate, toDate }, () =>
      {
        return Task.Run(() =>
        {
          var result1 = GetHistoricalDataFromYahoo(ticker, fromDate, toDate);
          return result1;
        }).ToExcelObservable();
      });
      if (result.Equals(ExcelError.ExcelErrorNA))
      {
        return "Dispatching...";
      }
      var array = (object[,])result;
      caller = Excel(xlfCaller) as ExcelReference;
      Debug.Print(string.Format("{0}, resized from GetHistoricalDataFromYahooAsyncResize", caller.ToString()));
      return ArrayResizer.Resize(array);
    }
can you guys please help?
regards
casbby
Hi Gabor,
Â
I’m quite surprised that you say it worked before, in v 0.32, and would like to have a closer look.
Â
Could you perhaps help me with a simple example that I can test this with myself?
Â
Thank you,
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 post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.
using System;using System.Collections.Generic;using System.Threading;using System.Threading.Tasks;using ExcelDna.Integration;
namespace AsyncResizeTest {Â Â public class ArrayResizer : XlCall {Â Â Â Â private static Queue<ExcelReference> ResizeJobs = new Queue<ExcelReference>();
    [ExcelFunction]    public static object[,] Resize(object[,] array) {      ExcelReference caller = Excel(xlfCaller) as ExcelReference;
      if (caller == null) {        return array;      }
      int rows = array.GetLength(0);      int columns = array.GetLength(1);
      if (rows == 0 || columns == 0) {        return array;      }
      if ((caller.RowLast - caller.RowFirst + 1 == rows)        && (caller.ColumnLast - caller.ColumnFirst + 1 == columns)) {        // Size is already good - just return the result.        return array;      }
      int rowLast = caller.RowFirst + rows - 1;      int columnLast = caller.ColumnFirst + columns - 1;
      // Check for the sheet limits.      if (rowLast > ExcelDnaUtil.ExcelLimits.MaxRows - 1        || columnLast > ExcelDnaUtil.ExcelLimits.MaxColumns - 1) {        // Can't resize - goes beyond the end of the sheet - just return #VALUE.        return new object[,] { { ExcelError.ExcelErrorValue } };      }
      EnqueueResize(caller, rows, columns);      ExcelAsyncUtil.QueueAsMacro(DoResizing);
      return array;    }
    private static void EnqueueResize(ExcelReference caller, int rows, int columns) {      ExcelReference target =        new ExcelReference(caller.RowFirst,                  caller.RowFirst + rows - 1,                  caller.ColumnFirst,                  caller.ColumnFirst + columns - 1,                  caller.SheetId);
      ResizeJobs.Enqueue(target);    }
    private static void DoResizing() {      while (ResizeJobs.Count > 0) {        DoResize(ResizeJobs.Dequeue());      }    }
    private static void DoResize(ExcelReference target) {      object oldEcho = Excel(xlfGetWorkspace, 40);      object oldCalculationMode = Excel(xlfGetDocument, 14);
      try {        // Get the current state for reset later        Excel(xlcEcho, false);        Excel(xlcOptionsCalculation, 3);
        // Get the formula in the first cell of the target        string formula = (string)Excel(xlfGetCell, 41, target);        ExcelReference firstCell =          new ExcelReference(target.RowFirst,                    target.RowFirst,                    target.ColumnFirst,                    target.ColumnFirst,                    target.SheetId);
        bool isFormulaArray = (bool)Excel(xlfGetCell, 49, target);
        if (isFormulaArray) {          object oldSelectionOnActiveSheet = Excel(xlfSelection);          object oldActiveCell = Excel(xlfActiveCell);
          // Remember old selection and select the first cell of the target          string firstCellSheet = (string)Excel(xlSheetNm, firstCell);          Excel(xlcWorkbookSelect, new object[] { firstCellSheet });
          object oldSelectionOnArraySheet = Excel(xlfSelection);          Excel(xlcFormulaGoto, firstCell);
          // Extend the selection to the whole array and clear          Excel(xlcSelectSpecial, 6);          ExcelReference oldArray = (ExcelReference)Excel(xlfSelection);
          oldArray.SetValue(ExcelEmpty.Value);          Excel(xlcSelect, oldSelectionOnArraySheet);          Excel(xlcFormulaGoto, oldSelectionOnActiveSheet);        }
        // Get the formula and convert to R1C1 mode        bool isR1C1Mode = (bool)Excel(xlfGetWorkspace, 4);        string formulaR1C1 = formula;
        if (!isR1C1Mode) {          // Set the formula into the whole target          formulaR1C1 = (string)Excel(xlfFormulaConvert, formula, true, false, ExcelMissing.Value, firstCell);        }
        // Must be R1C1-style references        object ignoredResult;        XlReturn retval = TryExcel(xlcFormulaArray, out ignoredResult, formulaR1C1, target);
        if (retval != XlReturn.XlReturnSuccess) {          firstCell.SetValue("'" + formula);        }
      } finally {        Excel(xlcEcho, oldEcho);        Excel(xlcOptionsCalculation, oldCalculationMode);      }    }  }
  internal static class ExcelTaskUtil {
    private static object Run<TResult>(string callerFunctionName, object callerParameters,                      Func<CancellationToken, Task<TResult>> taskSource) {
      return ExcelAsyncUtil.Observe(callerFunctionName, callerParameters,                     delegate {                       var cts = new CancellationTokenSource();                       var task = taskSource(cts.Token);                       return new ExcelTaskObservable<TResult>(task, cts);                     });    }
    private static object Run<TResult>(string callerFunctionName, object callerParameters,                     Func<Task<TResult>> taskSource) {
      return ExcelAsyncUtil.Observe(callerFunctionName, callerParameters,                     delegate {                       var task = taskSource();
                       return new ExcelTaskObservable<TResult>(task);
                     });    }
    private static object Run<TResult>(string callerFunctionName, object callerParameters,                     Func<CancellationToken, TResult> function) {
      return Run(callerFunctionName, callerParameters,            cancellationToken =>              Task.Factory.StartNew(() => function(cancellationToken), cancellationToken));    }
    private static object Run<TResult>(string callerFunctionName, object callerParameters, Func<TResult> function) {      return Run(callerFunctionName, callerParameters, () => Task.Factory.StartNew(function));    }
    public static object AsyncRun<TResult>(string callerFunctionName, object callerParameters, Func<TResult> function) {      var result = Run(callerFunctionName, callerParameters, function);
      if (result.Equals(ExcelError.ExcelErrorNA)) {        return ExcelError.ExcelErrorGettingData;      }
      return result;    }
    public static object[,] AsyncRunAndResize<TResult>(string callerFunctionName, object callerParameters, Func<TResult> function) {      var result = Run(callerFunctionName, callerParameters, function);
      if (result.Equals(ExcelError.ExcelErrorNA)) {        return new object[,] { { ExcelError.ExcelErrorGettingData } };      }
      return ArrayResizer.Resize((object[,])result);    }
    // Helper class to wrap a Task in an Observable - allowing one Subscriber.    class ExcelTaskObservable<TResult> : IExcelObservable {      readonly Task<TResult> task;      readonly CancellationTokenSource cts;
      public ExcelTaskObservable(Task<TResult> task) {        this.task = task;      }
      public ExcelTaskObservable(Task<TResult> task, CancellationTokenSource cts)        : this(task) {        this.cts = cts;      }
      public IDisposable Subscribe(IExcelObserver observer) {        switch (task.Status) {          case TaskStatus.RanToCompletion:            observer.OnNext(task.Result);            observer.OnCompleted();            break;          case TaskStatus.Faulted:            observer.OnError(task.Exception.InnerException);            break;          case TaskStatus.Canceled:            observer.OnError(new TaskCanceledException(task));            break;          default:            task.ContinueWith(t => {              switch (t.Status) {                case TaskStatus.RanToCompletion:                  observer.OnNext(t.Result);                  observer.OnCompleted();                  break;                case TaskStatus.Faulted:                  observer.OnError(t.Exception.InnerException);                  break;                case TaskStatus.Canceled:                  observer.OnError(new TaskCanceledException(t));                  break;              }            });
            break;        }
        // Check for cancellation support        if (cts != null) {          return new CancellationDisposable(cts);        }        // No cancellation        return DefaultDisposable.Instance;      }    }
    sealed class DefaultDisposable : IDisposable {
      public static readonly DefaultDisposable Instance = new DefaultDisposable();
      // Prevent external instantiation      DefaultDisposable() { }
      public void Dispose() { }    }
    sealed class CancellationDisposable : IDisposable {
      readonly CancellationTokenSource cts;      public CancellationDisposable(CancellationTokenSource cts) {        if (cts == null) {          throw new ArgumentNullException("cts");        }
        this.cts = cts;      }
      public CancellationDisposable()        : this(new CancellationTokenSource()) {      }
      public CancellationToken Token      {        get { return cts.Token; }      }
      public void Dispose() {        cts.Cancel();      }    }
  }
  public class Test {    private static Random rand = new Random();
    private static object[,] SimpleArrayTest() {      Thread.Sleep(3000);      int size = rand.Next(1, 15);      var result = new object[1, size];
      for (int i = 0; i < size; i++) {        result[0, i] = rand.NextDouble();      }
      return result;    }
    [ExcelFunction(Name = "ArrayTest", IsVolatile = false)]    public static object ArrayTest() {      return ExcelTaskUtil.AsyncRunAndResize("ArrayTest", new object[] { }, () => SimpleArrayTest());    }  }
}
Thank you very much Gabor, I'll have a closer look at this issue again.
Â
-Govert
public class Test {
private static Random rand = new Random();
To post to this group, send email to exc...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.
--
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 post to this group, send email to exc...@googlegroups.com.
Hi Gabor,
Â
Under v 0.32 and your new function, I don’t find that at recalculates and returns new values when pressing Ctrl+Shift+Alt+F9.
If you change the parameter value to a different maxSize, then it will change, but that’s because we’re making a new RTD topic.
Â
I understand that this is a feature people would like. It just doesn’t work right in v0.32, and in 0.33 the RTD part of the problem is fixed – this disconnect now happens correctly. A side effect is that the resizer doesn’t work for async arrays.
Â
I’m not sure whether async arrays can be made reliable, or whether array resizer can work for async arrays. But the v0.32 behaviour is not correct.