|
Note
|
Commercial feature
A commercial Vaadin subscription is required to use Spreadsheet in your project. |
Vaadin Spreadsheet is a component which allows displaying and interacting with the contents of an Excel file. You can directly modify cell data in an active worksheet by entering textual and numerical values, as well as using Excel formulas for spreadsheet calculations.
Vaadin Spreadsheet supports Excel XLSX files and has limited support for XLS files. In large spreadsheets, cell data is loaded on demand from the server. This allows the component to handle large files without performance issues.
Cell data can be copied and pasted between a spreadsheet and the clipboard. The clipboard can also be used to copy and paste data between Vaadin Spreadsheet and other spreadsheet applications, such as Microsoft Excel or Google Sheets.
|
Note
| Vaadin Spreadsheet is a Flow only component, and is not available for Hilla applications. |
new tab
Spreadsheet spreadsheet = new Spreadsheet();
spreadsheet.setHeight("400px");
add(spreadsheet);Maven Dependency
Install Spreadsheet by adding the dependency to the project. Below is an example of this, with a Maven dependency in pom.xml:
<dependency>
<groupId>com.vaadin</groupId>
<artifactId>vaadin-spreadsheet-flow</artifactId>
</dependency>Loading XLSX
To open an existing Excel file in Vaadin Spreadsheet, you need to pass an Excel file to the Spreadsheet constructor. You can load an Excel file from the local filesystem with a File reference or from memory or other sources with an InputStream.
new tab
InputStream stream = getClass()
.getResourceAsStream("/testsheets/simple-invoice.xlsx");
Spreadsheet spreadsheet = new Spreadsheet(stream);Add & Delete Sheets
A spreadsheet is actually a workbook, which contains one or more worksheets. You can create new sheets and delete existing ones with createNewSheet() and deleteSheet(), respectively.
When a sheet is deleted, the index of the sheets with a higher index is decremented by one. When the active worksheet is deleted, the next one by index is set as active or, if there are none, the previous one.
All operations on the spreadsheet content are processed through the currently active worksheet. You can set an existing sheet as active with setActiveSheetIndex().
Toggling Features
The Spreadsheet object provides the following configuration of various UI elements:
| Element | Description |
|---|---|
Grid Lines | Cells are by default separated by grid lines. You can control their visibility with |
Row and Column Headings | Row and column headings display the row and column indexes, and allow selecting and resizing the rows and columns. You can control their visibility with |
Top Bar | The top bar displays the address of the currently selected cell and an editor for cell content. You can control its visibility with |
Bottom Bar | The bottom bar displays sheet names and buttons for navigating between them. You can control its visibility with |
Frozen Panes
You can define panes of rows and columns that are frozen when scrolling. You can create the pane for the current worksheet with createFreezePane(), which takes the number of frozen rows and columns as parameters.
Configuring Cells
There are a few aspects of cells that you can configure: data, formulas, format, etc. They’re listed and described below.
Data
A cell may contain a variety of types, like string, number, and date. To set a cell value programmatically, the reference needs to be retrieved from the getCell() method. If a cell with the given coordinates exists, use the setCellValue() method to update the cell value. Otherwise, it can be created from the createCell() method, which takes the row and column coordinates and the cell value.
|
Note
|
Don’t forget to refresh the cells.
If cells that are changed in the active sheet that is currently being displayed in the browser, you need to call refreshCells() passing the cells changed to get them updated on the client side, as well.
|
Formulas
You can use Excel formulas in the cells of the spreadsheet. The formulas are evaluated on the server, and the result is displayed in the cell.
Format
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().
new tab
// Define a cell style for dates
CellStyle dateStyle = spreadsheet.getWorkbook().createCellStyle();
DataFormat format = spreadsheet.getWorkbook().createDataFormat();
dateStyle.setDataFormat(format.getFormat("yyyy-mm-dd"));
// Add some data rows
spreadsheet.createCell(1, 0, "Nicolaus");
spreadsheet.createCell(1, 1, "Copernicus");
spreadsheet.createCell(1, 2,
new GregorianCalendar(1999, 2, 19).getTime());
// Style the date cell
spreadsheet.getCell(1, 2).setCellStyle(dateStyle);Conditional Formatting
Applying conditional formatting to cells can help in highlighting patterns in the data. The formatting is applied to the cells that match the specified condition. The condition can be based on the value of the cell, or on the value of another cell.
Fonts
Cells can be styled by different fonts. A font definition not only includes a particular typeface, but also weight (e.g., 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.
new tab
// Create a cell
Cell cell = spreadsheet.createCell(2, 2, "Styled");
// Create and configure a cell style
CellStyle cellStyle = spreadsheet.getWorkbook().createCellStyle();
// Add a blue bottom border
cellStyle.setBorderBottom(BorderStyle.THICK);
cellStyle.setBottomBorderColor(IndexedColors.BLUE.getIndex());
// Set the text to bold
Font font = spreadsheet.getWorkbook().createFont();
font.setBold(true);
cellStyle.setFont(font);
// Apply the cell style to the cell
cell.setCellStyle(cellStyle);
// Request spreadsheet to refresh the cell
spreadsheet.refreshCells(cell);Comments
Cells may include comments, which are shown when the mouse is hovered over the cells. They’re indicated by ticks in the corner of the cells. The SpreadsheetDefaultActionHandler 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 on managing cell comments, we refer to the InsertDeleteCellCommentAction and EditCellCommentAction classes employed by the default action handler.
new tab
Drawing<?> drawing = spreadsheet.getActiveSheet()
.createDrawingPatriarch();
CreationHelper factory = spreadsheet.getActiveSheet().getWorkbook()
.getCreationHelper();
ClientAnchor anchor = factory.createClientAnchor();
Comment comment = drawing.createCellComment(anchor);
comment.setString(new XSSFRichTextString("First cell comment"));
spreadsheet.createCell(0, 0, "cell").setCellComment(comment);Merging Cells
You can merge spreadsheet cells with any variant of the addMergedRegion() method in Spreadsheet. The SpreadsheetDefaultActionHandler enables merging selected cells from the context menu.
Merged cells can be unmerged with removeMergedRegion(). This method takes a region index as its parameter. 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. You can iterate through them by knowing the number of regions, which you can find with getNumMergedRegions().
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.
Vaadin Spreadsheet provides default handling for hyperlink clicks. These can be overridden with a custom HyperlinkCellClickHandler, which you assign with setHyperlinkCellClickHandler().
Pop-up Buttons
You can add a pop-up button in a cell, which will open a drop-down pop-up overlay when clicked. The overlay can contain any Vaadin components. You can add a pop-up button with any of the setPopup() methods for different cell addressing forms.
You can create pop-up buttons for a row of cells in a cell range by defining a table, as described in "Tables & Filters".
Grouping
Multiple rows or multiple columns can be grouped together in a sheet. A grouping is indicated by a line with a collapse or expand button in the row or column heading. Rows and columns that are part of a group can be hidden or shown by clicking the button.
new tab
InputStream stream = getClass()
.getResourceAsStream("/testsheets/grouping.xlsx");
Spreadsheet spreadsheet = new Spreadsheet(stream);Tables & Filters
A cell range in a worksheet can be configured as a table, which adds pop-up menu buttons in the header row of the range. The pop-up menus contain Vaadin components, which you can use to implement various functionalities in the table, such as sorting or filtering. Vaadin Spreadsheet does not include any implementations of such features, merely the UI elements to enable them.
Such a table is defined by a SpreadsheetTable or a SpreadsheetFilterTable added to the spreadsheet.
SpreadsheetFilterTable is a spreadsheet table that allows filtering of the rows in the table in the pop-up menus. The menu is filled with checkboxes for each unique value in the column. Deselecting the items causes hiding of the respective rows in the spreadsheet.
new tab
int maxColumns = 5;
int maxRows = 5;
for (int column = 1; column < maxColumns + 1; column++) {
spreadsheet.createCell(1, column, "Column " + column);
}
for (int row = 2; row < maxRows + 2; row++) {
for (int col = 1; col < maxColumns + 1; col++) {
spreadsheet.createCell(row, col, row + col);
}
}
CellRangeAddress range = new CellRangeAddress(1, maxRows, 1,
maxColumns);
SpreadsheetTable table = new SpreadsheetFilterTable(spreadsheet, range);
spreadsheet.registerTable(table);
spreadsheet.refreshAllCellValues();Report Mode
In report mode, both the top and bottom bars are hidden. You can enable report mode with setReportStyle().
new tab
spreadsheet.setReportStyle(true);
spreadsheet.setActiveSheetProtected("");
spreadsheet.setRowColHeadingsVisible(false);Limitations
The Vaadin Spreadsheet has a few inherent limitations. Below is a list of them:
-
No provided toolbars, menus, or other controls for formatting cells.
-
Limited support for the older XSL format.
-
Constraints related to Apache POI, such as importing and exporting Excel files.
-
Using a table as a named range in formulas is not supported.
-
As a workaround, you can manually convert tables to ranges. Open the spreadsheet in Excel, right-click on the table, select the Convert to Range action, and then save the spreadsheet. The component is not serializable due to the internal usage of Apache POI. The
@PreserveOnRefreshannotation and session replication with Kubernetes Kit are not supported when using Spreadsheet.
-
-
The SUBTOTAL formula is limited to aggregate functions that do not ignore hidden values (i.e., function codes from 1 to 7, as well as 9) because they are not implemented in Apache POI.
-
Strict OOXML format is not supported by Apache POI.
-
No support for theming the component the same way as other Vaadin components.
-
Material and Lumo themes are not implemented.
-
Right-to-left languages are not supported.
-
There is limited support for accessibility features.