<%@ 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">
<head>
<title>VB.NET Microsoft Excel Reporting Sample for ASP.NET With Outlines Using SpreadsheetGear 2010</title>
<meta content="Microsoft Excel Reporting Sample showing grouping/outlining using VB.NET and SpreadsheetGear 2010, 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)
' Create a new workbook.
Dim workbook As SpreadsheetGear.IWorkbook = SpreadsheetGear.Factory.GetWorkbook()
' Get a reference to the first worksheet and name it.
Dim worksheet As SpreadsheetGear.IWorksheet = workbook.Worksheets("Sheet1")
worksheet.Name = "2005 Sales"
' Get the worksheet cells reference.
Dim cells As SpreadsheetGear.IRange = worksheet.Cells
' Clear any existing outlines.
cells.ClearOutline()
' Set column titles and formatting.
cells("A1").Formula = "Region"
cells("B1").Formula = "Quarter"
cells("C1").Formula = "Sales"
cells("A1:C1").Font.Bold = True
' Call method to group and summarize each region.
CreateRegion("East", cells("A2:C5"), cells("A6:C6"))
CreateRegion("West", cells("A7:C10"), cells("A11:C11"))
CreateRegion("North", cells("A12:C15"), cells("A16:C16"))
CreateRegion("South", cells("A17:C20"), cells("A21:C21"))
' Set summary total for all regions.
Dim totalCell As SpreadsheetGear.IRange = cells("C22")
totalCell.Formula = "=SUM(C6,C11,C16,C21)"
totalCell.NumberFormat = "$#,##0_);($#,##0)"
totalCell.Font.Bold = True
' Group all regions.
cells("A2:A21").EntireRow.Group()
' Collapse all region detail levels.
worksheet.Outline.ShowLevels(2, 0)
' Show detail for one region.
cells("A6").EntireRow.ShowDetail = True
' Stream the Excel spreadsheet to the client in a format
' compatible with Excel 97/2000/XP/2003/2007/2010.
Response.Clear()
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("Content-Disposition", "attachment; filename=report.xls")
workbook.SaveToStream(Response.OutputStream, SpreadsheetGear.FileFormat.XLS97)
Response.End()
End Sub 'Page_Load
Private Sub CreateRegion(ByVal region As String, ByVal detailCells As SpreadsheetGear.IRange, ByVal summaryCells As SpreadsheetGear.IRange)
' Set region data, formulas and formatting.
Dim quarter As Integer = 1
Dim iRow As Integer
For iRow = 0 To detailCells.RowCount - 1
detailCells(iRow, 0).Formula = region
detailCells(iRow, 1).Formula = "Q" + quarter.ToString()
detailCells(iRow, 2).Formula = "=RAND() * 10000"
detailCells(iRow, 2).NumberFormat = "$#,##0_);($#,##0)"
quarter = quarter + 1
Next iRow
' Group the region.
detailCells.EntireRow.Group()
' Get the data column of the detail cells.
Dim lastRow As Integer = detailCells.RowCount - 1
Dim lastColumn As Integer = detailCells.ColumnCount - 1
Dim dataCells As SpreadsheetGear.IRange = detailCells(0, lastColumn, lastRow, lastColumn)
' Set summary titles for the region.
summaryCells(0, 0).Formula = region
summaryCells(0, 1).Formula = "Total"
' Set summary total for the region.
Dim totalCell As SpreadsheetGear.IRange = summaryCells(0, 2)
totalCell.Formula = "=SUM(" + dataCells.Address + ")"
totalCell.NumberFormat = "$#,##0_);($#,##0)"
totalCell.Font.Bold = True
End Sub
</script>
</head>
<body>
</body>
</html>