(转载)c#、vb.net实现DataTable中行row、列Columns互换

原文出处：

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