Form controls are objects which can be added to a worksheet to enable interaction with a cell or a data range available in the worksheet. You can seek input from the end-user or provide him with options to choose from by using these form controls. Hence, these controls are apt to create forms such as feedback forms or consent forms.

GcExcel provides nine form controls through Grapecity.Documents.Excel.Forms namespace which contains classes and interfaces for each supported form control.

The table below lists the supported form controls and their images.

Form Control	Snapshots
Button
Dropdown
Checkbox
Spinner
Listbox
Option button
Group box
Label
Scrollbar

All the form controls possess some common features which are provided by IControl interface of the Grapecity.Documents.Excel.Forms namespace. You can disable these controls by setting the Enabled property to false, so that user cannot bring focus to that control. There is an option to even lock the controls from accepting user input by setting the Locked property to true. To define how a control is attached to the underlying cells, you can use the Placement property. You can also change ZOrder of the controls, bring form controls to front or send them to back by using the BringToFront and SendToBack properties.

Add and Remove Form Controls

GcExcel allows you to add or remove the form controls to a worksheet by using Controls property of the IWorksheet interface. To add a form control to worksheet, you can use Add<ControlName> method of the IControlCollection interface. For instance, AddButton method adds the button form control and AddDropdown method adds the dropdown control to worksheet. So, there are nine such methods, one for each form control and all of them accept location coordinates, width, and height of the form control as parameters.

GcExcel provides Delete method of the IControl interface to remove a particular form control from worksheet. To remove all the controls from worksheet, you can use Clear method of the IControlCollection interface.

The code below demonstrates how to add or delete form controls to or from a worksheet:

var workbook = new Workbook();
IWorksheet ws = workbook.Worksheets["Sheet1"];

// Add two controls
var lblResolution = ws.Controls.AddLabel(12.6, 20.4, 49.2, 18.6);
lblResolution.Text = "Resolution";
lblResolution.PrintObject = true;

var btnNative = ws.Controls.AddButton(199.8, 21, 127.8, 17.4);
btnNative.Text = "Use native resolution";
btnNative.PrintObject = true;

// Remove the first control
ws.Controls[0].Delete(); 
                
// Remove all the controls
// ws.Controls.Clear()
        

Link Form Controls to a Cell Range

The selection-based form controls, that are Checkbox, Option button, Listbox, Dropdown, and Scrollbar provide LinkedCell property of the ICellLinkControl interface that enables a two-way binding between value of the form control and the linked cell range. Linked cell range allows you to have a definite set of values in form control to avoid invalid data input from the end-user. You can update values of the form control by simply editing value in the linked cell range or vice-versa.

The code below shows how to link cell values to the Checkbox form control:

// Link a check box 
var checkBox1 = ws.Controls.AddCheckBox(54, 13.2, 64.2, 18); 
checkBox1.LinkedCell = ws.Range["$A$2"]; 
        

Find Form Controls

GcExcel uses zero-based indexing while placing the form controls on a worksheet. You can find a form control in the worksheet using its name or its type. To find an excel form control by its name, you can use nameOf operator to look for the specified name. While to find a control using its type, you can use the FormControlType enumeration.

See the code below to find the control by its name:

// add the control
var lblResolution = ws.Controls.AddLabel(12.6, 20.4, 49.2, 18.6); 
lblResolution.Text = "Resolution";
lblResolution.PrintObject = true;
lblResolution.Name = nameof(lblResolution);
                
// find the control by name
Console.WriteLine(ws.Controls[nameof(lblResolution)].Name);
        

See the code below to find the control by its type:

// Get control by type and change width (without type conversion)
foreach (var ctl in ws.Controls)
{
 switch (ctl.FormControlType)
  {
  case FormControlType.Button: 
           ctl.Width = 70;
           break;
 case FormControlType.CheckBox:
          ctl.Width = 60;
          break;
  }
}

Print Form Controls to Pdf

Worksheet having form controls can be exported to PDF format using Save method of the Workbook class. To be able to print form controls to PDF format, PrintObject property of the specific form control must be set to true. By default, value of this property is true for all form controls except the Button form control.

Visible property of the IControl interface has a similar effect while exporting form controls. If Visible property of a form control is set to false, then that form control is not exported to either of the PDF, XLSX or XLSM formats.

The code below indicates how to print a Button form control while saving it as PDF:

// Add a button control to worksheet and set its PrintObject property
var btn = worksheet.Controls.AddButton(360 * ColScale, 91.8, 103.94 * WidthScale, 19.79);
btn.Text = "Test settings";
btn.PrintObject = true;

// Save to a pdf file
workbook.Save("FormControlPdf.pdf");
        

Form Control Shapes

GcExcel form controls are also shapes. Hence, to recognize whether a particular shape is a form control, ShapeType enumeration provides a FormControl member. To add onto this, if a shape is a form control, you can get the form control associated with the shape using the Control property of the IShape interface. Also, you can get shape associated with a form control using ShapeRange property of the IControl interface.

Refer to the following code to use form control as shape:

// Add form control
var button1 = ws.Controls.AddButton(50, 100, 120, 40); 
var buttonShape1 = button1.ShapeRange[0]; 
                
// Duplicate 
buttonShape1.Duplicate();
                        
// Size and move     
buttonShape1.Left = 66.6;
buttonShape1.Top = 22.8;
buttonShape1.Width = 155.4; 
buttonShape1.Height = 49.2;
                
// Delete 
buttonShape1.Delete();

Limitations

• Export of rich text and styles in GcExcel form controls is not supported.
• Getting or setting macro names in form controls requires VBA project.
• VBA project and COM interoperability is required for invoking macro when OnAction.
• LinkedObject and ‘Copy to Clipboard’ features require Window OLE automation interoperability.
• Spell check in Excel form controls requires Windows language pack interoperability.
• There is loss of some nodes during Excel I/O of alternate content in drawing Xml and Vml.