原文出处:
https://www.cnblogs.com/mzoylee/archive/2009/04/23/1442484.html
今天在做Chart图表时,发现客户要求将数据列表显示的格式与数据中的行,列是互换的,效果如下。其实,单纯实现互换没这么麻烦,用不了这么多for循环,但是,互换后datatable中会默认产生名为Columns1的名字,这是我们所不需要的,所以用了以下这程方法。Name、Sales是数据库中的两个实际字段,能过互换后,DataTable的名称也要做出相应的调整,因此加了一个判断,这只是实现的二种方法。希望朋友们有更好的解决方法,请告知,在此谢过。也希望这段代码有点用。
方法一:上述图中是将gridview的头也绑定上了数据,自身带有一定的样式,可以在gridview中更改头的样式,如何你很懒那么还是用第二种方法吧
///
/// DataTable行列转换方法
///
///
///
private DataTable ConvertRCDataTable(DataTable dt)
{
try
{
int rowCount = dt.Rows.Count+1;
int columnsCount = dt.Columns.Count;
DataTable newDt = new DataTable();
DataColumn dc = new DataColumn();
for(int rowi=0;rowi
{
if(rowi==0)
{
if (dt.Columns[0].ToString() == "Name" || dt.Columns[0].ToString() == "Sales")
{
dt.Columns["Name"].ColumnName = "月份";
dt.Columns["Sales"].ColumnName = "价格";
}
newDt.Columns.Add(dt.Columns[0].ToString());
}
else
{
newDt.Columns.Add(dt.Rows[rowi-1][0].ToString());
}
}
for(int columnsi=0;columnsi
{
DataRow dr=newDt.NewRow();
for(int rowj=0;rowj
{
if(rowj==0)
{
dr[rowj]=dt.Columns[columnsi].ToString();
}
else
{
dr[rowj]=dt.Rows[rowj-1][columnsi].ToString();
}
}
if(columnsi!=0)
newDt.Rows.Add(dr);
}
return newDt;
}
catch(Exception ex)
{
throw new Exception(ex.ToString());
}
}
方法二:这种方法也可实现互换,但在方法中不能去除columns字的字段名字,需要将Gfidview的ShowHead属性设为False即可。
public DataTable RevertDataTable(DataTable dt)
{
int dtColumnsNum = dt.Columns.Count;
int dtRowsNum = dt.Rows.Count;
DataTable dtRes = new DataTable("ndt");
for(int i = 0; i<= dtRowsNum; i++)
{
if (dt.Columns[0].ToString() == "Name" || dt.Columns[0].ToString() == "Sales")
{
dt.Columns["Name"].ColumnName = "月份";
dt.Columns["Sales"].ColumnName = "价格";
}
dtRes.Columns.Add("n"+(i+1));
}
for (int i = 0; i < dtColumnsNum; i++)
{
ArrayList a = new ArrayList(dtRowsNum+1);
for (int j = 0; j <= dtRowsNum; j++)
{
if (j == 0)
{
a.Add(dt.Columns[i].ColumnName.ToString());
}
else
{
a.Add(dt.Rows[j-1][i].ToString());
}
}
dtRes.Rows.Add(a.ToArray());
}
return dtRes;
}
测试调用:
protected void Page_Load(object sender, EventArgs e)
{
string fileNameString = this.MapPath(".");
fileNameString += "//data//chartdata.mdb";
string myConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileNameString;
string mySelectQuery = "SELECT Name,Sales FROM REPS";
OleDbConnection myConnection = new OleDbConnection(myConnectionString);
myConnection.Open();
OleDbDataAdapter da = new OleDbDataAdapter(mySelectQuery, myConnection);
DataSet ds = new DataSet();
da.Fill(ds);
DataTable dt = ds.Tables[0];
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);
GridView1.DataSource = dt;
GridView1.DataBind();
GridView2.DataSource = RevertDataTable(dt);
GridView2.DataBind();
myConnection.Close();
}
最终结果:
vb.net写法:
方法一:
Private Function ConvertRCDataTable(ByVal dt As DataTable) As DataTable
Try
Dim rowCount As Integer = dt.Rows.Count + 1
Dim columnsCount As Integer = dt.Columns.Count
Dim newDt As DataTable = New DataTable()
Dim dc As DataColumn = New DataColumn()
For rowi As Integer = 0 To rowCount - 1
If rowi = 0 Then
If dt.Columns(0).ToString() = "日期" Then
dt.Columns("日期").ColumnName = "数量"
'dt.Columns("Sales").ColumnName = "价格"
End If
newDt.Columns.Add(dt.Columns(0).ToString())
Else
newDt.Columns.Add(dt.Rows(rowi - 1)(0).ToString())
End If
Next
For columnsi As Integer = 0 To columnsCount - 1
Dim dr As DataRow = newDt.NewRow()
For rowj As Integer = 0 To rowCount - 1
If rowj = 0 Then
dr(rowj) = dt.Columns(columnsi).ToString()
Else
dr(rowj) = dt.Rows(rowj - 1)(columnsi).ToString()
End If
Next
If columnsi <> 0 Then newDt.Rows.Add(dr)
Next
Return newDt
Catch ex As Exception
Throw New Exception(ex.ToString())
End Try
End Function
方法二:
Public Function RevertDataTable(ByVal dt As DataTable) As DataTable
Dim dtColumnsNum As Integer = dt.Columns.Count
Dim dtRowsNum As Integer = dt.Rows.Count
Dim dtRes As DataTable = New DataTable("ndt")
For i As Integer = 0 To dtRowsNum
'If dt.Columns(0).ToString() = "Name" OrElse dt.Columns(0).ToString() = "Sales" Then
' dt.Columns("Name").ColumnName = "月份"
' dt.Columns("Sales").ColumnName = "价格"
'End If
If dt.Columns(0).ToString() = "日期" Then
dt.Columns("日期").ColumnName = "数量"
'dt.Columns("Sales").ColumnName = "价格"
End If
dtRes.Columns.Add("n" & (i + 1))
Next
For i As Integer = 0 To dtColumnsNum - 1
Dim a As ArrayList = New ArrayList(dtRowsNum + 1)
For j As Integer = 0 To dtRowsNum
If j = 0 Then
a.Add(dt.Columns(i).ColumnName.ToString())
Else
a.Add(dt.Rows(j - 1)(i).ToString())
End If
Next
dtRes.Rows.Add(a.ToArray())
Next
Return dtRes
End Function