VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > Python基础教程 >
  • C#教程之C# 处理Excel公式(一)——创建、读取

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

工具使用

  • Spire.XLS for .NET 8.0

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

代码示例(供参考)

【示例1】创建Excel公式

步骤 1 :新建工作簿

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];

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

复制代码
//初始化currentRow、currentFormula
int currentColumn = 1;
int currentRow = 1;
string currentFormula = string.Empty;

//设置1、2列列宽
sheet.SetColumnWidth(1, 20);
sheet.SetColumnWidth(2, 12);

//写入测试数据
sheet.Range[currentColumn, 1].Value = "测试数据:";
sheet.Range[currentColumn, 2].NumberValue = 10;
sheet.Range[currentColumn, 3].NumberValue = 20; 
sheet.Range[currentColumn, 4].NumberValue = 30;
sheet.Range[currentColumn, 5].NumberValue = 40;
sheet.Range[currentColumn, 6].NumberValue = 50;

//写入文本并设置区域格式
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;
复制代码

步骤 3 :写入函数

复制代码
//算术运算
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;
复制代码

步骤 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[0];

            //初始化currentRow、currentFormula
            int currentColumn = 1;
            int currentRow = 1;
            string currentFormula = string.Empty;

            //设置1、2列列宽
            sheet.SetColumnWidth(1, 20);
            sheet.SetColumnWidth(2, 12);

            //写入测试数据
            sheet.Range[currentColumn, 1].Value = "测试数据:";
            sheet.Range[currentColumn, 2].NumberValue = 10;
            sheet.Range[currentColumn, 3].NumberValue = 20; 
            sheet.Range[currentColumn, 4].NumberValue = 30;
            sheet.Range[currentColumn, 5].NumberValue = 40;
            sheet.Range[currentColumn, 6].NumberValue = 50;

            //写入文本并设置区域格式
            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);
            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[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);
                }
            }
            Console.ReadLine();
        }
    }
}
复制代码

 

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

(本文完)


相关教程