Cell Types | Document Solutions for Excel, Java Edition

Cell Types

In This Topic

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:

Java	Copy Code
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);

Java

Copy Code

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:

Java	Copy Code
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);

Java

Copy Code

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:

Java	Copy Code
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);

Java

Copy Code

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:

Java	Copy Code
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);

Java

Copy Code

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