C#/VB.NET 创建、读取 Excel 公式

Excel公式是Excel工作表中进行数值计算的等式。公式输入是以“=”开始的，简单的公式有加、减、乘、除等计算。Excel函数就是一些内置的公式，大致包括数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。

本文将演示如果使用Spire.XLS在Excel中添加一些常见的函数公式，以及如何获取单元格中公式。

创建Excel公式

//新建一个工作簿
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)

C# 创建、读取 Excel 公式

读取Excel公式

//实例化一个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

返回顶部