Hi ucrhlyn,
Check the below code and modify according to your need.
HTML
<asp:GridView runat="server" ID="gvData" AutoGenerateColumns="false" OnDataBound="OnDataBound" OnRowCreated="OnRowCreated"
CssClass="table table-bordered table-striped table-depreciation">
<Columns>
<asp:BoundField DataField="year" HeaderText="year" ItemStyle-CssClass="notvisble" />
<asp:BoundField DataField="Month" HeaderText="Month" />
<asp:BoundField DataField="Depreciation" HeaderText="Depriciation Expenses" DataFormatString="{0:N2}" />
<asp:BoundField DataField="Accumulated" HeaderText="Accumulate Deprication at Month-End"
DataFormatString="{0:N2}" />
<asp:BoundField DataField="BookValue" HeaderText="Book Value at Month-End" DataFormatString="{0:N2}" />
</Columns>
</asp:GridView>
Namespaces
C#
using System.Data;
using System.Drawing;
VB.Net
Imports System.Data
Imports System.Drawing
Code
C#
protected void Page_Load(object sender, EventArgs e)
{
DateTime acquiredDate = new DateTime(2019, 9, 1);
DateTime startMonth = new DateTime(2019, 9, 1);
double depreciation = 100;
double slavege = 1.0;
int lifeMonth = 10;
DataTable dt = new DataTable();
dt.Columns.Add("year");
dt.Columns.Add("Month");
dt.Columns.Add("Depreciation", typeof(decimal));
dt.Columns.Add("Accumulated", typeof(decimal));
dt.Columns.Add("BookValue", typeof(decimal));
int i = 0;
double depreciationExpense = (depreciation - slavege) / lifeMonth;
double bookValue = depreciation - depreciationExpense;
double newDepreciationExpense = 0;
while (startMonth < acquiredDate.AddMonths(lifeMonth))
{
DataRow dr = dt.NewRow();
dr["year"] = startMonth.ToString("yyyy");
dr["Month"] = startMonth.ToString("MMM yyyy");
dr["Depreciation"] = depreciationExpense;
if (i == 0)
{
dr["Accumulated"] = Math.Round(depreciationExpense, 2);
dr["BookValue"] = Math.Round(bookValue, 2);
newDepreciationExpense = depreciationExpense;
}
else
{
newDepreciationExpense = newDepreciationExpense + depreciationExpense;
bookValue = bookValue - depreciationExpense;
dr["Accumulated"] = Math.Round(newDepreciationExpense, 2);
dr["BookValue"] = Math.Round(bookValue, 2);
}
dt.Rows.Add(dr);
startMonth = startMonth.AddMonths(1);
i += 1;
}
this.gvData.DataSource = dt;
this.gvData.DataBind();
}
private int currentId = 0;
private decimal subTotal = 0;
private decimal total = 0;
private int subTotalRowIndex = 0;
protected void OnRowCreated(object sender, GridViewRowEventArgs e)
{
subTotal = 0;
if (e.Row.RowType == DataControlRowType.DataRow)
{
DataTable dt = (e.Row.DataItem as DataRowView).DataView.Table;
int orderId = Convert.ToInt32(dt.Rows[e.Row.RowIndex]["year"]);
total += Convert.ToDecimal(dt.Rows[e.Row.RowIndex]["Depreciation"]);
if (orderId != currentId)
{
if (e.Row.RowIndex > 0)
{
for (int i = subTotalRowIndex; i <= e.Row.RowIndex - 1; i++)
{
subTotal += Convert.ToDecimal(gvData.Rows[i].Cells[2].Text);
}
string accumulated = gvData.Rows[e.Row.RowIndex - 1].Cells[3].Text;
string book = gvData.Rows[e.Row.RowIndex - 1].Cells[4].Text;
this.AddTotalRow("Sub Total", subTotal.ToString("N1"), accumulated, book);
subTotalRowIndex = e.Row.RowIndex;
}
currentId = orderId;
}
}
}
protected void OnDataBound(object sender, EventArgs e)
{
for (int i = subTotalRowIndex; i <= gvData.Rows.Count - 1; i++)
{
subTotal += Convert.ToDecimal(gvData.Rows[i].Cells[2].Text);
}
string accumulated = gvData.Rows[gvData.Rows.Count - 1].Cells[3].Text;
string book = gvData.Rows[gvData.Rows.Count - 1].Cells[4].Text;
this.AddTotalRow("Sub Total", subTotal.ToString("N1"), accumulated, book);
}
private void AddTotalRow(string labelText, string value, string accumulated, string bookValue)
{
GridViewRow row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Normal);
row.BackColor = ColorTranslator.FromHtml("#F9F9F9");
row.Cells.AddRange(new TableCell[5] {
new TableCell(),
new TableCell() { Text = labelText, HorizontalAlign = HorizontalAlign.Left },
new TableCell() { Text = value, HorizontalAlign = HorizontalAlign.Left },
new TableCell() { Text = accumulated, HorizontalAlign = HorizontalAlign.Left },
new TableCell() { Text = bookValue, HorizontalAlign = HorizontalAlign.Left }
});
gvData.Controls[0].Controls.Add(row);
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
Dim acquiredDate As DateTime = New DateTime(2019, 9, 1)
Dim startMonth As DateTime = New DateTime(2019, 9, 1)
Dim depreciation As Double = 100
Dim slavege As Double = 1.0
Dim lifeMonth As Integer = 10
Dim dt As DataTable = New DataTable()
dt.Columns.Add("year")
dt.Columns.Add("Month")
dt.Columns.Add("Depreciation", GetType(Decimal))
dt.Columns.Add("Accumulated", GetType(Decimal))
dt.Columns.Add("BookValue", GetType(Decimal))
Dim i As Integer = 0
Dim depreciationExpense As Double = (depreciation - slavege) / lifeMonth
Dim bookValue As Double = depreciation - depreciationExpense
Dim newDepreciationExpense As Double = 0
While startMonth < acquiredDate.AddMonths(lifeMonth)
Dim dr As DataRow = dt.NewRow()
dr("year") = startMonth.ToString("yyyy")
dr("Month") = startMonth.ToString("MMM yyyy")
dr("Depreciation") = depreciationExpense
If i = 0 Then
dr("Accumulated") = Math.Round(depreciationExpense, 2)
dr("BookValue") = Math.Round(bookValue, 2)
newDepreciationExpense = depreciationExpense
Else
newDepreciationExpense = newDepreciationExpense + depreciationExpense
bookValue = bookValue - depreciationExpense
dr("Accumulated") = Math.Round(newDepreciationExpense, 2)
dr("BookValue") = Math.Round(bookValue, 2)
End If
dt.Rows.Add(dr)
startMonth = startMonth.AddMonths(1)
i += 1
End While
Me.gvData.DataSource = dt
Me.gvData.DataBind()
End Sub
Private currentId As Integer = 0
Private subTotal As Decimal = 0
Private total As Decimal = 0
Private subTotalRowIndex As Integer = 0
Protected Sub OnRowCreated(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
subTotal = 0
If e.Row.RowType = DataControlRowType.DataRow Then
Dim dt As DataTable = (TryCast(e.Row.DataItem, DataRowView)).DataView.Table
Dim orderId As Integer = Convert.ToInt32(dt.Rows(e.Row.RowIndex)("year"))
total += Convert.ToDecimal(dt.Rows(e.Row.RowIndex)("Depreciation"))
If orderId <> currentId Then
If e.Row.RowIndex > 0 Then
For i As Integer = subTotalRowIndex To e.Row.RowIndex - 1
subTotal += Convert.ToDecimal(gvData.Rows(i).Cells(2).Text)
Next
Dim accumulated As String = gvData.Rows(e.Row.RowIndex - 1).Cells(3).Text
Dim book As String = gvData.Rows(e.Row.RowIndex - 1).Cells(4).Text
Me.AddTotalRow("Sub Total", subTotal.ToString("N1"), accumulated, book)
subTotalRowIndex = e.Row.RowIndex
End If
currentId = orderId
End If
End If
End Sub
Protected Sub OnDataBound(ByVal sender As Object, ByVal e As EventArgs)
For i As Integer = subTotalRowIndex To gvData.Rows.Count - 1
subTotal += Convert.ToDecimal(gvData.Rows(i).Cells(2).Text)
Next
Dim accumulated As String = gvData.Rows(gvData.Rows.Count - 1).Cells(3).Text
Dim book As String = gvData.Rows(gvData.Rows.Count - 1).Cells(4).Text
Me.AddTotalRow("Sub Total", subTotal.ToString("N1"), accumulated, book)
End Sub
Private Sub AddTotalRow(ByVal labelText As String, ByVal value As String, ByVal accumulated As String, ByVal bookValue As String)
Dim row As GridViewRow = New GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Normal)
row.BackColor = ColorTranslator.FromHtml("#F9F9F9")
row.Cells.AddRange(New TableCell(4) {
New TableCell(),
New TableCell() With {.Text = labelText, .HorizontalAlign = HorizontalAlign.Left},
New TableCell() With {.Text = value, .HorizontalAlign = HorizontalAlign.Left},
New TableCell() With {.Text = accumulated, .HorizontalAlign = HorizontalAlign.Left},
New TableCell() With {.Text = bookValue, .HorizontalAlign = HorizontalAlign.Left} })
gvData.Controls(0).Controls.Add(row)
End Sub
Screenshot
