Powered by SpreadsheetGear
<%@ 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 Chart with ASP.NET and C# - Returns Open XML Workbook for Excel 2007-2019 and Excel for Office 365 using SpreadsheetGear</title>
<meta content="Excel Report with Chart with ASP.NET, C# and 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="C#" runat="server">
     
    void Page_Load(Object sender, EventArgs e)
    {
        // Create a new workbook.
        SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
        SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];
        SpreadsheetGear.IWorksheetWindowInfo windowInfo = worksheet.WindowInfo;
        SpreadsheetGear.IRange cells = worksheet.Cells;
 
        // Set the worksheet name and turn off the default gridlines.
        worksheet.Name = "2010 Sales";
        worksheet.WindowInfo.DisplayGridlines = false;
  
        // Add worksheet title.
        SpreadsheetGear.IRange titleCell = cells["B1"];
        titleCell.Value = "Excel Report with Chart\nCreated with SpreadsheetGear and ASP.NET";
        titleCell.HorizontalAlignment = SpreadsheetGear.HAlign.Center;
        titleCell.VerticalAlignment = SpreadsheetGear.VAlign.Center;
        titleCell.Style = workbook.Styles["Heading 1"];
        titleCell.RowHeight = 48.0;
        cells["B1:G1"].Merge();

        // Create column headings for four regions and a total column.
        SpreadsheetGear.IRange columnHeadingCells = cells["B2:G2"];
        columnHeadingCells.Value = new string[,] { { "", "North", "South", "East", "West", "Total" } };
        SpreadsheetGear.IStyle columnHeadingStyle = workbook.Styles["Heading 2"];
        columnHeadingCells.Style = columnHeadingStyle;
        columnHeadingCells.HorizontalAlignment = SpreadsheetGear.HAlign.Center;

        // Create row headings for four quarters and a total row.
        SpreadsheetGear.IRange rowHeadingCells = cells["B3:B7"];
        rowHeadingCells.Value = new string[,] { { "Q1" }, { "Q2" }, { "Q3" }, { "Q4" }, { "Total" } };
        rowHeadingCells.HorizontalAlignment = SpreadsheetGear.HAlign.Right;

        // Create random data using multiple cell range.
        cells["C3:F6"].Formula = "=RAND()*1000000";

        // Add formulas which use the SUM worksheet function to total
        // the sales for each quarter and sales for each region.
        cells["G3:G6"].Formula = "=SUM(C3:F3)";
        cells["C7:G7"].Formula = "=SUM(C3:C6)";
        
        // Format the row headings and row totals.
        SpreadsheetGear.IRange rowHeadingAndTotalCells = cells["B3:B6,G3:G6"];
        rowHeadingAndTotalCells.Font.Bold = true;
        rowHeadingAndTotalCells.Font.Size = rowHeadingAndTotalCells.Font.Size + 1;

        // Format the grand totals.
        SpreadsheetGear.IRange grandTotalCells = cells["B7:G7"];
        grandTotalCells.Font.Bold = true;
        grandTotalCells.Font.Size = grandTotalCells.Font.Size + 2;
        grandTotalCells.Borders[SpreadsheetGear.BordersIndex.EdgeTop].Weight = SpreadsheetGear.BorderWeight.Medium;
        grandTotalCells.Borders[SpreadsheetGear.BordersIndex.EdgeBottom].LineStyle = SpreadsheetGear.LineStyle.Double;
        
        // Format data with "Currency (0)" named cell style.
        cells["C3:G7"].Style = workbook.Styles["Currency [0]"];
 
        // Add a conditional format to make every other row use 
        // the color from the Heading 2 bottom border.
        SpreadsheetGear.IFormatCondition condition = cells["B3:G6"].FormatConditions.Add(
            SpreadsheetGear.FormatConditionType.Expression,
            // Operator Does hnot matter for Expression
            SpreadsheetGear.FormatConditionOperator.Between, 
            "=MOD(ROW(),2)=0", null);
        condition.Interior.Color = columnHeadingStyle.Borders[SpreadsheetGear.BordersIndex.EdgeBottom].Color;
        
        // Autofit column B and set other columns to absolute widths since
        // they use random numbers, which could lead to "#######" showing up
        // in cells if they are autofitted.
        cells["B:B"].Columns.AutoFit();
        cells["C:F"].ColumnWidth = 13.6;
        cells["G:G"].ColumnWidth = 14.9;
        
        // Add a chart to the worksheet's shape collection.
        // NOTE: Calculate the coordinates of the chart by converting row
        //       and column coordinates to points.  Use fractional row 
        //       and colum values to get coordinates anywhere in between 
        //       row and column boundaries.
        double left = windowInfo.ColumnToPoints(1.0) + (72.0 / 6.0);
        double top = windowInfo.RowToPoints(8.0) + 3;
        double right = windowInfo.ColumnToPoints(7.0) - (72.0 / 6.0);
        double bottom = windowInfo.RowToPoints(22.0) - 3;
        SpreadsheetGear.Shapes.IShape chartShape =
            worksheet.Shapes.AddChart(left, top, right - left, bottom - top);
        SpreadsheetGear.Charts.IChart chart = chartShape.Chart;

        // Turn off the border around the chart for a cleaner look.
        chart.ChartArea.Format.Line.Visible = false;
        
        // Give the chart shape a name which will be used to refer to the chart
        // in other samples.
        chartShape.Name = "SalesByRegionChart";

        // Set the chart's source data range for the individual sales figures,
        // plotting series in columns.
        SpreadsheetGear.IRange source = cells["B2:F6"];
        chart.SetSourceData(source, SpreadsheetGear.Charts.RowCol.Columns);
        
        // Set the chart type.
        chart.ChartType = SpreadsheetGear.Charts.ChartType.ColumnClustered;

        // Add a new series for the totals and link it's series name 
        // and values to the worksheet.
        SpreadsheetGear.Charts.ISeries totalsSeries = chart.SeriesCollection.Add();        
        totalsSeries.Name = "=G2";
        totalsSeries.Values = "=G3:G6";
        
        // Change the total series chart type and plot the total series 
        // on the secondary axis.
        //
        // NOTE: This creates a combination chart using multiple chart groups
        //       and utilizes both primary and secondary axes sets.
        totalsSeries.ChartType = SpreadsheetGear.Charts.ChartType.Line;        
        totalsSeries.AxisGroup = SpreadsheetGear.Charts.AxisGroup.Secondary;
        
        // Add a chart title, set the text to refer to the title in the
        // worksheet, and change the font size.
        chart.HasTitle = true;
        chart.ChartTitle.Text = "=B1";
        chart.ChartTitle.Font.Size = 12;
        
        // Change the legend position to the bottom of the chart and
        // set the legend font to bold.
        chart.Legend.Position = SpreadsheetGear.Charts.LegendPosition.Bottom;
        chart.Legend.Font.Bold = true;

        // Change the value major gridlines from the default black to gray.
        chart.Axes[SpreadsheetGear.Charts.AxisType.Value].MajorGridlines.Format.Line.ForeColor.RGB = SpreadsheetGear.Colors.Gray;
            
        // Add a defined name which will be used by other samples to refer 
        // to the range of cells containing the data and the chart.       
        workbook.Names.Add("SalesDataAndChartRange", "=B1:G22");
 
        // Stream the Excel workbook to the client in the Open XML file
        // format compatible with Excel 2007-2019 and Excel for Office 365.
        Response.Clear();
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("Content-Disposition", "attachment; filename=report.xlsx");
        workbook.SaveToStream(Response.OutputStream, SpreadsheetGear.FileFormat.OpenXMLWorkbook);
        Response.End();
    }
    
</script>
</head>
<body>
</body>
</html>