Add and Remove Form Controls in Excel using Java

Recently I used a free third-party library called Free Spire.XLS for Java and it supports adding and manipulating multiple types of form controls, such as text box, option button, check box and combo box in Excel files using Java codes. Today this article will introduce how to do it.

First of all, we need to create a running environment by installing JDK 1.8.0 and Intellij IDEA 2019. Next, add a Jar file called Spire.Xls.jar to IDEA. There are two methods to do it. One is that get the package of the library from the link, find Spire.Xls.jar in the “lib” folder and then manually add it to IDEA. The other is that create a Maven project in IDEA, type the following codes in the pom.xml file and finally click the button “Import Changes”.

<repositories>
<repository>
<id>com.e-iceblue</id>
<url>http://repo.e-iceblue.cn/repository/maven-public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.xls.free</artifactId>
<version>3.9.1</version>
</dependency>
</dependencies>

USING THE CODE
Add Form Controls to an Excel Worksheet using Java 
import com.spire.xls.*;
import com.spire.xls.core.*;
import java.awt.*;

public class AddFormControl {
    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("Shaun");
        textbox.getFill().setForeColor(Color.PINK);
        textbox.setHAlignment(CommentHAlignType.Center);
        textbox.setVAlignment(CommentVAlignType.Center);

        sheet.getCellRange("A4").setText("Gender: ");
        //Add an option button
        IRadioButton radiobutton1 = sheet.getRadioButtons().add(4, 2, 18, 65);
        radiobutton1.setText("Male");
        //Add an option button
        IRadioButton radiobutton2 = sheet.getRadioButtons().add(4, 4, 18, 65);
        radiobutton2.setText("Female");

        sheet.getCellRange("A6").setText("Hobby: ");
        //Add a check box
        ICheckBox checkbox1 = sheet.getCheckBoxes().addCheckBox(6, 2, 18, 100);
        checkbox1.setCheckState(CheckState.Checked);
        checkbox1.setText("Photography");
        //Add a check box
        ICheckBox checkbox2 = sheet.getCheckBoxes().addCheckBox(6, 4, 18, 65);
        checkbox2.setCheckState(CheckState.Checked);
        checkbox2.setText("Travel");

        sheet.getCellRange("A8").setText("Profession: ");
        sheet.getCellRange("A20").setText("Student");
        sheet.getCellRange("A21").setText("Teacher");
        sheet.getCellRange("A22").setText("Doctor");
        //Add a combo box
        IComboBoxShape combobox = sheet.getComboBoxes().addComboBox(8, 2, 18, 65);
        combobox.setListFillRange(sheet.getCellRange("A20:A22"));
        combobox.setSelectedIndex(1);

        for (int column = 1; column < 5; column ++)
        {
            sheet.setColumnWidth(column, 15f);
        }

        //Save the file
        workbook.saveToFile("output/AddFormControls.xlsx", ExcelVersion.Version2013);
    }
}
Output

Remove Form Controls from an Excel Worksheet
import com.spire.xls.*;
public class RemoveFormControl {
    public static void main(String[] args) {
        //Load an Excel file
        Workbook workbook = new Workbook();
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\AddFormControls.xlsx");
        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Remove option buttons from the worksheet
        for(int j = 0; j < sheet.getRadioButtons().getCount(); j ++){
            sheet.getRadioButtons().get(j).remove();
        }

        //Remove check boxes from the worksheet
        for(int i = 0; i < sheet.getCheckBoxes().getCount(); i ++){
            sheet.getCheckBoxes().get(i).remove();
        }

        //Save the file
        workbook.saveToFile("output/RemoveFormControls.xlsx", ExcelVersion.Version2013);
    }
}
Output