Cell Types

DsExcel supports Button, CheckBox, ComboBox, and Hyperlink cell types. These cell types define the type of information in a cell and its behavior.

Cell types can be defined for a cell, range of cells, row, column or a worksheet. DsExcel library provides the getCellType method in IRange interface to get or set cell type for a cell or range of cells. If the cell types are different in a range of cells, the cell type of the top-left cell of the range will be returned. The CellType property of IWorksheet interface can be used to get or set cell type for a worksheet. Further, the EntireColumn and EntireRow property of IRange interface can be used to get or set cell types for columns and rows respectively.

Note: Cell types are not supported by Excel. So, these are lost after saving to Excel files. But the cell types work well with SpreadJS, and is retained during JSON I/O with SpreadJS.

Button Cell Type

Refer to the following code to create a Button cell type:

private static void ButtonCellTypes() {
    // Initialize workbook
    Workbook workbook = new Workbook();
    // Fetch default worksheet
    IWorksheet worksheet = workbook.getWorksheets().get(0);

    // Creating ButtonCellType
    ButtonCellType button = new ButtonCellType();
    button.setText("Click Me..!!");
    button.setButtonBackColor("LightBlue");
    button.setMarginLeft(10);
    worksheet.getRange("A1:B2").setCellType(button);

    // Saving workbook to Pdf
    workbook.save("151-ButtonCellTypes.pdf", SaveFileFormat.Pdf);

CheckBox Cell Type

Refer to the following code to create a CheckBox cell type:

private static void CheckBoxCellTypes() {
    // Initialize workbook
    Workbook workbook = new Workbook();
    // Fetch default worksheet
    IWorksheet worksheet = workbook.getWorksheets().get(0);

    // Creating CheckBoxCellType
    CheckBoxCellType checkBox = new CheckBoxCellType();
    checkBox.setCaption("Caption");
    checkBox.setTextTrue("True");
    checkBox.setTextFalse("False");
    checkBox.setIsThreeState(false);
    worksheet.getRange("A1:C3").setCellType(checkBox);

    worksheet.getRange("A1").setValue(true);
    worksheet.getRange("B2").setValue(true);

    // Saving workbook to Pdf
    workbook.save("152-CheckBoxCellTypes.pdf", SaveFileFormat.Pdf);

ComboBox Cell Type

Refer to the following code to create a ComboBox cell type:

private static void ComboCellTypes() {
    // Initialize workbook
    Workbook workbook = new Workbook();
    // Fetch default worksheet
    IWorksheet worksheet = workbook.getWorksheets().get(0);

    // Creating ComboBoxCellType
    ComboBoxCellType comboBox = new ComboBoxCellType();
    comboBox.setEditorValueType(EditorValueType.Value);

    ComboBoxCellItem comboItem = new ComboBoxCellItem();
    comboItem.setValue("US");
    comboItem.setText("United States");
    comboBox.getItems().add(comboItem);

    comboItem = new ComboBoxCellItem();
    comboItem.setValue("CN");
    comboItem.setText("China");
    comboBox.getItems().add(comboItem);

    comboItem = new ComboBoxCellItem();
    comboItem.setValue("JP");
    comboItem.setText("Japan");
    comboBox.getItems().add(comboItem);

    worksheet.getRange("A1:B2").setCellType(comboBox);
    worksheet.getRange("A1").setValue("CN");

    // Saving workbook to Pdf
    workbook.save("153-ComboCellTypes.pdf", SaveFileFormat.Pdf);

Hyperlink Cell Type

Refer to the following code to create a Hyperlink cell type:

private static void HyperlinkCellTypes() {
    // Initialize workbook
    Workbook workbook = new Workbook();
    // Fetch default worksheet
    IWorksheet worksheet = workbook.getWorksheets().get(0);

    // Creating HyperLinkCellType
    HyperLinkCellType hyperlinkCell = new HyperLinkCellType();
    hyperlinkCell.setText("Google Website");
    hyperlinkCell.setLinkColor("Blue");
    hyperlinkCell.setLinkToolTip("Google Website");
    hyperlinkCell.setVisitedLinkColor("Green");
    hyperlinkCell.setTarget(HyperLinkTargetType.Blank);

    worksheet.getRange("A1").setCellType(hyperlinkCell);
    worksheet.getRange("A1").setValue("https://www.google.co.in/");

    // Saving workbook to Pdf
    workbook.save("154-HyperlinkCellTypes.pdf", SaveFileFormat.Pdf);

Checkbox or Radiobutton List Cell Type

DsExcel supports JSON I/O and PDF exporting of SpreadJS files containing checkbox list and radiobutton list cell types. You can also download the JSON file containing radiobutton list and checkbox list cell type from here.

DsExcel also provides RadioButtonListCellType and CheckBoxListCellType classes in its API to add these cell types.

The following example code creates a checkbox list cell type for a cell in DsExcel.

//create a new workbook
Workbook workbook = new Workbook();

IWorksheet worksheet = workbook.getWorksheets().get(0);

CheckBoxListCellType cellType = new CheckBoxListCellType();
cellType.setDirection(CellTypeDirection.Horizontal);
cellType.setTextAlign(CellTypeTextAlign.Right);
cellType.setIsFlowLayout(false);
cellType.setMaxColumnCount(2);
cellType.setMaxRowCount(1);
cellType.setHorizontalSpacing(20);
cellType.setVerticalSpacing(5);

cellType.getItems().add(new SelectFieldItem("sample1", "1"));
cellType.getItems().add(new SelectFieldItem("sample2", "2"));
cellType.getItems().add(new SelectFieldItem("sample3", "3"));
cellType.getItems().add(new SelectFieldItem("sample4", "4"));
cellType.getItems().add(new SelectFieldItem("sample5", "5"));

worksheet.getRange("A1").setRowHeight(60);
worksheet.getRange("A1").setColumnWidth(25);

worksheet.getRange("A1").setCellType(cellType);
        
//check multiple options in the check box list
worksheet.getRange("A1").setValue(new Object[][]{
{new Object[]{"1", "3", "5"}}
});
        

//save to an pdf file
workbook.save("AddCheckBoxListCellType.pdf");

The following example code creates checkbox list cell type and sets the value of the option as a custom object.

//create a new workbook
Workbook workbook = new Workbook();

Workbook.setValueJsonSerializer(new CustomObjectJsonSerializer());
IWorksheet worksheet = workbook.getWorksheets().get(0);

CheckBoxListCellType cellType = new CheckBoxListCellType();
cellType.setDirection(CellTypeDirection.Horizontal);
cellType.setTextAlign(CellTypeTextAlign.Right);
cellType.setIsFlowLayout(false);
cellType.setMaxColumnCount(2);
cellType.setMaxRowCount(1);
cellType.setHorizontalSpacing(20);
cellType.setVerticalSpacing(5);

cellType.getItems().add(new SelectFieldItem("player1", new People(5, "Tom")));
cellType.getItems().add(new SelectFieldItem("player2", new People(5, "Jerry")));
cellType.getItems().add(new SelectFieldItem("player3", new People(6, "Mario")));
cellType.getItems().add(new SelectFieldItem("player4", new People(4, "Luigi")));

worksheet.getRange("A1").setRowHeight(40);
worksheet.getRange("A1").setColumnWidth(25);

worksheet.getRange("A1").setCellType(cellType);
worksheet.getRange("A1").setValue(new Object[][]{
{new Object[]{new People(5, "Tom"), new People(6, "Mario")}}
});
        
//save to an pdf file
workbook.save("AddCheckBoxListCellTypeCustomObject.pdf");
    
}
class CustomObjectJsonSerializer implements IJsonSerializer {
Gson gson = new Gson();
public final Object deserialize(String json) {
    return this.gson.fromJson(json, JsonElement.class);
}
    
public final String serialize(Object value) {
    return this.gson.toJson(value);
}
}
    
class People {
private int age;
private String name;
        
public int getAge() {
    return age;
}
        
public void setAge(int age) {
    this.age = age;
}
        
public String getName() {
    return name;
}
        
public void setName(String name) {
    this.name = name;
}
        
public People(int age, String name){
    this.age = age;
    this.name = name;
}
        
@Override
public boolean equals(Object obj){
    return obj instanceof People && age == ((People)obj).getAge() && name.equals(((People)obj).getName());
}
        
@Override
public int hashCode() {
    int hashCode = 17;
    
    hashCode = 31 * hashCode + this.age;
    hashCode = 31 * hashCode + (this.name == null ? 0 : this.name.hashCode());
    
    return hashCode;
}
}

The following example code creates a radio list cell type for a cell in DsExcel.

//create a new workbook
Workbook workbook = new Workbook();
 IWorksheet worksheet = workbook.getWorksheets().get(0);

RadioButtonListCellType cellType = new RadioButtonListCellType();

cellType.setDirection(CellTypeDirection.Horizontal);
cellType.setTextAlign(CellTypeTextAlign.Right);
cellType.setIsFlowLayout(false);
cellType.setMaxColumnCount(2);
cellType.setMaxRowCount(1);
cellType.setHorizontalSpacing(20);
cellType.setVerticalSpacing(5);

cellType.getItems().add(new SelectFieldItem("sample1", "1"));
cellType.getItems().add(new SelectFieldItem("sample2", "2"));
cellType.getItems().add(new SelectFieldItem("sample3", "3"));
cellType.getItems().add(new SelectFieldItem("sample4", "4"));
cellType.getItems().add(new SelectFieldItem("sample5", "5"));

worksheet.getRange("A1").setRowHeight(60);
worksheet.getRange("A1").setColumnWidth(25);

worksheet.getRange("A1").setCellType(cellType);
worksheet.getRange("A1").setValue("1");
        
//check multiple options in the radio button list
worksheet.getRange("A1").setValue(new Object[][]{
{
    new Object[]{"1", "3", "5"}}
});
        
//save to an pdf file
workbook.save("AddRadioListCellType.pdf");

The following example code creates radiobutton cell type and sets the value of the option as a custom object.

//create a new workbook
Workbook workbook = new Workbook();

Workbook.setValueJsonSerializer(new CustomObjectJsonSerializer());
IWorksheet worksheet = workbook.getWorksheets().get(0);

RadioButtonListCellType cellType = new RadioButtonListCellType();
cellType.setDirection(CellTypeDirection.Horizontal);
cellType.setTextAlign(CellTypeTextAlign.Right);
cellType.setIsFlowLayout(false);
cellType.setMaxColumnCount(2);
cellType.setMaxRowCount(1);
cellType.setHorizontalSpacing(20);
cellType.setVerticalSpacing(5);

cellType.getItems().add(new SelectFieldItem("player1", new People(5, "Tom")));
cellType.getItems().add(new SelectFieldItem("player2", new People(5, "Jerry")));
cellType.getItems().add(new SelectFieldItem("player3", new People(6, "Mario")));
cellType.getItems().add(new SelectFieldItem("player4", new People(4, "Luigi")));

worksheet.getRange("A1").setRowHeight(40);
worksheet.getRange("A1").setColumnWidth(25);

worksheet.getRange("A1").setCellType(cellType);
worksheet.getRange("A1").setValue(new People(6, "Mario"));

//save to an pdf file
workbook.save("AddRadioButtonCellTypeCustomObject.pdf");
}
    
class CustomObjectJsonSerializer implements IJsonSerializer {
Gson gson = new Gson();
public final Object deserialize(String json) {
    return this.gson.fromJson(json, JsonElement.class);
}
    
public final String serialize(Object value) {
    return this.gson.toJson(value);
}
}
    
class People {
private int age;
private String name;
        
public int getAge() {
    return age;
}
        
public void setAge(int age) {
    this.age = age;
}
        
public String getName() {
    return name;
}
        
public void setName(String name) {
    this.name = name;
}
        
public People(int age, String name){
    this.age = age;
    this.name = name;
}
    
@Override
public boolean equals(Object obj){
    return obj instanceof People && age == ((People)obj).getAge() && name.equals(((People)obj).getName());
}
        
@Override
public int hashCode() {
    int hashCode = 17;
    
    hashCode = 31 * hashCode + this.age;
    hashCode = 31 * hashCode + (this.name == null ? 0 : this.name.hashCode());
    
    return hashCode;
}
}

Set Size of Check Box, Check Box List and Radio Box List Cells

DsExcel supports setting the size of Check Box, Check Box List and Radio Box List Cells while performing JSON I/O. The setBoxSize and setAutoBoxSize methods are provided in the CheckBoxCellType, CheckBoxListCellType and RadioButtonListCellType classes. The setBoxSize method can be used to set the size of cell whereas the setAutoBoxSize method can be used to enable whether the box size should change with font size.

Refer to the following example code which sets the box size and setAutoBoxSize method to true for Check Box List cell.

//create a new workbook
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getWorksheets().get(0);
        
CheckBoxListCellType celltype = new CheckBoxListCellType();
celltype.setTextAlign(CellTypeTextAlign.Right);
celltype.setIsFlowLayout(false);
celltype.setMaxColumnCount(2);
celltype.setMaxRowCount(1);
celltype.setHorizontalSpacing(20);
celltype.setVerticalSpacing(5);
celltype.setBoxSize(40);
celltype.setAutoBoxSize(true);
        
celltype.getItems().add(new SelectFieldItem("sample1", "1"));
celltype.getItems().add(new SelectFieldItem("sample2", "2"));
celltype.getItems().add(new SelectFieldItem("sample3", "3"));
celltype.getItems().add(new SelectFieldItem("sample4", "4"));
celltype.getItems().add(new SelectFieldItem("sample5", "5"));
        
worksheet.getRange("A1:C3").setColumnWidth(25);
worksheet.getRange("A1:C3").setCellType(celltype);
worksheet.getRange("A1:C3").setValue(new Object[][]{
    {new Object[]{"1", "3", "5"}}
});
        
try {
    workbook.toJson(new FileOutputStream("checkboxlistsize.json"));
} catch (FileNotFoundException e1) {
    e1.printStackTrace();
}