Scatter Charts

This example demonstrates how to create an Scatter Chart.

Often your first step in any regression analysis is to create a scatter plot, which lets you visually explore association between two sets of values

Dropdown have types of scatter chart

  • Scatter line
  • Scatter line markers
  • Scatter markers
  • Scatter smoothed line
  • Scatter smoothed line markers

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 a generate button will launch an excel with above choices an scatter chart is drawn in excel

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 ScatterChartsExample : ExcelExampleBase
    {
        public int Type;

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

        public ScatterChartsExample(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 = "Scatter Chart";
            chart.ChartTitle = "Scatter Chart - Sales by Region";

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

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

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

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

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

                case 4:
                    chart.ChartType = ChartType.Scatter_SmoothedLine_Markers;
                    break;
            }

            chart.IsSeriesInRows = true;
            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 ScatterChartsExample
		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 = "Scatter Chart"
			chart.ChartTitle = "Scatter Chart - Sales by Region"

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

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

				Case 1
					chart.ChartType = ChartType.Scatter_Line_Markers

				Case 2
					chart.ChartType = ChartType.Scatter_Markers

				Case 3
					chart.ChartType = ChartType.Scatter_SmoothedLine

				Case 4
					chart.ChartType = ChartType.Scatter_SmoothedLine_Markers
			End Select

			chart.IsSeriesInRows = True
			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