Spread ASP.NET and Multiple-Column Combo Box Cells

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:

Name

Description

ColumnEdit

Gets or sets which list column (by index) serves as the edit portion of the combo box.

ColumnEditName

Gets or sets which list column (by name) serves as the edit portion of the combo box.

ColumnWidths

Gets or sets the widths (in pixels) of the columns.

DataColumn

Gets or sets which list column (by index) serves as the data portion of the combo box.

DataColumnName

Gets or sets which list column (by name) serves as the data portion of the combo box.

DataMember

Gets or sets the name of the data member that populates the list in the list portion of the combo box.

DataSource

Gets or sets the data source for the list portion of the combo box.

DataSourceID

Gets or sets the unique identifier of the data source for the MultiColumnComboBoxCellType.

EditorClientScriptUrl

Gets the URL of the client script file for the editor, if the editor supports client-side scripting. (Inherited from FarPoint.Web.Spread.BaseCellType)

ListAlignment

Gets or sets to which side of the editor the list aligns.

ListHeight

Gets or sets the height (in pixels) of the list.

ListOffset

Gets or sets how much (in pixels) the list offsets from the editor.

ListWidth

Gets or sets the width (in pixels) of the list.

RendererClientScriptUrl

Gets the URL of the client script file for the renderer, if the renderer supports client-side scripting. (Inherited from FarPoint.Web.Spread.BaseCellType)

ShowButton

Gets or sets whether to display the button.

UseValue

Gets or sets whether to use the value.

VerticalAlign

Gets 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. SpreadASPMultiDesign Spread Designer You can also use the Property Grid in the Spread Designer to set the cell type. SpreadASPMultiDesign1 Property Grid This example binds the multiple-column combo box cell to a data source. SpreadASPMultiC 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. SpreadASPMulti 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

MESCIUS inc.

comments powered by Disqus