FunctionX 0.3.0

FunctionX

A high-performance, Excel-compatible formula evaluation engine for .NET applications. FunctionX enables developers to execute Excel-like formulas with dynamic parameters, robust security validation, and comprehensive function support.

✨ Key Features

• 🔧 Excel Compatibility: 36 built-in functions matching Excel behavior
• ⚡ High Performance: Roslyn-powered compilation with intelligent caching
• 🛡️ Security-First: Advanced expression validation with pre-compiled regex patterns
• 🎯 Type Safety: Full nullable reference type support with comprehensive error handling
• 📊 Rich Data Support: Arrays, dictionaries, nested structures, and mixed data types
• 🔄 Async/Await: Modern asynchronous API design
• 🎪 Multi-Platform: Supports .NET 8.0, 9.0, and 10.0 (LTS)

🚀 Quick Start

Basic Usage

using FunctionX;

// Simple mathematical expressions
var result = await Fx.EvaluateAsync("1 + 2 * 3"); // Returns: 7

// Excel-style functions
var sum = await Fx.EvaluateAsync("SUM(1, 2, 3, 4, 5)"); // Returns: 15
var average = await Fx.EvaluateAsync("AVERAGE(10, 20, 30)"); // Returns: 20

// New in v0.3.0: Math functions
var sqrt = await Fx.EvaluateAsync("SQRT(16)"); // Returns: 4
var power = await Fx.EvaluateAsync("POWER(2, 10)"); // Returns: 1024
var mod = await Fx.EvaluateAsync("MOD(17, 5)"); // Returns: 2

Working with Parameters

// Using dynamic parameters with @ syntax
var parameters = new Dictionary<string, object?>
{
    { "sales", new[] { 1000, 1500, 2000, 1200 } },
    { "target", 1300 }
};

// Calculate values meeting criteria
var highSales = await Fx.EvaluateAsync("COUNTIF(@sales, \">1300\")", parameters); // Returns: 2
var totalHigh = await Fx.EvaluateAsync("SUMIF(@sales, \">@target\", @sales)", parameters); // Returns: 3500

// New in v0.3.0: AVERAGEIF
var avgHigh = await Fx.EvaluateAsync("AVERAGEIF(@sales, \">1300\")", parameters); // Returns: 1750

// Conditional logic with parameters
var performance = await Fx.EvaluateAsync(
    "IF(AVERAGE(@sales) >= @target, \"Above Target\", \"Below Target\")",
    parameters); // Returns: "Above Target"

Advanced Examples

// Complex nested expressions
var complexResult = await Fx.EvaluateAsync(
    "ROUND(AVERAGE(@data) * 1.15, 2)",
    new Dictionary<string, object?> { { "data", new[] { 85.6, 92.3, 78.9 } } }
); // Returns: 98.42

// Data validation and processing
var validationResult = await Fx.EvaluateAsync(
    "IF(AND(ISNUMBER(@input), NOT(ISBLANK(@input))), ABS(@input), 0)",
    new Dictionary<string, object?> { { "input", -42.5 } }
); // Returns: 42.5

// Text processing
var formatted = await Fx.EvaluateAsync(
    "PROPER(TRIM(@name))",
    new Dictionary<string, object?> { { "name", "  john doe  " } }
); // Returns: "John Doe"

// Math operations (v0.3.0)
var distance = await Fx.EvaluateAsync(
    "SQRT(POWER(@x, 2) + POWER(@y, 2))",
    new Dictionary<string, object?> { { "x", 3 }, { "y", 4 } }
); // Returns: 5

Error Handling

try
{
    var result = await Fx.EvaluateAsync("INVALID_FUNCTION(1, 2)");
}
catch (FxCompilationErrorException ex)
{
    Console.WriteLine($"Formula error: {ex.Message}");
}
catch (FxUnsafeExpressionException ex)
{
    Console.WriteLine($"Security error: {ex.Message}");
}
catch (FxValueException ex)
{
    Console.WriteLine($"Value error: {ex.Message}");
}
catch (FxNumException ex)
{
    Console.WriteLine($"Numeric error: {ex.Message}"); // e.g., SQRT of negative
}
catch (FxDivideByZeroException ex)
{
    Console.WriteLine($"Division error: {ex.Message}"); // e.g., MOD with zero divisor
}

📋 Implemented Functions

Total: 38 Excel-compatible functions

Function Details

🔧 Performance & Security

Performance Features

• Compilation Caching: Compiled expressions are cached for repeated use with configurable cache size
• Pre-compiled Regex: Security patterns use compiled regex for faster validation
• Optimized Execution: Roslyn-based compilation provides near-native performance
• Memory Efficient: Automatic cache trimming prevents unbounded memory growth
• Async Operations: Non-blocking evaluation with full async support

// Cache management
Fx.MaxCacheSize = 500; // Configure max cached expressions (default: 1000)
Fx.ClearCache(); // Clear compilation cache when needed
var (scripts, options) = Fx.GetCacheStatistics(); // Monitor cache usage

Security Features

• Expression Validation: 60+ pre-compiled regex patterns prevent injection attacks
• Forbidden Patterns: Blocks access to Process, File, Assembly, Reflection, etc.
• Length Limits: Expression length validation prevents DoS attacks
• Sandboxed Execution: Restricted access to system resources
• Type Safety: Strong typing with nullable reference type support

📦 Installation

Install via NuGet Package Manager:

# .NET CLI
dotnet add package FunctionX

# Package Manager Console
Install-Package FunctionX

# PackageReference
<PackageReference Include="FunctionX" Version="0.3.0" />

🎯 Supported Platforms

• .NET 8.0 - Long-term support (LTS)
• .NET 9.0 - Standard term support (STS)
• .NET 10.0 - Long-term support (LTS) ✨ Latest

Note: Starting from v0.3.0, FunctionX focuses on .NET 8.0+ for improved quality and performance. For older .NET versions, use v0.2.x.

🧪 Testing & Quality

• Comprehensive Test Suite: 180+ unit tests covering all functions
• Edge Case Coverage: Extensive testing of boundary conditions
• Performance Testing: Validated with large datasets
• Security Testing: Validation against injection attacks
• XML Documentation: Full IntelliSense support with XML comments

📝 Changelog

v0.3.0 (2024-2025)

New Features

• Added SQRT function - square root calculation
• Added POWER function - exponentiation
• Added MOD function - modulo with Excel-compatible sign behavior
• Added AVERAGEIF function - conditional averaging

Improvements

• Extended target frameworks to .NET 8.0, 9.0, and 10.0 (LTS)
• Pre-compiled 60+ security regex patterns for faster validation
• Added configurable cache size limit (MaxCacheSize) with automatic trimming
• Complete XML documentation for all public APIs
• DRY refactoring: extracted common criteria parsing logic

Breaking Changes

• Dropped support for .NET Standard 2.1, .NET 5.0, 6.0, 7.0
• Minimum supported version is now .NET 8.0
• Maximum supported version is .NET 10.0 (LTS)

v0.2.x

• Initial release with 32 Excel-compatible functions
• Support for .NET Standard 2.1 through .NET 9.0

🤝 Contributing

FunctionX is an open-source project. We welcome contributions!

How to Contribute

1. Fork the repository
2. Create a feature branch (git checkout -b feature/amazing-function)
3. Commit your changes (git commit -m 'Add amazing function')
4. Push to the branch (git push origin feature/amazing-function)
5. Open a Pull Request

Development Setup

git clone https://github.com/iyulab/FunctionX.git
cd FunctionX/src
dotnet build
dotnet test

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.