net9 aspose.cell 自定义公式AbstractCalculationEngine,带超链接excel转html后背景色丢失

AbstractCalculationEngine 是 Aspose.Cells 中一个强大的抽象类，允许您自定义公式计算逻辑。当您需要覆盖默认计算行为或实现自定义函数时非常有用。

直接上代码

1. 创建自定义计算引擎

using Aspose.Cells;
using System;

// 创建自定义计算引擎
public class CustomCalculationEngine : AbstractCalculationEngine
{
    public override void Calculate(CalculationData data)
    {
        // 检查函数名称
        if (data.FunctionName == "CUSTOMFUNCTION")
        {
            // 处理自定义函数
            double result = CalculateCustomFunction(data);
            data.CalculatedValue = result;
        }
    }

    private double CalculateCustomFunction(CalculationData data)
    {
        // 实现您的自定义逻辑
        if (data.ParamCount == 2)
        {
            double param1 = Convert.ToDouble(data.GetParamValue(0));
            double param2 = Convert.ToDouble(data.GetParamValue(1));
            return param1 * param2 + 100; // 示例计算
        }
        return 0;
    }
}

2. 使用自定义计算引擎

using Aspose.Cells;

// 使用自定义计算引擎
Workbook workbook = new Workbook("input.xlsx");

// 创建自定义计算引擎实例
CustomCalculationEngine customEngine = new CustomCalculationEngine();

// 设置计算选项
CalculationOptions options = new CalculationOptions();
options.CustomEngine = customEngine;
options.IgnoreError = true;

// 使用自定义引擎计算公式
workbook.CalculateFormula(options);

// 保存结果
workbook.Save("output.xlsx");

高级应用示例

示例1：自定义数学函数

public class MathCalculationEngine : AbstractCalculationEngine
{
    public override void Calculate(CalculationData data)
    {
        switch (data.FunctionName.ToUpper())
        {
            case "CUSTOMSUM":
                data.CalculatedValue = CustomSum(data);
                break;
            case "CUSTOMAVG":
                data.CalculatedValue = CustomAverage(data);
                break;
            case "DISCOUNT":
                data.CalculatedValue = CalculateDiscount(data);
                break;
            default:
                break;
        }
    }

    private double CustomSum(CalculationData data)
    {
        double sum = 0;
        for (int i = 0; i < data.ParamCount; i++)
        {
            object paramValue = data.GetParamValue(i);
            if (paramValue is double)
            {
                sum += (double)paramValue;
            }
        }
        return sum;
    }

    private double CustomAverage(CalculationData data)
    {
        double sum = CustomSum(data);
        return data.ParamCount > 0 ? sum / data.ParamCount : 0;
    }

    private double CalculateDiscount(CalculationData data)
    {
        if (data.ParamCount == 2)
        {
            double price = Convert.ToDouble(data.GetParamValue(0));
            double discountRate = Convert.ToDouble(data.GetParamValue(1));
            return price * (1 - discountRate / 100);
        }
        return 0;
    }
}

示例2：业务逻辑计算引擎

public class BusinessCalculationEngine : AbstractCalculationEngine
{
    private readonly double _taxRate;
    private readonly double _shippingCost;

    public BusinessCalculationEngine(double taxRate, double shippingCost)
    {
        _taxRate = taxRate;
        _shippingCost = shippingCost;
    }

    public override void Calculate(CalculationData data)
    {
        switch (data.FunctionName.ToUpper())
        {
            case "CALCULATETAX":
                data.CalculatedValue = CalculateTax(data);
                break;
            case "TOTALWITHSHIPPING":
                data.CalculatedValue = TotalWithShipping(data);
                break;
            case "BUSINESSPROFIT":
                data.CalculatedValue = CalculateProfit(data);
                break;
            default:
                break;
        }
    }

    private double CalculateTax(CalculationData data)
    {
        if (data.ParamCount >= 1)
        {
            double amount = Convert.ToDouble(data.GetParamValue(0));
            return amount * _taxRate;
        }
        return 0;
    }

    private double TotalWithShipping(CalculationData data)
    {
        if (data.ParamCount >= 1)
        {
            double subtotal = Convert.ToDouble(data.GetParamValue(0));
            return subtotal + _shippingCost + (subtotal * _taxRate);
        }
        return 0;
    }

    private double CalculateProfit(CalculationData data)
    {
        if (data.ParamCount == 2)
        {
            double revenue = Convert.ToDouble(data.GetParamValue(0));
            double cost = Convert.ToDouble(data.GetParamValue(1));
            return revenue - cost - (revenue * _taxRate);
        }
        return 0;
    }
}

示例3：条件计算引擎

public class ConditionalCalculationEngine : AbstractCalculationEngine
{
    public override void Calculate(CalculationData data)
    {
        if (data.FunctionName.StartsWith("VALIDATE_"))
        {
            string validationType = data.FunctionName.Substring(9);
            data.CalculatedValue = ValidateData(data, validationType);
        }
    }

    private bool ValidateData(CalculationData data, string validationType)
    {
        switch (validationType.ToUpper())
        {
            case "EMAIL":
                return IsValidEmail(data);
            case "PHONE":
                return IsValidPhone(data);
            case "DATE":
                return IsValidDate(data);
            default:
                return false;
        }
    }

    private bool IsValidEmail(CalculationData data)
    {
        if (data.ParamCount >= 1)
        {
            string email = data.GetParamValue(0)?.ToString();
            return !string.IsNullOrEmpty(email) && email.Contains("@") && email.Contains(".");
        }
        return false;
    }

    private bool IsValidPhone(CalculationData data)
    {
        if (data.ParamCount >= 1)
        {
            string phone = data.GetParamValue(0)?.ToString();
            return !string.IsNullOrEmpty(phone) && phone.Length >= 10;
        }
        return false;
    }

    private bool IsValidDate(CalculationData data)
    {
        if (data.ParamCount >= 1)
        {
            object dateValue = data.GetParamValue(0);
            return dateValue is DateTime;
        }
        return false;
    }
}

使用示例

// 使用业务计算引擎
var businessEngine = new BusinessCalculationEngine(taxRate: 0.08, shippingCost: 5.99);

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

// 设置一些测试数据
worksheet.Cells["A1"].PutValue(100); // 价格
worksheet.Cells["A2"].PutValue(0.1); // 折扣率
worksheet.Cells["A3"].Formula = "=DISCOUNT(A1, A2*100)"; // 使用自定义函数

// 设置计算选项
CalculationOptions options = new CalculationOptions
{
    CustomEngine = businessEngine,
    IgnoreError = true,
    Recursive = true
};

// 计算公式
workbook.CalculateFormula(options);

Console.WriteLine($"折扣后价格: {worksheet.Cells["A3"].Value}");

实例：带超链接excel转html后背景色丢失（D列带链接）

excel 公式D2单元格=HYPERLINK(C2,B2)

转html后D列条件格式背景色丢失。

 public class MyEngine : AbstractCalculationEngine
 {
     public override bool ProcessBuiltInFunctions => true;

     public override void Calculate(CalculationData data)
     {
         string funcName = data.FunctionName.ToUpper();
         if ("HYPERLINK".Equals(funcName))
         {
             if (data.ParamCount < 1) return;
             string hyperlink = data.GetParamText(0).ToString();
             string name = data.GetParamValue(data.ParamCount == 2 ? 1 : 0).ToString();
             data.CalculatedValue = hyperlink;
             //data.CalculatedValue =new string[] { name, hyperlink };
         }
     }
 }

替换超链接

    HtmlSaveOptions options = new HtmlSaveOptions();
    options.EnableCssCustomProperties = true; // 启用CSS优化
    var tempFilePath = "ExcelReportTest.xlsx";
    using (var fileStream = new FileStream(tempFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
    {
        // 加载Excel文件
        var workbook = new Aspose.Cells.Workbook(fileStream);
        var sheet = workbook.Worksheets[0];
        Cell hyperLinkCell = null;
        while (true)
        {
            // Loop to find the hyperlink formulas on this sheet.
            hyperLinkCell = sheet.Cells.Find("hyperlink", hyperLinkCell, new FindOptions()
            {
                CaseSensitive = false,
                LookInType = LookInType.OnlyFormulas,
            });

            // No more hyperlinks, we're done
            if (hyperLinkCell == null)
                break;
            // Calculate the hyperlink formula, using a custom engine
            var result = sheet.CalculateFormula(hyperLinkCell.Formula, new CalculationOptions()
            {
                CustomEngine = new MyEngine()
            });
            // 保存当前值
            object cellValue = hyperLinkCell.Value;

            // 如果需要保留值，重新设置值
            hyperLinkCell.PutValue(cellValue);
            // The result of the hyperlink formula with out custom engine will give us the name and address in an array
            if (result != null)
            {
                var linkString = sheet.Cells[result.ToString()].StringValue;
                sheet.Hyperlinks.Add(hyperLinkCell.Name, 1, 1, linkString);
            }
        }
        workbook.Save("HtmlSaveOptionsExample.html", options);
    }
}

转换后的html效果如下