Performance Guide

Refresh UI Update Manually

When calling Range setter method, zk spreadsheet will automatically check cell dependencies, update the dependent cells and refresh the spreadsheet UI of the range. However, in following cases, developers might not want such "automation" and like to control the evaluation and update by themselves:

• Change a lot of cells in a batch.

If we don't disable auto refresh in such case, the zk spreadsheet will generate a lot of small AU response to a browser which slows down browser rendering speed.

• initialize a book upon a data source (e.g. a database) before zk spreadsheet renders itself.

Sometimes we need to load the data from a database to initialize a sheet before zk spreadsheet renders in a browser. Disable the auto refresh can eliminate Spreadsheet's unnecessary internal calculations for rendering.

In order to manually control UI update, we have to:

1. disable auto-refresh with setAutoRefresh(false)
2. notify changed area with notifyChange()

	private void loadData() {
		Sheet sheet = ss.getSelectedSheet();
		for (int column  = 0 ; column < COLUMN_SIZE ; column++){
			for (int row = 0 ; row < ROW_SIZE ; row++ ){
				Range range = Ranges.range(sheet, row, column);
				range.setAutoRefresh(false);
				range.getCellData().setEditText(row+", "+column);
				CellOperationUtil.applyFontColor(range, "#0099FF");
				CellOperationUtil.applyAlignment(range, Alignment.CENTER);
			}
		}
		Ranges.range(ss.getSelectedSheet(), 0, 0, ROW_SIZE, COLUMN_SIZE).notifyChange();
                ...
	}

• line 6: disable the auto-refresh before changing cells (calling setter)
• line 12: notify changed range of cells

You can run the book's example code to know the speed difference between 2 cases.

Notify Affected Range

When notifying a change, remember to choose an affected range, not just those cells you modify. The following cases explain the reasons:

• Change a cell referenced by a formula in another cell.

If you change a cell, all those cells that contain a formula referencing the cell should also require an update.

• Insertion / deletion of cells / rows / columns.

If you insert a column, all columns after the inserted column also require an update.

Notify the whole sheet

If the affected cells are too distributed, you can consider notifying the whole sheet. But this might make a sheet blank for a moment because it will re-render the whole sheet.

Ranges.range(ss.getSelectedSheet()).notifyChange();

Notify the cached area

If rendering a whole sheet is too slow, you can also consider to notify the currently cached area.

Spreadsheet ss;
// change cells
ss.notifyLoadedAreaChange();

Ignore Height Calculation

since 3.8.3

If your application doesn't allow users to do any operation that needs a row height calculation e.g. enable / disable wrap text, change a font size, then you can set the attribute ignoreAutoHeight to true. This will improve client-side rendering speed much because it avoids time-consuming cell's height calculating for each row.

<!-- default is false -->
<spreadsheet  ignoreAutoHeight="true"/>

Initialize with Large Data

since 3.9.1

A typical use case is loading a template file and inserting application data from a database at the beginning. Normally, this will generate lots of internal events and formula dependency recalculation which is unnecessary before showing ZK Spreadsheet to a browser. You can implement PostImport and put your initialization logic in process(). Then ZSS will invoke process() right after the file importing and turn off those unnecessary update triggered by Range API. Therefore, it can speed up the data/formula importing.

public class PostImportComposer extends SelectorComposer<Component> implements PostImport{

	//..

	@Override
	public void process(Book book) {
		//implement initialization logic here
		initializeMassiveFormulas(book.getSheetAt(0));
	}

All source code listed in this book is at Github.