Powered by SpreadsheetGear
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>C# Spreadsheet Component Amortization Using ASP.NET, DataSet, DataGrid and SpreadsheetGear</title>
<meta content="C# spreadsheet component amortization sample demonstrating SpreadsheetGear, a royalty free Microsoft Excel compatible spreadsheet component for the Microsoft .NET Framework featuring the fastest and most complete calculation engine available. Create, read, modify, calculate and write Microsoft Excel workbooks from your Microsoft .NET, ASP.NET, C#, VB.NET and Microsoft Office solutions. Integrates with Microsoft Visual Studio .NET, including IntelliSense and Dynamic Help." name="description" />
<script language="C#" runat="server">
    
    void Page_Load(Object sender, EventArgs e)
    {
        // Initialize the form on first page load
        if (!Page.IsPostBack)
            Calculate();
    }
    
    public void CalculateButton_Click(Object sender, EventArgs e)
    {
        // Make sure page is valid
        if (Page.IsValid)
            Calculate();
    }
    
    void Calculate()
    {   
        // Open the workbook.
        String filename = Server.MapPath("files/amortization.xls");
        SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(filename);
        
        // Get IRange for cells from defined names.
        SpreadsheetGear.IRange pv = workbook.Names["PV"].RefersToRange;
        SpreadsheetGear.IRange rate = workbook.Names["Rate"].RefersToRange;
        SpreadsheetGear.IRange nper = workbook.Names["NPer"].RefersToRange;
        
        // Is the form being submitted?
        if (Page.IsPostBack)
        {
            // Copy the web form values to the worksheet.
            pv.Formula = TextBoxAmount.Text;
            rate.Formula = TextBoxRate.Text;
            nper.Formula = TextBoxPeriods.Text;
        }
        
        // Copy the formatted worksheet values to the web form.
        TextBoxAmount.Text = pv.Text;
        TextBoxRate.Text = rate.Text;
        TextBoxPeriods.Text = nper.Text;
        
        // Display payment and interest details
        LabelPayment.Text = workbook.Names["Payment"].RefersToRange.Text;
        LabelLastPayment.Text = workbook.Names["LastPayment"].RefersToRange.Text;
        LabelTotalInterest.Text = workbook.Names["TotalInterest"].RefersToRange.Text;
        
        // Display the amortization table in the DataGrid.
        // This returns a DataSet with one DataTable for the given defined name.
        DataSet dataSet = workbook.GetDataSet(
                              "AmortizationTableForNPer",
                              SpreadsheetGear.Data.GetDataFlags.FormattedText);
        DataGridAmortization.DataSource = dataSet;
        DataGridAmortization.DataBind();
    }
    
</script>
<style type="text/css">
    
.tableStyle 
{
    background-color: #EEEEEE;
    border-style: solid;
    border-width: 1px;
    border-color: #5959AA;
    color: #000000;
    font-size: 12px;
}
    
.tableHeaderStyle
{
    background-color: #5959AA;
    color: #FFFFFF;
    font-size: 12px;
    font-weight: bold;
}
    
</style>
</head>
<body style="font-family: Verdana;">
<h5>C# Spreadsheet Component Amortization Sample for ASP.NET<br />Using DataSet, DataGrid and <a href="https://www.spreadsheetgear.com/products/spreadsheetgear.net.aspx">SpreadsheetGear</a></h5>
<form id="form1" runat="server">
  <table>
    <tr>
      <td><table class="tableStyle "cellpadding="4" cellspacing="0">
          <tr class="tableHeaderStyle">
            <td colspan="2">Loan Calculator</td>
          </tr>
          <tr>
            <td align="right">Loan Amount (pv)</td>
            <td><asp:TextBox id="TextBoxAmount" Columns="12" runat="server"/></td>
          </tr>
          <tr>
            <td align="right">Interest Rate (rate)</td>
            <td><asp:TextBox id="TextBoxRate" Columns="8" runat="server"/></td>
          </tr>
          <tr>
            <td align="right">Total # of Periods (Nper)</td>
            <td><asp:TextBox id="TextBoxPeriods" Columns="8" runat="server"/></td>
          </tr>
          <tr>
            <td colspan="2" align="right"><asp:Button ID="ButtonSubmit" Text="Calculate" OnClick="CalculateButton_Click" runat="server"/></td>
          </tr>
        </table></td>
      <td width="10"></td>
      <td valign="top"><table style="font-size: 12px" cellpadding="4" cellspacing="0">
          <tr>
            <td>Payment</td>
            <td><asp:Label id="LabelPayment" runat="server"/></td>
          </tr>
          <tr>
            <td>Last Payment</td>
            <td><asp:Label id="LabelLastPayment" runat="server"/></td>
          </tr>
          <tr>
            <td>Total Interest</td>
            <td><asp:Label id="LabelTotalInterest" runat="server"/></td>
          </tr>
        </table>
        <br />
        <br />
        <asp:RangeValidator id="RangeVal1" 
             Type="Integer" 
             ControlToValidate="TextBoxPeriods" 
             MinimumValue="1" 
             MaximumValue="360" 
             ErrorMessage="Total # of Periods must be a value between 1 and 360!" 
             Font-Bold="true" 
             Font-Size="8pt" 
             runat="server"/></td>
    </tr>
  </table>
  <br />
  <table style="font-family: Verdana; font-size: 12px">
    <tr>
      <td>Amortization Schedule</td>
    </tr>
    <tr>
      <td><ASP:DataGrid ID="DataGridAmortization" BorderWidth="1px" BorderColor="#AAAAAA" CellPadding="4" EnableViewState="false" runat="server">
          <HeaderStyle BackColor="#5959AA" ForeColor="#FFFFFF" Font-Size="8pt" Font-Bold="True" />
          <ItemStyle BackColor="#EEEEEE" ForeColor="#000000" Font-Size="8pt" />
          <AlternatingItemStyle BackColor="#FFFFFF" ForeColor="#000000" Font-Size="8pt"/>
        </ASP:DataGrid>
      </td>
    </tr>
  </table>
</form>
<h6>Copyright 2005 © SpreadsheetGear LLC. All Rights Reserved.<br /><a href="https://www.spreadsheetgear.com">www.spreadsheetgear.com</a></h6>
</body>
</html>