Introduction
I was looking to produce DataGrid
s 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 DataGrid
s.
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)
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
INSERT INTO #temp
SELECT 1 , CategoryName, 'SubTotal', sum( ProductSales)
from #temp
group by CategoryName
INSERT INTO #temp
SELECT 2 , 'XXXXX', 'Grand Total', sum( ProductSales)
from #temp
Where sorty = 0
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)
{
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)
{
string CurrentColumn = e.Item.Cells[0].Text;
if( ( e.Item.ItemType.ToString()!= "Header"))
{
if (CurrentColumn == LastColumn)
{
e.Item.Cells[0].Text = "";
e.Item.Cells[0].Style.Add("BORDER", "none");
}
else
{
LastColumn = CurrentColumn;
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.
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;
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.
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)
{
}
That's the end, hope you like it, if you have any questions or queries please ask.
History
V10.0 11th-March-2005