此 Demo 展示如何插入公式到 Excel 并计算公式。

如果这不是您想要的 Demo，您可以通过填写表格获取免费定制 Demo。

如您有与我们产品相关的其他技术问题，请联系 该Email地址已收到反垃圾邮件插件保护。要显示它您需要在浏览器中启用JavaScript。；销售相关的问题，请联系 该Email地址已收到反垃圾邮件插件保护。要显示它您需要在浏览器中启用JavaScript。。

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

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