Live Razor Page Samples

Stacked Combination Chart

This sample shows how to create a new workbook, add some values, add a chart, use multiple chart groups and multiple axes groups to create a stacked combination chart, and stream it to Microsoft Excel.
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;

namespace Website.Pages.Support.Samples.RazorPages.Charting
{
    public partial class StackedComboChartModel : PageModel
    {
        public FileResult OnGet()
        {
            // 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;

            // Load some sample data.
            cells["G3:G7"].Value = new string[,] { { "Mon" }, { "Tue" }, { "Wed" }, { "Thu" }, { "Fri" } };
            cells["H2:K2"].Value = new string[,] { { "Breakfast", "Lunch", "Dinner", "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 colum values to get 
            //       coordinates anywhere in between row and column boundaries.
            double left = 10;
            double top = windowInfo.RowToPoints(0.25);
            double right = windowInfo.ColumnToPoints(6.0) - 10;
            double bottom = windowInfo.RowToPoints(13.75);
            SpreadsheetGear.Charts.IChart chart =
                worksheet.Shapes.AddChart(left, top, right - left, bottom - top).Chart;

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

            // Set the chart type.
            chart.ChartType = SpreadsheetGear.Charts.ChartType.ColumnStacked100;

            // Get a reference to the chart's series collection and each series.
            SpreadsheetGear.Charts.ISeriesCollection seriesCollection = chart.SeriesCollection;
            SpreadsheetGear.Charts.ISeries seriesBreakfast = seriesCollection[0];
            SpreadsheetGear.Charts.ISeries seriesLunch = seriesCollection[1];
            SpreadsheetGear.Charts.ISeries seriesDinner = seriesCollection[2];
            SpreadsheetGear.Charts.ISeries seriesTotal = 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;
            SpreadsheetGear.Charts.IDataLabels dataLabels = seriesTotal.DataLabels;

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

            // Change the fill formatting of the data labels.
            SpreadsheetGear.Shapes.IFillFormat fillFormat = dataLabels.Format.Fill;
            fillFormat.ForeColor.RGB = SpreadsheetGear.SystemColors.Window;
            fillFormat.Visible = true;

            // Change the line formatting of the data labels.
            SpreadsheetGear.Shapes.ILineFormat lineFormat = 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.
            SpreadsheetGear.Charts.IAxis valueAxis =
                chart.Axes[SpreadsheetGear.Charts.AxisType.Value];
            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;

            // Save workbook to stream using the Open XML (*.xlsx) file format compatible with Excel 2007 and later.
            System.IO.Stream workbookStream = workbook.SaveToStream(SpreadsheetGear.FileFormat.OpenXMLWorkbook);

            // Reset position to beginning of stream.
            workbookStream.Seek(0, System.IO.SeekOrigin.Begin);

            // Stream the Excel workbook to the client.
            var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            var fileName = "SpreadsheetGear-Sample-StackedComboChart.xlsx";
            return File(workbookStream, contentType, fileName);
        }
    }
}