Spreadsheet Component

The spreadsheet component embeds an Excel-compatible spreadsheet into a view. It renders an Apache POI workbook in the browser, lets the user edit it, and gives the view full programmatic access to the underlying POI model.

XML Element

spreadsheet

Java Class

Spreadsheet

Basics

First, install the add-on. Then add the component using the Add Component palette of the Studio View Designer. The component is in the Vaadin Commercial category.

If you add the component manually, declare the sprd namespace in the view descriptor:

<view xmlns="http://jmix.io/schema/flowui/view"
      xmlns:sprd="http://jmix.io/schema/vaadin-spreadsheet/ui"
      title="msg://spreadsheetBasicsView.title">
    <layout expand="spreadsheet">
        <sprd:spreadsheet id="spreadsheet" width="100%"/>
    </layout>
</view>

The component is injected into the view controller as usual:

@ViewComponent
private Spreadsheet spreadsheet;

The spreadsheet is not bound to a data container. Its content is either an XLSX file that you load into it, or cells that you fill from entity data. In both cases the workbook is kept in memory, and edits are lost on a page refresh unless they are saved explicitly.

Loading XLSX

The read() method loads a workbook into the component, replacing its current content. It takes an InputStream, so the file can come from anywhere: the application resources as in the example below, a file storage, or an upload made with fileStorageUploadField.

@Subscribe
public void onInit(final InitEvent event) {
    try (InputStream inputStream =
                 resources.getResourceAsStream("com/company/vaadinspreadsheetex1/budget.xlsx")) {
        spreadsheet.read(inputStream);
    } catch (IOException e) {
        throw new RuntimeException("Unable to read the budget template", e);
    }
}

The write() method does the opposite and returns the workbook with all the edits made by the user. Pass the bytes to the Downloader so that the user can save the current document:

@Subscribe("downloadBtn")
public void onDownloadBtnClick(final ClickEvent<JmixButton> event) {
    try {
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        spreadsheet.write(outputStream);
        downloader.download(outputStream.toByteArray(), "budget.xlsx", DownloadFormat.XLSX);
    } catch (IOException e) {
        throw new RuntimeException("Unable to write the spreadsheet", e);
    }
}

Filling XLSX from Entities

A spreadsheet can be built from entities instead of a file. Cells and attributes are not bound to each other, so the view fills the sheet when it opens and reads the values back when the user saves.

To fill the sheet, create cells from the items of a collection container:

@ViewComponent
private CollectionContainer<User> usersDc;

@Subscribe
public void onBeforeShow(final BeforeShowEvent event) {
    spreadsheet.createCell(0, 0, "Username");
    spreadsheet.createCell(0, 1, "First name");
    spreadsheet.createCell(0, 2, "Last name");

    List<User> users = usersDc.getItems();
    for (int i = 0; i < users.size(); i++) {
        User user = users.get(i);
        int row = i + 1;
        spreadsheet.createCell(row, 0, user.getUsername());
        spreadsheet.createCell(row, 1, user.getFirstName());
        spreadsheet.createCell(row, 2, user.getLastName());
    }

    spreadsheet.createFreezePane(1, 0);
}

To save, go through the rows, take the value of each cell, and assign it to the corresponding attribute. The entities come from a container, so the view’s data context already tracks them, and save() stores all the changes made in the loop:

@Subscribe("saveBtn")
public void onSaveBtnClick(final ClickEvent<JmixButton> event) {
    List<User> users = usersDc.getItems();
    for (int i = 0; i < users.size(); i++) {
        User user = users.get(i);
        int row = i + 1;
        user.setFirstName(getCellValue(row, 1));
        user.setLastName(getCellValue(row, 2));
    }
    dataContext.save();
}

@Nullable
private String getCellValue(int row, int column) {
    Cell cell = spreadsheet.getCell(row, column);
    return cell != null
            // DataFormatter returns the text of a cell whatever its type
            ? spreadsheet.getDataFormatter().formatCellValue(cell)
            : null;
}
Only the values are saved this way. Changes to the sheet itself, such as a new column, formatting, or formula, are lost when the view is closed.

Configuring Cells

Create cells through the component rather than through POI, so that the component knows what to render. Row and column indexes start with zero, so the code below fills the range A1:B2:

spreadsheet.createCell(0, 0, "Product");
spreadsheet.createCell(0, 1, "Amount");
spreadsheet.createCell(1, 0, "Widget");
spreadsheet.createCell(1, 1, 1200);

The value can be a String, Double, Boolean, Date, or Calendar.

Existing cells are obtained with getCell(), by indexes or by an address in the A1 notation. If you change a cell through the POI API, refresh it so that the browser shows the new value. refreshCells() takes several cells or a collection:

Cell cell = spreadsheet.getCell("B2");
cell.setCellValue(1500);
spreadsheet.refreshCells(cell);

Formulas are created by a separate method and evaluated on the server. Write them without the leading =:

spreadsheet.createFormulaCell(3, 1, "SUM(B2:B3)");

To recalculate all formulas at once, for example after replacing a block of values, call refreshAllCellValues().

Styling Cells

Fonts, colors, borders, and number formats belong to the workbook, so they are created through the POI API. Create a style, apply it to a cell, and refresh the cell:

Workbook workbook = spreadsheet.getWorkbook();

CellStyle headerStyle = workbook.createCellStyle();
Font boldFont = workbook.createFont();
boldFont.setBold(true);
headerStyle.setFont(boldFont);

CellStyle amountStyle = workbook.createCellStyle();
DataFormat dataFormat = workbook.createDataFormat();
amountStyle.setDataFormat(dataFormat.getFormat("#,##0.00"));

Cell headerCell = spreadsheet.getCell(0, 0);
headerCell.setCellStyle(headerStyle);

Cell amountCell = spreadsheet.getCell(1, 1);
amountCell.setCellStyle(amountStyle);

spreadsheet.refreshCells(headerCell, amountCell);
A workbook allows a limited number of cell styles, so create each style once and reuse it instead of creating one per cell.

Selection

The initially selected cell or range is set by the selection attribute in the A1 notation, for example A1 or B2:D8. The component notifies the view every time the user selects something else:

@Subscribe("spreadsheet")
public void onSpreadsheetSelectionChange(final Spreadsheet.SelectionChangeEvent event) {
    CellReference reference = event.getSelectedCellReference();
    selectionLabel.setText(reference.formatAsString());
}

The event also reports the whole selection: getIndividualSelectedCells() returns the separately selected cells, and getCellRangeAddresses() returns the selected ranges.

Adding Components to Cells

A cell can show a UI component instead of a value, for example a checkbox for a boolean column. The component asks a factory what to put in a cell, so supply a spreadsheetComponentFactory and return a component for the cells that need one:

@Supply(to = "spreadsheet", subject = "spreadsheetComponentFactory")
private SpreadsheetComponentFactory spreadsheetComponentFactory() {
    return new SpreadsheetComponentFactory() {

        @Override
        public Component getCustomComponentForCell(Cell cell, int rowIndex, int columnIndex,
                                                   Spreadsheet spreadsheet, Sheet sheet) {
            if (columnIndex == 3 && rowIndex > 0) {
                Checkbox checkbox = uiComponents.create(Checkbox.class);
                checkbox.setValue(cell != null && cell.getBooleanCellValue());
                return checkbox;
            }
            return null; // leave the cell as it is
        }

        @Override
        public Component getCustomEditorForCell(Cell cell, int rowIndex, int columnIndex,
                                                Spreadsheet spreadsheet, Sheet sheet) {
            return null;
        }

        @Override
        public void onCustomEditorDisplayed(Cell cell, int rowIndex, int columnIndex,
                                            Spreadsheet spreadsheet, Sheet sheet,
                                            Component customEditor) {
        }
    };
}

getCustomComponentForCell() is called first, and getCustomEditorForCell() only if it returns null.

Rows that contain components are never lower than the minimumRowHeightForComponents value, so the components remain visible.

Add & Delete Sheets

A workbook contains one or more sheets, and the component shows one of them at a time. A new sheet is created with a name, a number of rows, and a number of columns:

spreadsheet.createNewSheet("Summary", 100, 20); // name, rows, columns
spreadsheet.setSheetName(1, "Details");
spreadsheet.setActiveSheetIndex(1);

The active sheet is deleted in the same way:

if (spreadsheet.getNumberOfSheets() > 1) {
    spreadsheet.deleteSheet(spreadsheet.getActiveSheetIndex());
}

Sheets are addressed by index, and there are two kinds of index. Methods without the POIIndex suffix count only visible sheets, which is what the user sees in the sheet selection bar. Methods with the suffix, such as setActiveSheetWithPOIIndex() and deleteSheetWithPOIIndex(), count all sheets of the workbook, including hidden ones. The activeSheetIndex and activeSheetWithPOIIndex attributes work the same way.

Frozen Panes

Frozen rows and columns stay in place while the rest of the sheet scrolls, which keeps a header row visible in a long table:

spreadsheet.createFreezePane(1, 0); // the first row, no columns
The arguments are the number of frozen rows and then the number of frozen columns. The POI Sheet.createFreezePane() method takes them in the opposite order.

Grouping Rows and Columns

If the loaded document uses grouping, it is preserved in the application. The groups are shown with outline controls next to the headings, and the user collapses and expands them in the browser as in Excel.

The example below loads a document in which countries are grouped by region:

@Subscribe
public void onInit(final InitEvent event) {
    try (InputStream inputStream =
                 resources.getResourceAsStream("com/company/vaadinspreadsheetex1/countries.xlsx")) {
        spreadsheet.read(inputStream);
    } catch (IOException e) {
        throw new RuntimeException("Unable to read the countries document", e);
    }
}

Groups can also be created from code. The component has no API of its own for this, so use the POI API and then reload the component. The reload is what makes the outline controls appear:

@Subscribe("groupBtn")
public void onGroupBtnClick(final ClickEvent<JmixButton> event) {
    Sheet sheet = spreadsheet.getActiveSheet();
    sheet.groupColumn(1, 2); // columns B and C

    spreadsheet.reload();
}
Grouping works only with XLSX documents.

Report Mode

The reportStyle attribute hides the two toolbars of the component: the function bar above the sheet and the sheet selection bar below it.

<sprd:spreadsheet id="spreadsheet"
                  width="100%"
                  reportStyle="true"/>

Gridlines and row and column headings are not part of the report mode. They belong to the sheet and are taken from the loaded document. The component applies them again every time a workbook is loaded or another sheet is activated. Call setGridlinesVisible(false) and setRowColHeadingsVisible(false) after loading the document to turn them off, and protect the sheet if the user should not change it:

spreadsheet.setGridlinesVisible(false);
spreadsheet.setRowColHeadingsVisible(false);
spreadsheet.setActiveSheetProtected("");

Attributes

The following attributes are specific to spreadsheet:

Name Description Default

activeSheetIndex

The index of the sheet shown initially, counting only visible sheets.

—

activeSheetWithPOIIndex

The index of the sheet shown initially, counting all sheets of the underlying POI workbook, including hidden ones.

—

chartsEnabled

If true, the charts contained in the loaded workbook are rendered.

false

colBufferSize

The size, in pixels, of the horizontal buffer rendered to the left and right of the visible area. A larger buffer makes scrolling smoother at the cost of a larger payload.

200

defaultColumnCount

The number of columns in a newly created sheet.

—

defaultColumnWidth

The default width of a column, in pixels.

—

defaultPercentageFormat

The format applied to cells whose value is entered as a percentage.

0.00%

defaultRowCount

The number of rows in a newly created sheet.

—

defaultRowHeight

The default height of a row, in points.

—

functionBarVisible

If true, the formula bar is shown above the sheet.

true

invalidFormulaErrorMessage

The message shown when the user enters a formula that cannot be parsed. Accepts a message key.

—

maxColumns

The number of columns rendered in the active sheet.

—

maxRows

The number of rows rendered in the active sheet.

—

minimumRowHeightForComponents

The minimum height, in points, applied to rows that contain embedded components, so that the components remain visible. See Adding Components to Cells.

30

reportStyle

If true, the function bar and the sheet selection bar are hidden. See Report Mode.

false

rowBufferSize

The size, in pixels, of the vertical buffer rendered above and below the visible area.

200

selection

The initially selected cell or range in the A1 notation, for example A1 or B2:D8. See Selection.

—

sheetSelectionBarVisible

If true, the sheet tabs are shown at the bottom of the component.

true

statusLabelValue

The text shown in the status area of the component. Accepts a message key.

—

theme

The theme of the spreadsheet. Possible values: LUMO, VALO. LUMO aligns the component with the rest of the application; VALO is the classic spreadsheet look.

VALO

The following shared attributes are supported by spreadsheet:

id - alignSelf - classNames - colspan - css - height - justifySelf - maxHeight - maxWidth - minHeight - minWidth - visible - width

Handlers

The following handlers are specific to spreadsheet.

To generate a handler stub in Jmix Studio, use the Handlers tab of the Jmix UI inspector panel or the Generate Handler action available in the top panel of the view class and through the Code → Generate menu (Alt+Insert / Cmd+N).
Name Description

CellValueChangeEvent

Sent when the user has changed the value of one or more cells.

cellDeletionHandler

Spreadsheet.CellDeletionHandler deciding whether a cell may be cleared by the user. Use it to protect computed or reference cells from being emptied.

FormulaValueChangeEvent

Sent when the user has changed a formula in one or more cells.

hyperlinkCellClickHandler

Spreadsheet.HyperlinkCellClickHandler defining what happens when the user clicks a cell containing a hyperlink. Use it to navigate to an application view instead of opening an external URL.

ProtectedEditEvent

Sent when the user has attempted to edit a locked cell on a protected sheet.

RowHeaderDoubleClickEvent

Sent when the user has double-clicked a row header. The event provides the index of the row through getRowIndex().

SelectionChangeEvent

Sent when the selected cell or range has changed. See Selection.

SheetChangeEvent

Sent when the user has switched to another sheet.

spreadsheetComponentFactory

SpreadsheetComponentFactory supplying a component to be rendered inside a cell, either for display or for editing. See Adding Components to Cells.

spreadsheetHandler

SpreadsheetHandlerImpl handling the client-server protocol of the component. Override it only when the required behavior cannot be achieved through any other extension point.

The following shared handlers are supported by spreadsheet:

AttachEvent - DetachEvent

See Also