Cancelling a calculation (original) (raw)

From version 8.5.0 EPPlus supports cancelling a running formula calculation via the standard .NET CancellationToken mechanism. This is useful when processing untrusted or complex workbooks where calculation time is unbounded, for example in server-side scenarios where a timeout or resource limit needs to be enforced.

Note: CancellationToken support is available for .NET 4.0 and later. It is not available when targeting .NET 3.5.

How it works

The CancellationToken is set on the ExcelCalculationOption object and passed to any of the Calculate overloads that accept an options parameter or a configuration handler.

using var cts = new CancellationTokenSource(TimeSpan.FromSeconds(30));

package.Workbook.Calculate(opt => { opt.CancellationToken = cts.Token; });

When the token is signalled, the calculation engine will throw an OperationCanceledException at the next cancellation checkpoint. Checkpoints are evaluated per cell and per worksheet, so the cancellation is responsive without any measurable performance overhead on normal calculations.

Cancelled workbooks are inconsistent

When a calculation is cancelled, the workbook is left in a partially calculated, inconsistent state. EPPlus marks the workbook as cancelled and prevents any further use of it:

Save(), SaveAs() and GetAsByteArray() will throw InvalidOperationException
Calling Calculate() again on the same workbook will throw InvalidOperationException
The property Workbook.IsCalculationInconsistent returns true

A cancelled workbook must be disposed. If the workbook data is still needed, reload it from the source.

Timeout-based cancellation

The most common use case is cancelling after a fixed time limit. Use CancellationTokenSource with a TimeSpan:

using var package = new ExcelPackage(stream); using var cts = new CancellationTokenSource(TimeSpan.FromSeconds(30));

try { package.Workbook.Calculate(opt => { opt.CancellationToken = cts.Token; });

// Calculation completed — safe to save
package.SaveAs(outputStream);

} catch (OperationCanceledException) { // Calculation exceeded 30 seconds — discard the workbook logger.Warn("Calculation cancelled due to timeout."); } // The using statement disposes the package automatically

Cancellation from another thread

Because Calculate() is a synchronous, blocking call, cancellation must be triggered from a different thread. A common pattern is to run the calculation in a background thread and cancel it from the main thread:

using var package = new ExcelPackage(stream); using var cts = new CancellationTokenSource(); Exception calcException = null;

var calcThread = new Thread(() => { try { package.Workbook.Calculate(opt => opt.CancellationToken = cts.Token); } catch (OperationCanceledException ex) { calcException = ex; } });

calcThread.Start();

// Cancel after 10 seconds, or based on an external signal Thread.Sleep(TimeSpan.FromSeconds(10)); cts.Cancel();

calcThread.Join();

if (calcException != null || package.Workbook.IsCalculationInconsistent) { // Workbook is unusable — discard it }

Checking the workbook state

The IsCalculationInconsistent property lets you check whether a workbook has been left in a cancelled state, which is useful in more advanced workflows where the exception may have been caught further up the call stack:

if (package.Workbook.IsCalculationInconsistent) { // Do not use this workbook further package.Dispose(); }

Worksheet and range calculation

Cancellation works the same way when calculating a single worksheet or a range:

// Worksheet worksheet.Calculate(opt => opt.CancellationToken = cts.Token);

// Range worksheet.Cells["A1:D1000"].Calculate(opt => opt.CancellationToken = cts.Token);

In both cases, if the token is signalled, OperationCanceledException is thrown and the parent workbook is marked as inconsistent.