Sunday, February 3, 2008

Transpose DataGrid or GridView by transposing DataTable

New requirements poured in last week and one among them was to transpose a DataGrid by interchanging rows to columns and columns to rows. The usability engineer proposed that, by doing so, would enable him to utilize the recovered empty spaces on the screen to group data more efficiently. As you know, there is no straightforward way to flip a grid by setting a property or so. I googled to see if there were any solutions already  implemented by fellow .NET'ers to address this requirement and found an impressive article on Code Project. Well, I was looking for a much simpler solution and eventually decided to do it myself. It didn't take any longer than I spent googling. The idea is straight and simple, transpose the DataTable and bind it to the grid.

To demonstrate this approach, I'll first start by creating a sample DataTable with 3 columns and 5 rows using the code shown below. 

private DataTable GetSampleTable()
{
    DataTable dt = new DataTable();
    dt.Columns.Add(new DataColumn("First Name",typeof(string)));
    dt.Columns.Add(new DataColumn("Last Name",typeof(string)));
    dt.Columns.Add(new DataColumn("Age",typeof(Int32)));

    for (int i = 1; i < 6; i++)
    {
        DataRow dr = dt.NewRow();
        dr["First Name"] = "First Name " + i;
        dr["Last Name"] = "Last Name " + i;
        dr["Age"] = i*10;
        dt.Rows.Add(dr);
    }
    return dt;
} 
 

Original Grid

First Name Last Name Age
Firstname1 Lastname1 10
Firstname2 Lastname2 20
Firstname3 Lastname3 30
Firstname4 Lastname4 40
Firstname5 Lastname5 50

Interchange rows to columns and columns to rows by calling the GetTransposedTable() method as shown below. 

private DataTable GetTransposedTable(DataTable dt)
{
    DataTable newTable = new DataTable();
    newTable.Columns.Add(new DataColumn("0",typeof(string)));
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        DataRow newRow = newTable.NewRow();
        newRow[0] = dt.Columns[i].ColumnName;
        for (int j = 1; j <= dt.Rows.Count; j++)
        {
            if (newTable.Columns.Count < dt.Rows.Count + 1)
                newTable.Columns.Add(new DataColumn(j.ToString(),typeof(string)));
            newRow[j] = dt.Rows[j - 1][i];
        }
        newTable.Rows.Add(newRow);
    }
    return newTable;
} 
 

Bind the resulting DataTable to the ASP.NET GridView Control.

<asp:GridView ID="grdTransposed" runat="server">
 

Transposed Grid

0 1 2 3 4 5
First Name Firstname1 Firstname2 Firstname3 Firstname4 Firstname5
Last Name Lastname1 Lastname2 Lastname3 Lastname4 Lastname5
Age 10 20 30 40 50
 

 The column headers are numbered by default. You can hide them by setting ShowHeader="false" which would render a grid as shown below.

First Name Firstname1 Firstname2 Firstname3 Firstname4 Firstname5
Last Name Lastname1 Lastname2 Lastname3 Lastname4 Lastname5
Age 10 20 30 40 50
 

 Now, let's make the first column elements bold indicating that they're the new "headers" for the transposed grid. This could be tricky with the AutoGenerated columns as we don't have explicit control over them. By default, the HtmlEncode property on these columns are set to "True". This rules out an option of enclosing texts with HTML tags (<B></B>) when the columns are autogenerated.

newRow[0] = "<B>"+dt.Columns[i].ColumnName+"</B>";

This would render an ugly looking grid as shown below...

<B>First Name</B> Firstname1 Firstname2 Firstname3 Firstname4 Firstname5
<B>Last Name</B> Lastname1 Lastname2 Lastname3 Lastname4 Lastname5
<B>Age</B> 10 20 30 40 50
 

Moreover, the GridView column collection will not have autogenerated columns, that is, their count is 0. This rules out another option of looping through the columns and selectively setting the properties. However, by setting the AutoGenerateColumns property to False and dynamically creating bound columns, with HtmlEncode = "false", would give us more flexibility. The GenerateGridColumns() method that's shown below loops through each column in the transposed table, creates a BoundField and adds it to the GridView's DataControlFieldCollection.  

 

private void GenerateGridColumns(DataTable dt)
{
    grdTransposed.AutoGenerateColumns = false;

    for (int i = 0; i < dt.Columns.Count; i++)
    {
        BoundField field = new BoundField();
        field.DataField = dt.Columns[i].ColumnName;
        field.HtmlEncode = false;                
        grdTransposed.Columns.Add(field);        
    }
}
 

 I call the above method just before binding the table to the Grid.

            DataTable transposedTable = GetTransposedTable(dt);
            GenerateGridColumns(transposedTable);

            grdTransposed.DataSource = transposedTable;
            grdTransposed.DataBind();

 

The final Transposed grid rendered with "Headers" would look like this...

First Name Firstname1 Firstname2 Firstname3 Firstname4 Firstname5
Last Name Lastname1 Lastname2 Lastname3 Lastname4 Lastname5
Age 10 20 30 40 50
 

 Hope this helps!

9 comments:

akhilesh said...

very nice! every thing what i searching get all in systematic manner
thanks

Anonymous said...

great post thank you

Anonymous said...

I found this site using [url=http://google.com]google.com[/url] And i want to thank you for your work. You have done really very good site. Great work, great site! Thank you!

Sorry for offtopic

Andrew said...

Incredible. This simple piece of code will save me a lot of development time. Thanks!

Anonymous said...

could you please post sample of this project to download, or send me by mail?

K W said...

Wow - this looks like EXACTLY what I need! Thanks for posting it. Even if it doesn't work "as is", it has definitely given me the idea I was looking for. Appreciate it : )

Anonymous said...

Awesome. Well done. Definitely gives me a great starting point. I wonder if it will work as items are added and removed from the source DataGrid.

Anonymous said...

Superb code Ganesan.. handy to many of us...

Anonymous said...

Great one!!! Thanks for the code.

Just one thing, I used this example into a webpart for sharepoint in c# but the headers of the rows don´t look in bold.

What can i do? exist other method to do it?

Thanks again.