C# DataTable中的计算函数

来源：互联网发布：word数据转换成excel 编辑：程序博客网时间：2024/05/21 14:06

在日常开发中很少使到DataTable实现业务，因此知道DataTable的用法并不多，除了普通的查询以及排序。现在分享一个DataTable中的运算函数Compute，它能帮助我们直接使用DataTable进行求和、求个数等操作，这对于数据后加工挺有效果，因此分享一下，用法如下：

函数名：Compute

参数1：expression 表达式

参数2：filter 条件表达式

如：dataTable1中包含了ID，QTY(数量)两个字段

ID QTY

1 4

2 5

3 6

现在需要求ID大于1的和

var sum=dataTable1.Compute("Sum(QTY)","ID>1");

既可以实现，有点需要注意的地方就是，要进行聚合函数的列必须定义成数字类型，如int,decimal等。

static void Main(string[] args)
{
var dataTable1= InitDataTable();
Console.WriteLine(dataTable1.Compute("sum(QTY)","ID>1"));
Console.ReadKey();
}
/// <summary>
/// 初始化DataTable
/// </summary>
/// <returns></returns>
private static DataTable InitDataTable()
{
DataTable dataTable1 = new DataTable();
DataColumn dc = new DataColumn
{
ColumnName = "ID",
Caption = "序号"
};
dataTable1.Columns.Add(dc);
dc = new DataColumn
{
ColumnName = "QTY",
Caption = "数量",
DataType=typeof(decimal)
};
dataTable1.Columns.Add(dc);
DataRow dr = dataTable1.NewRow();
dr["ID"] = 1;
dr["QTY"] = 5;
dataTable1.Rows.Add(dr);
dr = dataTable1.NewRow();
dr["ID"] = 2;
dr["QTY"] = 54;
dataTable1.Rows.Add(dr);
dr = dataTable1.NewRow();
dr["ID"] = 3;
dr["QTY"] = 6;
dataTable1.Rows.Add(dr);
dr = dataTable1.NewRow();
dr["ID"] = 4;
dr["QTY"] = 7;
dataTable1.Rows.Add(dr);

return dataTable1;
}

如果使用两个字段的乘积求和需要提前设定表达式即在创建DataColunm时设定。

dc = new DataColumn
{
ColumnName = "TOTAL",
Caption = "ID*QTY",
DataType = typeof(decimal),
Expression="ID*QTY"
};
dataTable1.Columns.Add(dc);

效果如下：

static void Main(string[] args)
{
var dataTable1= InitDataTable();
Console.WriteLine(dataTable1.Compute("sum(TOTAL)","ID>1"));
Console.ReadKey();
}
/// <summary>
/// 初始化DataTable
/// </summary>
/// <returns></returns>
private static DataTable InitDataTable()
{
DataTable dataTable1 = new DataTable();
DataColumn dc = new DataColumn
{
ColumnName = "ID",
Caption = "序号",
DataType = typeof(decimal)
};
dataTable1.Columns.Add(dc);
dc = new DataColumn
{
ColumnName = "QTY",
Caption = "数量",
DataType=typeof(decimal)
};
dataTable1.Columns.Add(dc);
dc = new DataColumn
{
ColumnName = "TOTAL",
Caption = "ID*QTY",
DataType = typeof(decimal),
Expression="ID*QTY"
};
dataTable1.Columns.Add(dc);
DataRow dr = dataTable1.NewRow();
dr["ID"] = 1;
dr["QTY"] = 5;
dataTable1.Rows.Add(dr);
dr = dataTable1.NewRow();
dr["ID"] = 2;
dr["QTY"] = 54;
dataTable1.Rows.Add(dr);
dr = dataTable1.NewRow();
dr["ID"] = 3;
dr["QTY"] = 6;
dataTable1.Rows.Add(dr);
dr = dataTable1.NewRow();
dr["ID"] = 4;
dr["QTY"] = 7;
dataTable1.Rows.Add(dr);

return dataTable1;
}

阅读全文

1 0