文档原址：

实现复杂公式计算，比如含IF分支判断等，可考虑通过调用EXCEL公式获取值。

Excel公式计算参考：

方法一： MSScriptControl.ScriptControl

VB.NET

 

Dim exp As String = "3 + 4"
Dim t As Type = Type.GetTypeFromProgID("MSScriptControl.ScriptControl")
Dim obj As Object = Activator.CreateInstance(t)

t.InvokeMember("Language", System.Reflection.BindingFlags.SetProperty, _
           Nothing, obj, New Object() {         "vbscript"})

Dim result As Object = t.InvokeMember("Eval", System.Reflection.BindingFlags.InvokeMethod, _
                                       Nothing, obj, New Object() {exp})
MsgBox("method 1: " & CStr(result))

 

 

C#

var exp = "3 + (2 + 3)/5";
var type = Type.GetTypeFromProgID("MSScriptControl.ScriptControl");
var obj = Activator.CreateInstance(type);
type.InvokeMember("Language", BindingFlags.SetProperty, null,
                  obj, new object[] { "javascript" });

var result = type.InvokeMember("Eval", BindingFlags.InvokeMethod, null,
                               obj, new object[] { exp });
Console.WriteLine("{0} = {1}", exp, result);

 

 

方法二： CodeDOM

VB.NET

 

Dim oCodeProvider As VBCodeProvider = New VBCodeProvider
Dim oCParams As CompilerParameters = New CompilerParameters
Dim oCResults As CompilerResults = Nothing
Dim oAssy As System.Reflection.Assembly = Nothing
Dim oExecInstance As Object = Nothing
Dim oRetObj As Object = Nothing
Dim oMethodInfo As MethodInfo = Nothing
Dim oType As Type = Nothing
Dim strSource As String = _
    "Public Class MainClass " + vbCrLf + _
    "   Public Shared Function Eval() As Integer" + vbCrLf + _
    "      Return 3 + 4" + vbCrLf + _
    "   End Function" + vbCrLf + _
    "End Class"
oCParams.CompilerOptions = "/t:library"
oCParams.GenerateInMemory = True

oCResults = oCodeProvider.CompileAssemblyFromSource(oCParams, strSource)

If oCResults.Errors.Count <> 0 Then
    MsgBox("Error")
End If
oAssy = oCResults.CompiledAssembly

'oExecInstance = oAssy.CreateInstance("MainClass")
'oType = oExecInstance.GetType
'oMethodInfo = oType.GetMethod("Eval")
'oRetObj = oMethodInfo.Invoke(oExecInstance, Nothing)

oType = oAssy.GetType("MainClass")
oRetObj = oType.InvokeMember("Eval", BindingFlags.InvokeMethod, Nothing, Nothing, Nothing)
MsgBox("method 2: " & CStr(oRetObj))

 

 

C#

var exp = "3 + (2 + 3)/5";
var csCodeProvider = new CSharpCodeProvider();
var csParams = new CompilerParameters();
var source = "public class MainClass { public static object Eval() { return (#exp#); } }";
source = source.Replace("#exp#", exp);
csParams.CompilerOptions = "/t:library";
csParams.GenerateInMemory = true;

var csResults = csCodeProvider.
                CompileAssemblyFromSource(csParams, source);
if (csResults.Errors.Count > 0)
{
    Console.WriteLine(csResults.Errors[0].ToString());
    return;
}

var ass = csResults.CompiledAssembly;
var type = ass.GetType("MainClass");
var result = type.InvokeMember("Eval", BindingFlags.InvokeMethod,
                                null, null, null);

Console.WriteLine("{0} = {1}", exp, result);

 

 

方法三： DataColumn.Expression & DataTable.Compute方法。

VB.NET

Dim dt As DataTable = New DataTable
dt.Columns.Add("Val1", GetType(Integer))
dt.Columns.Add("Val2", GetType(Integer))
dt.Columns.Add("Result").Expression = String.Format("Val1 + Val2", Me.TextBox1.Text)

dt.Rows.Add(New Object() {3, 4})

MsgBox("method 3: " & dt.Rows(0)("Result"))

 

C#

var exp = "3 + (2 + 3)/5";
DataTable dt = new DataTable();
dt.Columns.Add("Result").Expression = exp;
dt.Rows.Add(dt.NewRow());

var result = dt.Rows[0]["Result"];

Console.WriteLine("{0} = {1}", exp, result);