Регулярное обновление Excel с помощью надстройки C#. ⇐ C#

[Anonymous]

Я пытаюсь получить случайное число

Код: Выделить всё

Excel

that would get refreshed at a regular time interval, by using a

Код: Выделить всё

C#

add-in exposed through the

Код: Выделить всё

Microsoft.Office.Interop.Excel

library. The function called in

Код: Выделить всё

Excel

is named "GetRandomLive" with the interval as an argument and returns the random number and a timestamp.
To check whether my

Код: Выделить всё

Excel

application is correctly identified, I have added a line that writes on the dummy cell "A1" the timer event SignalTime.
In the test, I have set an interval equal to 10,000 ms. I have checked that the

Код: Выделить всё

Excel

"Calculation Options" are correctly set to "Automatic". However, if indeed the

Код: Выделить всё

C#

library correctly writes the SignalTime 10,000 ms apart on the

Код: Выделить всё

Excel

target cell, it does not always recalculate the sheet. I tried several things (as per the code below) that leave me puzzled:

Код: Выделить всё

1) _excel.CalculateFull();     // => Works
2) _excel.Calculate();         // => Does not work
3) _sheet.Calculate();         // => Does not work

Obviously, I would not like to have to resort to a full recalculation of the Excel app, as it could easily become an overkill (in the presence of potentially many other complex calculations on various sheets). Ideally, I would like to restrict the recalculation to the function calling sheet or, even better, to the function calling cell or range.
Here is the code that I have written. Any light would be welcome.

Код: Выделить всё

using System;
using System.Timers;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

namespace Space
{
public class RandomLive
{
private static Excel.Application _excel;
private static Excel.Worksheet _sheet;
private static Timer _timer;
private static bool _first = true;
private static object[,] _data;

public RandomLive()
{
_excel = Marshal.GetActiveObject("Excel.Application") as Excel.Application;
}

public object[,] GetRandomLive(int interval)
{
_sheet = _excel.ActiveWorkbook.ActiveSheet;

if (_first)
{
_timer = new Timer();
_timer.Elapsed += (sender, args) => TimerElapsed(sender, args);
_timer.AutoReset = true;
_first = false;
}
_timer.Interval = interval;
_timer.Enabled = true;
_timer.Start();

_data = GetRandom();
return _data;
}

private static void TimerElapsed(object sender, ElapsedEventArgs args)
{
_data = GetRandom();
try
{
_sheet.Cells[1, 1].Value = args.SignalTime;
//_excel.CalculateFull();     // => Works
//_excel.Calculate();         // => Does not work
_sheet.Calculate();         // => Does not work
}
catch
{
return;
}
}

private static object[,] GetRandom()
{
_data = new object[2, 2];
_data[0, 0] = "Random";
_data[1, 0] = "Timestamp";

Random random = new Random();
double value = random.NextDouble();
_data[0, 1] = value;
_data[1, 1] = DateTime.Now;

return _data;
}
}
}

10 seconds after the first function call, the event SignalTime refreshes, as expected
However, after a another batch of time, if indeed the SignalTime still refreshes, the function value does not


Источник: https://stackoverflow.com/questions/781 ... arp-add-in