Powered by SpreadsheetGear
<%@ Page Language="VB" EnableViewState="false" %>
<!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">
<title>Excel Report with Formulas and Formatting using ASP.NET and VB.NET - Returns Richly Formatted XLS Workbook Using SpreadsheetGear</title>
<meta content="Excel Report with formulas and formatting using ASP.NET, Visual Basic .NET and SpreadsheetGear to return a richly formatted XLS workbook." name="description" />
<script language="VB" runat="server">

    Sub Page_Load(sender As Object, e As EventArgs)
        ' Create a workbook. 
        Dim workbook As SpreadsheetGear.IWorkbook = SpreadsheetGear.Factory.GetWorkbook()

        ' Get the worksheet and change the name. 
        Dim worksheet As SpreadsheetGear.IWorksheet = workbook.Worksheets("Sheet1")
        worksheet.Name = "2005 Sales"

        ' Get the worksheet cells reference. 
        Dim cells As SpreadsheetGear.IRange = worksheet.Cells

        ' Add column headers. 
        cells("B1").Formula = "Jan"
        cells("C1").Formula = "Feb"
        cells("D1").Formula = "Mar"

        ' Add row headers. 
        cells("A2").Formula = "West"
        cells("A3").Formula = "Central"
        cells("A4").Formula = "East"

        ' Add random data. 
        cells("B2:D4").Formula = "=RAND()*10000"

        ' Center the column headers. 
        cells("B1:D1").HorizontalAlignment = SpreadsheetGear.HAlign.Center

        ' Bold the row and column headers. 
        cells("B1:D1,A2:A4").Font.Bold = True

        ' Add defined names. 
        Dim names As SpreadsheetGear.INames = workbook.Names
        names.Add("Jan", "='2005 Sales'!$B$2:$B$4")
        names.Add("Feb", "='2005 Sales'!$C$2:$C$4")
        names.Add("Mar", "='2005 Sales'!$D$2:$D$4")
        names.Add("Sales", "='2005 Sales'!$B$2:$D$4")

        ' Format sales data as currency. 
        cells("Sales").NumberFormat = "$#,##0"

        ' Sum each month. 
        cells("B5").Formula = "=SUM(Jan)"
        cells("C5").Formula = "=SUM(Feb)"
        cells("D5").Formula = "=SUM(Mar)"

        ' Format sums. 
        cells("B5:D5").NumberFormat = "$#,##0"

        ' Stream the Excel workbook to the client in the Excel 97-2003 (xls) 
        ' file format compatible with Excel 97-2019 and Excel for Office 365.
        Response.ContentType = "application/vnd.ms-excel"
        Response.AddHeader("Content-Disposition", "attachment; filename=report.xls")
        workbook.SaveToStream(Response.OutputStream, SpreadsheetGear.FileFormat.Excel8)
    End Sub 'Page_Load