C# 处理Excel公式（一）——创建、读取Excel公式

2022-11-16 13:31:52

对于数据量较大的表格，需要计算一些特殊数值时，我们通过运用公式能有效提高我们数据处理的速度和效率，对于后期数据的增删改查等的批量操作也很方便。此外，对于某些数值的信息来源，我们也可以通过读取数据中包含的公式来获取。下面的示例中将分享通过C# 来创建、读取Excel公式的方法。

工具使用

Spire.XLS for .NET 8.0

下载安装该类库后，注意在程序中添加引用Spire.Xls.dll（dll文件可在安装路径下的Bin文件夹中获取）

代码示例（供参考）

【示例1】创建Excel公式

步骤 1 ：新建工作簿

Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[];

步骤 2 : 添加测试数据及文本，并设置文本格式等

//初始化currentRow、currentFormula

int currentColumn = ;

int currentRow = ;

string currentFormula = string.Empty;

//设置1、2列列宽

sheet.SetColumnWidth(, );

sheet.SetColumnWidth(, );

//写入测试数据

sheet.Range[currentColumn, ].Value = "测试数据：";

sheet.Range[currentColumn, ].NumberValue = ;

sheet.Range[currentColumn, ].NumberValue = ;

sheet.Range[currentColumn, ].NumberValue = ;

sheet.Range[currentColumn, ].NumberValue = ;

sheet.Range[currentColumn, ].NumberValue = ;

//写入文本并设置区域格式

currentRow += ;

sheet.Range[currentRow, ].Value = "公式";

sheet.Range[currentRow, ].Value = "结果";

CellRange range = sheet.Range[currentRow, , currentRow, ];

range.Style.Font.IsBold = true;

range.Style.KnownColor = ExcelColors.LightGreen1;

range.Style.FillPattern = ExcelPatternType.Solid;

range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;

步骤 3 ：写入函数

//算术运算

currentFormula = "=1/2+3*4";

sheet.Range[++currentRow, ].Text = currentFormula;

sheet.Range[currentRow, ].Formula = currentFormula;

//日期函数

currentFormula = "=Today()";

sheet.Range[++currentRow, ].Text = currentFormula;

sheet.Range[currentRow, ].Formula = currentFormula;

sheet.Range[currentRow, ].Style.NumberFormat = "YYYY/MM/DD";

//时间函数

currentFormula = "=NOW()";

sheet.Range[++currentRow, ].Text = currentFormula;

sheet.Range[currentRow, ].Formula = currentFormula;

sheet.Range[currentRow, ].Style.NumberFormat = "H:MM AM/PM";

//IF逻辑函数

currentFormula = "=IF(B1=5,\"Yes\",\"No\")";

sheet.Range[++currentRow, ].Text = currentFormula;

sheet.Range[currentRow, ].Formula = currentFormula;

//PI函数

currentFormula = "=PI()";

sheet.Range[++currentRow, ].Text = currentFormula;

sheet.Range[currentRow, ].Formula = currentFormula;

//三角函数

currentFormula = "=SIN(PI()/6)";

sheet.Range[++currentRow, ].Text = currentFormula;

sheet.Range[currentRow, ].Formula = currentFormula;

//计数函数

currentFormula = "=Count(B1:F1)";

sheet.Range[++currentRow, ].Text = currentFormula;

sheet.Range[currentRow, ].Formula = currentFormula;

//求最大值函数

currentFormula = "=MAX(B1:F1)";

sheet.Range[++currentRow, ].Text = currentFormula;

sheet.Range[currentRow, ].Formula = currentFormula;

//平均值函数

currentFormula = "=AVERAGE(B1:F1)";

sheet.Range[++currentRow, ].Text = currentFormula;

sheet.Range[currentRow, ].Formula = currentFormula;

//求和函数

currentFormula = "=SUM(B1:F1)";

sheet.Range[++currentRow, ].Text = currentFormula;

sheet.Range[currentRow, ].Formula = currentFormula;

步骤 4 :保存文档

workbook.SaveToFile("Excel公式.xlsx", FileFormat.Version2013);

System.Diagnostics.Process.Start("Excel公式.xlsx");

完成代码后，调试运行程序，生成文档：

全部代码：

using Spire.Xls;

namespace CreateFormula

{

    class Program

    {

        static void Main(string[] args)

        {

            //新建一个工作簿，获取第一张工作表

            Workbook workbook = new Workbook();

            Worksheet sheet = workbook.Worksheets[];

            //初始化currentRow、currentFormula

            int currentColumn = ;

            int currentRow = ;

            string currentFormula = string.Empty;

            //设置1、2列列宽

            sheet.SetColumnWidth(, );

            sheet.SetColumnWidth(, );

            //写入测试数据

            sheet.Range[currentColumn, ].Value = "测试数据：";

            sheet.Range[currentColumn, ].NumberValue = ;

            sheet.Range[currentColumn, ].NumberValue = ;

            sheet.Range[currentColumn, ].NumberValue = ;

            sheet.Range[currentColumn, ].NumberValue = ;

            sheet.Range[currentColumn, ].NumberValue = ;

            //写入文本并设置区域格式

            currentRow += ;

            sheet.Range[currentRow, ].Value = "公式";

            sheet.Range[currentRow, ].Value = "结果";

            CellRange range = sheet.Range[currentRow, , currentRow, ];

            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, ].Text = currentFormula;

            sheet.Range[currentRow, ].Formula = currentFormula;

            //日期函数

            currentFormula = "=Today()";

            sheet.Range[++currentRow, ].Text = currentFormula;

            sheet.Range[currentRow, ].Formula = currentFormula;

            sheet.Range[currentRow, ].Style.NumberFormat = "YYYY/MM/DD";

            //时间函数

            currentFormula = "=NOW()";

            sheet.Range[++currentRow, ].Text = currentFormula;

            sheet.Range[currentRow, ].Formula = currentFormula;

            sheet.Range[currentRow, ].Style.NumberFormat = "H:MM AM/PM";

            //IF逻辑函数

            currentFormula = "=IF(B1=5,\"Yes\",\"No\")";

            sheet.Range[++currentRow, ].Text = currentFormula;

            sheet.Range[currentRow, ].Formula = currentFormula;

            //PI函数

            currentFormula = "=PI()";

            sheet.Range[++currentRow, ].Text = currentFormula;

            sheet.Range[currentRow, ].Formula = currentFormula;

            //三角函数

            currentFormula = "=SIN(PI()/6)";

            sheet.Range[++currentRow, ].Text = currentFormula;

            sheet.Range[currentRow, ].Formula = currentFormula;

            //计数函数

            currentFormula = "=Count(B1:F1)";

            sheet.Range[++currentRow, ].Text = currentFormula;

            sheet.Range[currentRow, ].Formula = currentFormula;

            //求最大值函数

            currentFormula = "=MAX(B1:F1)";

            sheet.Range[++currentRow, ].Text = currentFormula;

            sheet.Range[currentRow, ].Formula = currentFormula;

            //平均值函数

            currentFormula = "=AVERAGE(B1:F1)";

            sheet.Range[++currentRow, ].Text = currentFormula;

            sheet.Range[currentRow, ].Formula = currentFormula;

            //求和函数

            currentFormula = "=SUM(B1:F1)";

            sheet.Range[++currentRow, ].Text = currentFormula;

            sheet.Range[currentRow, ].Formula = currentFormula;

            //保存文档并打开

            workbook.SaveToFile("Excel公式.xlsx", FileFormat.Version2013);

            System.Diagnostics.Process.Start("Excel公式.xlsx");

        }

    }

}

【示例2】读取Excel公式

步骤 1 ：实例化Workbook类，加载测试文档

Workbook workbook = new Workbook();

workbook.LoadFromFile("test.xlsx");

步骤 2 ：获取工作表

Worksheet sheet = workbook.Worksheets[0];

步骤 3：读取公式

//遍历[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);

    }

}

Console.ReadLine();

公式读取结果：

全部代码：

using Spire.Xls;

using System;

namespace ReadFormula

{

    class Program

    {

        static void Main(string[] args)

        {

            //实例化一个Workbook

            Workbook workbook = new Workbook();

            //加载测试文档

            workbook.LoadFromFile("test.xlsx");

            //获取第一个工作表

            Worksheet sheet = workbook.Worksheets[];

            //遍历[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);

                }

            }

            Console.ReadLine();

        }

    }

}

以上是本次关于“C# 创建、读取Excel公式”的全部内容。

（本文完）

码农公寓

工具使用

代码示例（供参考）

【示例1】创建Excel公式

【示例2】读取Excel公式

相关文章