ASP.NET - Ideal Control to Display a Pivot Table

Question

I am trying to display a tabular set of data in a databound control, but I need to pivot the table such that the individual records are table columns rather than table rows. The end result is a table with a fixed number of columns and a variable number of rows each displaying single field for all of the records like this. Due to the fact that the <tr /> tags would have to be defined for each field, rather than each record, a repeater isn't a suitable way to go about this. What I am wondering is if there are any built in ASP.NET controls that can achieve what I want. I was eying the ListView control, however I am not certain if it is in fact capable of what I'm describing.

Effectively, assuming records like the following:

       Number Yardage Par  ...
(Hole)   1     300     4   ...
(Hole)   2     275     4   ...
(Hole)   3     390     5   ...
(Hole)  ...    ...    ...  ...

I need to display:

           1   2   3   ...
Yardage:  300 275 390  ...
    Par:   4   4   5   ...
    ...:  ... ... ...  ...

A viable alternative to fighting with <tr /> tags would of course be to use display: inline <divs> with some graceful CSS, but if I can preserve the <table> structure that would be ideal.

Thanks!

Answer 1

I've recently encountered the same issue and when looking for an answer found that most solutions worked around pivotting the source data.

It occurred to me that the problem is not with the source data but the way in which we wish to render it. Although Chris's answer above does look to alter the data at point of render I found for my needs that it wouldn't be flexible enough if I needed a templated grid view. It was then it occurred that perhaps a better solution to the problem would be capture a grid's table HTML markup and alter that - this would in effect mean that the solution could be applied to absolutely any control that renders table markup, and any template controls contained within it would continue to work.

Due to time pressures I have only implemented the solution with a grid view; I originally wanted to make a template server control, that if any control was placed inside it would check it's markup output and pivot any tables contained within it)

Anyway, here's the code that's needed to implement this solution for a grid view.

Custom Server Control code

[ToolboxData("<{0}:PivotGridView runat=server></{0}:PivotGridView>")]
public class PivotGridView : GridView
{
    bool _pivotGrid = true;

    [Browsable(true)]
    public bool PivotGrid
    {
        get 
        { 
            return _pivotGrid; 
        }
        set 
        { 
            _pivotGrid = value; 
            EnsureChildControls(); 
        }
    }

    protected override void RenderContents(HtmlTextWriter output)
    {
        if (_pivotGrid)
        {
            System.IO.TextWriter baseOutputTextWriter = new System.IO.StringWriter();
            HtmlTextWriter baseOutputHtmlWriter = new HtmlTextWriter(baseOutputTextWriter);

            base.RenderContents(baseOutputHtmlWriter);

            output.Write(HtmlParserService.PivotHtmlTableMarkup(baseOutputTextWriter.ToString()));
        }
        else
        {
            base.RenderContents(output);
        }
    }
}

HTML Parser Service code, separated out for easy implementation elsewhere.

//... using System.Text.RegularExpressions;

public static class HtmlParserService
{        
    /// <summary>
    /// Takes HTML markup locates any table definition held within it and returns that
    /// markup with the table HTML pivotted
    /// </summary>
    /// <param name="html"></param>
    /// <returns></returns>
    public static string PivotHtmlTableMarkup(string html)
    {
        html = ReplaceShorthandTableTags(html);

        int openingTableTagIndex;
        string openingTableTagText;
        int closingTableTagIndex;
        string tableContentText;

        tableContentText = GetTagContentText("table", html, out openingTableTagIndex, out openingTableTagText, out closingTableTagIndex);

        MatchCollection rows = GetTagMatches("tr", tableContentText);
        if (rows.Count > 0)
        {
            MatchCollection columns = GetTagMatches("(td|th)", rows[0].Value);

            StringBuilder pivottedTableMarkup = new StringBuilder();

            for (int i = 0; i < columns.Count; i++)
            {
                pivottedTableMarkup.Append("<tr>");
                foreach (Match row in rows)
                {
                    if (row.Value.Length > 0)
                    {
                        columns = GetTagMatches("(td|th)", row.Value);

                        if (columns.Count>i)
                        {
                            pivottedTableMarkup.Append(columns[i].Value);
                        }
                    }
                }
                pivottedTableMarkup.Append("</tr>");
            }

            string preTableText = "";
            if (openingTableTagIndex > 1)
            {
                preTableText = html.Substring(1, openingTableTagIndex);
            }

            string postTableText;
            postTableText = html.Substring(closingTableTagIndex, html.Length - closingTableTagIndex);

            string newHtmlWithPivottedTable;
            newHtmlWithPivottedTable = preTableText + openingTableTagText + pivottedTableMarkup.ToString() + postTableText;

            return newHtmlWithPivottedTable;
        }
        else
        {
            return html;
        }

    }

    /// <summary>
    /// Gets the content between the specified tag.
    /// </summary>
    /// <param name="tag">The tag excluding any markup (e.g. "table" not "<table>"</param>
    /// <param name="text">The xml text string to extract content from</param>
    /// <param name="openingTagIndex">Outputs the indexed position of the opening tag</param>
    /// <param name="openingTagText">Outputs the definition of the tag, e.g. it's attributes etc</param>
    /// <param name="closingTagIndex">Outputs the indexed position of the closing tag</param>
    /// <returns></returns>
    public static string GetTagContentText(string tag, string text, out int openingTagIndex, out string openingTagText, out int closingTagIndex)
    {
        string contentText;

        openingTagIndex = text.ToLower().IndexOf("<" + tag);
        openingTagText = text.Substring(openingTagIndex, text.IndexOf(">", openingTagIndex) - openingTagIndex+1);
        closingTagIndex = text.ToLower().LastIndexOf("</" + tag + ">");

        contentText = text.Substring(
            openingTagIndex + openingTagText.Length,
            closingTagIndex - (openingTagIndex + openingTagText.Length) );

        return contentText;
    }

    /// <summary>
    /// Returns a collection of matches containing the content of each matched tag
    /// </summary>
    /// <param name="tag">HTML tag to match.  Exclude opening and close angled braces.
    /// Multiple tags can be matched by specifying them in the following format (tag1|tag2),
    /// e.g. (td|th)</param>
    /// <param name="html"></param>
    /// <returns></returns>
    public static MatchCollection GetTagMatches(string tag, string html)
    {
        Regex regexp = new Regex(@"<" + tag + @"\b[^>]*>(.*?)</" + tag + @">", RegexOptions.IgnoreCase | RegexOptions.Singleline);
        return regexp.Matches(html);
    }

    /// <summary>
    /// Ensures any shorthand XML tags are full expressed, e.g.
    /// <td/> is converted to <td></td>
    /// </summary>
    /// <param name="value"></param>
    /// <returns></returns>
    private static string ReplaceShorthandTableTags(string value)
    {
        value=value.Replace("<tr/>", "<tr></tr>");
        value=value.Replace("<td/>", "<td></td>");
        value=value.Replace("<th/>", "<th></th>");

        return value;
    }


}

Answer 2

Your best bet will probably be to create your own server control.

I've done something similar to this by creating a custom server control that extended a GridView. I then pivoted the DataSource and added columns to the gridview dynamically on databind.

I used the GridView approach to keep the look and behavior similar to the rest of my site. Alternatively you could create a simpler custom control that created html for a table in the Render method.

Best of luck.

EDIT: Here is an example of how to make the server control based on a table (It wasn't that hard, but be warned I didn't test this... ):

public class PivotTable : Control
{
    protected Table pivotTable = new Table();
    private DataTable _datasource;
    [DesignerSerializationVisibility(DesignerSerializationVisibility.Content),
     Category("Data"), Browsable(true),
     Description("Gets and sets the DataSource for the Control.  Needs to be a DataTable")]
    public DataTable DataSource
    {
        get { return _datasource; }
        set
        {
            if (value.GetType() == typeof(DataTable))
            {
                throw new Exception("DataSource must be a DataTable.");
            }
            _datasource = value;
        }
    }

    public override ControlCollection Controls
    {
        get
        {
            EnsureChildControls();
            return base.Controls;
        }
    }

    protected override void CreateChildControls()
    {
        Table tbl = new Table();
        foreach (DataColumn dc in DataSource.Columns)
        {
            TableRow tr = new TableRow();
            TableHeaderCell header = new TableHeaderCell();
            header.Text = dc.ColumnName;
            tr.Controls.Add(header);

            foreach (DataRow dr in DataSource.Rows)
            {
                TableCell tc = new TableCell();
                tc.Text = dr[dc.ColumnName].ToString();
                tr.Controls.Add(tc);
            }

            tbl.Controls.Add(tr);
        }

        Controls.Add(tbl);
    }
}

Answer 3

You can use the Table component but you need to work hard enought to get what you want. The best solution for me is to use GridView.You can fill the pivoted result set to a datatable and create datatable columns and rows dynamically. When you get the desired format you can bind the datatable to the GridView.

I had done something similar before, it was way easier to implement rather then trying to create your own control.

Answer 4

3rd party crosstab controls are available. DevXpress ASPxPivotGrid comes to mind first.

And if you need the crosstabs purely for standalone non-interactive reports, then you can consider using separate reporting tools and embed the reports inside an ASP.NET report viewer control. SQL Server Reporting Services 2008 has a nice tablix region for tabular and matrix data. Telerik Reporting also has crosstabs.

But, if your needs are simple and well-defined, rolling out a HTML table control as suggested is probably the fastest solution.