/// <summary>
/// 行转列
/// </summary>
/// <param name="_outDataSource">数据源</param>
/// <param name="categoryname">列名:行业</param>
/// <param name="date">列名:日期(该列为:行转列)</param>
/// <param name="zongshu">列名:条数</param>
/// <returns></returns>
private DataTable RCC(DataTable _outDataSource, string categoryname, string date, string zongshu)
{
//从DataTable中读取不重复的日期行,用来构造新DataTable的列
DataTable distinct_date = _outDataSource.DefaultView.ToTable(true, date);
DataTable new_DataTable = new DataTable();
//将行业列添加到新表中
DataColumn new_d_col = new DataColumn();
new_d_col.ColumnName = categoryname;
new_d_col.Caption = "";
new_DataTable.Columns.Add(new_d_col);
StringBuilder str_sum = new StringBuilder();
//开始在新表中构造日期列
foreach (DataRow dr in distinct_date.Rows)
{
new_d_col = new DataColumn();
new_d_col.DataType = typeof(decimal);
new_d_col.ColumnName = dr[date].ToString();
new_d_col.Caption = dr[date].ToString();
new_d_col.DefaultValue = 0;
new_DataTable.Columns.Add(new_d_col);
//这个的目的是为合计列构造expression 注释 为 可算合计
// str_sum.Append("+[").Append(dr[date].ToString()).Append("]");
}
将合计列添加到新表中 注释 为 可算合计
//new_d_col = new DataColumn();
//new_d_col.DataType = typeof(decimal);
//new_d_col.ColumnName = "Sum";
//new_d_col.Caption = "合计";
//new_d_col.DefaultValue = 0;
//new_d_col.Expression = str_sum.ToString().Substring(1);
//new_DataTable.Columns.Add(new_d_col);
/*到此新表已经构建完毕,下面开始为新表添加数据*/
//从原DataTable中读出不重复的行业,以行业为关键字来构造新表的行
DataTable distinct_object = _outDataSource.DefaultView.ToTable(true, categoryname);
DataRow[] drs;
DataRow new_dr;
foreach (DataRow dr in distinct_object.Rows)
{
new_dr = new_DataTable.NewRow();
new_dr[categoryname] = dr[categoryname].ToString();
foreach (DataRow _dr in distinct_date.Rows)
{
drs = _outDataSource.Select(categoryname + "='" + dr[categoryname].ToString() + "' and " + date + "='" + _dr[date].ToString() + "'");
if (drs.Length != 0)
{
new_dr[_dr[date].ToString()] = Math.Round(Convert.ToDecimal(drs[0][zongshu]), 2);
}
}
new_DataTable.Rows.Add(new_dr);
}
return new_DataTable;
}
调用方法:
DataTable newTable = new DataTable();
newTable.Columns.Add("行业", typeof(string)); //行业名称
newTable.Columns.Add("年份", typeof(string)); //年份
newTable.Columns.Add("条数", typeof(string)); //条数
int years = Convert.ToInt32(DateTime.Now.ToString("yyyy"));
if (!string.IsNullOrEmpty(currentyear))
{
years = Convert.ToInt32(currentyear);
}
for (var i = years; i >= years - 9; i--)
{
DataTable dt = GetGroupIndustryByYear(i.ToString());
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
DataRow n_dr = newTable.NewRow();
n_dr["行业"] = Convert.ToString(dr["INDUSTRYCATEGORYNAME"]);
n_dr["年份"] = Convert.ToString(i);
n_dr["条数"] = Convert.ToString(dr["count"]);
newTable.Rows.Add(n_dr);
}
}
}
DataTable newDT = RCC(newTable);