c#/vb.net 创建、读取 excel 公式-k8凯发天生赢家

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);

'新建一个工作簿
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公式

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

'实例化一个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函数公式的书写方法，请参考：