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.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")%>%;'> </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(); } }
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(); }