DataGridView – Stacked Header – Export to HTML/Excel

Done with implementing the stacked headers for DataGridView I was next asked to provide option to export data to Excel with same formatted headers.

I had to choose between .NET Office library or simple HTML table based approach to represent data. I chose the later because Excel reads the HTML table well and I did not want to use heavy Office libraries.

HTML Table is a flat structure which uses rowspan and colspan to achieve the grouping. Visualizing grouping for large number of columns and varied levels of grouping becomes difficult.

Let us look at a sample to understand what has to be done to render the headers in the same grouped format. Consider the sample below:

<table border="1">
    <tr>
        <td colspan="3">Parent</td>
    </tr>    
    <tr>
        <td rowspan="2" >Child1</td>        
        <td colspan="2">Child Parent</td>
    </tr>
    <tr>
        <td >Child3</td>
        <td >Child4</td>
    </tr>
</table>

It renders as the table shown below:

From the example, we can see:

1. We need to have one row for each level of header.

2. Use colspan to span parent headers over its children.

3. For varied nesting, ex. first column has one level while second column has two levels. In First column use rowspan equal to the levels in the second column.

With the gained understanding I decided to use the same recursive technique as in painting but with an additional Dictionary to hold the <tr> elements for each level of header. I had to use Dictionary because I could not close each row as I had to traverse the entire header tree to complete each row.

Source Code

I’ve explained the source code via comments.

public static class DataGridExporter
{
    //Extension method to export data grid content to table format
    public static void ExportToHtmlOrExcel(this DataGridView dataGridView,
        string filePath, Header header)
    {
        StringBuilder strTable = new StringBuilder();

        //Begin the table element
        strTable.Append("<table border=\"1\">");

        //Get number of levels which are valid, basically ones excluding empty string.
        //Empty string was used as a hack to avoid grouping of distant headers with same name.
        int noOfLevels = StackedHeaderGenerator.Instance.NoOfValidLevels(header);
        int temp;

        dtHeaderRows.Clear();

        //Generate the <td> tags for the headers
        GenerateHeader(dataGridView, header, 0, noOfLevels, out temp);

        //Sort the keys in the header as key represents the level.
        List<int> keys = dtHeaderRows.Keys.ToList();
        keys.Sort();
        foreach (int level in keys)
        {
            //Create a row for each level
            strTable.AppendFormat("<tr>{0}</tr>", dtHeaderRows[level]);
        }

        //Export the data
        foreach (DataGridViewRow objRow in dataGridView.Rows)
        {
            strTable.Append("<tr>");
            foreach (DataGridViewCell objColumn in objRow.Cells)
            {
                if (objColumn.Visible)
                {
                    strTable.Append(string.Format("<td align=\"center" + 
                       "\">{0}</td>", objColumn.Value));
                }
            }
            strTable.Append("</tr>");
        }
        strTable.Append("</table>");

        StreamWriter writer = new StreamWriter(filePath);
        writer.Write(strTable.ToString());
        writer.Close();
    }

    //Dictionary to hold headers for each level.
    private static Dictionary<int, StringBuilder> dtHeaderRows = 
              new Dictionary<int, StringBuilder>();

    //Generate the header row,column structure
    private static void GenerateHeader(DataGridView dataGridView, 
            Header header, int iLevel, int noOfLevels, out int childCount)
    {
        //If this header does not have a child, it is leaf node.
        if (0 == header.Children.Count)
        {
            //Width is zero if the header is not visible.
            if (header.Width == 0)
            {
                childCount = 0;
                return;
            }

            //Used in arriving at the column span of the parent.
            childCount = 1;
            //Check if this level has been added into the header, if exists append the <td> to the same element.
            StringBuilder temp = dtHeaderRows.ContainsKey(iLevel) ? dtHeaderRows[iLevel] : new StringBuilder();
            temp.AppendFormat("<td rowspan=\"{0}\"  align=\"center\">{1}</td>", noOfLevels - iLevel, header.Name);
            dtHeaderRows[iLevel] = temp;
        }
        else
        {
            int tempColumns = 0, count = 0;
            //Generate <td> for each child.
            foreach (Header child in header.Children)
            {
                GenerateHeader(dataGridView, child, header.Name == "" ? iLevel : iLevel + 1, noOfLevels, out tempColumns);
                count += tempColumns;
            }
            //Total number of columns in this header. Used in colspan.
            childCount = count;
            if (header.Width != 0 && header.Name != "")
            {
                StringBuilder temp = dtHeaderRows.ContainsKey(iLevel) ? dtHeaderRows[iLevel] : new StringBuilder();
                temp.AppendFormat("<td colspan=\"{0}\"  align=\"center\">{1}</td>", childCount, header.Name);
                dtHeaderRows[iLevel] = temp;
            }
        }
    }
}

You can find the complete StackedHeader component at DataGridView-Stacked Header.