Spread Windows Forms 18 Product Documentation

Binding with cell range

A series contains three parts (category, series name, and data). You can bind each part to an instance of the series data field. The entire chart control can not be bound; however, you can use a cell range or a formula to put data in the chart.

You can use an instance of the SeriesDataField class to bind each part into a cell ranges.

!type=note
Note: In addition to using the SeriesDataField class to bind each series to a range of cells, you can also use AddChart method of the SheetView class to add a chart bound to a range of cells to the sheet. In such a case, a series is automatically created from the target cell range.

Using the code

Set the SeriesDataField object to the following property of the target series (for example, the LineSeries class for line charts). In the Formula property of the SeriesDataField object, specify the binding cell rangs or the formula.

SeriesNameDataSource property
Binds series names into a range of cells.
DataSource property of the StringCollection object referenced in the CategoryNames property
Binds categories into a range of cells.
DataSource property of the DoubleCollection object referenced in the Values property
Binds the data into a range of cells.

Example

The following example first uses the AddChart method of the SheetView class in the form's Load event to add a chart bound to a range of cells.

Then in the button's Click event, add data to the cell and bind the chart to the new cell range. Use the SeriesDataField object to bind existing series category names, data, new series name, and data into a range of cells.

private void Form1_Load(object sender, EventArgs e)
{
    object[,] values = { { "", "Category-1", "Category-2" }, { "Series-A", 2.0, 5.0 }, { "Series-B", 4.0, 5.0 } };
    fpSpread1.Sheets[0].SetArray(0, 0, values);
    FarPoint.Win.Spread.Model.CellRange cellRange = new FarPoint.Win.Spread.Model.CellRange(0, 0, values.GetLength(0), values.GetLength(1));
    fpSpread1.Sheets[0].AddChart(cellRange, typeof(FarPoint.Win.Chart.LineSeries), 400, 400, 100, 100);
}
private void button1_Click(object sender, EventArgs e)
{
    fpSpread1.Sheets[0].SetArray(0, 3, new object[,] { { "Category-3" }, { 4.0 }, { 2.0 } });
    fpSpread1.Sheets[0].SetArray(3, 0, new object[,] { { "Series-C", 3.0, 2.0, 1.0 } });
    FarPoint.Win.Chart.YPlotArea plotArea = (FarPoint.Win.Chart.YPlotArea)fpSpread1.Sheets[0].Charts[0].Model.PlotAreas[0];
    FarPoint.Win.Chart.LineSeries series;
    FarPoint.Win.Spread.Chart.SeriesDataField data;
    series = (FarPoint.Win.Chart.LineSeries)plotArea.Series[0];
    series.CategoryNames.DataSource = new FarPoint.Win.Spread.Chart.SeriesDataField(fpSpread1, "DataFieldCategoryName", "Sheet1!$B$1:$D$1", FarPoint.Win.Spread.Chart.SegmentDataType.Text);
    data = (FarPoint.Win.Spread.Chart.SeriesDataField)series.Values.DataSource;
    data.Formula = "Sheet1!$B$2:$D$2";
    series = (FarPoint.Win.Chart.LineSeries)plotArea.Series[1];
    data = (FarPoint.Win.Spread.Chart.SeriesDataField)series.Values.DataSource;
    data.Formula = "Sheet1!$B$3:$D$3";
    series = new FarPoint.Win.Chart.LineSeries();
    series.SeriesNameDataSource = new FarPoint.Win.Spread.Chart.SeriesDataField(fpSpread1, "DataFieldSeriesName", "Sheet1!$A$4:$A$4", FarPoint.Win.Spread.Chart.SegmentDataType.Text);
    series.Values.DataSource = new FarPoint.Win.Spread.Chart.SeriesDataField(fpSpread1, "DataFieldValue", "Sheet1!$B$4:$D$4");
    plotArea.Series.Add(series);
}

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim values As Object(,) = {{"", "Category-1", "Category-2"}, {"Series-A", 2.0, 5.0}, {"Series-B", 4.0, 5.0}}
    FpSpread1.Sheets(0).SetArray(0, 0, values)
    Dim cellRange As New FarPoint.Win.Spread.Model.CellRange(0, 0, values.GetLength(0), values.GetLength(1))
    FpSpread1.Sheets(0).AddChart(cellRange, GetType(FarPoint.Win.Chart.LineSeries), 400, 400, 100, 100)
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    FpSpread1.Sheets(0).SetArray(0, 3, New Object(,) {{"Category-3"}, {4.0}, {2.0}})
    FpSpread1.Sheets(0).SetArray(3, 0, New Object(,) {{"Series-C", 3.0, 2.0, 1.0}})
    Dim plotArea As FarPoint.Win.Chart.YPlotArea = DirectCast(FpSpread1.Sheets(0).Charts(0).Model.PlotAreas(0), FarPoint.Win.Chart.YPlotArea)
    Dim series As FarPoint.Win.Chart.LineSeries
    Dim data As FarPoint.Win.Spread.Chart.SeriesDataField
    series = DirectCast(plotArea.Series(0), FarPoint.Win.Chart.LineSeries)
    series.CategoryNames.DataSource = New FarPoint.Win.Spread.Chart.SeriesDataField(FpSpread1, "DataFieldCategoryName", "Sheet1!$B$1:$D$1", FarPoint.Win.Spread.Chart.SegmentDataType.Text)
    data = DirectCast(series.Values.DataSource, FarPoint.Win.Spread.Chart.SeriesDataField)
    data.Formula = "Sheet1!$B$2:$D$2"
    series = DirectCast(plotArea.Series(1), FarPoint.Win.Chart.LineSeries)
    data = DirectCast(series.Values.DataSource, FarPoint.Win.Spread.Chart.SeriesDataField)
    data.Formula = "Sheet1!$B$3:$D$3"
    series = New FarPoint.Win.Chart.LineSeries()
    series.SeriesNameDataSource = New FarPoint.Win.Spread.Chart.SeriesDataField(FpSpread1, "DataFieldSeriesName", "Sheet1!$A$4:$A$4", FarPoint.Win.Spread.Chart.SegmentDataType.Text)
    series.Values.DataSource = New FarPoint.Win.Spread.Chart.SeriesDataField(FpSpread1, "DataFieldValue", "Sheet1!$B$4:$D$4")
    plotArea.Series.Add(series)
End Sub

The following sample code binds a control to a data table. The data in the data table is set in the cell. A chart using these data is added by the AddChart method of the SheetView class.

DataTable dt = new DataTable("Test");
DataRow dr = default(DataRow);
dt.Columns.Add("Series1");
dt.Columns.Add("Series2");
dr = dt.NewRow();
dr[0] = 1;
dr[1] = 4;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr[0] = 2;
dr[1] = 5;
dt.Rows.Add(dr);
dr = dt.NewRow();           
dr[0] = 3;
dr[1] = 6;           
dt.Rows.Add(dr);
fpSpread1.DataSource = dt;
FarPoint.Win.Spread.Model.CellRange cellRange = new FarPoint.Win.Spread.Model.CellRange(0, 0, 3, 3);
fpSpread1.Sheets[0].AddChart(cellRange, typeof(FarPoint.Win.Chart.PointSeries), 400, 400, 0, 0);

Dim dt As New DataTable("Test")
Dim dr As DataRow
dt.Columns.Add("Series1")
dt.Columns.Add("Series2")
dr = dt.NewRow()
dr(0) = 1
dr(1) = 4
dt.Rows.Add(dr)
dr = dt.NewRow()
dr(0) = 2
dr(1) = 5
dt.Rows.Add(dr)
dr = dt.NewRow()
dr(0) = 3
dr(1) = 6
dt.Rows.Add(dr)
FpSpread1.DataSource = dt
Dim cellRange As New FarPoint.Win.Spread.Model.CellRange(0, 0, 3, 3)
FpSpread1.Sheets(0).AddChart(cellRange, GetType(FarPoint.Win.Chart.PointSeries), 400, 400, 0, 0)

Binding with cell range

Using the code

Example

See Also