网站添加百度统计代码吗,wordpress 工作室模板,网站优化 推广,网站后台中表格制作AbstractCalculationEngine 是 Aspose.Cells 中一个强大的抽象类#xff0c;允许您自定义公式计算逻辑。当您需要覆盖默认计算行为或实现自定义函数时非常有用。直接上代码1. 创建自定义计算引擎using Aspose.Cells;
using System;// 创建自定义计算引擎
public class CustomC…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, were 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效果如下
