Managing Controls

Introduction

Developers can add different drawing objects such as text boxes, check boxes, radio buttons, combo boxes, labels, buttons, lines, rectangles, arcs, ovals, spinners, scroll bars, group boxes etc. Aspose.Cells provides the Aspose.Cells.Drawing namespace which contains all the drawing objects. However, there are a few drawing objects or shapes that are not supported yet. Create these drawing objects in a designer spreadsheet using Microsoft Excel and then import the designer spreadsheet to Aspose.Cells. Aspose.Cells allows you to load these drawing objects from a designer spreadsheet and write them to a generated file.

Adding Text Box Control to a Worksheet

One way to stress important information in a report is to use a text box. For example, add text to highlight the company name or to indicate the geographic region with the highest sales etc. Aspose.Cells provides the TextBoxCollection class, used to add a new text box to the collection. There is another class, TextBox, which represents a text box used to define all types of settings. It has some important members:

• The TextFrame property returns a MsoTextFrame object used to adjust the contents of the text box.
• The Placement property specifies the placement type.
• The Font property specifies the font attributes.
• The AddHyperlink method adds a hyperlink for the text box.
• The FillFormat property returns an MsoFillFormat object used to set the fill format for the text box.
• The LineFormat property returns the MsoLineFormat object usually used to style and weight of the text box line.
• The Text property specifies the input text for the text box.

The following example creates two textboxes in the first worksheet of the workbook. The first text box is well-furnished with different format settings. The second is a simple one.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
    System.IO.Directory.CreateDirectory(dataDir);

// Instantiate a new Workbook.
Workbook workbook = new Workbook();

// Get the first worksheet in the book.
Worksheet worksheet = workbook.Worksheets[0];

// Add a new textbox to the collection.
int textboxIndex = worksheet.TextBoxes.Add(2, 1, 160, 200);

// Get the textbox object.
Aspose.Cells.Drawing.TextBox textbox0 = worksheet.TextBoxes[textboxIndex];

// Fill the text.
textbox0.Text = "ASPOSE______The .NET & JAVA Component Publisher!";

// Get the textbox text frame.
//MsoTextFrame textframe0 = textbox0.TextFrame;

// Set the textbox to adjust it according to its contents.
//textframe0.AutoSize = true;

// Set the placement.
textbox0.Placement = PlacementType.FreeFloating;

// Set the font color.
textbox0.Font.Color = Color.Blue;

// Set the font to bold.
textbox0.Font.IsBold = true;

// Set the font size.
textbox0.Font.Size = 14;

// Set font attribute to italic.
textbox0.Font.IsItalic = true;

// Add a hyperlink to the textbox.
textbox0.AddHyperlink("http:// Www.aspose.com/");

// Get the filformat of the textbox.
Aspose.Cells.Drawing.FillFormat fillformat = textbox0.Fill;            

// Get the lineformat type of the textbox.
Aspose.Cells.Drawing.LineFormat lineformat = textbox0.Line;           

// Set the line weight.
lineformat.Weight = 6;

// Set the dash style to squaredot.
lineformat.DashStyle = MsoLineDashStyle.SquareDot;

// Add another textbox.
textboxIndex = worksheet.TextBoxes.Add(15, 4, 85, 120);

// Get the second textbox.
Aspose.Cells.Drawing.TextBox textbox1 = worksheet.TextBoxes[textboxIndex];

// Input some text to it.
textbox1.Text = "This is another simple text box";

// Set the placement type as the textbox will move and
// Resize with cells.
textbox1.Placement = PlacementType.MoveAndSize;

// Save the excel file.
workbook.Save(dataDir + "book1.out.xls");

Manipulating Text Box Controls in Designer Spreadsheets

Aspose.Cells also lets you access textboxes in the designer worksheets and manipulate them. Use the Worksheet.TextBoxes property to get the textboxes collection in the sheet.

The following example uses the Microsoft Excel file that we created in the above example. It gets the text strings of the two textboxes and changes the text of the second textbox to save the file.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Instantiate a new Workbook.
// Open the existing excel file.
Workbook workbook = new Workbook(dataDir + "book1.xls");

// Get the first worksheet in the book.
Worksheet worksheet = workbook.Worksheets[0];

// Get the first textbox object.
Aspose.Cells.Drawing.TextBox textbox0 = worksheet.TextBoxes[0];

// Obtain the text in the first textbox.
string text0 = textbox0.Text;

// Get the second textbox object.
Aspose.Cells.Drawing.TextBox textbox1 = worksheet.TextBoxes[1];

// Obtain the text in the second textbox.
string text1 = textbox1.Text;

// Change the text of the second textbox.
textbox1.Text = "This is an alternative text";

// Save the excel file.
workbook.Save(dataDir + "output.out.xls");

Adding Check Box Control to a Worksheet

Check boxes are handy if you want to provide a way for a user to choose between two options, such as true or false; yes or no. Aspose.Cells allows you to use check boxes in worksheets. For instance, you may have developed a financial projection worksheet in which you can either account for a particular acquisition or not. In this case, you might want to place a check box at the top of the worksheet. You can then link the status of this check box to another cell, so that if the check box is selected, the value of the cell is True; if it is not selected, the value of the cell is False.

Using Microsoft Excel

To place a check box control in your worksheet, follow these steps:

1. Make sure the Forms toolbar is displayed.
2. Click the Check Box tool on the Forms toolbar.
3. In your worksheet area, click and drag to define the rectangle that will hold the check box and the label beside the check box.
4. Once the check box is placed, move the mouse cursor into the label area and change the label.
5. In the Cell Link field, specify the address of the cell to which this check box should be linked.
6. Click on OK.

Using Aspose.Cells

Aspose.Cells provides the CheckBoxCollection class, which is used to add a new check box to the collection. There is another class, Aspose.Cells.Drawing.CheckBox, which represents a check box. It has some important members:

• The LinkedCell property specifies a cell which is linked to the check box.
• The Text property specifies the text string associated with the check box. It is the label of the check box.
• The Value property specifies if the check box is checked or not.

The following example shows how to add a checkbox to the worksheet.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
    System.IO.Directory.CreateDirectory(dataDir);

// Instantiate a new Workbook.
Workbook excelbook = new Workbook();

// Add a checkbox to the first worksheet in the workbook.
int index = excelbook.Worksheets[0].CheckBoxes.Add(5, 5, 100, 120);

// Get the checkbox object.
Aspose.Cells.Drawing.CheckBox checkbox = excelbook.Worksheets[0].CheckBoxes[index];

// Set its text string.
checkbox.Text = "Click it!";

// Put a value into B1 cell.
excelbook.Worksheets[0].Cells["B1"].PutValue("LnkCell");

// Set B1 cell as a linked cell for the checkbox.
checkbox.LinkedCell = "B1";

// Check the checkbox by default.
checkbox.Value = true;

// Save the excel file.
excelbook.Save(dataDir + "book1.out.xls");

Adding Radio Button Control to the Worksheet

A radio button, or an option button, is a control made of a round box. The user makes his or her decision by selecting the round box. A radio button is usually, if not always, accompanied by others. Such radio buttons appear and behave as a group. The user decides which button is valid by selecting only one of them. When the user clicks one button, it is filled. When one button in the group is selected, buttons of the same group are empty.

Using Microsoft Excel

To place a Radio Button control in your worksheet, follow these steps:

1. Make sure the Forms toolbar is displayed.
2. Click the Option Button tool.
3. In the worksheet, click and drag to define the rectangle that will hold the option button and the label beside the option button.
4. Once the radio button is placed in the worksheet, move the mouse cursor into the label area and change the label.
5. In the Cell Link field, specify the address of the cell to which this radio button should be linked.
6. Click OK.

Using Aspose.Cells

Aspose.Cells.Drawing.ShapeCollection class provides a method named AddRadioButton, which is used to add a radio button control to a worksheet. The method returns an Aspose.Cells.Drawing.RadioButton object. The classAspose.Cells.Drawing.RadioButton represents an option button. It has some important members:

• The LinkedCell property specifies a cell which is linked to the radio button.
• The Text property specifies the text string associated with the radio button. It is the label of the radio button.
• The IsChecked property specifies if the radio button is checked or not.
• The FillFormat property specifies the fill format of the radio button.
• The LineFormat property specifies the line format styles of the option button.

The following example shows how to add radio buttons to a worksheet. The example adds three radio buttons representing age groups.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
    System.IO.Directory.CreateDirectory(dataDir);

// Instantiate a new Workbook.
Workbook excelbook = new Workbook();

// Insert a value.
excelbook.Worksheets[0].Cells["C2"].PutValue("Age Groups");

// Set the font text bold.
excelbook.Worksheets[0].Cells["C2"].GetStyle().Font.IsBold = true;

// Add a radio button to the first sheet.
Aspose.Cells.Drawing.RadioButton radio1 = excelbook.Worksheets[0].Shapes.AddRadioButton(3, 0, 2, 0, 30, 110);

// Set its text string.
radio1.Text = "20-29";

// Set A1 cell as a linked cell for the radio button.
radio1.LinkedCell = "A1";

// Make the radio button 3-D.
radio1.Shadow = true;             

// Set the weight of the radio button.
radio1.Line.Weight = 4;       

// Set the dash style of the radio button.
radio1.Line.DashStyle = MsoLineDashStyle.Solid;            

// Add another radio button to the first sheet.
Aspose.Cells.Drawing.RadioButton radio2 = excelbook.Worksheets[0].Shapes.AddRadioButton(6, 0, 2, 0, 30, 110);

// Set its text string.
radio2.Text = "30-39";

// Set A1 cell as a linked cell for the radio button.
radio2.LinkedCell = "A1";

// Make the radio button 3-D.
radio2.Shadow = true;                

// Set the weight of the radio button.
radio2.Line.Weight = 4;
            
// Set the dash style of the radio button.
radio2.Line.DashStyle = MsoLineDashStyle.Solid;            

// Add another radio button to the first sheet.
Aspose.Cells.Drawing.RadioButton radio3 = excelbook.Worksheets[0].Shapes.AddRadioButton(9, 0, 2, 0, 30, 110);

// Set its text string.
radio3.Text = "40-49";

// Set A1 cell as a linked cell for the radio button.
radio3.LinkedCell = "A1";

// Make the radio button 3-D.
radio3.Shadow = true;            

// Set the weight of the radio button.
radio3.Line.Weight = 4;

// Set the dash style of the radio button.
radio3.Line.DashStyle = MsoLineDashStyle.Solid;

// Save the excel file.
excelbook.Save(dataDir + "book1.out.xls");

Adding Combo Box Control to a Worksheet

To make data entry easier, or to limit entries to certain items that you define, you can create a combo box, or drop-down list of valid entries that is compiled from cells elsewhere on the worksheet. When you create a drop-down list for a cell, it displays an arrow next to that cell. To enter information in that cell, click the arrow, and then click the entry that you want.

Using Microsoft Excel

To place a combo box control in your worksheet, follow these steps:

1. Make sure the Forms toolbar is displayed.
2. Click on the Combo Box tool.
3. In your worksheet area, click and drag to define the rectangle that will hold the combo box.
4. Once the combo box is placed in the worksheet, right-click the control to click Format Control and specify the input range.
5. In the Cell Link field, specify the address of the cell to which this combo box should be linked.
6. Click on OK.

Using Aspose.Cells

The Aspose.Cells.Drawing.ShapeCollection class provides a method named AddComboBox, which is used to add a combo box control to a worksheet. The method returns an Aspose.Cells.Drawing.ComboBox object. The class Aspose.Cells.Drawing.ComboBox represents a combo box. It has some important members:

• The LinkedCell property specifies a cell which is linked to the combo box.
• The InputRange property specifies the worksheet range of cells used to fill the combo box.
• The DropDownLines property specifies the number of list lines displayed in the drop-down portion of a combo box.
• The Shadow property indicates whether the combo box has 3D shading.

The following example shows how to add a combo box to the worksheet.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
    System.IO.Directory.CreateDirectory(dataDir);

// Create a new Workbook.
Workbook workbook = new Workbook();

// Get the first worksheet.
Worksheet sheet = workbook.Worksheets[0];

// Get the worksheet cells collection.
Cells cells = sheet.Cells;

// Input a value.
cells["B3"].PutValue("Employee:");

// Set it bold.
cells["B3"].GetStyle().Font.IsBold = true;

// Input some values that denote the input range
// For the combo box.
cells["A2"].PutValue("Emp001");
cells["A3"].PutValue("Emp002");
cells["A4"].PutValue("Emp003");
cells["A5"].PutValue("Emp004");
cells["A6"].PutValue("Emp005");
cells["A7"].PutValue("Emp006");

// Add a new combo box.
Aspose.Cells.Drawing.ComboBox comboBox = sheet.Shapes.AddComboBox(2, 0, 2, 0, 22, 100);

Adding Label Control to a Worksheet

Labels are a means of give users information about a speadsheet’s contents. Aspose.Cells makes it possible to add and manipulate labels in a worksheet. The ShapeCollection class provides a method named AddLabel, used to add a label control to the worksheet. The method returns a Label object. The class Label represents a label in the worksheet. It has some important members:

• The Text method specifies a label’s caption string.
• The Placement method specifies the PlacementType, the way the label is attached to the cells in the worksheet.

The following example shows how to add a label to the worksheet.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
    System.IO.Directory.CreateDirectory(dataDir);

// Create a new Workbook.
Workbook workbook = new Workbook();

// Get the first worksheet in the workbook.
Worksheet sheet = workbook.Worksheets[0];

// Add a new label to the worksheet.
Aspose.Cells.Drawing.Label label = sheet.Shapes.AddLabel(2, 0, 2, 0, 60, 120);

// Set the caption of the label.
label.Text = "This is a Label";

// Set the Placement Type, the way the
// Label is attached to the cells.
label.Placement = PlacementType.FreeFloating;

// Saves the file.
workbook.Save(dataDir + "book1.out.xls");

Adding List Box Control to a Worksheet

A list box control creates a list control that allows single or multiple item selection.

Using Microsoft Excel

To place a list box control in a worksheet:

1. Make sure the Forms toolbar is displayed.
2. Click on the List Box tool.
3. In your worksheet area, click and drag to define the rectangle that will hold the list box.
4. Once the list box is placed in the worksheet, right-click on the control to click Format Control and specify the input range.
5. In the Cell Link field, specify the address of the cell to which this list box should be linked and set the selection type (Single, Multi, Extend) attribute
6. Click OK.

Using Aspose.Cells

The ShapeCollection class provides a method named AddListBox, which is used to add a list box control to a worksheet. The method returns a Aspose.Cells.Drawing.ListBox object. The class ListBox represents a list box. It has some important members:

• The LinkedCell method specifies a cell which is linked to the list box.
• The InputRange method specifies the worksheet range of cells used to fill the list box.
• The SelectionType method specifies the selection mode of the the list box.
• The Shadow method indicates whether the list box has 3D shading.

The following example shows how to add a list box to the worksheet.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
    System.IO.Directory.CreateDirectory(dataDir);

// Create a new Workbook.
Workbook workbook = new Workbook();

// Get the first worksheet.
Worksheet sheet = workbook.Worksheets[0];

// Get the worksheet cells collection.
Cells cells = sheet.Cells;

// Input a value.
cells["B3"].PutValue("Choose Dept:");

// Set it bold.
cells["B3"].GetStyle().Font.IsBold = true;

// Input some values that denote the input range
// For the list box.
cells["A2"].PutValue("Sales");
cells["A3"].PutValue("Finance");
cells["A4"].PutValue("MIS");
cells["A5"].PutValue("R&D");
cells["A6"].PutValue("Marketing");
cells["A7"].PutValue("HRA");

// Add a new list box.
Aspose.Cells.Drawing.ListBox listBox = sheet.Shapes.AddListBox(2, 0, 3, 0, 122, 100);

// Set the placement type.
listBox.Placement = PlacementType.FreeFloating;

// Set the linked cell.
listBox.LinkedCell = "A1";

// Set the input range.
listBox.InputRange = "A2:A7";

// Set the selection tyle.
listBox.SelectionType = SelectionType.Single;

// Set the list box with 3-D shading.
listBox.Shadow = true;

// Saves the file.
workbook.Save(dataDir + "book1.out.xls");

Adding Button Control to a Worksheet

Buttons are useful to perform some actions. Sometimes, it is useful to assign a VBA Macro to the button or assign a hyperlink to open a web page.

Using Microsoft Excel

To place a button control in your worksheet:

1. Make sure the Forms toolbar is displayed.
2. Click on the Button tool.
3. In your worksheet area, click and drag to define the rectangle that will hold the button.
4. Once the list box is placed in the worksheet, right-click on the control and select Format Control, then specify a VBA Macro and attributes related font, alignment, size, margin etc.
5. Click on OK.

Using Aspose.Cells

The ShapeCollection class provides a method named AddButton, used to add a button control to the worksheet. The method returns an Aspose.Cells.Drawing.Button object. The class Aspose.Cells.Drawing.Button represents a button. It has some important members:

• The Text property specifies the caption of button.
• The Font property specifies the font attributes for the label of the button control.
• The Placement property specifies the PlacementType, the way the button is attached to the cells in the worksheet.
• The AddHyperlink property adds a hyperlink for the button control. Clicking on the button will navigate to related URL.

The following example shows how to add a button to the worksheet.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
    System.IO.Directory.CreateDirectory(dataDir);

// Create a new Workbook.
Workbook workbook = new Workbook();

// Get the first worksheet in the workbook.
Worksheet sheet = workbook.Worksheets[0];

// Add a new button to the worksheet.
Aspose.Cells.Drawing.Button button = sheet.Shapes.AddButton(2, 0, 2, 0, 28, 80);

// Set the caption of the button.
button.Text = "Aspose";

// Set the Placement Type, the way the
// Button is attached to the cells.
button.Placement = PlacementType.FreeFloating;

// Set the font name.
button.Font.Name = "Tahoma";

// Set the caption string bold.
button.Font.IsBold = true;

// Set the color to blue.
button.Font.Color = Color.Blue;

// Set the hyperlink for the button.
button.AddHyperlink("http:// Www.aspose.com/");

// Saves the file.
workbook.Save(dataDir + "book1.out.xls");