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 doneif (hyperLinkCell == null)break;// Calculate the hyperlink formula, using a custom enginevar 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 arrayif (result != null){var linkString = sheet.Cells[result.ToString()].StringValue;sheet.Hyperlinks.Add(hyperLinkCell.Name, 1, 1, linkString);}}workbook.Save("HtmlSaveOptionsExample.html", options);}
}

轉換后的html效果如下