Add Combo Box to Worksheet in Excel

Introduction

Creating interactive Excel spreadsheets can greatly enhance the user experience, especially when you add form elements like combo boxes. Combo boxes allow users to select options from a predefined list, adding ease and efficiency to data input. With Aspose.Cells for .NET, you can programmatically create combo boxes in Excel sheets without using Excel directly. This powerful library allows developers to manipulate Excel files in various ways, including the ability to automate form controls. In this tutorial, we’ll walk you through the process of adding a combo box to a worksheet in Excel using Aspose.Cells for .NET. If you’re looking to build dynamic, user-friendly spreadsheets, this guide will help you get started.

Prerequisites

Before we dive into the code, let’s make sure you have everything you need:

  • Aspose.Cells for .NET: Download and install the Aspose.Cells for .NET library from the download page.
  • .NET Framework: Ensure you have .NET Framework installed on your machine. Any version supported by Aspose.Cells will work.
  • Development Environment: Use an IDE like Visual Studio to manage your project and write code.
  • Aspose License: You can work without a license in evaluation mode, but for a full version, you’ll need to apply a license. Obtain a temporary license if needed.

Import Packages

To get started, you need to import the required namespaces into your project. Here’s what you need:

using System.IO;
using Aspose.Cells;

These are essential for interacting with Excel files and manipulating form elements like combo boxes in the workbook. Let’s break down the process of adding a combo box into multiple simple steps for easy understanding.

Step 1: Set Up the Document Directory

The first step is to create a directory where your Excel files will be saved. You can create a new folder if it doesn’t already exist.

// The path to the documents directory.
string dataDir = "Your Document Directory";
// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
    System.IO.Directory.CreateDirectory(dataDir);
  • dataDir: Specifies the location where the output file will be saved.
  • System.IO.Directory.Exists: Checks if the directory already exists.
  • System.IO.Directory.CreateDirectory: Creates the directory if it’s missing.

Step 2: Create a New Workbook

Now, create a new Excel workbook where you’ll be adding the combo box.

// Create a new Workbook.
Workbook workbook = new Workbook();
  • Workbook workbook: Initializes a new instance of the Workbook class, representing an Excel file.

Step 3: Get the Worksheet and Cells

Next, access the first worksheet from the workbook and retrieve the cells collection where you will input data.

// Get the first worksheet.
Worksheet sheet = workbook.Worksheets[0];
// Get the worksheet cells collection.
Cells cells = sheet.Cells;
  • Worksheet sheet: Fetches the first worksheet from the workbook.
  • Cells cells: Gets the collection of cells from the worksheet.

Step 4: Input Values for Combo Box

Now, we need to input some values into the cells. These values will serve as options for the combo box.

// 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");
  • cells[“B3”].PutValue: Places the label “Employee” in cell B3.
  • Font.IsBold = true: Sets the text to bold to make it stand out.
  • Input range: Inputs several employee IDs in cells A2 to A7. These will appear in the combo box dropdown.

Step 5: Add the Combo Box to the Worksheet

The next step is to add the combo box control to your worksheet. This combo box will let users pick one of the employee IDs you entered earlier.

// Add a new combo box.
Aspose.Cells.Drawing.ComboBox comboBox = sheet.Shapes.AddComboBox(2, 0, 2, 0, 22, 100);
  • AddComboBox: Adds a new combo box to the worksheet. The numbers (2, 0, 2, 0, 22, 100) represent the position and dimensions of the combo box.

To make the combo box functional, we need to link it to a specific cell and define the range of cells it will pull its options from.

// Set the linked cell.
comboBox.LinkedCell = "A1";
// Set the input range.
comboBox.InputRange = "A2:A7";
  • LinkedCell: Links the combo box’s selection to cell A1. The selected value from the combo box will appear in this cell.
  • InputRange: Defines the cell range (A2:A7) containing the values that will populate the combo box options.

Step 7: Customize the Combo Box Appearance

You can further customize the combo box by specifying the number of dropdown lines and enabling 3D shading for better aesthetics.

// Set no. of list lines displayed in the combo box's list portion.
comboBox.DropDownLines = 5;
// Set the combo box with 3-D shading.
comboBox.Shadow = true;
  • DropDownLines: Controls how many options will be visible in the combo box dropdown at once.
  • Shadow: Adds a 3D shading effect to the combo box.

Step 8: AutoFit Columns and Save the Workbook

Finally, let’s auto-fit the columns for a clean layout and save the workbook.

// AutoFit Columns
sheet.AutoFitColumns();
// Saves the file.
workbook.Save(dataDir + "book1.out.xls");
  • AutoFitColumns: Automatically adjusts the column widths to fit the content.
  • Save: Saves the workbook as an Excel file in the specified directory.

Conclusion

Adding a combo box to your Excel worksheets using Aspose.Cells for .NET is a straightforward process that greatly improves data input flexibility. By programmatically creating form controls, you can build interactive spreadsheets with ease. This tutorial showed you how to add a combo box, link it to a cell, and configure its input range, all using Aspose.Cells. Aspose.Cells provides a vast range of features for Excel file manipulation, making it an ideal choice for developers looking to automate spreadsheet tasks. Try it out with a free trial.

FAQ’s

Can I use Aspose.Cells without Excel installed?

Yes, Aspose.Cells works independently of Excel and does not require Excel to be installed.

How do I apply a license in Aspose.Cells?

You can apply a license by obtaining it from here and calling License.SetLicense() in your code.

What formats does Aspose.Cells support for saving files?

Aspose.Cells supports saving files in multiple formats like XLSX, XLS, CSV, PDF, and more.

Is there a limit to the number of combo boxes I can add?

No, there is no strict limit; you can add as many combo boxes as your project requires.

How do I get support for Aspose.Cells?

You can get support from the Aspose forum.

Add List Box to Worksheet in Excel Add Scroll Bar to Worksheet in Excel