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;
    }


}