ZK Spreadsheet Essentials/Working with ZK Spreadsheet/Configure and Control ZK Spreadsheet/Change Cell's Style and Text Format

Style Supported	Notes
Font / Font Color / Fill Color	Cause of browser limitation, font also depends on installed font on client side
Border / Border Color	Cause of browser limitation, only solid/dashed/dotted border are supported now.
Horizontal Alignment	Vertical Alignment has not implemented yet
Text Wrap & Overflow
Horizontal Merged Cell	Vertical Merged Cell has not implemented yet

Prepare

1. User's selection range
We need to know user's selection range to set style. We can use onCellSelection event.

spreadsheet.addEventListener(Events.ON_CELL_SELECTION, new EventListener() {

	public void onEvent(Event event) throws Exception {
		Rect selection = spreadsheet.getSelection();
	}
});

2. Clone cell style
If we wanna to keep original cell style and only modify part of style, we can use the sample code below to clone a cell style.

CellStyle cloneStyle(CellStyle srcStyle, Book book) {
	CellStyle newStyle =  book.createCellStyle();
	newStyle.cloneStyleFrom(srcStyle);
	return newStyle;
}

Cell Style

Color

We can get color from CellStyle.getFillForegroundColor(), it return a color index. However, we usually use color in #RRGGBB format. We can translate string to index by BookHelper.rgbToIndex(book, color)

After we clone cell style, we can modify color by CellStyle.setFillForegroundColor(), and use Range.setStyle() to set new style

void setCellColor(String color) {
		Rect rect = getSelection();
		Sheet sheet = spreadsheet.getSelectedSheet();
		Book book = spreadsheet.getBook();
		short colorIndex = BookHelper.rgbToIndex(book, color);
		
		for (int row = rect.getTop(); row <= rect.getBottom(); row++) {
			for (int col = rect.getLeft(); col <= rect.getRight(); col++) {
				Cell cell = Utils.getOrCreateCell(sheet, row, col);
				CellStyle cellStyle = cell.getCellStyle();
				final short srcColor = cellStyle.getFillForegroundColor();
				
				if (srcColor != colorIndex) {
					CellStyle newStyle = cloneStyle(cellStyle, book);
					newStyle.cloneStyleFrom(cellStyle);
					newStyle.setFillForegroundColor(colorIndex);
					
					Ranges.range(sheet, row, col).setStyle(newStyle);
				}
			}
		}
	}

Alignment

We can get cell's alignment information by CellStyle.getAlignment(), and use CellStyle.setAlignment() to set alignment.

	void setAlignment(short alignment) {
		Rect rect = getSelection();
		Sheet sheet = spreadsheet.getSelectedSheet();
		Book book = spreadsheet.getBook();
		for (int row = rect.getTop(); row <= rect.getBottom(); row++) {
			for (int col = rect.getLeft(); col <= rect.getRight(); col++) {

				Cell cell = Utils.getOrCreateCell(sheet, row, col);
				short srcAlign = cell.getCellStyle().getAlignment();
				if (srcAlign != alignment) {
					CellStyle newStyle = cloneStyle(cell.getCellStyle(), book);
					newStyle.setAlignment(alignment);
					
					Ranges.range(sheet, row, col).setStyle(newStyle);
				}
			}
		}
	}

Border

We can set border by Range.setBorders(), specify
1. Border position Border position can be top (BookHelper.BORDER_EDGE_TOP) , left (BookHelper.BORDER_EDGE_LEFT) etc...

2. Border Style Style can be BorderStyle.MEDIUM or set BorderStyle.NONE to remove border.

3. Border color Color in #RRGGBB format

//Border color
String color = "#000000";

//Border style
BorderStyle style = "none".equals(border) ? BorderStyle.NONE : BorderStyle.MEDIUM;
		
Rect rect = getSelection();
Sheet sheet = spreadsheet.getSelectedSheet();
int lCol = rect.getLeft();
int rCol = rect.getRight();
int tRow = rect.getTop();
int bRow = rect.getBottom();
if ("bottom".equals(border)) {
	Ranges.range(sheet, tRow, lCol, bRow, rCol).
		setBorders(BookHelper.BORDER_EDGE_BOTTOM, style, color);
} else if ("top".equals(border)) {
	Ranges.range(sheet, tRow, lCol, tRow, rCol).
		setBorders(BookHelper.BORDER_EDGE_TOP, style, color);
} else if ("left".equals(border)) {
	Ranges.range(sheet, tRow, lCol, bRow, lCol).
		setBorders(BookHelper.BORDER_EDGE_LEFT, style, color);
} else if ("right".equals(border)) {
	Ranges.range(sheet, tRow, rCol, bRow, rCol).
		setBorders(BookHelper.BORDER_EDGE_RIGHT, style, color);
} else if ("none".equals(border)) {
	Ranges.range(sheet, tRow, lCol, bRow, rCol).
		setBorders(BookHelper.BORDER_FULL, style, color);
} else if ("full".equals(border)) {
	Ranges.range(sheet, tRow, lCol, bRow, rCol).
		setBorders(BookHelper.BORDER_FULL, style, color);
}

Font Style

From CellStyle.getFontIndex(), we can font index in the book, then we can get Font from Book.getFontAt()
We can create Font by BookHelper.getOrCreateFont() and specify attributes, for example font family, font size, color, ext...

Font family

void setFontFamily(String fontName) {
	Rect rect = getSelection();
	Sheet sheet = spreadsheet.getSelectedSheet();
	Book book = spreadsheet.getBook();
	for (int row = rect.getTop(); row <= rect.getBottom(); row++) {
		for (int col = rect.getLeft(); col <= rect.getRight(); col++) {
			Cell cell = Utils.getOrCreateCell(sheet, row, col);
			Font srcFont = book.getFontAt(cell.getCellStyle().getFontIndex());
					
			if (srcFont.getFontName() != fontName) {
				Font newFont = 
BookHelper.getOrCreateFont(book, srcFont.getBoldweight(), BookHelper.getFontColor(book, srcFont), 
Font.getFontHeight(), fontName, 	
srcFont.getItalic(), srcFont.getStrikeout(), srcFont.getTypeOffset(), srcFont.getUnderline());
						
				CellStyle newStyle = cloneStyle(cell.getCellStyle(), book);
				newStyle.setFont(newFont);
						
				Ranges.range(sheet, row, col).setStyle(newStyle);
			}
		}
	}
}

Strikethrough

All source code listed in this book is at Github.