| 
| 
tab 1
此 demo 展示如何插入公式到 excel 并计算公式。
如果这不是您想要的 demo，您可以通过填写表格获取免费定制 demo。
如您有与我们产品相关的其他技术问题，请联系 该email地址已收到反垃圾邮件插件保护。要显示它您需要在浏览器中启用javascript。；销售相关的问题，请联系 该email地址已收到反垃圾邮件插件保护。要显示它您需要在浏览器中启用javascript。。
tab 2
using spire.xls;
namespace demoonlinecode
{
class calculateformulas
{
public void democalculateformulas(string resultfile)
{
workbook workbook = new workbook();
worksheet sheet = workbook.worksheets[0];
calculate(workbook, sheet);
workbook.savetofile(resultfile, excelversion.version2010);
}
public void calculate(workbook workbook, worksheet sheet)
{
int currentrow = 1;
string currentformula = string.empty;
object formularesult = null;
string value = string.empty;
// set width respectively of column a ,column b,column c
sheet.setcolumnwidth(1, 32);
sheet.setcolumnwidth(2, 16);
sheet.setcolumnwidth(3, 16);
//set the value of cell a1
sheet.range[currentrow , 1].value = "examples of formulas :";
// set the value of cell a2
sheet.range[ currentrow, 1].value = "test data:";
// set the style of cell a1
cellrange range = sheet.range["a1"];
range.style.font.isbold = true;
range.style.fillpattern = excelpatterntype.solid;
range.style.knowncolor = excelcolors.lightgreen1;
range.style.borders[borderslinetype.edgebottom].linestyle = linestyletype.medium;
// additive operation of mutiple cells
sheet.range[currentrow, 2].numbervalue = 7.3;
sheet.range[currentrow, 3].numbervalue = 5;
sheet.range[currentrow, 4].numbervalue = 8.2;
sheet.range[currentrow, 5].numbervalue = 4;
sheet.range[currentrow, 6].numbervalue = 3;
sheet.range[currentrow, 7].numbervalue = 11.3;
// create arithmetic expression string about cells
currentformula = "=sheet1!$b$3 sheet1!$c$3 sheet1!$d$3 sheet1!$e$3 sheet1!$f$3 sheet1!$g$3";
//caculate arithmetic expression about cells
formularesult = workbook.caculateformulavalue(currentformula);
value = formularesult.tostring();
sheet.range[currentrow, 2].value = value;
// set the value and format of two head cell
sheet.range[ currentrow, 1].value = "formulas"; ;
sheet.range[currentrow, 2].value = "results";
sheet.range[currentrow, 2].horizontalalignment = horizontalaligntype.right;
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;
// expression caculation
// create arithmetic tables enclosed type string
currentformula = "=33*3/4-2 10";
sheet.range[ currentrow, 1].text = currentformula;
// caculate arithmetic expression
formularesult = workbook.caculateformulavalue(currentformula);
value = formularesult.tostring();
sheet.range[currentrow, 2].value = value;
/// the mathematics function ///
//absolute value function
// create abosolute value function string
currentformula = "=abs(-1.21)";
sheet.range[ currentrow, 1].text = currentformula;
// caculate abosulte value function
formularesult = workbook.caculateformulavalue(currentformula);
value = formularesult.tostring();
sheet.range[currentrow, 2].value = value;
/// statistical function///
// sum function
// create sum function string
currentformula = "=sum(18,29)";
sheet.range[ currentrow, 1].text = currentformula;
// caculate sum function
formularesult = workbook.caculateformulavalue(currentformula);
value = formularesult.tostring();
sheet.range[currentrow, 2].value = value;
///logic function///
//not function
// create not function string
currentformula = "=not(true)";
sheet.range[currentrow, 1].text = currentformula;
//caculate not function
formularesult = workbook.caculateformulavalue(currentformula);
value = formularesult.tostring();
sheet.range[currentrow, 2].value = value;
sheet.range[currentrow, 2].horizontalalignment = horizontalaligntype.right;
//string manipulation function//
//get the substring
// build substring function
currentformula = "=mid(\"world\",4,2)";
sheet.range[ currentrow, 1].text = currentformula;
//caculate substring function
formularesult = workbook.caculateformulavalue(currentformula);
value = formularesult.tostring();
sheet.range[currentrow, 2].value = value;
sheet.range[currentrow, 2].horizontalalignment = horizontalaligntype.right;
// random function
// create random function string.
currentformula = "=rand()";
sheet.range[ currentrow, 1].text = currentformula;
//caculate random function
formularesult = workbook.caculateformulavalue(currentformula);
value = formularesult.tostring();
sheet.range[currentrow, 2].value = value;
}
}
}
tab 3
imports spire.xls
namespace demoonlinecode
class calculateformulas
public sub democalculateformulas(resultfile as string)
dim workbook as new workbook()
dim sheet as worksheet = workbook.worksheets(0)
calculate(workbook, sheet)
workbook.savetofile(resultfile, excelversion.version2010)
end sub
public sub calculate(workbook as workbook, sheet as worksheet)
dim currentrow as integer = 1
dim currentformula as string = string.empty
dim formularesult as object = nothing
dim value as string = string.empty
' set width respectively of column a ,column b,column c
sheet.setcolumnwidth(1, 32)
sheet.setcolumnwidth(2, 16)
sheet.setcolumnwidth(3, 16)
'set the value of cell a1
sheet.range(system.math.max(system.threading.interlocked.increment(currentrow), currentrow - 1), 1).value = "examples of formulas :"
' set the value of cell a2
sheet.range(system.threading.interlocked.increment(currentrow), 1).value = "test data:"
' set the style of cell a1
dim range as cellrange = sheet.range("a1")
range.style.font.isbold = true
range.style.fillpattern = excelpatterntype.solid
range.style.knowncolor = excelcolors.lightgreen1
range.style.borders(borderslinetype.edgebottom).linestyle = linestyletype.medium
' additive operation of mutiple cells
sheet.range(currentrow, 2).numbervalue = 7.3
sheet.range(currentrow, 3).numbervalue = 5
sheet.range(currentrow, 4).numbervalue = 8.2
sheet.range(currentrow, 5).numbervalue = 4
sheet.range(currentrow, 6).numbervalue = 3
sheet.range(currentrow, 7).numbervalue = 11.3
' create arithmetic expression string about cells
currentformula = "=sheet1!$b$3 sheet1!$c$3 sheet1!$d$3 sheet1!$e$3 sheet1!$f$3 sheet1!$g$3"
'caculate arithmetic expression about cells
formularesult = workbook.caculateformulavalue(currentformula)
value = formularesult.tostring()
sheet.range(currentrow, 2).value = value
' set the value and format of two head cell
sheet.range(system.threading.interlocked.increment(currentrow), 1).value = "formulas"
sheet.range(currentrow, 2).value = "results"
sheet.range(currentrow, 2).horizontalalignment = horizontalaligntype.right
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
' expression caculation
' create arithmetic tables enclosed type string
currentformula = "=33*3/4-2 10"
sheet.range(system.threading.interlocked.increment(currentrow), 1).text = currentformula
' caculate arithmetic expression
formularesult = workbook.caculateformulavalue(currentformula)
value = formularesult.tostring()
sheet.range(currentrow, 2).value = value
'the mathematics function
'absolute value function
' create abosolute value function string
currentformula = "=abs(-1.21)"
sheet.range(system.threading.interlocked.increment(currentrow), 1).text = currentformula
' caculate abosulte value function
formularesult = workbook.caculateformulavalue(currentformula)
value = formularesult.tostring()
sheet.range(currentrow, 2).value = value
' statistical function//
' sum function
' create sum function string
currentformula = "=sum(18,29)"
sheet.range(system.threading.interlocked.increment(currentrow), 1).text = currentformula
' caculate sum function
formularesult = workbook.caculateformulavalue(currentformula)
value = formularesult.tostring()
sheet.range(currentrow, 2).value = value
'logic function
'not function
' create not function string
currentformula = "=not(true)"
sheet.range(currentrow, 1).text = currentformula
'caculate not function
formularesult = workbook.caculateformulavalue(currentformula)
value = formularesult.tostring()
sheet.range(currentrow, 2).value = value
sheet.range(currentrow, 2).horizontalalignment = horizontalaligntype.right
'string manipulation function/
'get the substring
' build substring function
currentformula = "=mid(""world"",4,2)"
sheet.range(system.threading.interlocked.increment(currentrow), 1).text = currentformula
'caculate substring function
formularesult = workbook.caculateformulavalue(currentformula)
value = formularesult.tostring()
sheet.range(currentrow, 2).value = value
sheet.range(currentrow, 2).horizontalalignment = horizontalaligntype.right
' random function
' create random function string.
currentformula = "=rand()"
sheet.range(system.threading.interlocked.increment(currentrow), 1).text = currentformula
'caculate random function
formularesult = workbook.caculateformulavalue(currentformula)
value = formularesult.tostring()
sheet.range(currentrow, 2).value = value
end sub
end class
end namespace
