Binding a Cell Range in Spread as a Data Source to an External Control

You can bind a range of cells in Spread as a data source for an external control such as a DataGrid control. The following diagram shows the objects involved.

Binding Spread Cell Range as a Data Source to an External Control

These are the objects involved:

SpreadDataView
SpreadDataRowView
ISpreadDataViewDataFormatter
ISpreadDataViewMapper
DefaultSpreadDataViewDataFormatter
DefaultSpreadDataViewMapper

Creating a Custom Formatter

You can create a custom formatter class by inheriting from ISpreadDataViewDataFormatter.

Using Code

Create the class.
Assign the class to the data view.

Example

This example code creates a custom class.

C#	Copy Code
public class MySpreadDataViewDataFormatter : ISpreadDataViewDataFormatter { private SpreadDataColumn column; private SheetView sheetView; public SheetView SheetView; { get { return sheetView; } set { sheetView = value; } } public MySpreadDataViewDataFormatter(SpreadDataColumn ownerColumn,SheetView sheetView) { if (ownerColumn == null) { throw new ArgumentNullException("ownerColumn"); } column = ownerColumn; this.SheetView = sheetView; } public object GetCellValue(Cell cell) { object ret = null; try { ret = this.SheetView.GetValue(cell.Row.Index, cell.Column.Index); ret += ": Customized format"; } catch { ret = " No value"; } return ret; } public void SetCellValue(Cell cell, object value) { this.SheetView.SetValue(cell.Row.Index, cell.Column.Index, value + ": Customized format"); } } // Assign new formatter dataSet = BuildDataSet(5,5); this.spreadDataBindingAdapter1.Spread = this.fpSpread1; this.spreadDataBindingAdapter1.SheetName = this.fpSpread1.ActiveSheet.SheetName; this.spreadDataBindingAdapter1.DataSource = dataSet.Tables[0]; spreadDataBindingAdapter1.MapperInfo = new MapperInfo(1, 2, 3, 4); MySpreadDataViewDataFormatter testFormatter = new MySpreadDataViewDataFormatter (this.spreadDataBindingAdapter1.SpreadDataView.Columns[2], fpSpread1.ActiveSheet); this.spreadDataBindingAdapter1.SpreadDataView.Columns[2].Formatter = testFormatter; this.spreadDataBindingAdapter1.FillSpreadDataByDataSource();

Copy Code

public class MySpreadDataViewDataFormatter : ISpreadDataViewDataFormatter
{
private SpreadDataColumn column;
private SheetView sheetView;
public SheetView SheetView;
{
get { return sheetView; }
set { sheetView = value; }
}
public MySpreadDataViewDataFormatter(SpreadDataColumn ownerColumn,SheetView sheetView)
{
if (ownerColumn == null)
{
throw new ArgumentNullException("ownerColumn");
}
column = ownerColumn;
this.SheetView = sheetView;
}
public object GetCellValue(Cell cell)
{
object ret = null;
try
{
ret = this.SheetView.GetValue(cell.Row.Index, cell.Column.Index);
ret += ": Customized format";
}
catch
{
ret = " No value";
}
return ret;
}
public void SetCellValue(Cell cell, object value)
{
this.SheetView.SetValue(cell.Row.Index, cell.Column.Index, value + ": Customized format");
}
}
// Assign new formatter
dataSet = BuildDataSet(5,5);
this.spreadDataBindingAdapter1.Spread = this.fpSpread1;
this.spreadDataBindingAdapter1.SheetName = this.fpSpread1.ActiveSheet.SheetName;
this.spreadDataBindingAdapter1.DataSource = dataSet.Tables[0];
spreadDataBindingAdapter1.MapperInfo = new MapperInfo(1, 2, 3, 4);
MySpreadDataViewDataFormatter testFormatter = new MySpreadDataViewDataFormatter
(this.spreadDataBindingAdapter1.SpreadDataView.Columns[2], fpSpread1.ActiveSheet);
this.spreadDataBindingAdapter1.SpreadDataView.Columns[2].Formatter = testFormatter;
this.spreadDataBindingAdapter1.FillSpreadDataByDataSource();

Creating a Custom Mapper

You can create a custom mapper class by inheriting from ISpreadDataViewMapper.

Using Code

Create the class.
Assign the class to the data view.

Example

This example code creates a custom class.

C#	Copy Code
public class MySpreadDataViewMapper : ISpreadDataViewMapper { ... } //Assign customized Mapper for SpreadDataView dataSet = BuildDataSet(5,5); this.spreadDataBindingAdapter1.Spread = this.fpSpread1; this.spreadDataBindingAdapter1.SheetName = this.fpSpread1.ActiveSheet.SheetName; this.spreadDataBindingAdapter1.DataSource = dataSet.Tables[0]; MySpreadDataViewMapper testMapper = new MySpreadDataViewMapper (); this.spreadDataBindingAdapter1.SpreadDataView.Mapper = testMapper; spreadDataBindingAdapter1.MapperInfo = new MapperInfo(1, 2, 3, 4); this.spreadDataBindingAdapter1.FillSpreadDataByDataSource();

Copy Code

public class MySpreadDataViewMapper : ISpreadDataViewMapper
{
...
}
//Assign customized Mapper for SpreadDataView
dataSet = BuildDataSet(5,5);
this.spreadDataBindingAdapter1.Spread = this.fpSpread1;
this.spreadDataBindingAdapter1.SheetName = this.fpSpread1.ActiveSheet.SheetName;
this.spreadDataBindingAdapter1.DataSource = dataSet.Tables[0];
MySpreadDataViewMapper testMapper = new MySpreadDataViewMapper ();
this.spreadDataBindingAdapter1.SpreadDataView.Mapper = testMapper;
spreadDataBindingAdapter1.MapperInfo = new MapperInfo(1, 2, 3, 4);
this.spreadDataBindingAdapter1.FillSpreadDataByDataSource();

VB	Copy Code
Public Class MySpreadDataViewMapper Implements FarPoint.Win.Spread.Data.ISpreadDataViewMapper ... End Class dataSet = BuildDataSet(5, 5) Me.spreadDataBindingAdapter1.Spread = Me.fpSpread1 Me.spreadDataBindingAdapter1.SheetName = Me.fpSpread1.ActiveSheet.SheetName Me.spreadDataBindingAdapter1.DataSource = dataSet.Tables(0) Dim testMapper As New MySpreadDataViewMapper() Me.spreadDataBindingAdapter1.SpreadDataView.Mapper = testMapper spreadDataBindingAdapter1.MapperInfo = New MapperInfo(1, 2, 3, 4) Me.spreadDataBindingAdapter1.FillSpreadDataByDataSource()

Copy Code

Public Class MySpreadDataViewMapper
Implements FarPoint.Win.Spread.Data.ISpreadDataViewMapper
...
End Class
dataSet = BuildDataSet(5, 5)
Me.spreadDataBindingAdapter1.Spread = Me.fpSpread1
Me.spreadDataBindingAdapter1.SheetName = Me.fpSpread1.ActiveSheet.SheetName
Me.spreadDataBindingAdapter1.DataSource = dataSet.Tables(0)
Dim testMapper As New MySpreadDataViewMapper()
Me.spreadDataBindingAdapter1.SpreadDataView.Mapper = testMapper
spreadDataBindingAdapter1.MapperInfo = New MapperInfo(1, 2, 3, 4)
Me.spreadDataBindingAdapter1.FillSpreadDataByDataSource()