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>Excel Report with Chart with ASP.NET and VB.NET Returns Open XML (XLSX) Workbook Using SpreadsheetGear</title>
<meta content="Excel Report with 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

        ' Set the worksheet name and turn off the default gridlines.
        worksheet.Name = "2010 Sales"
        worksheet.WindowInfo.DisplayGridlines = False

        ' Add worksheet title.
        Dim titleCell As SpreadsheetGear.IRange = cells("B1")
        titleCell.Value = "Excel Report with Chart" + ControlChars.Lf + "Created 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

        ' Create column headings for four regions and a total column.
        Dim columnHeadingCells As SpreadsheetGear.IRange = cells("B2:G2")
        columnHeadingCells(0, 1).Value = "North"
        columnHeadingCells(0, 2).Value = "South"
        columnHeadingCells(0, 3).Value = "East"
        columnHeadingCells(0, 4).Value = "West"
        columnHeadingCells(0, 5).Value = "Total"
        Dim columnHeadingStyle As SpreadsheetGear.IStyle = workbook.Styles("Heading 2")
        columnHeadingCells.Style = columnHeadingStyle
        columnHeadingCells.HorizontalAlignment = SpreadsheetGear.HAlign.Center

        ' Create row headings for four quarters and a total row.
        Dim rowHeadingCells As SpreadsheetGear.IRange = cells("B3:B7")
        rowHeadingCells(0, 0).Value = "Q1"
        rowHeadingCells(1, 0).Value = "Q2"
        rowHeadingCells(2, 0).Value = "Q3"
        rowHeadingCells(3, 0).Value = "Q4"
        rowHeadingCells(4, 0).Value = "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.
        Dim rowHeadingAndTotalCells As SpreadsheetGear.IRange = cells("B3:B6,G3:G6")
        rowHeadingAndTotalCells.Font.Bold = True
        rowHeadingAndTotalCells.Font.Size = rowHeadingAndTotalCells.Font.Size + 1

        ' Format the grand totals.
        Dim grandTotalCells As SpreadsheetGear.IRange = 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.
        Dim condition As SpreadsheetGear.IFormatCondition = cells("B3:G6").FormatConditions.Add( _
            SpreadsheetGear.FormatConditionType.Expression, _
            SpreadsheetGear.FormatConditionOperator.Between, _
            "=MOD(ROW(),2)=0", Nothing)
        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("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.
        Dim left As Double = windowInfo.ColumnToPoints(1.0) + 72.0 / 6.0
        Dim top As Double = windowInfo.RowToPoints(8.0) + 3
        Dim right As Double = windowInfo.ColumnToPoints(7.0) - 72.0 / 6.0
        Dim bottom As Double = windowInfo.RowToPoints(22.0) - 3
        Dim chartShape As SpreadsheetGear.Shapes.IShape = _
            worksheet.Shapes.AddChart(left, top, right - left, bottom - top)
        Dim chart As SpreadsheetGear.Charts.IChart = 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.
        Dim source As SpreadsheetGear.IRange = 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.
        Dim totalsSeries As SpreadsheetGear.Charts.ISeries = 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.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