Excel API Library for .NET - Sample Browser | Document Solutions | Async Calculation

[フレーム]

Refer to the example code below for adding and utilizing custom Async formulas.


 
 //create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
#region Define custom classes
//public static class FetchService
//{
// public static DownloadFile FetchData = new DownloadFile();
//}
//public class DownloadFile
//{
// private volatile int _percentage = 0;
// public int FetchPercentage
// {
// get
// {
// return _percentage;
// }
// set
// {
// _percentage = value;
// }
// }
//}
//public class FetchDataFunction : AsyncCustomFunction
//{
// public FetchDataFunction()
// : base("FetchData", FunctionValueType.Text, new Parameter[] { })
// {
// }
// async protected override Task<object> EvaluateAsync(object[] arguments, ICalcContext context)
// {
// var file = FetchService.FetchData;
// for (int i = 0; i < 100; i++)
// {
// // Simulate fetching data, fetch 1% every 0.1 second.
// await Task.Delay(100);
// file.FetchPercentage++;
// }
// return "Fetch complete";
// }
//}
//public class FetchPercentageFunction : CustomFunction
//{
// public FetchPercentageFunction()
// : base("FetchPercentage", FunctionValueType.Text, new Parameter[] { })
// {
// }
// public override object Evaluate(object[] arguments, ICalcContext context)
// {
// var fetchPercentage = FetchService.FetchData.FetchPercentage;
// return fetchPercentage;
// }
//} 
#endregion
var ws = workbook.ActiveSheet;
Excel.Workbook.AddCustomFunction(new FetchDataFunction());
Excel.Workbook.AddCustomFunction(new FetchPercentageFunction());
ws.Range["A1"].Formula = "=FetchData()";
ws.Range["B1"].Formula = "=FetchPercentage()";
workbook.Calculate();
while (ws.Range["A1"].Value is CalcError)
{
 // Retrieve the current progress every 1 second.
 System.Threading.Thread.Sleep(1000);
 // Mark cell B1 as Dirty to trigger a recalculation of the B1 cell.
 ws.Range["B1"].Dirty();
 var FetchState = ws.Range["A1"].Value;
 var FetchPercentage = ws.Range["B1"].Value;
 if (FetchState is CalcError)
 {
 FetchState = "Fetching";
 }
 else
 {
 // If the FetchState is not CalcError.Busy, then update the FetchPercentage.
 ws.Range["B1"].Dirty();
 FetchPercentage = ws.Range["B1"].Value;
 }
 Console.WriteLine(FetchState + "\t" + FetchPercentage + "%");
}


 
 ' Create a new Workbook
Dim workbook As New Workbook
#Region "Define custom classes"
'Public Module FetchService
' Public FetchData As New FetchData()
'End Module
'Public Class FetchData
' Private _percentage As Integer = 0
' Public Property FetchPercentage() As Integer
' Get
' Return _percentage
' End Get
' Set(ByVal value As Integer)
' _percentage = value
' End Set
' End Property
'End Class
'Public Class FetchDataFunction
' Inherits AsyncCustomFunction
' Public Sub New()
' MyBase.New("FetchData", FunctionValueType.Text, New Parameter() {})
' End Sub
' Protected Overrides Async Function EvaluateAsync(ByVal arguments() As Object, ByVal context As ICalcContext) As Task(Of Object)
' Dim file = FetchService.FetchData
' For i As Integer = 0 To 99
' ' Simulate fetching data, fetch 1% every 0.1 second.
' Await Task.Delay(100)
' file.FetchPercentage += 1
' Next i
' Return "Fetch complete"
' End Function
'End Class
'Public Class FetchPercentageFunction
' Inherits CustomFunction
' Public Sub New()
' MyBase.New("FetchPercentage", FunctionValueType.Text, New Parameter() {})
' End Sub
' Public Overrides Function Evaluate(ByVal arguments() As Object, ByVal context As ICalcContext) As Object
' Dim fetchPercentage = FetchService.FetchData.FetchPercentage
' Return fetchPercentage
' End Function
'End Class
#End Region
Dim ws = workbook.ActiveSheet
Excel.Workbook.AddCustomFunction(New FetchDataFunction())
Excel.Workbook.AddCustomFunction(New FetchPercentageFunction())
ws.Range("A1").Formula = "=FetchData()"
ws.Range("B1").Formula = "=FetchPercentage()"
workbook.Calculate()
Do While TypeOf ws.Range("A1").Value Is CalcError
 ' Retrieve the current progress every 1 second.
 System.Threading.Thread.Sleep(1000)
 ' Mark cell B1 as Dirty to trigger a recalculation of the B1 cell.
 ws.Range("B1").Dirty()
 Dim FetchState = ws.Range("A1").Value
 Dim FetchPercentage = ws.Range("B1").Value
 If TypeOf FetchState Is CalcError Then
 FetchState = "Fetching"
 Else
 ' If the FetchState is not CalcError.Busy, then update the FetchPercentage.
 ws.Range("B1").Dirty()
 FetchPercentage = ws.Range("B1").Value
 End If
 Console.WriteLine(FetchState.ToString & vbTab & FetchPercentage.ToString & "%")
Loop