You may wish to provide a list of items for the user to select to speed up typing or data entry. You can use automatic completion (type ahead) for user input in the cell with Spread for Windows Forms. Use the IAutoCompleteSupport interface and its properties to provide the automatic completion feature in the editable cell types. There are two main properties that need to be set. First set the automatic completion mode. The options include whether to suggest a list of possible completions, a drop-down list of possible completions, both, or none. Second, set the source of the suggestions and drop-down list. The source is the list of items that are considered for completion. You can create a custom source and define your own list of items or you can set various system sources. There are two properties in the interface that provide settings for the custom source. The first one sets the list of possible candidates for a custom source. The second sets whether to fill the list with the list of values from other cells in the column. To use the values in the cells in the column, for example, you would set the source to custom and then set automatic fill. The automatic fill only adds items to the custom source if they are above or below the cell without a blank cell in between. The following sample uses automatic completion. You can use the combo boxes to choose the fill option, the mode, and the column to apply the automatic completion to. Add three combo boxes to the form named cbAutofillSource, cbAutoCompleteMode, and cbColumn. Add a Spread control. Add a list box named lbCustom. Add the following code and add the appropriate code to the SelectedIndexChanged event for each combo control. 
C#
private void Form1_Load(object sender, EventArgs e) { GetSampleData(); InitData(); }public void GetSampleData() { //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 CompanyName, ContactName, ContactTitle, Country from Customers"; System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(conStr); DataSet ds = new DataSet(); System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sqlStr, conn); da.Fill(ds);fpSpread1.ActiveSheet.DataAutoSizeColumns = true; fpSpread1.ActiveSheet.DataMember = "Patients"; fpSpread1.ActiveSheet.DataSource = ds; //Set the column width to the widest string in the column for (int i = 0; i < fpSpread1.ActiveSheet.Columns.Count; i++) fpSpread1.ActiveSheet.Columns[i].Width = fpSpread1.ActiveSheet.Columns[i].GetPreferredWidth(); //Always be in edit mode when entering a cell fpSpread1.EditModePermanent = true; //Highlight the text for replacing when going into cell fpSpread1.EditModeReplace = true; }public void InitData() { //Autofill DataSource options cbAutofillSource.DataSource = new string[] { "CustomSource and All Column Items Below Cell", "CustomSource Only", "Column Items Below Cell Only" }; cbAutofillSource.SelectedIndex = 0; //AutoComplete Mode options cbAutoCompleteMode.DataSource = new string[] { "None", "Suggest", "Append", "SuggestAppend" }; cbAutoCompleteMode.SelectedIndex = 3; //Column options cbColumn.DataSource = new string[] { "Company (0)", "Contact (1)", "Title (2)", "Country (3)" }; cbColumn.SelectedIndex = 0; }public void CustomAutoComplete(int column, int acm, int AutoFillAutoCompleteCustomSource) { //INPUTS: //_AutoCompleteMode options // "None", "Suggest", "Append", "SuggestAppend" //_AutoFillAutoCompleteCustomSource options // "CustomSource and All Items Below it", "CustomSource Only", "Column Items Below only" //Build the custom source suggestion list string[] mySuggestionList = new string[] {"Benjamin", "Bruce", "Cheryl", "Daniel", "Dave", "Eric", "Hana", "Harol", "Henry", "Jane", "Jessica", "John", "Julia", "Kathy", "Lance", "Laura", "Mark", "Marry", "Meggie", "Mike", "Naomi", "Paul", "Peter", "Roger", "Rose", "Ruth", "Ryan", "Shirley", "Tan", "Thomas", "Tony", "Varun", "$1234", "VND1234", "12345 %", "13 %", "02/19/1991", "Sunday, August 08, 2007", "Saturday, March 09, 1997", "February", "0901234567", "0953336650"}; //Bind listbox to show options to user lbCustom.DataSource = mySuggestionList; //Add the custom source to the collection AutoCompleteStringCollection myAutoCompleteStringCollection = new AutoCompleteStringCollection(); myAutoCompleteStringCollection.AddRange(mySuggestionList); //Create the cell type FarPoint.Win.Spread.CellType.GeneralCellType myAutoFillCellType = new FarPoint.Win.Spread.CellType.GeneralCellType(); //AutoCompleteSource sets what source to use for the AutoComplete list items myAutoFillCellType.AutoCompleteSource = AutoCompleteSource.CustomSource; //AutoCompleteCustomSource specifies a list of items to use when AutoCompleteSource is set to //Custom// if (AutoFillAutoCompleteCustomSource == 2) //"Column Items Below only" //Set this to nothing to use the items below in this column only - no custom lists! myAutoFillCellType.AutoCompleteCustomSource = null; else //Use the custom list in conjunction with AutoFillAutoCompleteCustomSource setting myAutoFillCellType.AutoCompleteCustomSource = myAutoCompleteStringCollection; //AutoFillAutoCompleteCustomSource determines whether the custom list is automatically filled with data from the column // true = use the custom source AND the column list below it // false = use custom source list only if (AutoFillAutoCompleteCustomSource == 1) //CustomSource Only //Use custom source list only myAutoFillCellType.AutoFillAutoCompleteCustomSource = false; else //Use the custom source AND the column list below it myAutoFillCellType.AutoFillAutoCompleteCustomSource = true; //AutoCompleteMode specifies what type of auto complete to use myAutoFillCellType.AutoCompleteMode = (AutoCompleteMode)acm; //Assign cell type to column // could do a cell instead // FpSpread1.ActiveSheet.Cells(0, 0).CellType = myAutoFillCellType fpSpread1.ActiveSheet.Columns[column].CellType = myAutoFillCellType; } private void cbAutofillSource_SelectedIndexChanged(object sender, EventArgs e) { CustomAutoComplete(cbColumn.SelectedIndex, cbAutoCompleteMode.SelectedIndex, cbAutofillSource.SelectedIndex); } private void cbAutoCompleteMode_SelectedIndexChanged(object sender, EventArgs e) { CustomAutoComplete(cbColumn.SelectedIndex, cbAutoCompleteMode.SelectedIndex, cbAutofillSource.SelectedIndex); } private void cbColumn_SelectedIndexChanged(object sender, EventArgs e) { CustomAutoComplete(cbColumn.SelectedIndex, cbAutoCompleteMode.SelectedIndex, cbAutofillSource.SelectedIndex); }
VB
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load GetSampleData() InitData() End SubPublic Sub GetSampleData() 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 CompanyName, ContactName, ContactTitle, Country from Customers" Dim conn As New System.Data.OleDb.OleDbConnection(conStr) Dim ds As DataSet = New DataSet() Dim da As New System.Data.OleDb.OleDbDataAdapter(sqlStr, conn) da.Fill(ds)FpSpread1.ActiveSheet.DataAutoSizeColumns = True FpSpread1.ActiveSheet.DataMember = "Patients" FpSpread1.ActiveSheet.DataSource = ds 'Set the column width to the widest string in the column For i As Integer = 0 To FpSpread1.ActiveSheet.Columns.Count - 1 FpSpread1.ActiveSheet.Columns(i).Width = FpSpread1.ActiveSheet.Columns(i).GetPreferredWidth() Next 'Always be in edit mode when entering a cell FpSpread1.EditModePermanent = True 'Highlight the text for replacing when going into cell FpSpread1.EditModeReplace = True End SubPublic Sub InitData() 'Autofill DataSource options cbAutofillSource.DataSource = New String() {"CustomSource and All Column Items Below Cell", "CustomSource Only", "Column Items Below Cell Only"} cbAutofillSource.SelectedIndex = 0 'AutoComplete Mode options cbAutoCompleteMode.DataSource = New String() {"None", "Suggest", "Append", "SuggestAppend"} cbAutoCompleteMode.SelectedIndex = 3 'Column options cbColumn.DataSource = New String() {"Company (0)", "Contact (1)", "Title (2)", "Country (3)"} cbColumn.SelectedIndex = 0 End SubPublic Sub CustomAutoComplete(column As Integer, acm As Integer, AutoFillAutoCompleteCustomSource As Integer) 'INPUTS: '_AutoCompleteMode options ' "None", "Suggest", "Append", "SuggestAppend" '_AutoFillAutoCompleteCustomSource options ' "CustomSource and All Items Below it", "CustomSource Only", "Column Items Below only" 'Build the custom source suggestion list Dim mySuggestionList As String() = New String() {"Benjamin", "Bruce", "Cheryl", "Daniel", "Dave", "Eric", _ "Hana", "Harol", "Henry", "Jane", "Jessica", "John", _ "Julia", "Kathy", "Lance", "Laura", "Mark", "Marry", _ "Meggie", "Mike", "Naomi", "Paul", "Peter", "Roger", _ "Rose", "Ruth", "Ryan", "Shirley", "Tan", "Thomas", _ "Tony", "Varun", "$1234", "VND1234", "12345 %", "13 %", _ "02/19/1991", "Sunday, August 08, 2007", "Saturday, March 09, 1997", "February", "0901234567", "0953336650"} 'Bind listbox to show options to user lbCustom.DataSource = mySuggestionList 'Add the custom source to the collection Dim myAutoCompleteStringCollection As New AutoCompleteStringCollection() myAutoCompleteStringCollection.AddRange(mySuggestionList) 'Create the cell type Dim myAutoFillCellType As New FarPoint.Win.Spread.CellType.GeneralCellType() 'AutoCompleteSource sets what source to use for the AutoComplete list items myAutoFillCellType.AutoCompleteSource = AutoCompleteSource.CustomSource 'AutoCompleteCustomSource specifies a list of items to use when AutoCompleteSource is set to //Custom// If AutoFillAutoCompleteCustomSource = 2 Then '"Column Items Below only" 'Set this to nothing to use the items below in this column only - no custom lists! myAutoFillCellType.AutoCompleteCustomSource = Nothing Else 'Use the custom list in conjunction with AutoFillAutoCompleteCustomSource setting myAutoFillCellType.AutoCompleteCustomSource = myAutoCompleteStringCollection End If 'AutoFillAutoCompleteCustomSource determines whether the custom list is automatically filled with data from the column ' true = use the custom source AND the column list below it ' false = use custom source list only If AutoFillAutoCompleteCustomSource = 1 Then 'CustomSource Only 'Use custom source list only myAutoFillCellType.AutoFillAutoCompleteCustomSource = False Else 'Use the custom source AND the column list below it myAutoFillCellType.AutoFillAutoCompleteCustomSource = True End If 'AutoCompleteMode specifies what type of auto complete to use myAutoFillCellType.AutoCompleteMode = DirectCast(acm, AutoCompleteMode) 'Assign cell type to column ' could do a cell instead ' FpSpread1.ActiveSheet.Cells(0, 0).CellType = myAutoFillCellType fpSpread1.ActiveSheet.Columns(column).CellType = myAutoFillCellType End Sub Private Sub cbAutofillSource_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cbAutofillSource.SelectedIndexChanged CustomAutoComplete(cbColumn.SelectedIndex, cbAutoCompleteMode.SelectedIndex, cbAutofillSource.SelectedIndex) End Sub Private Sub cbAutoCompleteMode_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cbAutoCompleteMode.SelectedIndexChanged CustomAutoComplete(cbColumn.SelectedIndex, cbAutoCompleteMode.SelectedIndex, cbAutofillSource.SelectedIndex) End Sub Private Sub cbColumn_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cbColumn.SelectedIndexChanged CustomAutoComplete(cbColumn.SelectedIndex, cbAutoCompleteMode.SelectedIndex, cbAutofillSource.SelectedIndex) End Sub End Class
MESCIUS inc.