Hi ashaharka,
Refer below sample.
You need to write formula for what you want like if you want addition you need to write A2+B2 and if you want subtraction so write A2-B2 like you have write formula.
HTML
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" OnRowDataBound="GridView1_RowDataBound">
<Columns>
<asp:TemplateField HeaderText="Number1">
<ItemTemplate>
<asp:TextBox runat="server" ID="txtNumber1" Text='<%#Eval("Number1") %>' />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Number2">
<ItemTemplate>
<asp:TextBox runat="server" ID="txtNumber2" Text='<%#Eval("Number2") %>' />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Total">
<ItemTemplate>
<asp:Label ID="lblTotal" runat="server" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<br />
<asp:Button Text="Export" OnClick="ExportExcel" runat="server" />
Namespaces
C#
using System.IO;
using System.Data;
using System.Drawing;
VB.Net
Imports System.IO
Imports System.Data
Imports System.Drawing
Code
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
BindGrid();
}
}
private void BindGrid()
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[] { new DataColumn("Number1", typeof(int)),
new DataColumn("Number2", typeof(int)) });
dt.Rows.Add(1, 3);
dt.Rows.Add(2, 4);
dt.Rows.Add(3, 5);
dt.Rows.Add(4, 6);
GridView1.DataSource = dt;
GridView1.DataBind();
}
protected void ExportExcel(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
using (StringWriter sw = new StringWriter())
{
HtmlTextWriter hw = new HtmlTextWriter(sw);
//To Export all pages
GridView1.AllowPaging = false;
this.BindGrid();
GridView1.HeaderRow.BackColor = Color.White;
foreach (TableCell cell in GridView1.HeaderRow.Cells)
{
cell.BackColor = GridView1.HeaderStyle.BackColor;
}
foreach (GridViewRow row in GridView1.Rows)
{
(row.FindControl("lblTotal") as Label).Text = "=A" + (row.RowIndex + 2) + "*B" + (row.RowIndex + 2);
row.BackColor = Color.White;
foreach (TableCell cell in row.Cells)
{
if (row.RowIndex % 2 == 0)
{
cell.BackColor = GridView1.AlternatingRowStyle.BackColor;
}
else
{
cell.BackColor = GridView1.RowStyle.BackColor;
}
cell.CssClass = "textmode";
List<Control> controls = new List<Control>();
//Add controls to be removed to Generic List
foreach (Control control in cell.Controls)
{
controls.Add(control);
}
//Loop through the controls to be removed and replace then with Literal
foreach (Control control in controls)
{
switch (control.GetType().Name)
{
case "HyperLink":
cell.Controls.Add(new Literal { Text = (control as HyperLink).Text });
break;
case "TextBox":
cell.Controls.Add(new Literal { Text = (control as TextBox).Text });
break;
case "LinkButton":
cell.Controls.Add(new Literal { Text = (control as LinkButton).Text });
break;
case "CheckBox":
cell.Controls.Add(new Literal { Text = (control as CheckBox).Text });
break;
case "RadioButton":
cell.Controls.Add(new Literal { Text = (control as RadioButton).Text });
break;
case "Label":
cell.Controls.Add(new Literal { Text = (control as Label).Text });
break;
}
cell.Controls.Remove(control);
}
}
}
GridView1.RenderControl(hw);
//style to format numbers to string
string style = @"<style> .textmode { } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
}
public override void VerifyRenderingInServerForm(Control control)
{
/* Verifies that the control is rendered */
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
(e.Row.FindControl("lblTotal") as Label).Text = ((Convert.ToInt16((e.Row.FindControl("txtNumber1") as TextBox).Text) * (Convert.ToInt16((e.Row.FindControl("txtNumber2") as TextBox).Text)))).ToString();
}
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
BindGrid()
End If
End Sub
Private Sub BindGrid()
Dim dt As DataTable = New DataTable()
dt.Columns.AddRange(New DataColumn() {New DataColumn("Number1", GetType(Integer)), New DataColumn("Number2", GetType(Integer))})
dt.Rows.Add(1, 3)
dt.Rows.Add(2, 4)
dt.Rows.Add(3, 5)
dt.Rows.Add(4, 6)
GridView1.DataSource = dt
GridView1.DataBind()
End Sub
Protected Sub ExportExcel(ByVal sender As Object, ByVal e As EventArgs)
Response.Clear()
Response.Buffer = True
Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls")
Response.Charset = ""
Response.ContentType = "application/vnd.ms-excel"
Using sw As StringWriter = New StringWriter()
Dim hw As HtmlTextWriter = New HtmlTextWriter(sw)
GridView1.AllowPaging = False
Me.BindGrid()
GridView1.HeaderRow.BackColor = Color.White
For Each cell As TableCell In GridView1.HeaderRow.Cells
cell.BackColor = GridView1.HeaderStyle.BackColor
Next
For Each row As GridViewRow In GridView1.Rows
TryCast(row.FindControl("lblTotal"), Label).Text = "=A" & (row.RowIndex + 2) & "*B" + (row.RowIndex + 2)
row.BackColor = Color.White
For Each cell As TableCell In row.Cells
If row.RowIndex Mod 2 = 0 Then
cell.BackColor = GridView1.AlternatingRowStyle.BackColor
Else
cell.BackColor = GridView1.RowStyle.BackColor
End If
cell.CssClass = "textmode"
Dim controls As List(Of Control) = New List(Of Control)()
For Each control As Control In cell.Controls
controls.Add(control)
Next
For Each control As Control In controls
Select Case control.[GetType]().Name
Case "HyperLink"
cell.Controls.Add(New Literal With {
.Text = (TryCast(control, HyperLink)).Text
})
Case "TextBox"
cell.Controls.Add(New Literal With {
.Text = (TryCast(control, TextBox)).Text
})
Case "LinkButton"
cell.Controls.Add(New Literal With {
.Text = (TryCast(control, LinkButton)).Text
})
Case "CheckBox"
cell.Controls.Add(New Literal With {
.Text = (TryCast(control, CheckBox)).Text
})
Case "RadioButton"
cell.Controls.Add(New Literal With {
.Text = (TryCast(control, RadioButton)).Text
})
Case "Label"
cell.Controls.Add(New Literal With {
.Text = (TryCast(control, Label)).Text
})
End Select
cell.Controls.Remove(control)
Next
Next
Next
GridView1.RenderControl(hw)
Dim style As String = "<style> .textmode { } </style>"
Response.Write(style)
Response.Output.Write(sw.ToString())
Response.Flush()
Response.End()
End Using
End Sub
Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
End Sub
Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
If e.Row.RowType = DataControlRowType.DataRow Then
TryCast(e.Row.FindControl("lblTotal"), Label).Text = ((Convert.ToInt16((TryCast(e.Row.FindControl("txtNumber1"), TextBox)).Text) * (Convert.ToInt16((TryCast(e.Row.FindControl("txtNumber2"), TextBox)).Text)))).ToString()
End If
End Sub
Screenshot
