Live Razor Page Samples

Stock Chart

This sample shows how to create a new workbook, copy data from a DataTable, add a stock chart, use various formatting to format the chart, and stream it to Microsoft Excel.

Supporting Files

The following file is utilized by this sample:

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;

namespace Website.Pages.Support.Samples.RazorPages.Charting
{
    public partial class StockChartModel : 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;

            // 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(7.0) - 10;
            double bottom = windowInfo.RowToPoints(15.75);
            SpreadsheetGear.Charts.IChart chart =
                worksheet.Shapes.AddChart(left, top, right - left, bottom - top).Chart;

            // Get the source data range from an existing XML File.
            SpreadsheetGear.IRange source = GetSourceData(worksheet);

            // Set the chart's source data range, plotting series in columns.
            chart.SetSourceData(source, SpreadsheetGear.Charts.RowCol.Columns);

            // Set the chart type to a volume-open-high-low-close stock chart.
            chart.ChartType = SpreadsheetGear.Charts.ChartType.StockVOHLC;

            // Get a reference to the first chart group used for volume series.
            SpreadsheetGear.Charts.IChartGroup volumeGroup = chart.ChartGroups[0];

            // Increase the volume series bar width by decreasing the gap width.
            volumeGroup.GapWidth = 75;

            // Get a reference to the second chart group used for stock values.
            SpreadsheetGear.Charts.IChartGroup stockGroup = chart.ChartGroups[1];

            // Change the fill color of the down bars.
            stockGroup.DownBars.Format.Fill.ForeColor.RGB = SpreadsheetGear.Colors.Red;

            // Change the fill color of the up bars.
            stockGroup.UpBars.Format.Fill.ForeColor.RGB = SpreadsheetGear.Colors.Green;

            // Get a reference to the primary category axis.
            SpreadsheetGear.Charts.IAxis categoryAxis =
                chart.Axes[SpreadsheetGear.Charts.AxisType.Category];

            // Change the primary category axis to always use a category scale.
            categoryAxis.CategoryType =
                SpreadsheetGear.Charts.CategoryType.CategoryScale;

            // Set the label frequency of the primary category axis.
            categoryAxis.TickLabelSpacing = 7;

            // Get a reference to the primary value axis.
            SpreadsheetGear.Charts.IAxis valueAxis =
                chart.Axes[SpreadsheetGear.Charts.AxisType.Value];

            // Set the min, max, and major units of the primary value axis.
            valueAxis.MinimumScale = 0;
            valueAxis.MaximumScale = 130000;
            valueAxis.MajorUnit = 26000;

            // Hide the major gridlines on the primary value axis.
            valueAxis.HasMajorGridlines = false;

            // Get a reference to the secondary value axis.
            valueAxis = chart.Axes[
                SpreadsheetGear.Charts.AxisType.Value,
                SpreadsheetGear.Charts.AxisGroup.Secondary];

            // Set the min, max, and major units of the secondary value axis.
            valueAxis.MinimumScale = 0;
            valueAxis.MaximumScale = 130;
            valueAxis.MajorUnit = 26;

            // Hide the major gridlines on the secondary value axis.
            valueAxis.HasMajorGridlines = false;

            // Delete the legend.
            chart.HasLegend = false;

            // Add a chart title and change the font size.
            chart.HasTitle = true;
            chart.ChartTitle.Text = "ABC Company (Volume-Open-High-Low-Close)";
            chart.ChartTitle.Font.Size = 10;

            // 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-StockChart.xlsx";
            return File(workbookStream, contentType, fileName);
        }

        private SpreadsheetGear.IRange GetSourceData(SpreadsheetGear.IWorksheet worksheet)
        {
            // Load a data set from an XML file with sample stock data.
            System.Data.DataSet dataset = new System.Data.DataSet();
            dataset.ReadXml(INTERNAL_GetSupportingFileStream("stockvohlc.xml"));
            dataset.ReadXml("files/stockvohlc.xml");

            // Get a reference to the VOHLC data table.
            System.Data.DataTable datatable = dataset.Tables["VOHLC"];

            // Get a reference to a range matching the size of the data table.
            int row1 = 0;
            int col1 = 7;
            int row2 = row1 + datatable.Rows.Count;
            int col2 = col1 + datatable.Columns.Count - 1;
            SpreadsheetGear.IRange source = worksheet.Cells[row1, col1, row2, col2];

            // Copy the data table to the range.
            source.CopyFromDataTable(
                datatable, SpreadsheetGear.Data.SetDataFlags.None);

            // return the range.
            return source;
        }
    }
}