数据库可以定义表不同列之间的计算公式，进行自动公式计算，但如何实现行上的动态公式计算呢？行由于可以动态扩展，在某些应用场景下将能很好的解决实际问题。这也是Excel为什么如何好用的一个重要原因，它可以建立单元格之间的勾稽关系，通过函数的方式实现强大的计算能力。
下面我们就探讨一下如何在WPF中实现一种基于行字段的动态公式计算，具体的示例如下所示。
1 项目结构
首先，在Visual Studio IDE中新建一个WPF应用程序WpfApp_DynCalc，并添加一个类DynCalc.cs，项目具体的结构如下图所示：
2 代码实现
在WPF中，UI界面是通过XAML语言实现的，主界面对应的文件为MainWindow.xaml，因此在Visual Studio中对文件MainWindow.xaml进行编辑，核心代码如下：
<Window x:Class="WpfApp_DynCalc.MainWindow" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" Title="WPF动态计算示例" Height="350" Width="525"> <Grid> <Grid.Resources> <Style TargetType="DataGrid"> <!--网格线颜色--> <Setter Property="CanUserResizeColumns" Value="false"/> <Setter Property="Background" Value="#E6DBBB" /> <Setter Property="BorderBrush" Value="#d6c79b" /> <Setter Property="HorizontalGridLinesBrush"> <Setter.Value> <SolidColorBrush Color="#d6c79b"/> </Setter.Value> </Setter> <Setter Property="VerticalGridLinesBrush"> <Setter.Value> <SolidColorBrush Color="#d6c79b"/> </Setter.Value> </Setter> </Style> <!--标题栏样式--> <!--<Style TargetType="DataGridColumnHeader" > <Setter Property="Width" Value="50"/> <Setter Property="Height" Value="30"/> <Setter Property="FontSize" Value="14" /> <Setter Property="Background" Value="White" /> <Setter Property="FontWeight" Value="Bold"/> </Style>--> <Style TargetType="DataGridColumnHeader"> <Setter Property="SnapsToDevicePixels" Value="True" /> <Setter Property="MinWidth" Value="0" /> <Setter Property="MinHeight" Value="28" /> <Setter Property="Foreground" Value="#323433" /> <Setter Property="FontSize" Value="14" /> <Setter Property="Cursor" Value="Hand" /> <Setter Property="Template"> <Setter.Value> <ControlTemplate TargetType="DataGridColumnHeader"> <Border x:Name="BackgroundBorder" BorderThickness="0,1,0,1" BorderBrush="#e6dbba" Width="Auto"> <Grid > <Grid.ColumnDefinitions> <ColumnDefinition Width="*" /> </Grid.ColumnDefinitions> <ContentPresenter Margin="0,0,0,0" VerticalAlignment="Center" HorizontalAlignment="Center"/> <Path x:Name="SortArrow" Visibility="Collapsed" Data="M0,0 L1,0 0.5,1 z" Stretch="Fill" Grid.Column="2" Width="8" Height="6" Fill="White" Margin="0,0,50,0" VerticalAlignment="Center" RenderTransformOrigin="1,1" /> <Rectangle Width="1" Fill="#d6c79b" HorizontalAlignment="Right" Grid.ColumnSpan="1" /> <!--<TextBlock Background="Red"> <ContentPresenter></ContentPresenter></TextBlock>--> </Grid> </Border> </ControlTemplate> </Setter.Value> </Setter> <Setter Property="Height" Value="25"/> </Style> <!--行样式触发--> <!--背景色改变必须先设置cellStyle 因为cellStyle会覆盖rowStyle样式--> <Style TargetType="DataGridRow"> <Setter Property="Background" Value="#F2F2F2" /> <Setter Property="Height" Value="25"/> <Setter Property="Foreground" Value="Black" /> <Style.Triggers> <!--隔行换色--> <Trigger Property="AlternationIndex" Value="0" > <Setter Property="Background" Value="#e7e7e7" /> </Trigger> <Trigger Property="AlternationIndex" Value="1" > <Setter Property="Background" Value="#f2f2f2" /> </Trigger> <Trigger Property="IsMouseOver" Value="True"> <Setter Property="Background" Value="LightGray"/> <!--<Setter Property="Foreground" Value="White"/>--> </Trigger> <Trigger Property="IsSelected" Value="True"> <Setter Property="Foreground" Value="Black"/> </Trigger> </Style.Triggers> </Style> <!--单元格样式触发--> <Style TargetType="DataGridCell"> <Setter Property="Template"> <Setter.Value> <ControlTemplate TargetType="DataGridCell"> <TextBlock TextAlignment="Center" VerticalAlignment="Center" > <ContentPresenter /> </TextBlock> </ControlTemplate> </Setter.Value> </Setter> <Style.Triggers> <Trigger Property="IsSelected" Value="True"> <!--<Setter Property="Background" Value="White"/> <Setter Property="BorderThickness" Value="0"/>--> <Setter Property="Foreground" Value="Black"/> </Trigger> </Style.Triggers> </Style> </Grid.Resources> <DataGrid Name="dgrid" HorizontalAlignment="Left" Margin="10,10,0,0" VerticalAlignment="Top" Height="256" Width="498" AutoGenerateColumns="False" > <DataGrid.Columns> <DataGridTextColumn Header="指标" Binding="{Binding Zb}" Width="118"/> <DataGridTextColumn Header="值" Binding="{Binding Value}" Width="100"/> <DataGridTextColumn Header="公式" Binding="{Binding Formula}" Width="188"/> </DataGrid.Columns> </DataGrid> <Button Content="计 算" HorizontalAlignment="Left" Margin="419,281,0,0" VerticalAlignment="Top" Width="85" Click="Button_Click_1" Height="28" /> <TextBlock Name="lblResult" HorizontalAlignment="Left" Margin="28,281,0,0" TextWrapping="Wrap" Text="" VerticalAlignment="Top"/> </Grid> </Window>
从代码中可知，界面中通过DataGrid控件生成一个表格，其中绑定了相关的变量，进行动态的数据展现。而计算但你的后台逻辑是通过Button_Click_1事件实现的，后台代码文件为MainWindow.xaml.cs，编辑后台文件MainWindow.xaml.cs，核心代码如下:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows; using System.Windows.Controls; using System.Windows.Data; using System.Windows.Documents; using System.Windows.Input; using System.Windows.Media; using System.Windows.Media.Imaging; using System.Windows.Navigation; using System.Windows.Shapes; using System.Collections.ObjectModel; namespace WpfApp_DynCalc { /// <summary> /// MainWindow.xaml 的交互逻辑 /// </summary> public partial class MainWindow : Window { public MainWindow() { InitializeComponent(); ObservableCollection<Dynformula> ofs = new ObservableCollection<Dynformula>(); ofs.Add(new Dynformula { Zb = "A", Value = "1", Formula = "" }); ofs.Add(new Dynformula { Zb = "B", Value = "2", Formula = "2*A+1" }); ofs.Add(new Dynformula { Zb = "C", Value = "3", Formula = "B*B" }); ofs.Add(new Dynformula { Zb = "D", Value = "4", Formula = "C-2" }); ofs.Add(new Dynformula { Zb = "Z", Value = "5", Formula = "D+C" }); this.dgrid.ItemsSource = ofs; } private void Button_Click_1(object sender, RoutedEventArgs e) { this.lblResult.Text = "计算..."; this.dgrid.ItemsSource= DynCalc.CalcScript(ref this.dgrid); this.lblResult.Text = "计算完成!"; } } public class Dynformula { private string zb; public string Zb { get { return zb; } set { zb = value; } } private string value; public string Value { get { return this.value; } set { this.value = value; } } private string formula; public string Formula { get { return formula; } set { formula = value; } } } }
其中计算按钮的逻辑中，调用了一个核心方法DynCalc.CalcScript(ref this.dgrid)，它在类文件DynCalc.cs中定义，其核心带代码如下所示：
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace WpfApp_DynCalc { using System.Collections.ObjectModel; using System.Windows; using System.Windows.Controls; using System.Reflection; using System.Globalization; using Microsoft.CSharp; using System.CodeDom; using System.CodeDom.Compiler; public static class DynCalc { public static ObservableCollection<Dynformula> CalcScript(ref DataGrid dgrid) { CSharpCodeProvider objCSharpCodePrivoder = new CSharpCodeProvider(); ICodeCompiler objICodeCompiler = objCSharpCodePrivoder.CreateCompiler(); CompilerParameters objCompilerParameters = new CompilerParameters(); objCompilerParameters.ReferencedAssemblies.Add("System.dll"); objCompilerParameters.GenerateExecutable = false; objCompilerParameters.GenerateInMemory = true; CompilerResults cr = objICodeCompiler.CompileAssemblyFromSource(objCompilerParameters, GenerateCode(ref dgrid)); if (cr.Errors.HasErrors) { Console.WriteLine("编译错误："); foreach (CompilerError err in cr.Errors) { Console.WriteLine(err.ErrorText); } return null; } else { // 通过反射，调用实例 Assembly objAssembly = cr.CompiledAssembly; object objDynCalc = objAssembly.CreateInstance("DynamicCodeGenerate.RunScript"); //MethodInfo objMI = objHelloWorld.GetType().GetMethod("OutPut"); //Console.WriteLine(objMI.Invoke(objHelloWorld, null)); ObservableCollection<Dynformula> ofsnew = new ObservableCollection<Dynformula>(); //循环datagrid进行公式计算并赋值 for (int i = 0; i < dgrid.Items.Count; i++) { Dynformula item = dgrid.Items[i] as Dynformula; if (item == null) { break; } string zb = item.Zb; PropertyInfo pinfo = objDynCalc.GetType().GetProperty(zb); if (pinfo != null && pinfo.CanRead) { //获取属性get值 object obj_Name = pinfo.GetValue(objDynCalc, null); // item.Value = obj_Name.ToString(); ofsnew.Add(new Dynformula { Zb = item.Zb, Value = obj_Name.ToString(), Formula = item.Formula}); } } return ofsnew; } } /// <summary> /// 计算逻辑C#脚本动态构建 /// </summary> /// <param name="dgrid">存有指标以及指标计算公式的datagrid</param> /// <returns>C#脚本</returns> static string GenerateCode(ref DataGrid dgrid) { StringBuilder sb = new StringBuilder(); StringBuilder sb构建函数内容 = new StringBuilder(); sb.Append("using System;"); sb.Append(Environment.NewLine); sb.Append("namespace DynamicCodeGenerate"); sb.Append(Environment.NewLine); sb.Append("{"); sb.Append(Environment.NewLine); sb.Append(" public class RunScript"); sb.Append(Environment.NewLine); sb.Append(" {"); //------------------------------------------------------------ for(int i=0;i<dgrid.Items.Count;i++) { Dynformula item = dgrid.Items[i] as Dynformula; if (item == null) { break; } string zb = item.Zb; sb.Append(Environment.NewLine); sb.AppendFormat(" public double _{0};", item.Zb); sb.Append(Environment.NewLine); sb.AppendFormat(" public double {0}",item.Zb); sb.Append(Environment.NewLine); sb.Append(" {"); sb.Append(Environment.NewLine); if (item.Formula.Trim() != "") { sb.Append(" set{ "+item.Zb+"=value;}" ); sb.Append(Environment.NewLine); sb.Append(" get{return "+ item.Formula + ";}"); } else { sb.Append(" set{ _" + item.Zb + "=value;}"); sb.Append(Environment.NewLine); sb.Append(" get{return _"+item.Zb+";} "); sb.Append(Environment.NewLine); sb构建函数内容.Append(" _" + item.Zb + "=" + item.Value); } sb.Append(Environment.NewLine); sb.Append(" }"); sb.Append(Environment.NewLine); } //-------------------------------------------- //构造函数进行赋值 sb.Append(Environment.NewLine); sb.Append(" public RunScript()"); sb.Append(Environment.NewLine); sb.Append(" {"); sb.Append(Environment.NewLine); sb.AppendFormat(" {0};",sb构建函数内容.ToString()); sb.Append(Environment.NewLine); sb.Append(" }"); sb.Append(Environment.NewLine); //---------------------------------------------- sb.Append(Environment.NewLine); sb.Append(" public string OutPut()"); sb.Append(Environment.NewLine); sb.Append(" {"); sb.Append(Environment.NewLine); sb.Append(" return \"Hello world!\";"); sb.Append(Environment.NewLine); sb.Append(" }"); //----------------------------------------- sb.Append(Environment.NewLine); sb.Append(" }"); sb.Append(Environment.NewLine); sb.Append("}"); string code = sb.ToString(); Console.WriteLine(code); return code; } } }
其中用到了C#内置的CSharpCodeProvider类，它内置了一个C#语言的编译器，可以用来动态的执行C#代码，因此，单击按钮后，就需要在表格中获取指标的值，并动态构建脚本来执行，这样就可以获取到对应的变量值，并显示到UI上。
3 效果
运行程序，其中值列的值为初始值，点击计算后会根据公式列配置进行动态计算，初始化界面如下:
可见现在的值是根据公式配置进行动态计算的。当然代码经过扩展还可以支持函数和简单的逻辑判断，如if ...else等。实现更强大的逻辑处理。