Powered by SpreadsheetGear
<%@ Page Language="VB" EnableViewState="true" %>

<!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>Dashboard from 1693 Analytics Powered by SpreadsheetGear using ASP.NET
        and VB.NET</title>
    <meta content="Dashboard from 1693 Analytics Powered by 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(ByVal sender As Object, ByVal e As EventArgs)
            
            ' Call routine to dynamically build the states.
            BuildStates()

            ' NOTE: Construct a url which references the SpreadsheetGear Image Rendering Utility
            '       which will return an image to be displayed in an image control.
            
            ' The name of the page containing the SpreadsheetGear Image Rendering Utility.
            Dim imageRenderingUtility As String = "spreadsheetgearimageVB.aspx"

            ' The name of the workbook which contains the 1693 Analytics dasbboard.
            Dim file As String = "?file=files/1693analytics.xls"

            ' The name of the worksheet which will be rendered.
            Dim worksheet As String = "&worksheet=Template"

            ' The range which will be rendered by the image rendering utility
            ' which has been given a user defined name of "Dashboard".
            Dim range As String = "&range=Dashboard"

            ' The currently selected state will be placed in
            ' the cell, which has been given a user defined name of 
            ' "CurrentState", by the SpreadsheetGear Image Rendering Utility.
            Dim value As String = "&CurrentState=" + ListState.SelectedValue

            ' Set the Image Url.
            Dim url As String = imageRenderingUtility + file + worksheet + range + value
            ImageMain.ImageUrl = url
            
        End Sub 'Page_Load
        
        Sub BuildStates()
            
            ' If page is loading for the first time...
            If Not Page.IsPostBack Then
                
                ' Open the dashboard workbook.
                Dim filename As String = Server.MapPath("files/1693analytics.xls")
                Dim workbook As SpreadsheetGear.IWorkbook = SpreadsheetGear.Factory.GetWorkbook(filename)
      
                ' Dynamically build the state list using a defined name.
                Dim stateRange As SpreadsheetGear.IRange = workbook.Names("ListOfStates").RefersToRange
                Dim cell As SpreadsheetGear.IRange
                For Each cell In stateRange
                    ListState.Items.Add(cell.Text)
                Next cell
                
                ' Set the number of displayed rows to match the number of states.
                ListState.Rows = ListState.Items.Count
      
                ' Select the first state.
                ListState.SelectedIndex = 0
                
            End If
   
            ' Set the focus to the state list.
            ListState.Focus()
            
        End Sub 'BuildStates
    
    </script>

</head>
<body style="font-family: Verdana; font-size: small">
    <form id="form1" runat="server">
        <table cellpadding="4">
            <tr>
                <td nowrap>
                    <asp:Label ID="LabelTitle" Font-Size="Large" runat="server">Dashboard from 1693 Analytics - Powered by SpreadsheetGear and ASP.NET</asp:Label></td>
                <td width="100%">
                </td>
            </tr>
            <tr>
                <td>
                    This sample shows how to create a dashboard where the user chooses from a list of
                    states, and relevant educational cost data and charts are returned for that state.
                    The model and data for this sample are contained in a static Excel workbook which
                    has been provided by Burt Baker at <a href="http://www.1693net.com/">1693 Analytics,
                        LLC</a>, but it could just as easily come from a workbook which is dynamically
                    generated by SpreadsheetGear.<br />
                    <br />
                    The key steps in the generation of this dashboard are:
                    <ul>
                        <li>The first time this page is loaded, the BuildStates() method loads the workbook
                            from disk and populates the listbox of states from the range of cells which have
                            been given the defined name "ListOfStates".</li>
                        <li>The Page_Load() method constructs a URL which will instruct the image rendering
                            utility what to render.</li>
                        <li>The constructed URL is assigned to the Image control on this page.</li>
                        <li>The browser generates an HTTP request to the image rendering utility which generates
                            the dashboard.</li>
                    </ul>
                </td>
            </tr>
        </table>
        <table cellpadding="4">
            <tr>
                <td>
                    <asp:Label ID="LabelState" runat="server">State:</asp:Label></td>
                <td>
                    <asp:Label ID="LabelImage" runat="server">Dashboard Generated by SpreadsheetGear Image Rendering Utility:</asp:Label></td>
            </tr>
            <tr>
                <td valign="top">
                    <asp:ListBox ID="ListState" Width="100" Rows="8" AutoPostBack="true" runat="server">
                    </asp:ListBox></td>
                <td width="742" height="895" valign="top">
                    <asp:Image ID="ImageMain" BorderStyle="Solid" BorderWidth="1" runat="server"></asp:Image></td>
            </tr>
        </table>
    </form>
    <h5>
        Copyright 2009-2017 © SpreadsheetGear LLC. All Rights Reserved.<br />
        <a href="https://www.spreadsheetgear.com">www.spreadsheetgear.com</a></h5>
</body>
</html>