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>Combination Chart with ASP.NET and VB.NET Returns Open XML (XLSX) Workbook Using SpreadsheetGear</title>
<meta content="Combination Chart with ASP.NET, Visual Basic .NET 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="VB" runat="server">

    Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        ' Create a new workbook.
        Dim workbook As SpreadsheetGear.IWorkbook = SpreadsheetGear.Factory.GetWorkbook()
        Dim worksheet As SpreadsheetGear.IWorksheet = workbook.Worksheets("Sheet1")
        Dim windowInfo As SpreadsheetGear.IWorksheetWindowInfo = worksheet.WindowInfo
        Dim cells As SpreadsheetGear.IRange = worksheet.Cells

        ' Load some sample data.
        cells("G3").Formula = "Mon"
        cells("G4").Formula = "Tue"
        cells("G5").Formula = "Wed"
        cells("G6").Formula = "Thu"
        cells("G7").Formula = "Fri"
        cells("H2").Formula = "Breakfast"
        cells("I2").Formula = "Lunch"
        cells("J2").Formula = "Dinner"
        cells("K2").Formula = "Total"
        cells("H3:J7").Formula = "=INT(RAND() * 500) + 300"
        cells("K3:K7").Formula = "=SUM(H3:J3)"

        ' 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 column values to get 
        '       coordinates anywhere in between row and column boundaries.
        Dim left As Double = 10
        Dim top As Double = windowInfo.RowToPoints(0.25)
        Dim right As Double = windowInfo.ColumnToPoints(6.0) - 10
        Dim bottom As Double = windowInfo.RowToPoints(13.75)
        Dim chart As SpreadsheetGear.Charts.IChart = _
            worksheet.Shapes.AddChart(left, top, right - left, bottom - top).Chart

        ' Set the chart's source data range, plotting series in columns.
        Dim source As SpreadsheetGear.IRange = cells("G2:K7")
        chart.SetSourceData(source, SpreadsheetGear.Charts.RowCol.Columns)

        ' Set the chart type to a 100% stacked column.
        chart.ChartType = SpreadsheetGear.Charts.ChartType.ColumnStacked100

        ' Get a reference to the chart's series collection and each series.
        Dim seriesCollection As SpreadsheetGear.Charts.ISeriesCollection = chart.SeriesCollection
        Dim seriesBreakfast As SpreadsheetGear.Charts.ISeries = seriesCollection(0)
        Dim seriesLunch As SpreadsheetGear.Charts.ISeries = seriesCollection(1)
        Dim seriesDinner As SpreadsheetGear.Charts.ISeries = seriesCollection(2)
        Dim seriesTotal As SpreadsheetGear.Charts.ISeries = seriesCollection(3)

        ' Change the last series chart type and plot it on the secondary axis. 
        ' NOTE: This creates a combination chart using multiple chart groups
        '       and utilizes both primary and secondary axes sets, allowing for
        '       disproportionate ranges of values to be plotted separately.
        seriesTotal.ChartType = SpreadsheetGear.Charts.ChartType.Line
        seriesTotal.AxisGroup = SpreadsheetGear.Charts.AxisGroup.Secondary

        ' Change the fill color of each primary series.
        seriesBreakfast.Format.Fill.ForeColor.RGB = SpreadsheetGear.Colors.DarkOrange
        seriesLunch.Format.Fill.ForeColor.RGB = SpreadsheetGear.Colors.OrangeRed
        seriesDinner.Format.Fill.ForeColor.RGB = SpreadsheetGear.Colors.Red

        ' Change the line color of the total series.
        seriesTotal.Format.Line.ForeColor.RGB = SpreadsheetGear.Colors.Yellow

        ' Add data labels to the total series and get a reference to the data labels.
        seriesTotal.HasDataLabels = True
        Dim dataLabels As SpreadsheetGear.Charts.IDataLabels = seriesTotal.DataLabels

        ' Position each data label below each data point.
        dataLabels.Position = SpreadsheetGear.Charts.DataLabelPosition.Below

        ' Change the fill formatting of the data labels.
        Dim fillFormat As SpreadsheetGear.Shapes.IFillFormat = dataLabels.Format.Fill
        fillFormat.ForeColor.RGB = SpreadsheetGear.SystemColors.Window
        fillFormat.Visible = True

        ' Change the line formatting of the data labels.
        Dim lineFormat As SpreadsheetGear.Shapes.ILineFormat = dataLabels.Format.Line
        lineFormat.ForeColor.RGB = SpreadsheetGear.SystemColors.WindowText
        lineFormat.Visible = True

        ' Change the legend position to the top of the chart.
        chart.Legend.Position = SpreadsheetGear.Charts.LegendPosition.Top

        ' Get a reference to the primary value axis, hide major
        ' gridlines, and use a fixed major unit scaling value.
        Dim valueAxis As SpreadsheetGear.Charts.IAxis = _
        valueAxis.HasMajorGridlines = False
        valueAxis.MajorUnit = 0.25

        ' Change the chart font name and size.
        chart.ChartArea.Font.Name = "Verdana"
        chart.ChartArea.Font.Size = 8

        ' Add a chart title and change the font size.
        chart.HasTitle = True
        chart.ChartTitle.Text = "Weekday Calorie Intake"
        chart.ChartTitle.Font.Size = 9

        ' Stream the Excel workbook to the client in the Open XML file
        ' format compatible with Excel 2007-2019 and Excel for Office 365.
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        Response.AddHeader("Content-Disposition", "attachment; filename=report.xlsx")
        workbook.SaveToStream(Response.OutputStream, SpreadsheetGear.FileFormat.OpenXMLWorkbook)
    End Sub 'Page_Load