Powered by SpreadsheetGear
<%@ Page Language="VB" %>
<%@ 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>Visual Basic .NET Spreadsheet Component Amortization Using ASP.NET, DataSet, DataGrid and SpreadsheetGear</title>
<meta content="VB.NET 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="VB" runat="server">
    
    Sub Page_Load(sender As Object, e As EventArgs)
        ' Initialize the form on first page load
        If Not Page.IsPostBack Then
            Calculate()
        End If
    End Sub 'Page_Load
    
    Public Sub CalculateButton_Click(sender As Object, e As EventArgs)
        ' Make sure page is valid
        If Page.IsValid Then
            Calculate()
        End If
    End Sub 'CalculateButton_Click
    
    Sub Calculate()
        ' Open the workbook.
        Dim filename As String = Server.MapPath("files/amortization.xls")
        Dim workbook As SpreadsheetGear.IWorkbook = SpreadsheetGear.Factory.GetWorkbook(filename)
    
        ' Get IRange for cells from defined names.
        Dim pv As SpreadsheetGear.IRange = workbook.Names("PV").RefersToRange
        Dim rate As SpreadsheetGear.IRange = workbook.Names("Rate").RefersToRange
        Dim nper As SpreadsheetGear.IRange = workbook.Names("NPer").RefersToRange
    
        ' Is the form being submitted?
        If Page.IsPostBack Then
            ' Copy the web form values to the worksheet.
            pv.Formula = TextBoxAmount.Text
            rate.Formula = TextBoxRate.Text
            nper.Formula = TextBoxPeriods.Text
        End If
    
        ' 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.
        Dim dataSet As DataSet = workbook.GetDataSet("AmortizationTableForNPer", SpreadsheetGear.Data.GetDataFlags.FormattedText)
        DataGridAmortization.DataSource = dataSet
        DataGridAmortization.DataBind()
    End Sub 'Calculate
    
</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>Visual Basic .NET Spreadsheet Component Amortization Sample for<br />ASP.NET 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>