此 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