//第一步，取得每行匹配的 行记录标题
//可以是一个，也可以是组合，比如 车型+排量
//**重要！必须定义
string rowTitle = "车型,排量";
string[] rowTitles = rowTitle.Split(',');
//第二步，取得 行记录值 **重要！必须定义
List<string> rowKeys = new List<string>();
rowKeys.Add("飞度,1.4");
rowKeys.Add("飞度,1.5");
rowKeys.Add("雅阁,1.8");
//检查异常,并生成行记录SQL
List<string> rowSQL=new List<string>();
if (rowTitles.Length != rowKeys[0].Split(',').Length)
{
throw new Exception("行记录不匹配");
}
else
{
for (int i = 0; i < rowKeys.Count; i++)
{
string sql="";
for (int j = 0; j < rowTitles.Length; j++)
{
if (j>0)
{
sql+=" and " +rowTitles[j]+"='"+ rowKeys[i].Split(',')[j]+"'";
}
else
{
sql+= rowTitles[j]+"='"+ rowKeys[i].Split(',')[j]+"'";
}
}
rowSQL.Add(sql);
}
}
//第三步，取得数据源DataTable
List<DataColumn> dtcl=new List<DataColumn>();
dtcl.Add(new DataColumn("车型"));
dtcl.Add(new DataColumn("排量"));
dtcl.Add(new DataColumn("销量"));
dtcl.Add(new DataColumn("产量"));
DataTable dt = new DataTable();
for (int i = 0; i < dtcl.Count; i++)
{
dt.Columns.Add(dtcl[i]);
}
DataRow dr = dt.NewRow();
dr["车型"] = "飞度";
dr["排量"] = "1.4";
dr["销量"] = "6";
dr["产量"] = "5";
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["车型"] = "飞度";
dr["排量"] = "1.5";
dr["销量"] = "23";
dr["产量"] = "5";
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["车型"] = "雅阁";
dr["排量"] = "1.8";
dr["销量"] = "10";
dr["产量"] = "9";
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["车型"] = "飞度";
dr["排量"] = "1.5";
dr["销量"] = "10";
dr["产量"] = "15";
dt.Rows.Add(dr);
Dictionary<string, DataRow[]> rowValues=new Dictionary<string, DataRow[]>();
for (int i = 0; i < rowKeys.Count; i++)
{
rowValues.Add(rowKeys[i], dt.Select(rowSQL[i]));
}
//最后一步， 生成最终的合并DataTable
DataTable dtEx = dt.Clone();
//计算列
List<string> calColumns = new List<string>();
List<decimal> calColumnSums = new List<decimal>();
calColumns.Add("销量");
calColumns.Add("产量");
calColumnSums.Add(0);
calColumnSums.Add(0);
for (int i = 0; i < rowValues.Count; i++)
{
DataRow drEx = SumRow(rowValues[rowKeys[i]], calColumns, calColumnSums);
dtEx.ImportRow(drEx);
for (int j = 0; j < calColumnSums.Count; j++)
{
calColumnSums[j] = 0;
}
}