Live SpreadsheetGear API Samples
Shapes Form Controls GroupBox and OptionButtons
Creates a GroupBox and series of OptionButtons that are linked to a cell. Also demonstrates adding these control relative to a range of cells--adding an OptionButton within each row of the provided range and surrounding all OptionButtons within a GroupBox.
// Create a new workbook and some local variables.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
SpreadsheetGear.IWorksheet worksheet = workbook.ActiveWorksheet;
SpreadsheetGear.IWorksheetWindowInfo windowInfo = worksheet.WindowInfo;
SpreadsheetGear.IRange cells = worksheet.Cells;
double left, top, width, height;
// We will add an Option Button to each cell in this range.
SpreadsheetGear.IRange optionsRange = cells["B3:B7"];
optionsRange.ColumnWidth = 15;
// Setup GroupBox
{
// Setup another IRange representing the boundaries of a GroupBox, which will
// contain all of the Option Buttons.
SpreadsheetGear.IRange groupBoxRange = optionsRange[
-1, 0, // Expand range up one row.
optionsRange.RowCount, 0 // Expand range down one row.
];
// Use the RowToPoints(...) and ColumnToPoints(...) methods to get to top-left
// coordinates (relative to the top-left edge of the worksheet) of the beginning
// of the range (i.e., the top-left edge of cell B2).
left = windowInfo.ColumnToPoints(groupBoxRange.Column);
top = windowInfo.RowToPoints(groupBoxRange.Row);
width = groupBoxRange.Width;
height = groupBoxRange.Height;
// Create the GroupBox shape with the above-calculated position and dimensions.
SpreadsheetGear.Shapes.IShape groupBoxShape = worksheet.Shapes.AddFormControl(
SpreadsheetGear.Shapes.FormControlType.GroupBox, left, top, width, height);
// Set the GroupBox header text.
groupBoxShape.TextFrame.Characters.Text = "My Group Box";
}
// Add individual OptionButtons. Iterate through each cell in optionsRange.
for (int i = 0; i < optionsRange.RowCount; i++)
{
// Get reference to desired cell.
SpreadsheetGear.IRange cell = optionsRange[i, 0];
// Calculate coordinates and dimensions of OptionButton.
left = windowInfo.ColumnToPoints(cell.Column) + 10; // Add a little padding
top = windowInfo.RowToPoints(cell.Row);
width = cell.Width - 10; // Offset padding from "left"
height = cell.Height;
// Add OptionButton and set its label text.
SpreadsheetGear.Shapes.IShape optionShape = worksheet.Shapes.AddFormControl(
SpreadsheetGear.Shapes.FormControlType.OptionButton, left, top, width, height);
optionShape.TextFrame.Characters.Text = $"My Option {i + 1}";
// If this is the first OptionButton added, link it to a cell. All other
// OptionButtons added to this GroupBox will also be linked to this cell.
if (i == 0)
{
SpreadsheetGear.Shapes.IControlFormat optionControl = optionShape.ControlFormat;
optionControl.LinkedCell = "A1";
}
}
// Set the linked cell's value to a valid index of an OptionButton. Note that
// OptionButton indexes are 1-based. This will select the 3rd OptionBox.
cells["A1"].Value = 3;
Run This Sample
Download File
Download an Excel Open XML Workbook (*.xlsx) file with the results of this sample.