Spread Windows Forms 18 Product Documentation

Adding ChartSheet

ChartSheet represents a sheet that contains only a chart. To add a ChartSheet in the workbook, users first need to set the value of the EnhancedShapeEngine property to True in order to enable the new shape engine. You can then add a ChartSheet in a workbook using the Charts.Add() method of the IWorkbook Interface. Here, the Charts property returns the collection of charts in the specified workbook.

Note that you can fit the chart to the current viewport in the ChartSheet by setting the value of the View.ZoomToFit property to true as shown below:

fpSpread1.AsWorkbook().Charts[0].View.ZoomToFit = true;

FpSpread1.AsWorkbook().Charts(0).View.ZoomToFit = True

However, if you want to embed a chart in a worksheet that contains other items, like gridlines, cells, data, etc., then you need to add a Chart to a worksheet instead.

The image below depicts a sample ChartSheet.

The following sub-sections discuss various ChartSheet operations in a workbook.

Adding ChartSheet Using Code

A ChartSheet can be added before or after the active sheet. While adding, you can specify the position and number of ChartSheets to add.

The following example code displays different scenarios of adding ChartSheets.

fpSpread1.Features.EnhancedShapeEngine = true;
fpSpread1.AsWorkbook().ActiveSheet.SetValue(1, 1, new int[,] { { 1, 4 }, { 2, 5 }, { 3, 6 } });
      
//Add a chart before the active sheet
fpSpread1.AsWorkbook().Charts.Add();
fpSpread1.Sheets[0].Charts[0].DataFormula = "Sheet1!B2:C4";
fpSpread1.Sheets[0].Charts[0].ViewType = ChartViewType.View3D;
         
//Add 2 charts before Sheet1
fpSpread1.AsWorkbook().Charts.Add(before: 1, count: 2);
//or
//fpSpread1.AsWorkbook().Charts.Add(before: "Sheet1", count: 2);
fpSpread1.Sheets[1].Charts[0].DataFormula = "Sheet1!B2:B4";
fpSpread1.Sheets[2].Charts[0].DataFormula = "Sheet1!C2:C4";
//Add one chart after Sheet1
fpSpread1.AsWorkbook().Charts.Add(after: 3, count: 1);
//or
//fpSpread1.AsWorkbook().Charts.Add(after: "Sheet1", count: 1);
fpSpread1.Sheets[4].Charts[0].DataFormula = "Sheet1!B2:C2";

FpSpread1.Features.EnhancedShapeEngine = True
FpSpread1.AsWorkbook().ActiveSheet.SetValue(1, 1, New Integer(,) {
{1, 4},
{2, 5},
{3, 6}})
'Add a chart before the active sheet
FpSpread1.AsWorkbook().Charts.Add()
FpSpread1.Sheets(0).Charts(0).DataFormula = "Sheet1!B2:C4"
FpSpread1.Sheets(0).Charts(0).ViewType = ChartViewType.View3D
'Add 2 charts before Sheet1 
FpSpread1.AsWorkbook().Charts.Add(before:=1, count:=2)
FpSpread1.Sheets(1).Charts(0).DataFormula = "Sheet1!B2:B4"
FpSpread1.Sheets(2).Charts(0).DataFormula = "Sheet1!C2:C4"
'Add one chart after Sheet1
FpSpread1.AsWorkbook().Charts.Add(after:=3, count:=1)
'or
'FpSpread1.AsWorkbook().Charts.Add(after:="Sheet1", count:=1);
FpSpread1.Sheets(4).Charts(0).DataFormula = "Sheet1!B2:C2"

Adding ChartSheet at Runtime

You can add a ChartSheet in a workbook at runtime as well by following the steps below.

Run the code below to load a spread control with EnhancedShapeEngine and TabStrip editing enabled.

fpSpread1.Features.EnhancedShapeEngine = true;
fpSpread1.TabStrip.Editable = true;

fpSpread1.Features.EnhancedShapeEngine = True
    fpSpread1.TabStrip.Editable = True

Right-click on Sheet1 in tab strip.
Choose the Insert option from the menu.
On the Insert dialog, select Chart and then click OK.
A new ChartSheet has been created as Chart1 before Sheet1.
Right-click on the Chart Area and choose the Select Data… option to select the data source.
Now go to Sheet1 containing data and select the required range.
The reference will appear in the text box in the Select Data Source dialog.
Click OK. The chart will appear in the ChartSheet.

The following GIF illustrates how to add a ChartSheet in a workbook at runtime.

Move Chart to ChartSheet

If a chart already exists in a worksheet, Spread also lets you move it to a ChartSheet.

The following example code depicts how to move a chart from the worksheet to the ChartSheet.

fpSpread1.Features.EnhancedShapeEngine = true;
fpSpread1.AsWorkbook().ActiveSheet.SetValue(1, 1, new int[,] { { 1, 4 }, { 2, 3 }, { 3, 2 } });
fpSpread1.AsWorkbook().Charts.Add(before: 0, count: 2);
fpSpread1.Sheets[0].Charts[0].DataFormula = "Sheet1!C2:C3";
fpSpread1.Sheets[0].Charts[0].ViewType = ChartViewType.View3D;
fpSpread1.Sheets[1].Charts[0].DataFormula = "Sheet1!B2:B4";
IChart chart1 = fpSpread1.AsWorkbook().Charts[1];
IChart chart2 = fpSpread1.AsWorkbook().Charts[0];
chart1.Location(ChartLocation.Object, "Chart2");  // To move Chart1 to Chart2
chart2.Location(ChartLocation.NewSheet, "NewChartSheet"); // To move Chart2 to new sheet

fpSpread1.Features.EnhancedShapeEngine = True
fpSpread1.AsWorkbook().ActiveSheet.SetValue(1, 1, New Integer(,) {
{1, 4},
{2, 3},
{3, 2}})
fpSpread1.AsWorkbook().Charts.Add(before:=0, count:=2)
fpSpread1.Sheets(0).Charts(0).DataFormula = "Sheet1!C2:C3"
fpSpread1.Sheets(0).Charts(0).ViewType = ChartViewType.View3D
fpSpread1.Sheets(1).Charts(0).DataFormula = "Sheet1!B2:B4"
Dim chart1 As IChart = fpSpread1.AsWorkbook().Charts(1)
Dim chart2 As IChart = fpSpread1.AsWorkbook().Charts(0)
chart1.Location(ChartLocation.Object, "Chart2") ' To move Chart1 to Chart2
chart2.Location(ChartLocation.NewSheet, "NewChartSheet") ' To move Chart2 to new sheet

Get ChartSheet from Charts/Sheets Collection

A ChartSheet can also be obtained from a charts collection as well as from the Sheets collection.

The following example code depicts how to retrieve ChartSheet from the Charts collection and from the Sheets collection.

fpSpread1.Features.EnhancedShapeEngine = true;
fpSpread1.AsWorkbook().ActiveSheet.SetValue(1, 1, new int[,] { { 1, 4 }, { 2, 5 }, { 3, 6 } });
fpSpread1.AsWorkbook().Charts.Add();
fpSpread1.Sheets[0].Charts[0].DataFormula = "Sheet1!B2:C4";
IChart chart1 = fpSpread1.AsWorkbook().Charts[0]; // To get from charts collection
chart1 = (IChart)fpSpread1.AsWorkbook().Sheets[0]; // To get from sheets collection

fpSpread1.Features.EnhancedShapeEngine = True
fpSpread1.AsWorkbook().ActiveSheet.SetValue(1, 1, New Integer(,) {
{1, 4},
{2, 5},
{3, 6}})
fpSpread1.AsWorkbook().Charts.Add()
fpSpread1.Sheets(0).Charts(0).DataFormula = "Sheet1!B2:C4"
Dim chart1 As IChart = fpSpread1.AsWorkbook().Charts(0) ' To get from charts collection
Dim chart1 As IChart = CType(fpSpread1.AsWorkbook().Sheets(0), IChart) ' To get from sheets collection

Adding ChartSheet Using Code
Adding ChartSheet at Runtime
Move Chart to ChartSheet
Get ChartSheet from Charts/Sheets Collection