Form controls are the original controls in Excel. They are compatible with both the old and the new versions of Excel, and can be inserted into any place of an Excel worksheet to reference and interact with cell data. In this article, I will demonstrate how to insert form controls in Excel along with how to read values from form controls in Java using Free Spire.XLS for Java API.
Add Dependencies
Method 1: If you are using maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.
<repositories>
<repository>
<id>com.e-iceblue</id>
<name>e-iceblue</name>
<url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.xls.free</artifactId>
<version>5.1.0</version>
</dependency>
</dependencies>
Method 2: If you are not using maven, you can download the JAR file from this link, extract the zip file and then import the Spire.Xls.jar file under the lib folder into your project as a dependency.
Insert Form Controls in Excel in Java
This example explains how to insert the following types of form controls into an Excel worksheet:
- Text box
- Option button
- Check box
- Combo box
Below are the main steps for your reference:
- Create an instance of Workbook class.
- Get the desired worksheet by its index.
- Add a text box to the worksheet using XlsWorksheetBase.getTextBoxes().addTextBox() method.
- Set text, back color and text alignment for the text box.
- Add an option button to the worksheet using XlsWorksheetBase.getRadioButtons().add() method.
- Set text and check state for the option button.
- Add a check box to the worksheet using XlsWorksheetBase.getCheckBoxes().addCheckBox() method.
- Set text and check state for the check box.
- Add values to the worksheet.
- Add a combo box to the worksheet using XlsWorksheetBase.getComboBoxes().addComboBox() method.
- Set the input data range for the combo box.
- Set the default selected item by its index.
- Loop through the columns in the worksheet and set column widths.
- Save the result file.
import com.spire.xls.*;
import com.spire.xls.core.*;
import java.awt.*;
public class InsertFormControls {
public static void main(String[] args){
//Create a Workbook instance
Workbook workbook = new Workbook();
//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
sheet.getCellRange("A2").setText("Name: ");
//Add a text box
ITextBoxShape textbox = sheet.getTextBoxes().addTextBox(2, 2, 18, 65);
textbox.setText("Jackie Hong");
textbox.getFill().setForeColor(new Color(144, 238, 144));
textbox.setHAlignment(CommentHAlignType.Center);
textbox.setVAlignment(CommentVAlignType.Center);
sheet.getCellRange("A4").setText("Gender: ");
//Add an option button
IRadioButton optionbutton1 = sheet.getRadioButtons().add(4, 2, 18, 65);
optionbutton1.setText("Male");
optionbutton1.setCheckState(CheckState.Checked);
//Add an option button
IRadioButton optionbutton2 = sheet.getRadioButtons().add(4, 4, 18, 65);
optionbutton2.setText("Female");
sheet.getCellRange("A6").setText("Hobby: ");
//Add a check box
ICheckBox checkbox1 = sheet.getCheckBoxes().addCheckBox(6, 2, 18, 100);
checkbox1.setText("Hiking");
//Add a check box
ICheckBox checkbox2 = sheet.getCheckBoxes().addCheckBox(6, 4, 18, 65);
checkbox2.setCheckState(CheckState.Checked);
checkbox2.setText("Camping");
sheet.getCellRange("A8").setText("Age: ");
sheet.getCellRange("A20").setText("20 or younger");
sheet.getCellRange("A21").setText("21 to 40");
sheet.getCellRange("A22").setText("41 to 60");
sheet.getCellRange("A23").setText("61 or older");
//Add a combo box
IComboBoxShape combobox = sheet.getComboBoxes().addComboBox(8, 2, 18, 65);
combobox.setListFillRange(sheet.getCellRange("A20:A23"));
combobox.setSelectedIndex(2);
for (int column = 1; column < 5; column ++)
{
sheet.setColumnWidth(column, 15f);
}
//Save the file
workbook.saveToFile("AddControls.xlsx", ExcelVersion.Version2013);
}
}
Read Values of Form Controls in Excel in Java
The following steps explain how to read the values from a specific text box, option button, check box and combo box in Excel:
- Create an instance of Workbook class.
- Load the Excel file using Workbook.loadFromFile() method.
- Get the desired worksheet by its index.
- Create an instance of StringBuilder class.
- Get the desired text box using XlsWorksheetBase.getTextBoxes().get(index) method.
- Get the text of the text box and append to the StringBuilder instance.
- Get the desired option button using XlsWorksheetBase.getRadioButtons().get(index) method.
- Get the check state of the option button and append to the StringBuilder instance.
- Get the desired check box using XlsWorksheetBase.getCheckBoxes().get(index) method.
- Get the check state of the check box and append to the StringBuilder instance.
- Get the desired combo box using XlsWorksheetBase.getComboBoxes().get(index) method.
- Get the selected value of the combo box and append to the StringBuilder instance.
- Print out the text in the StringBuilder instance.
import com.spire.xls.CheckState;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
import com.spire.xls.core.*;
public class ReadValues {
public static void main(String[] args){
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load the Excel file
workbook.loadFromFile("AddControls.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.getWorksheets().get(0);
//Create a StringBuilder instance
StringBuilder sb = new StringBuilder();
//Get the first text box
ITextBox textBox = sheet.getTextBoxes().get(0);
//Get the text of the text box
String text = textBox.getText();
sb.append("TextBox value: " + text + "\n");
//Get the first option button
IRadioButton optionButton = sheet.getRadioButtons().get(0);
//Get the check state of the option button
CheckState optionButtonState = optionButton.getCheckState();
sb.append("Option button state: " + optionButtonState.toString() + "\n");
//Get the first check box
ICheckBox checkBox = sheet.getCheckBoxes().get(0);
//Get the check state of the check box
CheckState checkBoxState = checkBox.getCheckState();
sb.append("Check box state: " + checkBoxState.toString() + "\n");
//Get the first combo box
IComboBoxShape comboBox = sheet.getComboBoxes().get(0);
//Get the selected value of the combo box
String seletectedValue = comboBox.getSelectedValue();
sb.append("Combo box selected item: " + seletectedValue.toString() + "\n");
System.out.println(sb.toString());
}
}
Top comments (0)