Form Controls in Spreadsheet for WPF

• Nov 29, 2023
• 4 minutes to read

Form controls controls are interactive elements (buttons, checkboxes, and drop-down lists) that can be added to a worksheet to create user-friendly interfaces. These controls allow users to input or manage data.

Form controls are available in the following formats:

• XLSX
• XLSM
• XLTX
• XLTM

Form Controls in Code

The SpreadsheetControl allows you to manage form controls in code. You can add, edit and remove form controls. Documents with form controls are processed without content loss.

Create Form Controls

The table below lists available form controls and API used to create each type.

Form Control	Class	Method
Button	ButtonFormControl	FormControlCollection.AddButton
Check Box	CheckBoxFormControl	FormControlCollection.AddCheckBox
Combo box	ComboBoxFormControl	FormControlCollection.AddComboBox
Group box	GroupBoxFormControl	FormControlCollection.AddGroupBox
List box	ListBoxFormControl	FormControlCollection.AddListBox
Radio Button	RadioButtonFormControl	FormControlCollection.AddRadioButton
Scrollbar	ScrollbarFormControl	FormControlCollection.AddScrollbar
Spin Button	SpinnerFormControl	FormControlCollection.AddSpinner

Note

The FormControlCollection.Add... method call adds a new item to both FormControlCollection and ShapeCollection.

The code sample below creates a button, a list box, and a checkbox form controls.

C#

using DevExpress.Spreadsheet;

IWorkbook workbook = spreadsheetControl.Document;

var formControls = workbook.Worksheets[0].FormControls;

// Create a button form control:
var buttonCellRange = workbook.Worksheets[0].Range["B2:C2"];
var buttonFormControl = formControls.AddButton(buttonCellRange);
buttonFormControl.PlainText = "Click Here";

// Create a list box form control:
var comboCellRange = workbook.Worksheets[0].Range["B4:C4"];
var comboBoxControl = formControls.AddComboBox(comboCellRange);
comboBoxControl.DropDownLines = 3;
comboBoxControl.SourceRange = workbook.Worksheets[0].Range["E2:E6"];
comboBoxControl.SelectedIndex = 1;

// Create a check box form control:
var checkRange = workbook.Worksheets[0].Range["D5:E5"];
var checkBoxControl = formControls.AddCheckBox(checkRange);
checkBoxControl.CheckState = FormControlCheckState.Checked;
checkBoxControl.PlainText = "Reviewed";

VB.NET

Imports DevExpress.Spreadsheet

Private workbook As spreadsheetControl.Document

Private formControls = workbook.Worksheets(0).FormControls

' Create a button form control:
Private buttonCellRange = workbook.Worksheets(0).Range("B2:C2")
Private buttonFormControl = formControls.AddButton(buttonCellRange)
buttonFormControl.PlainText = "Click Here"

' Create a list box form control:
Dim comboCellRange = workbook.Worksheets(0).Range("B4:C4")
Dim comboBoxControl = formControls.AddComboBox(comboCellRange)
comboBoxControl.DropDownLines = 3
comboBoxControl.SourceRange = workbook.Worksheets(0).Range("E2:E6")
comboBoxControl.SelectedIndex = 1

' Create a check box form control:
Dim checkRange = workbook.Worksheets(0).Range("D5:E5")
Dim checkBoxControl = formControls.AddCheckBox(checkRange)
checkBoxControl.CheckState = FormControlCheckState.Checked
checkBoxControl.PlainText = "Reviewed"

Access and Modify Form Controls

The Worksheet.FormControls property obtains all form controls in a worksheet. You can use one of the following ways to obtain a specific form control:

Access the item in the collection by its index Call the FormControlCollection.GetFormControlsByName method Call the FormControlCollection.GetFormControlById method The FormControl.Id and FormControl.Name properties return the form control’s identifier and name.

Print and Export Form Controls

You can print and export to PDF workbooks with form controls. Each form control has the PrintObject property that specifies whether to print the form control.

This option also affects the following operations:

• Form control export to PDF format
• Export of a cell range that contains a form control to an image
• Export of a worksheet with form controls to an image

The code sample below retrieves all button form controls from a worksheet and disables printing for all buttons:

C#

using DevExpress.Spreadsheet;
using System.Linq;

Workbook workbook = new Workbook();
workbook.LoadDocument("Document.xlsx");

var formControls = workbook.Worksheets[0].FormControls;

var buttons = formControls.Where(formControl => formControl.FormControlType == FormControlType.Button).Cast<ButtonFormControl>();
foreach (ButtonFormControl button in buttons) {
    button.PrintObject = false;
}

VB.NET

Imports DevExpress.Spreadsheet
Imports System.Linq

Private workbook As New Workbook()
workbook.LoadDocument("Document.xlsx")

Dim formControls = workbook.Worksheets(0).FormControls

Dim buttons = formControls.Where(Function(formControl) formControl.FormControlType = FormControlType.Button).Cast(Of ButtonFormControl)()
For Each button As ButtonFormControl In buttons
  button.PrintObject = False
Next button

Remove Form Controls

Call the FormControlCollection.Remove or FormControlCollection.RemoveAt method to remove a form control. The FormControlCollection.Clear() method removes all form controls from a worksheet.

The code sample below removes all check boxes from a workbook:

C#

Workbook workbook = new Workbook();
workbook.LoadDocument("Document.xlsx");

var formControls = workbook.Worksheets[0].FormControls;

for (int i = formControls.Count - 1; i >= 0; i--) {
    if (formControls[i].FormControlType == FormControlType.CheckBox)
        formControls.RemoveAt(i);
}

VB.NET

Dim workbook As New Workbook()
workbook.LoadDocument("Document.xlsx")

Dim formControls = workbook.Worksheets(0).FormControls

For i As Integer = formControls.Count - 1 To 0 Step -1
  If formControls(i).FormControlType = FormControlType.CheckBox Then
    formControls.RemoveAt(i)
  End If
Next i

Limitations

The Spreadsheet for WPF ships with the following form control limitations:

• Spreadsheet Control does not support form control interactivity. You cannot input or select form control data.
• Spreadsheet Control does not display ComboBoxFormControl and ListBoxFormControl values specified by the SourceRange property.
• Spreadsheet Control does not display fill and outline color of the CheckBoxFormControl and RadioButtonFormControl. However, you can specify the ShapeFormatBase.Fill and ShapeFormatBase.Outline properties for check boxes and radio buttons in code, save the document in a supported format and display it in Microsoft Excel. For other form controls these properties return null.
• The ShapeText property returns null for ComboBoxFormControl, ListBoxFormControl, ScrollbarFormControl, and SpinnerFormControl.