using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;
/// <summary>
/// trans 的摘要说明
/// author: jorans xu
/// </summary>
public class trans//转换类
{
public trans()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// DataTable 行转成列
/// </summary>
/// <param name="DataTableSource">数据源</param>
/// <param name="groupbyDataMeber">按哪些字段转成列</param>
/// <param name="needTransDataMenber">需要转的列</param>
/// <param name="data">需要转的列对应的值</param>
/// <param name="defaultvalue">当对应的列没有值时,使用该值</param>
/// <returns></returns>
public DataTable transRowToCol(DataTable DataTableSource,string groupbyDataMeber,string needTransDataMenber,string data,string defaultvalue)
{
DataTable dtSource = new DataTable();
//建立一个副本。
dtSource = DataTableSource.Copy();
DataTable dt = new DataTable();
bool flag = true;
//这是datatable中groupby的字段
string[] group=groupbyDataMeber.Split(',');
int count = group.Length;
//移到另外一个datatable,添加列名
for (int i = 0; i < group.Length; i++)
{
DataColumn dc = new DataColumn();
dc.ColumnName = group[i].ToString();
dt.Columns.Add(dc);
}
//这是动态添加列名,因为我们不知道要转换的列的值到底有多少。
//转换的列的值就是作为新的datatable的列名
for (int j = 0; j < dtSource.Rows.Count; j++)
{
string colValue = dtSource.Rows[j][needTransDataMenber].ToString();
for (int k = dt.Columns.Count - 1; k >= 0; k--)
{
if(colValue==dt.Columns[k].ColumnName)//已经存在,则不需要再新增列名
{
break;
}
if (k == 0)//遍历完所有列,如果没有找到,则新增列名
{
DataColumn dc = new DataColumn();
dc.ColumnName = colValue;
dt.Columns.Add(dc);
}
}
}
//以下是新增数据行
for (int i = 0; i < dtSource.Rows.Count;i++ )
{
DataRow dr = dt.NewRow();
//直接把值移到新的datatable,这些是group by的列
for (int k = 0; k < count;k++ )
{
dr[group[k]]=dtSource.Rows[i][group[k]].ToString();
}
for(int j=count;j<dt.Columns.Count;j++)
{
if (dtSource.Rows[i][needTransDataMenber].ToString() == dt.Columns[j].ColumnName)
{
dr[dt.Columns[j].ColumnName] = dtSource.Rows[i][data].ToString();
}
else
{
dr[dt.Columns[j].ColumnName] = defaultvalue;//在对应的列下没有值时,给默认值
}
}
dt.Rows.Add(dr);//添加了一行
//以下是向原datatable继续遍历。如果有相同的groupby的字段则可以修改列值了
//因为groupby的字段可能不只一个,故使用标志位flag
for (int l = i + 1; l < dtSource.Rows.Count; l++)
{
for (int m = 0; m < count; m++)
{
if (dtSource.Rows[l][group[m]].ToString() != dt.Rows[dt.Rows.Count - 1][group[m]].ToString())
{
flag = false;
break;
}
else
{
flag = true;
}
}
if (flag == true)
{
for (int j = count; j < dt.Columns.Count; j++)
{
if (dtSource.Rows[l][needTransDataMenber].ToString() == dt.Columns[j].ColumnName)
{
dt.Rows[dt.Rows.Count-1][j] = dtSource.Rows[l][data].ToString();
dtSource.Rows.RemoveAt(l);//删除一行
l--;
break;
}
}
}
}
}
return dt;
}
/// <summary>
/// DataTable 列转成行
/// </summary>
/// <param name="DataTableSource">数据源</param>
/// <param name="groupbyDataMeber">按哪些字段转成列</param>
/// <param name="needTransCols">需要转的列</param>
/// <param name="beColName">需要转的这些列的列名</param>
/// <param name="valueColName">值对应的列名</param>
/// <returns></returns>
public DataTable transColToRow(DataTable DataTableSource,string groupbyDataMeber, string needTransCols, string beColName, string valueColName)
{
DataTable dts = DataTableSource;
DataTable dt = new DataTable();
string[] group=groupbyDataMeber.Split(',');
string[] colname=needTransCols.Split(',');
//create dt columnname
for (int i = 0; i < group.Length + 2;i++ )
{
if (i < group.Length)
{
DataColumn dc = new DataColumn();
dc.ColumnName = group[i];
dt.Columns.Add(dc);
}
else
{
dt.Columns.Add(beColName, System.Type.GetType("System.String"));
dt.Columns.Add(valueColName, System.Type.GetType("System.String"));
break;
}
}
for (int i = 0; i < dts.Rows.Count;i++ )
{
for (int j = 0; j < colname.Length;j++ )
{
DataRow dr = dt.NewRow();
for (int k = 0; k < group.Length; k++)
{
dr[group[k]] = dts.Rows[i][group[k]].ToString();
}
dr[beColName] = colname[j];
dr[valueColName] = dts.Rows[i][colname[j]].ToString();
dt.Rows.Add(dr);
}
}
return dt;
}
/// <summary>
/// DataTable 新增一行
/// </summary>
/// <param name="dtSource">数据源</param>
/// <param name="rowsValue">列值</param>
/// <returns></returns>
public DataTable addNewRow(DataTable dtSource,string rowsValue)
{
string[] values = rowsValue.Split(',');
DataRow dr = dtSource.NewRow();
for (int i = 0; i < values.Length; i++)
{
if (i < dtSource.Columns.Count)
{
dr[i] = values[i];
}
}
dtSource.Rows.Add(dr);
return dtSource;
}
/// <summary>
/// DataTable 指定行号新增一行
/// </summary>
/// <param name="dtSource">数据源</param>
/// <param name="rowsValue">列值</param>
/// <param name="index">插入行的位置</param>
/// <returns></returns>
public DataTable addNewRow(DataTable dtSource, string rowsValue,int index)
{
string[] values = rowsValue.Split(',');
DataRow dr = dtSource.NewRow();
for (int i = 0; i < values.Length; i++)
{
if (i < dtSource.Columns.Count)
{
dr[i] = values[i];
}
}
dtSource.Rows.InsertAt(dr,index);
return dtSource;
}
/// <summary>
/// GridView合并行,groupbyDataMeberIndex字段的数据必须有序
/// </summary>
/// <param name="dtSource">GridView</param>
/// <param name="groupbyDataMeber">group by 哪些字段的index</param>
/// <returns></returns>
public GridView mergeRows(GridView dtSource, string groupbyDataMeberIndex)
{
string[] group = groupbyDataMeberIndex.Split(',');
bool flag=false;
for (int j = 0; j < group.Length; j++)
{
int index = int.Parse(group[j]);
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int k = i + 1; k < dtSource.Rows.Count; k++)
{
string test1 = dtSource.Rows[i].Cells[index].Text.ToString();
string test2 = dtSource.Rows[k].Cells[index].Text.ToString();
if (dtSource.Rows[i].Cells[index].Text.ToString() == dtSource.Rows[k].Cells[index].Text.ToString())
{
flag = true;
}
else
{
flag = false;
}
if (flag == false)
{
if (k > i + 1)//多行一样,可合并
{
dtSource.Rows[i].Cells[index].RowSpan = k - i;
for (int m = i + 1; m < i + k - i; m++)
{
dtSource.Rows[m].Cells[index].Visible = false;
}
}
i = k - 1;//next 应为k,因为i还要进行i++,所以先k-1
break;
}
if (flag = true && k == dtSource.Rows.Count - 1)
{
dtSource.Rows[i].Cells[index].RowSpan = k - i + 1;
for (int m = i + 1; m < i + (k - i + 1); m++)
{
dtSource.Rows[m].Cells[index].Visible = false;
string test = dtSource.Rows[m].Cells[index].Text;
}
i = k;
break;
}
}
}
}
return dtSource;
}
}