This project is a simple Visual Studio 2015 (Framework 3.5 ) project written in C# with a web form and GridView to show management of GridView programmatically. It shows population of cells and aggregation of data. It also shows how the data can be exported to Excel worksheet using comma separated values (CSV).
Introduction
This article is for developers / programmers that are very familiar with Visual Studio Web Applications. The purpose of this simple project is to demonstrate how to manage GridView
/Datagrid
in Visual Studio web form programmatically. I said GridView
or Datagrid
control because the two controls have a lot of similarities in Visual Studio.
At times, while in a hurry to quickly deliver on a project, I just select any of the two controls and move on because programming them to me is very similar, but others may not see them that way. But for the purpose of this project, I used GridView
to demonstrate my idea.
This project presents basically three (3) things that usually give developers challenges and consume their valuable time.
- Populating and Formatting
Gridview
cells using Datatable - Aggregating column data (
Sum
, Count
, Average
, etc.) - Exporting the Data to physical disk.
Note that we are only looking at pure programming without formatting and setting fields/properties in Visual Studio design mode. We are aware it is possible to set some properties at design mode, but at times, you do not know the complete structure of your datatable and you want to do this programmatically. So also, you want to export the data to Excel, Word or PDF.
This project is not specific about your database connection that will generate your datatable. As long as you have a datatable that can be the datasource of your GridView
, you are good to go.
For the purpose of this project, because we do not want to establish any database connection, I just created an arbitrary datatable and manually populated it for my demo (salary stuff). You can change the datatable to your own datatable coming from your database connection.
Background
This project presents basically three things that usually give developers challenges and consume their valuable time using GridView
or Datagrid
programmatically.
We will address these three areas of challenge:-
- Populating and formatting
Gridview
cells - Aggregating column data (
Sum
, Count
, Average
, etc.). We will only deal with sum
. - Exporting the Data to physical disk. We will only look at CSV/Excel.
Using the Code
The entire code is hereby attached, it was written in C# and in 3.5 Framework, may also work in lower or higher frameworks.
In order to adapt this project to your situation, you must add reference to "Microsoft.VisualBasic" and add the following to the two classes in the web form codebehind and the other class for CSV.
using Microsoft.VisualBasic;
It is a very simple project with only one web form and a class.
The web form is the Start Page and the class is just to convert datatable to string using a stringbuilder object. The Web Form has a Button on it that exports the GridView
data to CSV/Excel.
The web form is called "ManageGridView
" and the only class is called CSV_Manager
.
When you run the app, it automatically loads data on the GridView
using the datatable
I manually created in the class to populate the grid.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Text;
using Microsoft.VisualBasic;
namespace ManageGridView
{
public partial class ManageGridView : System.Web.UI.Page
{
private void InitializeComponent()
{
Button1.Click -= new System.EventHandler(this.Button1_Click);
Button1.Click += new System.EventHandler(this.Button1_Click);
GridView1.RowDataBound -= new GridViewRowEventHandler
(this.GridView1_RowDataBound);
GridView1.RowDataBound += new GridViewRowEventHandler
(this.GridView1_RowDataBound);
}
private void Page_Init(System.Object sender, System.EventArgs e)
{
InitializeComponent();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid();
}
}
private void BindGrid(DataTable MyDt = null)
{
DataTable BindDt = new DataTable();
if (MyDt == null)
{
BindDt = CSV_Manager.ArbitraryDatatable();
}
else
{
BindDt = MyDt;
}
GridView1.DataSource = BindDt;
GridView1.DataBind();
using (BindDt)
{
double totalSalary = BindDt.AsEnumerable().Sum
(row => row.Field<double>("Salary"));
GridView1.FooterRow.Cells[2].Text = "Total ==>";
GridView1.FooterRow.Cells[3].Text = totalSalary.ToString("N2");
GridView1.FooterRow.Cells[3].HorizontalAlign = HorizontalAlign.Right;
}
}
protected void Button1_Click(object sender, EventArgs e)
{
StringBuilder sbAgr = new StringBuilder();
DataTable MyDt = CSV_Manager.ArbitraryDatatable();
BindGrid(MyDt);
if (MyDt != null)
{
string csvString = CSV_Manager.ExportDatatableToCSV(MyDt);
double totalSalary = MyDt.AsEnumerable().Sum(row =>
row.Field<double>("Salary"));
for (int i = 0; i < MyDt.Rows.Count; i++)
{
if (i == 2)
{
sbAgr.Append("Total ==>" + ',');
}
else if (i == 3)
{
sbAgr.Append(totalSalary.ToString() + ',');
}
else
{
sbAgr.Append(" " + ',');
}
}
sbAgr.Append("\r\n");
csvString = csvString + sbAgr.ToString();
if (csvString != "")
{
Response.AddHeader("Content-Disposition",
"attachment; filename=Saved_Datatable.csv");
Response.ContentType = "application/ms-excel";
Response.ContentEncoding =
System.Text.Encoding.GetEncoding("utf-8");
Response.Write(csvString);
Response.End();
}
}
}
protected void GridView1_RowDataBound(Object Sender, GridViewRowEventArgs E)
{
if (E.Row.RowType == DataControlRowType.DataRow)
{
double myText = Conversion.Val(E.Row.Cells[3].Text);
string DateText = Formatted_Date(E.Row.Cells[2].Text);
E.Row.Cells[3].HorizontalAlign = HorizontalAlign.Right;
E.Row.Cells[3].ForeColor = System.Drawing.Color.Green;
E.Row.Cells[2].Text = DateText;
E.Row.Cells[3].Text = myText.ToString("N2");
}
}
public static string Formatted_Date(string YourDate)
{
if (Information.IsDate(YourDate) == false)
{
return "";
}
DateTime moment = Convert.ToDateTime(YourDate);
string monthName = moment.ToString("MMM");
string theDay = moment.Day.ToString();
if (moment.Day < 10)
{
theDay = "0" + theDay;
}
return theDay + "-" + monthName + "-" + moment.Year;
}
public ManageGridView()
{
Load += Page_Load;
Init += Page_Init;
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Globalization;
using System.Web.Script.Serialization;
using System.IO;
using System.Net;
using System.Text;
using System.Runtime.Serialization;
using System.Xml;
using System.Xml.Linq;
using System.Web.UI.HtmlControls;
using Microsoft.VisualBasic;
namespace ManageGridView
{
public static class CSV_Manager
{
public static string ExportDatatableToCSV(DataTable GridViewDatatable)
{
string leftOne = "";
StringBuilder sbldr = new StringBuilder();
if (GridViewDatatable.Columns.Count != 0)
{
foreach (DataColumn col in GridViewDatatable.Columns)
{
string colName = col.ColumnName;
sbldr.Append(colName + ',');
}
sbldr.Append("\r\n");
foreach (DataRow row in GridViewDatatable.Rows)
{
foreach (DataColumn column in GridViewDatatable.Columns)
{
string colData = "" + row[column].ToString();
if (colData.Contains(','))
{
colData = String.Format("\"{0}\"", colData);
}
leftOne = Strings.Left(colData, 1);
if (column.DataType == typeof(string))
{
try
{
int result = int.Parse(leftOne);
if (result == 0 && leftOne != "-" && leftOne != ".")
{ colData = "'" + colData; }
}
catch
{ }
}
sbldr.Append(colData + ',');
}
sbldr.Append("\r\n");
}
}
return sbldr.ToString();
}
public static DataTable ArbitraryDatatable()
{
DataTable dt = new DataTable();
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Age", typeof(int));
dt.Columns.Add("Date_Employed", typeof(DateTime));
dt.Columns.Add("Salary", typeof(double));
DataRow dr1 = dt.NewRow();
dr1["Name"] = "Raphael";
dr1["Age"] = 48;
dr1["Date_Employed"] = "03-Jan-2019";
dr1["Salary"] = 78000;
dt.Rows.Add(dr1);
DataRow dr2 = dt.NewRow();
dr2["Name"] = "Mohammad";
dr2["Age"] = 24;
dr2["Date_Employed"] = "01-Aug-2018";
dr2["Salary"] = 35000;
dt.Rows.Add(dr2);
DataRow dr3 = dt.NewRow();
dr3["Name"] = "Nelson";
dr3["Age"] = 54;
dr3["Date_Employed"] = "01-Apr-2019";
dr3["Salary"] = 65000.78;
dt.Rows.Add(dr3);
DataRow dr4 = dt.NewRow();
dr4["Name"] = "Lydia";
dr4["Age"] = 36;
dr4["Date_Employed"] = "01-Jun-2019";
dr4["Salary"] = 37500;
dt.Rows.Add(dr4);
DataRow dr5 = dt.NewRow();
dr5["Name"] = "Desiree";
dr5["Age"] = 35;
dr5["Date_Employed"] = "01-Jun-2018";
dr5["Salary"] = 28000;
dt.Rows.Add(dr5);
return dt;
}
public static string Formatted_Date(string YourDate)
{
if (Information.IsDate(YourDate) == false)
{
return "";
}
DateTime moment = Convert.ToDateTime(YourDate);
string monthName = moment.ToString("MMM");
string theDay = moment.Day.ToString();
if (moment.Day < 10)
{
theDay = "0" + theDay;
}
return theDay + "-" + monthName + "-" + moment.Year;
}
}
}
Points of Interest
Exporting grid data to Excel or any other format does not mean you have to loop through the cells of the grid itself. A grid must have a datatable or other datasource which you can target to export the data.
In this case, I did not export the data from the grid directly, but I targeted the datatable (its datasource), which is easier for me to manipulate anytime. You may also want to adopt this strategy in future.
Challenges
I had a lot of challenges programming GridView
and Datagrid
especially programming them before. From my experience, a datagrid will break down while binding to datasource if a field a column is bound to does not exist in its datatable used as datasource. So I prefer not to bind at design time, but do everything at runtime programmatically.
History
- 17th April, 2022: Initial version