Cell Content and Formatting
- Cell Formatting
- Cell Font Style
- Cell Comments
- Merging Cells
- Components in Cells
- Hyperlinks
- Popup Buttons in Cells
In the following, we go through various user interface features in the table cells.
Cell Formatting
Formatting cell values can be accomplished by using cell styles. A cell style must be created in the workbook by using createCellStyle(). Cell data format is set for the style with setDataFormat().
// Define a cell style for dates
CellStyle dateStyle = sheet.getWorkbook().createCellStyle();
DataFormat format = sheet.getWorkbook().createDataFormat();
dateStyle.setDataFormat(format.getFormat("yyyy-mm-dd"));
// Add some data rows
sheet.createCell(1, 0, "Nicolaus");
sheet.createCell(1, 1, "Copernicus");
sheet.createCell(1, 2,
new GregorianCalendar(1999,2,19).getTime());
// Style the date cell
sheet.getCell(1,2).setCellStyle(dateStyle);Cell Font Style
Cells can be styled by different fonts. A font definition not only includes a particular typeface, but also weight (bold or normal), emphasis, underlining, and other such font attributes.
A font definition is managed by Font class in the Apache POI API. A new font can be created with createFont() in the workbook.
For example, in the following we make a header row in a spreadsheet bold:
// Create some column captions in the first row
sheet.createCell(0, 0, "First Name");
sheet.createCell(0, 1, "Last Name");
sheet.createCell(0, 2, "Born");
// Create a bold font
Font bold = sheet.getWorkbook().createFont();
bold.setBold(true);
// Set the cells in the first row as bold
for (int col=0; col <= 2; col++)
sheet.getCell(0, col).getCellStyle().setFont(bold);Cell Comments
Cells may have comments that are indicated by ticks in the corner of the cells, and the comment is shown when mouse is hovered on the cells. The SpreadsheetDefaultActionHandler described in "Default Context Menu" enables adding comments from the context menu.
A new comment can be added through the POI API of a cell, with addComment(). For a detailed example for managing cell comments, we refer to the InsertDeleteCellCommentAction and EditCellCommentAction classes employed by the default action handler.
Merging Cells
You can merge spreadsheet cells with any variant of the addMergedRegion() method in Spreadsheet.
The SpreadsheetDefaultActionHandler described in "Default Context Menu" enables merging selected cells from the context menu.
Previously merged cells can be unmerged with removeMergedRegion(). The method takes as its parameter a region index. You can search for a particular region through the POI Sheet API for the active sheet, which you can obtain with getActiveSheet(). The getMergedRegion() returns a merged region by index and you can iterate through them by knowing the number of regions, which you can find out with getNumMergedRegions().
Components in Cells
You can have Vaadin components in spreadsheet cells and bind field components to the cell data. The components can be shown all the time or work as editors that appear when a cell is activated for editing.
Components in a spreadsheet must be generated by a SpreadsheetComponentFactory, which you need to implement.
Hyperlinks
Hyperlinks in cells can point to other worksheets in the current workbook, or to external URLs. Links must be added through the POI API. Spreadsheet provides default handling for hyperlink clicks, which can be overridden with a custom HyperlinkCellClickHandler, which you assign with setHyperlinkCellClickHandler().
Popup Buttons in Cells
You can add a popup button in a cell, clicking which opens a drop-down popup overlay, which can contain any Vaadin components. You can add a popup button with any of the setPopup() methods for different cell addressing forms. A popup button is a PopupButton object, which is a regular Vaadin component container, so you can add any components to it by addComponent().
You can create popup buttons for a row of cells in a cell range by defining a table, as described in "Tables Within Spreadsheets".