Spread Windows Forms 17
Spread Windows Forms 17.0 Product Documentation / Developer's Guide / Chart Control / Creating Charts / Connecting to Data / Binding with cell range
In This Topic
    Binding with cell range
    In This Topic

    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.

    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.

    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.

    C#
    Copy Code 
    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);
}
    Visual Basic
    Copy Code 
    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.

    C#
    Copy Code 
    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);
    Visual Basic
    Copy Code 
    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)

     

    See Also
    Using a Bound Data Source
    Using an UnBound Data Source