<%@ Page Language="C#" 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>C# Microsoft Excel Reporting Sample for ASP.NET With Outlines Using SpreadsheetGear 2010</title>
<meta content="Microsoft Excel Reporting Sample showing grouping/outlining using C# 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="C#" runat="server">
void Page_Load(Object sender, EventArgs e)
{
// Create a new workbook.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
// Get a reference to the first worksheet and name it.
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];
worksheet.Name = "2005 Sales";
// Get the worksheet cells reference.
SpreadsheetGear.IRange cells = 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.
SpreadsheetGear.IRange totalCell = 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();
}
private void CreateRegion(string region,
SpreadsheetGear.IRange detailCells, SpreadsheetGear.IRange summaryCells)
{
// Set region data, formulas and formatting.
int quarter = 1;
for (int iRow = 0; iRow < detailCells.RowCount; iRow++)
{
detailCells[iRow, 0].Formula = region;
detailCells[iRow, 1].Formula = "Q" + quarter++;
detailCells[iRow, 2].Formula = "=RAND() * 10000";
detailCells[iRow, 2].NumberFormat = "$#,##0_);($#,##0)";
}
// Group the region.
detailCells.EntireRow.Group();
// Get the data column of the detail cells.
int lastRow = detailCells.RowCount - 1;
int lastColumn = detailCells.ColumnCount - 1;
SpreadsheetGear.IRange dataCells =
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.
SpreadsheetGear.IRange totalCell = summaryCells[0, 2];
totalCell.Formula = "=SUM(" + dataCells.Address + ")";
totalCell.NumberFormat = "$#,##0_);($#,##0)";
totalCell.Font.Bold = true;
}
</script>
</head>
<body>
</body>
</html>