Powered by SpreadsheetGear 2010
 
<%@ 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>Excel Report with Formulas and Formatting using ASP.NET and C# - Returns Richly Formatted Excel Workbook Using SpreadsheetGear 2010</title>
<meta content="Excel Report with Formulas and Formatting using ASP.NET, C# and SpreadsheetGear 2010 to return a richly formatted Excel workbook." name="description" />
<script language="C#" runat="server">
     
    void Page_Load(Object sender, EventArgs e)
    {
        // Create a workbook. 
        SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(); 
        
        // Get the worksheet and change the name. 
        SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"]; 
        worksheet.Name = "2005 Sales"; 
        
        // Get the worksheet cells reference. 
        SpreadsheetGear.IRange cells = 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. 
        SpreadsheetGear.INames names = 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 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();
    }
    
</script>
</head>
<body>
</body>
</html>