Advanced DataGrid Formating with ItemDataBound

Introduction

I was looking to produce DataGrids that had the same functionality as printed reports and that looked like some very expensive reporting management systems I was tasked with replacing, with .NET. The end product had to draw the users' attention to the data they wanted, not fluff the user with extra fields and details. This article helps you get started, in using a few simple tricks to spice up the look and feel of your DataGrids.

I'll show you how to:

• Create groupings for columns
• Highlight sub totals and grand totals
• Highlight single cells based on data values

SQL Data and Database

I used Northwind and SQL Server DB to produce the data for this article with a sub total for each product category and a grand total at the end. The code will work for any data source. I have included the SQL stored procedure for those who are interested in recreating exactly what's shown.

I tend to do nearly all my business logic on the database side and use the DataGrid for display purposes, so all sorting and totals are done here.

CREATE PROCEDURE usp_sales_by_cate AS

create table #temp ( Sorty int, CategoryName varchar(50), 
                  ProductName varchar(50), ProductSales real)

-- Get Base Sales
INSERT INTO #temp
SELECT 0, dbo.Categories.CategoryName, dbo.Products.ProductName, 
SUM(dbo.[Order Details Extended].ExtendedPrice) 
AS ProductSales
FROM dbo.Categories INNER JOIN
dbo.Products INNER JOIN
dbo.Orders INNER JOIN
dbo.[Order Details Extended] ON dbo.Orders.OrderID = 
             dbo.[Order Details Extended].OrderID ON 
dbo.Products.ProductID = 
  dbo.[Order Details Extended].ProductID ON dbo.Categories.CategoryID = 
                                   dbo.Products.CategoryID
WHERE (dbo.Orders.OrderDate BETWEEN '19970101' AND '19971231')
GROUP BY dbo.Categories.CategoryName, dbo.Products.ProductName
ORDER BY dbo.Categories.CategoryName

-- Build SubTotal
INSERT INTO #temp
SELECT 1 , CategoryName, 'SubTotal', sum( ProductSales)
from #temp

group by CategoryName
-- Build Grand Total
INSERT INTO #temp
SELECT 2 , 'XXXXX', 'Grand Total', sum( ProductSales)
from #temp  
Where sorty = 0  

-- Display Values
SELECT CategoryName, ProductName, ProductSales from #temp
order by CategoryName, Sorty

Creating Groupings

What I don't want is that every category be displayed on every first column, I only want the category to be displayed when it changes. This gives a nice and clean look to the DataGrid, allowing users to find items easily and quickly.

First, I create a public variable that will work across the whole page, and more importantly, remember something each time the <Itemdatabound> is run for each row of the DataGrid. On Page Load, it is also preset (I've had a few strange issues not doing this).

public class WebForm1 : System.Web.UI.Page
{
  public string LastColumn;
..
..

private void Page_Load(object sender, System.EventArgs e)
{
// Set LastColumn to blank. 
if (!IsPostBack) LastColumn = "";

Then I use the <LastColumn> variable to track the changes in the data being written to the DataGrid. If the cell value for cell[0] has not changed, the text of the cell is deleted and the border of the cell is removed:

e.Item.Cells[0].Style.Add("BORDER", "none").

To ignore the DataGrid header:

if( ( e.Item.ItemType.ToString()!= "Header"))
       {.....

This can also be used for items, alternative items and footers on a DataGrid to specialize your code.

Below is the full code to group the first column on the DataGrid.

private void DataGrid1_ItemDataBound(object sender, 
       System.Web.UI.WebControls.DataGridItemEventArgs e)
{
         //Get the current column 0 text
            string CurrentColumn = e.Item.Cells[0].Text;

     // Skip Headers 
     if( ( e.Item.ItemType.ToString()!= "Header"))
       {
            // Has there been a change 
           if (CurrentColumn == LastColumn)
             {
               // No Change in Column 0 
               //blank and remove boarder
               e.Item.Cells[0].Text = "";
               e.Item.Cells[0].Style.Add("BORDER", "none"); 
             }
           else
               {
                 // this is the first of the series
                 // set LastColumn to current colums
                 LastColumn = CurrentColumn;
                // Add a little back colour to the Cell[0]
                e.Item.Cells[0].BackColor = 
                              System.Drawing.Color.WhiteSmoke;
               }
}

Highlighting Subtotals and Grand Totals

This is fairly simple, it tests if cell text is "Sub Total" / "Grand Total", then sets the font, bold, colors etc. to what you want.

// Check to see if its a SubTotal
string MyCol2 = e.Item.Cells[1].Text;

if (MyCol2 == "SubTotal")
  {
    e.Item.Font.Bold = true;
    e.Item.BackColor = Color.DimGray;
    e.Item.ForeColor = Color.White;

   // blank out Column 0 so its approves 
    e.Item.Cells[0].Text = "";
    e.Item.Cells[0].Style.Add("BORDER", "none"); 
    e.Item.Cells[0].BackColor = Color.Transparent;
  }

if (MyCol2 == "Grand Total")
   {
     e.Item.Font.Bold = true;
     e.Item.BackColor = Color.Red;
     e.Item.ForeColor = Color.White;
     e.Item.Cells[0].Style.Add("BORDER", "none"); 
     e.Item.Cells[0].BackColor = Color.Transparent;
}

Highlighting Single Cells

I use this all the time to show negative values in red (the way accountants like them). I find that parsing string to a number can produce some weird errors so I generally wrap it in a try, catch to ignore any errors that may crop up.

// Make some items standout if below 5000 in sales
string MyStr = e.Item.Cells[2].Text;

try
{
    double MyValue = double.Parse(MyStr);
      
    if (MyValue < 5000 )
    {
        e.Item.Cells[2].ForeColor = Color.Red;
        e.Item.Cells[2].Font.Bold = true;
    }
}
catch(Exception)
{
  // its not a valid number
}

That's the end, hope you like it, if you have any questions or queries please ask.

History

V10.0 11^th-March-2005