You might want to display multiple columns of data in a cell and limit the user choices. Spread ASP.NET allows you to do this with a multiple-column combo box cell.

You can create a combo box cell with multiple columns in the drop-down list. You can provide a drop-down list and allow the user to choose from a displayed list.

Specify the list of items by binding the cell. You can also choose which column is displayed in the edit area of the cell with the ColumnEditName property.

The multiple-column combo cell can be bound to data by setting the DataSource, DataSourceID, DataMember, DataColumn, or DataColumnName property.

The following properties are available for the multiple-column combo cell and are located in the MultiColumnComboBoxCellType class:
NameDescription
ColumnEditGets or sets which list column (by index) serves as the edit portion of the combo box.
ColumnEditNameGets or sets which list column (by name) serves as the edit portion of the combo box.
ColumnWidthsGets or sets the widths (in pixels) of the columns.
DataColumnGets or sets which list column (by index) serves as the data portion of the combo box.
DataColumnNameGets or sets which list column (by name) serves as the data portion of the combo box.
DataMemberGets or sets the name of the data member that populates the list in the list portion of the combo box.
DataSourceGets or sets the data source for the list portion of the combo box.
DataSourceIDGets or sets the unique identifier of the data source for the MultiColumnComboBoxCellType.
EditorClientScriptUrlGets the URL of the client script file for the editor, if the editor supports client-side scripting. (Inherited from FarPoint.Web.Spread.BaseCellType)
ListAlignmentGets or sets to which side of the editor the list aligns.
ListHeightGets or sets the height (in pixels) of the list.
ListOffsetGets or sets how much (in pixels) the list offsets from the editor.
ListWidthGets or sets the width (in pixels) of the list.
RendererClientScriptUrlGets the URL of the client script file for the renderer, if the renderer supports client-side scripting. (Inherited from FarPoint.Web.Spread.BaseCellType)
ShowButtonGets or sets whether to display the button.
UseValueGets or sets whether to use the value.
VerticalAlignGets or sets the vertical alignment of content in MultiColumnComboBox items.

The multiple-column combo cell returns the column edit value (the value which appears in the cell) as the postback data. This is the value returned from the EditValues property inside the UpdateCommand event. Set the UseValue property to true, to use the DataColumn or DataColumnName (instead of ColumnEdit or ColumnEditName) value as the postback data.

You can use the Spread Designer to set the cell type and most of the properties for the cell type. Setting the DataSource property for a cell requires code.

You can set cell types in the Spread Designer using the following steps:

  1. In the work area, select the cell or cells for which you want to set the cell type.

  2. Select the Home menu.

  3. Select the SetCellType icon under the CellType section.

  4. Select the cell type and any other cell properties.

  5. Select OK to close the dialog.

  6. Click Apply and Exit to close the Spread Designer.


You can also right-click on the cell or cells in the work area and then set the cell using the CellType tab.

Spread Designer


You can also use the Property Grid in the Spread Designer to set the cell type.

Property Grid


This example binds the multiple-column combo box cell to a data source.

Multiple-Column Combo Cell


C#

protected void Page_Load(object sender, EventArgs e)
{
if (this.IsPostBack) return;
//Add sample data
string conStr = "Provider=Microsoft.JET.OLEDB.4.0;data source= C:\\Program Files (x86)\\GrapeCity\\Spread Studio 8\\Common\\nwind.mdb";
string sqlStr = "Select OrderID, CustomerID, ShipName, Freight from Orders";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(conStr);
System.Data.DataSet ds = new System.Data.DataSet();
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sqlStr, conn);
da.Fill(ds);
FpSpread1.ActiveSheetView.DefaultStyle.Font.Name ="Calibri";

FarPoint.Web.Spread.MultiColumnComboBoxCellType cb = new FarPoint.Web.Spread.MultiColumnComboBoxCellType();
cb.DataSource = ds;
cb.ShowButton = true;
cb.ColumnEditName = "ShipName";
FpSpread1.ActiveSheetView.Cells[0, 0].CellType = cb;
FpSpread1.ActiveSheetView.Columns[0].Width = 300;
}


VB

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If (IsPostBack) Then
Return
End If

'Add sample data
Dim conStr As String = "Provider=Microsoft.JET.OLEDB.4.0;data source= C:\Program Files (x86)\GrapeCity\Spread Studio 8\Common\nwind.mdb"
Dim sqlStr As String = "Select OrderID, CustomerID, ShipName, Freight from Orders"
Dim conn As New System.Data.OleDb.OleDbConnection(conStr)
Dim ds As New DataSet()
Dim da As New System.Data.OleDb.OleDbDataAdapter(sqlStr, conn)
da.Fill(ds)
FpSpread1.ActiveSheetView.DefaultStyle.Font.Name = "Calibri"

Dim cb As New FarPoint.Web.Spread.MultiColumnComboBoxCellType()
cb.DataSource = ds
cb.ShowButton = True
cb.ColumnEditName = "ShipName"
FpSpread1.ActiveSheetView.Cells(0, 0).CellType = cb
FpSpread1.ActiveSheetView.Columns(0).Width = 300
End Sub


This example sets the UseValue property and returns the cell value using the UpdateCommand event.

Multiple-Column Combo Cell


C#
protected void Page_Load(object sender, EventArgs e)
{
if (this.IsPostBack) return;

System.Data.DataSet ds = new System.Data.DataSet();
System.Data.DataTable name;
System.Data.DataTable city;
name = ds.Tables.Add("Customers");
name.Columns.AddRange(new System.Data.DataColumn[] { new System.Data.DataColumn("LastName", typeof(string)), new System.Data.DataColumn("FirstName", typeof(string)), new System.Data.DataColumn("ID", typeof(Int32)) });
name.Rows.Add(new object[] { "Fielding", "William", 0 });
name.Rows.Add(new object[] { "Williams", "Arthur", 1 });
name.Rows.Add(new object[] { "Zuchini", "Theodore", 2 });
city = ds.Tables.Add("City/State");
city.Columns.AddRange(new System.Data.DataColumn[] { new System.Data.DataColumn("City", typeof(string)), new System.Data.DataColumn("Owner", typeof(Int32)), new System.Data.DataColumn("State", typeof(string)) });
city.Rows.Add(new object[] { "Atlanta", 0, "Georgia" });
city.Rows.Add(new object[] { "Boston", 1, "Mass." });
city.Rows.Add(new object[] { "Tampa", 2, "Fla." });

FarPoint.Web.Spread.MultiColumnComboBoxCellType cb = new FarPoint.Web.Spread.MultiColumnComboBoxCellType();
cb.DataSource = ds;
cb.ShowButton = true;
cb.DataMember = "City/State";
cb.UseValue = true;
//Displays the State after a list item is selected
cb.ColumnEditName = "State";
FpSpread1.ActiveSheetView.Cells[0, 0].CellType = cb;
FpSpread1.ActiveSheetView.Columns[0].Width = 280;
}

protected void FpSpread1_UpdateCommand(object sender, FarPoint.Web.Spread.SpreadCommandEventArgs e)
{
int colcnt;
int i;
string strvalue;
int r = (int)e.CommandArgument;
colcnt = e.EditValues.Count - 1;
for (i = 0; i <= colcnt; i++)
{
if (!object.ReferenceEquals(e.EditValues[i], FarPoint.Web.Spread.FpSpread.Unchanged))
strvalue = e.EditValues[i].ToString();
}
}


VB
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If (IsPostBack) Then
Return
End If

Dim ds As New System.Data.DataSet
Dim name As DataTable
Dim city As DataTable
name = ds.Tables.Add("Customers")
name.Columns.AddRange(New DataColumn() {New DataColumn("LastName", Type.GetType("System.String")), New DataColumn("FirstName",
Type.GetType("System.String")), New DataColumn("ID", Type.GetType("System.Int32"))})
name.Rows.Add(New Object() {"Fielding", "William", 0})
name.Rows.Add(New Object() {"Williams", "Arthur", 1})
name.Rows.Add(New Object() {"Zuchini", "Theodore", 2})
city = ds.Tables.Add("City/State")
city.Columns.AddRange(New DataColumn() {New DataColumn("City", Type.GetType("System.String")), New DataColumn("Owner", Type.GetType("System.Int32")),
New DataColumn("State", Type.GetType("System.String"))})
city.Rows.Add(New Object() {"Atlanta", 0, "Georgia"})
city.Rows.Add(New Object() {"Boston", 1, "Mass."})
city.Rows.Add(New Object() {"Tampa", 2, "Fla."})

Dim cb As New FarPoint.Web.Spread.MultiColumnComboBoxCellType()
cb.DataSource = ds
cb.ShowButton = True
cb.DataMember = "City/State"
cb.UseValue = True
'Displays the State after a list item Is selected
cb.ColumnEditName = "State"
FpSpread1.ActiveSheetView.Cells(0, 0).CellType = cb
FpSpread1.ActiveSheetView.Columns(0).Width = 280
End Sub

Protected Sub FpSpread1_UpdateCommand(sender As Object, e As SpreadCommandEventArgs) Handles FpSpread1.UpdateCommand
Dim colcnt As Integer
Dim i As Integer
Dim strvalue As String
Dim r As Integer = CInt(e.CommandArgument)
colcnt = e.EditValues.Count - 1
If Not Object.ReferenceEquals(e.EditValues(i), FarPoint.Web.Spread.FpSpread.Unchanged) Then
strvalue = e.EditValues(i).ToString()
End If
End Sub