|
028-81705109
|
|

|

## 计算公式

### Tab 1

Mathematic Functions:

 Calculate symbol : ABS SIN COS TAN Calculate Data:

Logic Function:

 Calculate symbol : AND OR NOT Calculate Data: TRUE FALSE TRUE FALSE

Simple Expression:

 Calculate symbol : SUM SUBTRACT PRODUCT QUOTIENT MOD POWER Calculate Data:

MID Functions:

 Text : Start Number:
 Number Charts:

Option:

 Excel Version: Excel 97-2003 Workbook Excel 2007 Workbook Excel 2007 Binary Workbook Excel 2010 Workbook Excel 2010 Binary Workbook Excel 2013 Workbook Excel 2016 Workbook

### 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";

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

' 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"

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, 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"

' 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)"

' 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)"

' 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)"

'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()"

'Caculate random function
formulaResult = workbook.CaculateFormulaValue(currentFormula)
value = formulaResult.ToString()
sheet.Range(currentRow, 2).Value = value

End Sub

End Class

End Namespace``````