Deepak's Blog

Month: October, 2012

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("&lt;table border=\"1\"&gt;");

            //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 &lt;td&gt; tags for the headers
            GenerateHeader(dataGridView, header, 0, noOfLevels, out temp);

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

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

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

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

        //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 &lt;td&gt; to the same element.
                StringBuilder temp = dtHeaderRows.ContainsKey(iLevel) ? dtHeaderRows[iLevel] : new StringBuilder();
                temp.AppendFormat("&lt;td rowspan=\"{0}\"  align=\"center\"&gt;{1}&lt;/td&gt;", noOfLevels - iLevel, header.Name);
                dtHeaderRows[iLevel] = temp;
            }
            else
            {
                                int tempColumns = 0, count = 0;
                                //Generate &lt;td&gt; 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 &amp;&amp; header.Name != "")
                {
                    StringBuilder temp = dtHeaderRows.ContainsKey(iLevel) ? dtHeaderRows[iLevel] : new StringBuilder();
                    temp.AppendFormat("&lt;td colspan=\"{0}\"  align=\"center\"&gt;{1}&lt;/td&gt;", childCount, header.Name);
                    dtHeaderRows[iLevel] = temp;
                }
            }
        }
    }

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

Advertisements

DataGridView – Stacked Header

DataGridView forms an integral part of any application and with specific needs of the applications, harnessing power of DataGridView is essential. I faced a similar scenario in a project where we had a DataGridView having more than hundred columns generated dynamically. The columns are generated based on the hierarchy of the inputs (more than twelve different cases each having its own input hierarchy) with the Columns header text depicting the hierarchy ex. Component End 1 Input1, Component End 1 Input 2, Component End 2 Input 1, Component End 2 Input 2.

Having redundant text in column header is not user friendly requiring the user to read entire text to know what the input is for. We decided to have stacked headers where we group the inputs based on the hierarchy.

As we know the default Winforms DataGridView does not support Stacked Headers and going for a 3rd party Grid just for the Stacked Header was not of much value so I had to do this on my own.

The solution is divided into four steps:

  1. Since I already had the column header text generated based on the hierarchy I decided to use header text for grouping. For this I changed the header text to use ‘.’ to define hierarchy. ex: Component.End 1.Input1, Component.End 1.Input 2, Component.End 2.Input 1, Component.End 2.Input 2
  2. Generate a simple tree representing hierarchy of the columns.
  3. Measure width required by each group (considering columns width, visibility).
  4. Render the headers.

As a result of this exercise, developer can quickly convert the column headers to stacked header by changing the header text and using this one line of code to draw stacked headers.

StackedHeaderDecorator objRenderer = new StackedHeaderDecorator(objDataGrid);

This one line takes care of step 2, 3 and 4 of the DataGridView while leaving Step 1 to the user of this solution.

Code

The component consists of three classes and an interface.

Header

Represents a header and its children. As a whole it forms the representation of the headers as a tree which is rendered by StackedHeaderDecorator.

Properties

  • Children: Holds the children rendered under this header
  • Name: Name of the header, used by the renderer as the header text to be drawn.
  • X and Y: Left, Top location of the start of the header.
  • Width and Height: Size of the region taken by this header. This is set dynamically when the measuring of the header is done.
  • ColumnId: If this is a lowest header it is the id of the column it represents else it is the id of the first visible column in the Header in Children property.

Methods

AcceptRenderer

Accepts the renderer which renders this header. It first paints the children then self.

        public void AcceptRenderer(StackedHeaderDecorator objRenderer)
{
foreach (Header objChild in Children)
{
objChild.AcceptRenderer(objRenderer);
}
if (-1 != ColumnId && !string.IsNullOrEmpty(Name.Trim()))
{
objRenderer.Render(this);
}
}
Measure

Calculates the region required by the Header including its Children.

public void Measure(DataGridView objGrid, int iY, int iHeight)
{
Width = 0;
if (Children.Count > 0)
{
int tempY = string.IsNullOrEmpty(Name.Trim()) ? iY : iY + iHeight;
bool columnWidthSet = false;
foreach (Header child in Children)
{
child.Measure(objGrid, tempY, iHeight);
Width += child.Width;
if (!columnWidthSet && Width > 0)
{
ColumnId = child.ColumnId;
columnWidthSet = true;
}
}
}
else if (-1 != ColumnId && objGrid.Columns[ColumnId].Visible)
{
Width = objGrid.Columns[ColumnId].Width;
}
Y = iY;
if (Children.Count == 0)
{
Height = objGrid.ColumnHeadersHeight - iY;
}
else
{
Height = iHeight;
}
}

StackedHeaderDecorator

Decorates the DataGridView hooking into it several events which paint/refresh the header of the DataGridView.  It also enables DoubleBuffering on the DataGrid.

It uses an instance of implementation of IStackedHeaderGenerator to generate the headers. By default it uses the StackedHeaderGenerator implementation of IStackedHeaderGenerator which uses the HeaderText to generate the Header tree. You can pass your implementation of the Generator via the overloaded constructor.

These are the events handlers hooked to the DataGridView

        objDataGrid.Scroll += objDataGrid_Scroll;
objDataGrid.Paint += objDataGrid_Paint;
objDataGrid.ColumnRemoved += objDataGrid_ColumnRemoved;
objDataGrid.ColumnAdded += objDataGrid_ColumnAdded;
objDataGrid.ColumnWidthChanged += objDataGrid_ColumnWidthChanged;

All events other than PaintEvent just invalidate the DataGridView regions, so we will look into the PaintEvent Handler, RenderColumnHeaders and Render which do the heavy lifting.

PaintEvent Handler

Calculates the number of levels of stacking, sets the height of the ColumnHeader and calls RenderColumnHeaders.

       void objDataGrid_Paint(object sender, PaintEventArgs e)
{
iNoOfLevels = NoOfLevels(objHeaderTree);
objGraphics = e.Graphics;
objDataGrid.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.DisableResizing;
objDataGrid.ColumnHeadersHeight = iNoOfLevels * 20;
if (null != objHeaderTree)
{
RenderColumnHeaders();
}
}
RenderColumnHeaders

Fills the background rectangle of the column header then loops through each child measuring and rendering.

        private void RenderColumnHeaders()
        {
            objGraphics.FillRectangle(new SolidBrush(objDataGrid.ColumnHeadersDefaultCellStyle.BackColor),
                                      new Rectangle(objDataGrid.DisplayRectangle.X, objDataGrid.DisplayRectangle.Y,
                                                    objDataGrid.DisplayRectangle.Width, objDataGrid.ColumnHeadersHeight));

            foreach (Header objChild in objHeaderTree.Children)
            {
                objChild.Measure(objDataGrid, 0, objDataGrid.ColumnHeadersHeight/iNoOfLevels);
                objChild.AcceptRenderer(this);
            }
        }
RenderColumnHeaders

Renders the header, it checks if it is a leaf header or parent header. It uses the Clip feature of GDI+ to correctly draw the clipped header when user scrolls the DataGridView.

  public void Render(Header objHeader)
{
if (objHeader.Children.Count == 0)
{
Rectangle r1 = objDataGrid.GetColumnDisplayRectangle(objHeader.ColumnId, true);
if (r1.Width == 0)
{
return;
}
r1.Y = objHeader.Y;
r1.Width += 1;
r1.X -= 1;
r1.Height = objHeader.Height;
objGraphics.SetClip(r1);

if (r1.X + objDataGrid.Columns[objHeader.ColumnId].Width < objDataGrid.DisplayRectangle.Width)
{
r1.X -= (objDataGrid.Columns[objHeader.ColumnId].Width - r1.Width);
}
r1.X -= 1;
r1.Width = objDataGrid.Columns[objHeader.ColumnId].Width;
objGraphics.DrawRectangle(Pens.Gray, r1);
objGraphics.DrawString(objHeader.Name,
objDataGrid.ColumnHeadersDefaultCellStyle.Font,
new SolidBrush(objDataGrid.ColumnHeadersDefaultCellStyle.ForeColor),
r1,
objFormat);
objGraphics.ResetClip();
}
else
{
int x = objDataGrid.RowHeadersWidth;
for (int i = 0; i < objHeader.Children[0].ColumnId; ++i)
{
if (objDataGrid.Columns[i].Visible)
{
x += objDataGrid.Columns[i].Width;
}
}
if (x > (objDataGrid.HorizontalScrollingOffset + objDataGrid.DisplayRectangle.Width - 5))
{
return;
}

Rectangle r1 = objDataGrid.GetCellDisplayRectangle(objHeader.ColumnId, -1, true);
r1.Y = objHeader.Y;
r1.Height = objHeader.Height;
r1.Width = objHeader.Width  + 1;
if (r1.X < objDataGrid.RowHeadersWidth)
{
r1.X = objDataGrid.RowHeadersWidth;
}
r1.X -= 1;
objGraphics.SetClip(r1);
r1.X = x - objDataGrid.HorizontalScrollingOffset;
r1.Width -= 1;
objGraphics.DrawRectangle(Pens.Gray, r1);
r1.X -= 1;
objGraphics.DrawString(objHeader.Name, objDataGrid.ColumnHeadersDefaultCellStyle.Font,
new SolidBrush(objDataGrid.ColumnHeadersDefaultCellStyle.ForeColor),
r1, objFormat);
objGraphics.ResetClip();
}
}

There is one bug and several improvements I see in this code and I will work on them and update the articles.

Export to HTML Table

I’ve written a simple class which exports the grid data along with the grouped headers to html table which you can save into an Excel file. You can find the details in this article.

Bug

If you plan to use the default StackedHeaderGenerator for header generation then you have to know there is a bug in there which crops up if you have two top level headers with same name but do not represent consecutive columns then the header generated is incorrect. Since it uses ‘.’ separated header text to generate headers, you can add additional ‘.’s to get the correct header tree.

Download SourceCode. Please change the extension from .doc to .zip as WordPress does not allow exe’s or dll’s in uploads.

Note: The source code posted is not completely tested and polished, please use it diligently.