How to apply Databar formatting on Gridview Like Excel conditional formatting options using asp.net c#.

Thursday, November 28, 2013 Sourav Mondal ASP.NET

Introduction

In this post I am explain explain how to apply Databar formatting on Gridview Like Excel conditional formatting options using asp.net c#.
In MS Excel Data bars is a conditional formats that create visual effects in our data. These conditional formats make it easier to compare the values of a range of cells at the same time, just as we do in a bar chart.
Here in this example, I have created a sales report shown data in a Gridview, where we can quickly see which months have the smallest sales, and which months have the largest using Databars like MS Excel.

Steps :

Step - 1 : Create New Project.

Go to File > New > Project > Select asp.net web forms application > Entry Application Name > Click OK.

Step-2: Add a Database.

Go to Solution Explorer > Right Click on App_Data folder > Add > New item > Select SQL Server Database Under Data > Enter Database name > Add.

Step-3: Create table for store/fetch data.

Open Database > Right Click on Table > Add New Table > Add Columns > Save > Enter table name > Ok.

In this example, I have used one tables as below

Step-4: Add Entity Data Model.

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select ADO.net Entity Data Model under data > Enter model name > Add.
A popup window will come (Entity Data Model Wizard) > Select Generate from database > Next >
Chose your data connection > select your database > next > Select tables > enter Model Namespace > Finish.

Step-5: Modify model for Store Percentage of data.

            namespace ASPGridDatabar
            {
                using System;
                using System.Collections.Generic;
    
                public partial class SalesData
                {
                    public int SalesDataID { get; set; }
                    public string MonthName { get; set; }
                    public decimal SalesAmount { get; set; }
                    public int Percentage { get; set; }
                }
            }

Step-6: Add a Webpage and Design for show data with Databars.

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select web form/ web form using master page under Web > Enter page name > Add.

HTML Code

     <h3>Sales Report Month wise with Databars Like Excel using ASP.NET and C#.</h3>
    <div style="padding:20px;">
        <br />
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" CellPadding="4">
            <Columns>
                <asp:BoundField HeaderText="Month" DataField="MonthName" />
                <asp:BoundField HeaderText="Sales Amount" DataField="SalesAmount" />
                <asp:TemplateField>
                    <ItemTemplate>
                        <div class="container">
                            <div class="graph">
                                <div class="databar" style='width:<%#Eval("Percentage")%>%;'>
                                    &nbsp;
                                </div>
                            </div>
                            <div class="datavalue">
                                <%#Eval("SalesAmount") %>
                            </div>
                        </div>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
    </div>

Css Code

             .container 
            {
                width:100px;
                height:20px;
                position:relative;
            }
            .graph, .datavalue {
                 width:100%;
                 height:100%;
                 top:0;
                 left:0;
                 position:absolute;/* for making 2 div overlap*/ 
                 text-align:right;
                 color:black;
                 font-weight:bold;
            }
            .graph {
                width:95%; /*for same looks as it is in excel*/
            }
 
            .datavalue {
                z-index:10;/*For make it over graph div*/
            }
            .databar {
               /*show databar style like excel*/
               background-image:url('/images/Databar.png');
               background-position:right center;
               background-repeat:repeat-y;
               height:96%;
               background-color:rgb(16,145,240);
            }

Step-7: Write code into page load event for show data.

Write below code into Page_Load event for show data from database.

            protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    PopulateSaleData();
                }
            }

And here is the functioin PopulateSalesData

            private void PopulateSaleData()
            {
                List<SalesData> allData = new List<SalesData>();
                using (MyDatabaseEntities dc = new MyDatabaseEntities())
                {
                    allData = dc.SalesDatas.ToList();
                }
                int max = Convert.ToInt32(allData.Max(a=>a.SalesAmount));
                foreach (var a in allData)
                {
                    decimal p = (100 * a.SalesAmount) / max;
                    a.Percentage = Convert.ToInt32(p);
                }
 
                GridView1.DataSource = allData;
                GridView1.DataBind();
            }