对DataTable进行分组

因为程序的特殊情景，需要在sql查出来的DataTable进行分组，DataTable分组可以使用linq，也可以自己写分组程序。

linq相对简单：

cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 300;
MySqlDataReader reader = cmd.ExecuteReader();

DataTable tb = new DataTable();
detailTable.Load(reader);
reader.Close();

var query = from c in tb .AsEnumerable()

group c by new {

pingming = c.Field<string>("品名"),

guige = c.Field<string>("规格")

}

into s

select new

{

pingming = s.Select(p => p.Field<string>("品名")).First(),

shuliang = s.Sum(p => Convert.ToInt32(p.Field<string>("数量"))),

guige = s.Select(p => p.Field<string>("规格")).First(),

biaohao = string.Join(";",s.Select(p => p.Field<string>("表号")))

};

　　　　 DataTable tbc = tb.Clone();

query.ToList().ForEach(p =>tbc.Rows.Add(p.pingming, p.guige,p.biaohao,p.shuliang));//同下面foreach，只不过下面版本循环体可以做一些逻辑操作

　　　　 foreach(var row in query )

　　　　　{

　　　　　　　　DataRow r = tbc.NewRow();

　　　　　　　　r["pingming"]=row.pingming;

　　　　　　　　r["guige "]=row.guige ;

　　　　　　　　r["biaohao "]=row.biaohao;

　　　　　　　　r["shuliang "]=row.shuliang ;

　　　　　　　　tbc.Rows.Add(r);

　　　　　}

注意：变量query只制定了LINQ查询，该查询不是通过这个赋值语句执行的，只要使用foreach循环访问，该查询就会执行。

但是在linq中分组的字段是写死的，而我的需求是选了哪些列对哪些列进行分组，这样的话需要自己写对DataTable 的分组。

dt 是分组前的DataTable，dt_result 是分组后的DataTable，strGroups是分组列以逗号隔开，numColumsStr是以#分割的汇总列

　　dt_result=AddGroup(dt, strGroups, numColumsStr);

/// <summary>
/// 返回分组汇总后的datatable
/// </summary>
/// <param name="dt">要分组汇总的DataTable</param>
/// <param name="strGroupsField">分组列</param>
/// <param name="numColumsStr">需要求和的列，用#分割</param>
/// <returns></returns>
public DataTable AddGroup(DataTable dt, string strGroupFields, string numColumsStr)
{
if (dt.Rows.Count==0)
return dt;
DataTable table = TableSort(dt, strGroupFields);
DataTable result = dt.Clone();//结果
result.Columns["cinema_pay_type_name"].AllowDBNull = true;
result.Columns["consume_type"].AllowDBNull = true;
IList<string> numColumns = new List<string>();//存放汇总的数量或金额列
string[] columns = numColumsStr.Split('#');
foreach (string str in columns)
{
numColumns.Add(str);
}
string[] strGroupField = strGroupFields.Split(',');
string prevDept = "";//此组合列值
if (strGroupField.Length > 1)//按多个字段分组
{
for (int i = 0; i < strGroupField.Length; i++)
{
prevDept = prevDept + ","+table.Rows[0][strGroupField[i]].ToString();
}
prevDept = prevDept.Trim(',');
}
else if (strGroupFields != "" && strGroupField.Length == 1)//只按一列分组
{
prevDept = table.Rows[0][strGroupFields].ToString();//前一组的分组列值
}
else
{
return dt;
}
int NoOwn = 0;//同组项的第一行记录的位置
int index = -1;//索引
foreach (DataRow item in table.Rows)
{
index++;//当前行索引
//如果相邻两行分组字段相同则继续寻找下行，否则该行为同组的最后一行。进行数据处理
//currGroup == prevDept 表示为同组则继续比较下一行
string currGroup = GetGroupStr(table, item, strGroupFields);//此行分组字段组合，如系统管理员,支付宝,票房
if (currGroup == prevDept && index != table.Rows.Count - 1)
{
continue;
}
else if (index == table.Rows.Count - 1)//到最后一行了
{
if (currGroup.ToString() != prevDept)//最后一行与上一行不是一组
{
//上一组结束，将此组行创建出来
DataRow row = CreateGroupRow(table, strGroupFields, numColumns, NoOwn, index - 1);//新建一组行
result.Rows.Add(row.ItemArray);
//最后一行是下一组
DataRow row2 = table.NewRow();
for (int i = 0; i < strGroupField.Length; i++)
{
row2[strGroupField[i]] = table.Rows[index][strGroupField[i]];
}
foreach (string columnName in numColumns)
{
if (columnName == "ratio_amount")
{
row2[columnName] = decimal.Parse(item[columnName].ToString() == "" ? "0" : item[columnName].ToString());
}
else
row2[columnName] = item[columnName];
}
result.Rows.Add(row2.ItemArray);
}
else//最后一行与上一行同组
{
DataRow row = CreateGroupRow(table, strGroupFields, numColumns, NoOwn, index);
result.Rows.Add(row.ItemArray);
}
}
else//与上一行不同
{
prevDept = GetGroupStr(table, item, strGroupFields);//当前分组标记，新的组开始查找
DataRow row = CreateGroupRow(table, strGroupFields,numColumns, NoOwn, index-1);
NoOwn = index;
result.Rows.Add(row.ItemArray);
}

}
return result;
}
/// <summary>
/// 将DataTable按分组列排序
/// </summary>
/// <param name="oldTable">排序前DataTable</param>
/// <param name="FieldName">分组字段</param>
/// <returns>排序后DataTable</returns>
public DataTable TableSort(DataTable oldTable, string FieldNames)
{
DataTable table = oldTable.Clone();//复制表结构
DataRow[] sortRows = oldTable.Select(null,FieldNames);
table = sortRows.CopyToDataTable();
//foreach(DataRow item in sortRows)
//{
// table.ImportRow(item);
//}
return table;
}
/// <summary>
/// 获得DataTable中需要汇总的数字列
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
private static IList<string> GetNumColumns(DataTable table)
{
IList<string> numColumns = new List<string>();//存放数字行
foreach (DataColumn column in table.Columns)
{
if (column.DataType == typeof(Decimal) || column.DataType == typeof(Int32) || column.DataType == typeof(Int64)
|| column.DataType == typeof(float) || column.DataType == typeof(Double))
{
numColumns.Add(column.ColumnName);
}
}
return numColumns;
}
/// <summary>
/// 返回这一组汇总行
/// </summary>
/// <param name="table">分组表</param>
/// <param name="numColumns">需要汇总的列</param>
/// <param name="startRowIndex">开始行</param>
/// <param name="endRowIndex">结束行</param>
/// <returns></returns>
private DataRow CreateGroupRow(DataTable table,string strGroupFields, IList<string> numColumns, int startRowIndex, int endRowIndex)
{
DataRow row = table.NewRow();//汇总行
//非汇总列取第一行值
string[] strGroupField = strGroupFields.Split(',');
for (int i = 0; i < strGroupField.Length; i++)
{
if (strGroupField[i] == "cinema_sell_date")
{
row[strGroupField[i]] = table.Rows[startRowIndex][strGroupField[i]];
}
else
{
row[strGroupField[i]] = table.Rows[startRowIndex][strGroupField[i]].ToString();
}

}
//从同组项的第一行记录的位置到同组的最后一行

foreach (string columnName in numColumns)
{
object tempt = 0 ;
long suml=0;
decimal sumd = 0;
for (int i = startRowIndex; i < endRowIndex + 1; i++)
{
//object count = row[columnName];//汇总行列值
tempt = table.Rows[i][columnName];//当前行列值
//如果值为null 或 "" 则默认为 0
if (tempt == null || tempt.ToString().Trim() == "")
{
tempt = 0;
}
if (columnName == "sum_count" )
{
suml += Int64.Parse(tempt.ToString());//累加
}
else
sumd += decimal.Parse(tempt.ToString());//累加
}
if (columnName == "sum_count" || columnName == "ratio_amount")
{

row[columnName] = suml;
}
else
row[columnName] = sumd;

}
return row;
}
/// <summary>
/// 获得此行分组字段的组合，以，隔开
/// </summary>
/// <param name="table"></param>
/// <param name="item"></param>
/// <param name="strGroupFields"></param>
/// <returns></returns>
private string GetGroupStr(DataTable table,DataRow item,string strGroupFields)
{
string[] strGroupField = strGroupFields.Split(',');
string currGroupStr = "";
for(int i = 0; i < strGroupField.Length; i++)
{
currGroupStr = currGroupStr + "," + item[strGroupField[i]].ToString();
}
currGroupStr = currGroupStr.Trim(',');
return currGroupStr;
}