Excel公式是Excel工作表中进行数值计算的等式。公式输入是以“=”开始的,简单的公式有加、减、乘、除等计算。Excel函数就是一些内置的公式,大致包括数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。
本文将演示如果使用Spire.XLS在Excel中添加一些常见的函数公式,以及如何获取单元格中公式。
创建Excel公式
C#
//新建一个工作簿
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
//初始化currentRow、currentFormula
int currentRow = 1;
string currentFormula = string.Empty;
//设置1、2列列宽
sheet.SetColumnWidth(1, 32);
sheet.SetColumnWidth(2, 16);
//写入测试数据
sheet.Range[currentRow, 1].Value = "测试数据:";
sheet.Range[currentRow, 2].NumberValue = 1;
sheet.Range[currentRow, 3].NumberValue = 2; ;
sheet.Range[currentRow, 4].NumberValue = 3;
sheet.Range[currentRow, 5].NumberValue = 4;
sheet.Range[currentRow, 6].NumberValue = 5;
//写入文本并设置区域格式
currentRow += 2;
sheet.Range[currentRow, 1].Value = "公式"; ;
sheet.Range[currentRow, 2].Value = "结果";
CellRange range = sheet.Range[currentRow, 1, currentRow, 2];
range.Style.Font.IsBold = true;
range.Style.KnownColor = ExcelColors.LightGreen1;
range.Style.FillPattern = ExcelPatternType.Solid;
range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;
//算术运算
currentFormula = "=1/2+3*4";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
//日期函数
currentFormula = "=TODAY()";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
sheet.Range[currentRow, 2].Style.NumberFormat = "YYYY/MM/DD";
//时间函数
currentFormula = "=NOW()";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
sheet.Range[currentRow, 2].Style.NumberFormat = "H:MM AM/PM";
//IF逻辑函数
currentFormula = "=IF(B1=5,\"Yes\",\"No\")";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
//PI函数
currentFormula = "=PI()";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
//三角函数
currentFormula = "=SIN(PI()/6)";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
//计数函数
currentFormula = "=Count(B1:F1)";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
//求最大值函数
currentFormula = "=MAX(B1:F1)";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
//平均值函数
currentFormula = "=AVERAGE(B1:F1)";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
//求和函数
currentFormula = "=SUM(B1:F1)";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
//保存文档
workbook.SaveToFile("Excel公式.xlsx",FileFormat.Version2013);
VB.NET
'新建一个工作簿
Dim workbook As New Workbook()
Dim sheet As Worksheet = workbook.Worksheets(0)
'初始化currentRow、currentFormula
Dim currentRow As Integer = 1
Dim currentFormula As String = String.Empty
'设置1、2列列宽
sheet.SetColumnWidth(1, 32)
sheet.SetColumnWidth(2, 16)
'写入测试数据
sheet.Range(currentRow, 1).Value = "测试数据:"
sheet.Range(currentRow, 2).NumberValue = 1
sheet.Range(currentRow, 3).NumberValue = 2
sheet.Range(currentRow, 4).NumberValue = 3
sheet.Range(currentRow, 5).NumberValue = 4
sheet.Range(currentRow, 6).NumberValue = 5
'写入文本并设置区域格式
currentRow += 2
sheet.Range(currentRow, 1).Value = "公式"
sheet.Range(currentRow, 2).Value = "结果"
Dim range As CellRange = sheet.Range(currentRow, 1, currentRow, 2)
range.Style.Font.IsBold = True
range.Style.KnownColor = ExcelColors.LightGreen1
range.Style.FillPattern = ExcelPatternType.Solid
range.Style.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Medium
'算术运算
currentFormula = "=1/2+3*4"
sheet.Range(System.Threading.Interlocked.Increment(currentRow), 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
'日期函数
currentFormula = "=TODAY()"
sheet.Range(System.Threading.Interlocked.Increment(currentRow), 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
sheet.Range(currentRow, 2).Style.NumberFormat = "YYYY/MM/DD"
'时间函数
currentFormula = "=NOW()"
sheet.Range(System.Threading.Interlocked.Increment(currentRow), 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
sheet.Range(currentRow, 2).Style.NumberFormat = "H:MM AM/PM"
'IF逻辑函数
currentFormula = "=IF(B1=5,""Yes"",""No"")"
sheet.Range(System.Threading.Interlocked.Increment(currentRow), 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
'PI函数
currentFormula = "=PI()"
sheet.Range(System.Threading.Interlocked.Increment(currentRow), 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
'三角函数
currentFormula = "=SIN(PI()/6)"
sheet.Range(System.Threading.Interlocked.Increment(currentRow), 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
'计数函数
currentFormula = "=Count(B1:F1)"
sheet.Range(System.Threading.Interlocked.Increment(currentRow), 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
'求最大值函数
currentFormula = "=MAX(B1:F1)"
sheet.Range(System.Threading.Interlocked.Increment(currentRow), 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
'平均值函数
currentFormula = "=AVERAGE(B1:F1)"
sheet.Range(System.Threading.Interlocked.Increment(currentRow), 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
'求和函数
currentFormula = "=SUM(B1:F1)"
sheet.Range(System.Threading.Interlocked.Increment(currentRow), 1).Text = currentFormula
sheet.Range(currentRow, 2).Formula = currentFormula
'保存文档
workbook.SaveToFile("Excel公式.xlsx", FileFormat.Version2013)
读取Excel公式
C#
//实例化一个Workbook
Workbook workbook = new Workbook();
//加载一个Excel文档
workbook.LoadFromFile("Excel公式.xlsx");
//获取第一个工作表
Worksheet sheet = workbook.Worksheets[0];
//遍历[B1:B13]的单元格
foreach (var cell in sheet.Range["B1:B13"])
{
//判断是否含有公式
if(cell.HasFormula)
{
//输出含有公式的单元格及公式
string certainCell = String.Format("Cell[{0},{1}]", cell.Row, cell.Column);
Console.WriteLine(certainCell +" 含有公式: " + cell.Formula);
}
}
VB.NET
'实例化一个Workbook
Dim workbook As New Workbook()
'加载一个Excel文档
workbook.LoadFromFile("Excel公式.xlsx")
'获取第一个工作表
Dim sheet As Worksheet = workbook.Worksheets(0)
'遍历[B1:B13]的单元格
For Each cell As var In sheet.Range("B1:B13")
'判断是否含有公式
If cell.HasFormula Then
'输出含有公式的单元格及公式
Dim certainCell As String = [String].Format("Cell[{0},{1}]", cell.Row, cell.Column)
Console.WriteLine((certainCell & Convert.ToString(" 含有公式: ")) + cell.Formula)
End If
Next
更多Excel函数公式的书写方法,请参考:https://support.office.com/en-us/article/Formulas-and-functions-294d9486-b332-48ed-b489-abe7d0f9eda9