Stock Charts

This example demonstrates how to create an Stock Chart.

Stock charts in Excel help present your stock’s data in a much simpler and easy to read manner.

Dropdown to choose from types of stock charts

  • Stock high low close
  • Stock open high low close
  • Stock volume high low close
  • Stock volume open high

Choosing excel version depending upon installation or need.

Using dropdown version.

  • Excel 97-2003
  • Excel 2007
  • Excel 2010
  • Excel 2013
  • Excel 2016

After making above choice .

After clicking generate button will launch an excel with stock chart and above choices

using System;
using System.Collections.Generic;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Text;

using OfficeComponent.Excel;
using OfficeComponent.Excel.Charts;

namespace OfficeComponent.Samples
{
    class StockChartsExample : ExcelExampleBase
    {
        public int Type;

        public StockChartsExample(string commonDataPath, string outputDir)
            : base(commonDataPath, outputDir)
        {
        }

        public StockChartsExample(string commonDataPath, string outputDir, string xmlFile)
            : base(commonDataPath, outputDir, xmlFile)
        {

        }

        public override string Execute()
        {            
            WorkbookManager manager = new WorkbookManager();

            // Add a workbook.
            Workbook workbook = manager.Workbooks.Add();
            workbook.Version = SaveAsFormat;

            IWorksheet sheet = workbook.Worksheets[0];

            CreateChartData(sheet);

            // Hide gridlines.
            sheet.IsGridLinesVisible = false;

            #region Chart
            // Create Chart

            IChart chart = workbook.Charts.Add();
            chart.Name = "Stock Chart";
            chart.ChartTitle = "Stock Chart - Sales by Region";

            chart.DataRange = sheet.Range["A3:C15"];

            switch (Type)
            {
                case 0:
                    chart.ChartType = ChartType.Stock_HighLowClose;
                    break;

                case 1:
                    chart.ChartType = ChartType.Stock_OpenHighLowClose;
                    break;

                case 2:
                    chart.ChartType = ChartType.Stock_VolumeHighLowClose;
                    break;

                case 3:
                    chart.ChartType = ChartType.Stock_VolumeOpenHighLowClose;
                    break;

            }

            chart.Legend.Position = ExcelLegendPosition.Bottom;
            chart.Legend.IsVerticalLegend = false;

            chart.Activate();

            #endregion

            string fileName = OutputDir + "\\" + this.GetType().Name + "_" + Guid.NewGuid().ToString() + GetExtension(SaveAsFormat);
            // Save the document.
            workbook.SaveAs(fileName);

            // Close the document.
            workbook.Close();

            return fileName;
        }

        void CreateChartData(IWorksheet sheet)
        {
            //Put values for rows in column A
            sheet.Range["B3"].Text = "Precipitation,in.";
            sheet.Range["C3"].Text = "Temperature,deg.F";

            sheet.Range["A4"].Text = "Jan";
            sheet.Range["A5"].Text = "Feb";
            sheet.Range["A6"].Text = "March";
            sheet.Range["A7"].Text = "Apr";
            sheet.Range["A8"].Text = "May";
            sheet.Range["A9"].Text = "June";
            sheet.Range["A10"].Text = "July";
            sheet.Range["A11"].Text = "Aug";
            sheet.Range["A12"].Text = "Sept";
            sheet.Range["A13"].Text = "Oct";
            sheet.Range["A14"].Text = "Nov";
            sheet.Range["A15"].Text = "Dec";

            sheet.Range["B4"].Number = 10.9;
            sheet.Range["B5"].Number = 8.9;
            sheet.Range["B6"].Number = 8.6;
            sheet.Range["B7"].Number = 4.8;
            sheet.Range["B8"].Number = 3.2;
            sheet.Range["B9"].Number = 1.4;
            sheet.Range["B10"].Number = 0.6;
            sheet.Range["B11"].Number = 0.7;
            sheet.Range["B12"].Number = 1.7;
            sheet.Range["B13"].Number = 5.4;
            sheet.Range["B14"].Number = 9.0;
            sheet.Range["B15"].Number = 10.4;

            sheet.Range["C4"].Number = 47.5;
            sheet.Range["C5"].Number = 48.7;
            sheet.Range["C6"].Number = 48.9;
            sheet.Range["C7"].Number = 50.2;
            sheet.Range["C8"].Number = 53.1;
            sheet.Range["C9"].Number = 56.3;
            sheet.Range["C10"].Number = 58.1;
            sheet.Range["C11"].Number = 59.0;
            sheet.Range["C12"].Number = 58.5;
            sheet.Range["C13"].Number = 55.4;
            sheet.Range["C14"].Number = 51.1;
            sheet.Range["C15"].Number = 47.8;
            sheet.UsedRange.AutofitColumns();
        }
    }
}
Imports System.Drawing.Imaging
Imports System.IO
Imports System.Text

Imports OfficeComponent.Excel
Imports OfficeComponent.Excel.Charts

Namespace OfficeComponent.Samples
	Friend Class StockChartsExample
		Inherits ExcelExampleBase
		Public Type As Integer

		Public Sub New(ByVal commonDataPath As String, ByVal outputDir As String)
			MyBase.New(commonDataPath, outputDir)
		End Sub

		Public Sub New(ByVal commonDataPath As String, ByVal outputDir As String, ByVal xmlFile As String)
			MyBase.New(commonDataPath, outputDir, xmlFile)

		End Sub

		Public Overrides Function Execute() As String
			Dim manager As New WorkbookManager()

			' Add a workbook.
			Dim workbook As Workbook = manager.Workbooks.Add()
			workbook.Version = SaveAsFormat

			Dim sheet As IWorksheet = workbook.Worksheets(0)

			CreateChartData(sheet)

			' Hide gridlines.
			sheet.IsGridLinesVisible = False

'			#Region "Chart"
			' Create Chart

			Dim chart As IChart = workbook.Charts.Add()
			chart.Name = "Stock Chart"
			chart.ChartTitle = "Stock Chart - Sales by Region"

			chart.DataRange = sheet.Range("A3:C15")

			Select Case Type
				Case 0
					chart.ChartType = ChartType.Stock_HighLowClose

				Case 1
					chart.ChartType = ChartType.Stock_OpenHighLowClose

				Case 2
					chart.ChartType = ChartType.Stock_VolumeHighLowClose

				Case 3
					chart.ChartType = ChartType.Stock_VolumeOpenHighLowClose

			End Select

			chart.Legend.Position = ExcelLegendPosition.Bottom
			chart.Legend.IsVerticalLegend = False

			chart.Activate()

'			#End Region

			Dim fileName As String = OutputDir & "\" & Me.GetType().Name & "_" & Guid.NewGuid().ToString() & GetExtension(SaveAsFormat)
			' Save the document.
			workbook.SaveAs(fileName)

			' Close the document.
			workbook.Close()

			Return fileName
		End Function

		Private Sub CreateChartData(ByVal sheet As IWorksheet)
			'Put values for rows in column A
			sheet.Range("B3").Text = "Precipitation,in."
			sheet.Range("C3").Text = "Temperature,deg.F"

			sheet.Range("A4").Text = "Jan"
			sheet.Range("A5").Text = "Feb"
			sheet.Range("A6").Text = "March"
			sheet.Range("A7").Text = "Apr"
			sheet.Range("A8").Text = "May"
			sheet.Range("A9").Text = "June"
			sheet.Range("A10").Text = "July"
			sheet.Range("A11").Text = "Aug"
			sheet.Range("A12").Text = "Sept"
			sheet.Range("A13").Text = "Oct"
			sheet.Range("A14").Text = "Nov"
			sheet.Range("A15").Text = "Dec"

			sheet.Range("B4").Number = 10.9
			sheet.Range("B5").Number = 8.9
			sheet.Range("B6").Number = 8.6
			sheet.Range("B7").Number = 4.8
			sheet.Range("B8").Number = 3.2
			sheet.Range("B9").Number = 1.4
			sheet.Range("B10").Number = 0.6
			sheet.Range("B11").Number = 0.7
			sheet.Range("B12").Number = 1.7
			sheet.Range("B13").Number = 5.4
			sheet.Range("B14").Number = 9.0
			sheet.Range("B15").Number = 10.4

			sheet.Range("C4").Number = 47.5
			sheet.Range("C5").Number = 48.7
			sheet.Range("C6").Number = 48.9
			sheet.Range("C7").Number = 50.2
			sheet.Range("C8").Number = 53.1
			sheet.Range("C9").Number = 56.3
			sheet.Range("C10").Number = 58.1
			sheet.Range("C11").Number = 59.0
			sheet.Range("C12").Number = 58.5
			sheet.Range("C13").Number = 55.4
			sheet.Range("C14").Number = 51.1
			sheet.Range("C15").Number = 47.8
			sheet.UsedRange.AutofitColumns()
		End Sub
	End Class
End Namespace