Insert and Read Form Controls in Excel in Java

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.

• Insert Form Controls in Excel in Java
• Read Values of Form Controls in Excel in Java

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());
    }
}