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

This article is out of date, please refer to http://books.zkoss.org/wiki/ZK_Spreadsheet_Essentials for more up to date information.

Purpose

ZK Spreadsheet supports various cell style and font style.

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.
Alignment	Horizontal, Vertical Alignment
Text Wrap & Overflow
Merged Cell	Horizontal/Vertical Merged Cell
Data Formats	Format cell

Clone Cell Style

To keep original cell style and only modify part of style, we can use the CellStyle.cloneStyleFrom(CellStyle) to clone a cell style.

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

Cell Style

Color

We get color from CellStyle.getFillForegroundColorColor().

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

public void setCellColor(String color) {
	Rect rect = getSelection();
	Worksheet sheet = spreadsheet.getSelectedSheet();
	Book book = spreadsheet.getBook();
	final Color newColor = BookHelper.HTMLToColor(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 Color srcColor = cellStyle.getFillForegroundColorColor();
				
			if (!Objects.equals(newColor, srcColor)) {
				CellStyle newStyle = cloneStyle(cellStyle, book);
				newStyle.cloneStyleFrom(cellStyle);
				BookHelper.setFillForegroundColor(newStyle, newColor);//use CellStyle.setFillForegroundColor
				Ranges.range(sheet, row, col).setStyle(newStyle);
			}
		}
	}
}

Alignment

Horizontal alignment

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

public void setAlignment(short alignment) {
	Rect rect = getSelection();
	Worksheet 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);
			}
		}
	}
}

VerticalAlignment alignment

We can get the cell's vertical alignment information by CellStyle.getVerticalAlignment(), and use CellStyle.setVerticalAlignment(Short) to set vertical alignment.

public void setVerticalAlignment(short alignment) {
	Rect rect = getSelection();
	Worksheet 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().getVerticalAlignment();
			if (srcAlign != alignment) {
				CellStyle newStyle = cloneStyle(cell.getCellStyle(), book);
				newStyle.setVerticalAlignment(alignment);
				Ranges.range(sheet, row, col).setStyle(newStyle);
			}
		}
	}
}

Border

We can set the border by Range.setBorders(Short, BorderStyle, String)

void setBorder(String border) {
	//Border color
	String color = "#000000";
	//Border style
	BorderStyle style = "none".equals(border) ? BorderStyle.NONE : BorderStyle.MEDIUM;
		
	Rect rect = getSelection();
	Worksheet 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 get font index in the book, then we can get Font from Book.getFontAt() Book.getFontAt(String) We can get or create Font by BookHelper.getOrCreateFont(Book, Sort, Color, Short, String, Boolean, Boolean, Short, Byte)

Font family

void setFontFamily(String fontName) {
	Rect rect = getSelection();
	Worksheet 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);
			}
		}
	}
}

Font size

We can get the font height by using Font.getFontHeight() , however the font size is different from the font height. The font height in unit's of 1/20th of a point, so we can transform the font size to the font height by

short getFontHeight(int fontSize) {
	return (short) (fontSize * 20);
}

After we get the font height to set, we can use BookHelper.getOrCreateFont(Book, Sort, Color, Short, String, Boolean, Boolean, Short, Byte).

void setFontSize(short fontHeight) {
	Rect rect = getSelection();
	Worksheet 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.getFontHeight() != fontHeight) {
				Font newFont = BookHelper.getOrCreateFont(book, srcFont.getBoldweight(), 
BookHelper.getFontColor(book, srcFont), fontHeight, srcFont.getFontName(), 
srcFont.getItalic(), srcFont.getStrikeout(), srcFont.getTypeOffset(), srcFont.getUnderline());
					
				CellStyle newStyle = cloneStyle(cell.getCellStyle(), book);
				newStyle.setFont(newFont);	
				Ranges.range(sheet, row, col).setStyle(newStyle);
			}
		}
	}
}

Bold

We can get the font bold weight by employing Font.getBoldweight()

void setFontBold(boolean isBold) {
	Rect rect = getSelection();
	Worksheet 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());
					
			boolean srcBold = srcFont.getBoldweight() == Font.BOLDWEIGHT_BOLD; 
			if (srcBold != isBold) {
				Font newFont = BookHelper.getOrCreateFont(book, 
isBold ? Font.BOLDWEIGHT_BOLD : Font.BOLDWEIGHT_NORMAL, 
BookHelper.getFontColor(book, srcFont), srcFont.getFontHeight(), 
srcFont.getFontName(), srcFont.getItalic(), srcFont.getStrikeout(),
 srcFont.getTypeOffset(), srcFont.getUnderline());
						
				CellStyle newStyle = cloneStyle(cell.getCellStyle(), book);
				newStyle.setFont(newFont);
				Ranges.range(sheet, row, col).setStyle(newStyle);
			}
		}
	}
}

Italic

We can know whether the font uses italic or not by Font.getItalic()

void setItalic(boolean isItalic) {
	Rect rect = getSelection();
	Worksheet 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());
					
			boolean srcItalic = srcFont.getItalic();
			if (srcItalic != isItalic) {
				Font newFont = BookHelper.getOrCreateFont(book, 
srcFont.getBoldweight(), BookHelper.getFontColor(book, srcFont), 
srcFont.getFontHeight(), srcFont.getFontName(), isItalic, 
srcFont.getStrikeout(), srcFont.getTypeOffset(), srcFont.getUnderline());
					
				CellStyle newStyle = cloneStyle(cell.getCellStyle(), book);
				newStyle.setFont(newFont);
				Ranges.range(sheet, row, col).setStyle(newStyle);
			}
		}
	}
}

Underline

We can get the font underline information by using Font.getUnderline()

void setUnderline(boolean isUnderline){
	Rect rect = getSelection();
	Worksheet 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());
				
			boolean srcUnderline = srcFont.getUnderline() == Font.U_SINGLE;
			if (srcUnderline != isUnderline) {
				Font newFont = BookHelper.getOrCreateFont(book, srcFont.getBoldweight(), 
BookHelper.getFontColor(book, srcFont), srcFont.getFontHeight(), 
srcFont.getFontName(), srcFont.getItalic(), srcFont.getStrikeout(), 
srcFont.getTypeOffset(), isUnderline ? Font.U_SINGLE : Font.U_NONE);
						
				CellStyle newStyle = cloneStyle(cell.getCellStyle(), book);
				newStyle.setFont(newFont);		
				Ranges.range(sheet, row, col).setStyle(newStyle);
			}
		}
	}
}

Strikethrough

We can know whether the font uses strikethrough or not by Font.getStrikeout()

void setStrikethrough(boolean isStrikethrough) {
	Rect rect = getSelection();
	Worksheet 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());
					
			boolean srcStrikethrough = srcFont.getStrikeout();
			if (srcStrikethrough != isStrikethrough) {
				Font newFont = BookHelper.getOrCreateFont(book, srcFont.getBoldweight(), 
BookHelper.getFontColor(book, srcFont), srcFont.getFontHeight(), 
srcFont.getFontName(), srcFont.getItalic(), isStrikethrough, 
srcFont.getTypeOffset(), srcFont.getUnderline());
						
				CellStyle newStyle = cloneStyle(cell.getCellStyle(), book);
				newStyle.setFont(newFont);
				Ranges.range(sheet, row, col).setStyle(newStyle);
			}
		}
	}
}

Data Format

Get data format using CellStyle.getDataFormat() and set new data format using CellStyle.setDataFormat(format)

	private void setCellFormat(String format) {
		Book book = spreadsheet.getBook();
		short dstFormat = book.createDataFormat().getFormat(format);
		
		Rect rect = getSelection();
		Worksheet sheet = spreadsheet.getSelectedSheet();
		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 srcCellStyle = cell.getCellStyle();
				short srcDataFormat = srcCellStyle.getDataFormat();
				
				if (srcDataFormat != dstFormat) {
					CellStyle newStyle = book.createCellStyle();
					newStyle.cloneStyleFrom(srcCellStyle);
					newStyle.setDataFormat(dstFormat);
					Ranges.range(sheet, row, col).setStyle(newStyle);
				}
			}
		}
	}

Composer

Current Selection

Rect selection;
public void onCellSelection$spreadsheet(CellSelectionEvent event) {
	selection = spreadsheet.getSelection();
}

Select Font Family

Combobox fontFamily;
public void onSelect$fontFamily() {
	String fontName = fontFamily.getText();
	setFontFamily(fontName);
}

Select Font Size

Combobox fontSize
public void onSelect$fontSize() {
	short fontHeight = getFontHeight(Integer.parseInt(fontSize.getText()));
	setFontSize(fontHeight);
}

Select Font Color

Colorbox fontColor;
public void onChange$fontColor() {
	String color = fontColor.getColor();
	setFontColor(color);
}

Set Font Bold

boolean isBold;
Toolbarbutton boldBtn;
public void onClick$boldBtn() {
	isBold = !isBold;
	setFontBold(isBold);
}

Set Font Italic

boolean isItalic;
Toolbarbutton italicBtn;
public void onClick$italicBtn() {
	isItalic = !isItalic;
	setItalic(isItalic);
}

Set Font Underline

boolean isUnderline;
Toolbarbutton underlineBtn;
public void onClick$underlineBtn() {
	isUnderline = !isUnderline;
	setUnderline(isUnderline);
}

Set Font Strikethrough

boolean isStrikethrough;
Toolbarbutton strikethroughBtn;
public void onClick$strikethroughBtn() {
	isStrikethrough = !isStrikethrough;
	setStrikethrough(isStrikethrough);
}

Select Cell Color

Colorbox cellColor;
public void onChange$cellColor() {
	String color = cellColor.getColor();
	setCellColor(color);
}

public void setCellColor(String color) {
    Rect rect = getSelection();
    Worksheet sheet = spreadsheet.getSelectedSheet();
    Book book = spreadsheet.getBook();
    final Color newColor = BookHelper.HTMLToColor(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 Color srcColor = cellStyle.getFillForegroundColorColor();
            
            if (!Objects.equals(newColor, srcColor)) {
                CellStyle newStyle = cloneStyle(cellStyle, book);
                newStyle.cloneStyleFrom(cellStyle);
                BookHelper.setFillForegroundColor(newStyle, newColor);
                
                Ranges.range(sheet, row, col).setStyle(newStyle);
            }
        }
    }
}

Set Cell Alignment

public void onAlignClick(ForwardEvent event) {
	String alignStr = (String) event.getData();

	short align = CellStyle.ALIGN_GENERAL;
	boolean isVer = false;
	if (alignStr.equals("left")) {
		align = CellStyle.ALIGN_LEFT;
	} else if (alignStr.equals("center")) {
		align = CellStyle.ALIGN_CENTER;
	} else if (alignStr.equals("right")) {
		align = CellStyle.ALIGN_RIGHT;
	} else if (alignStr.equals("top")) {
		align = CellStyle.VERTICAL_TOP;
		isVer = true;
	} else if (alignStr.equals("middle")) {
		align = CellStyle.VERTICAL_CENTER;
		isVer = true;
	} else if (alignStr.equals("bottom")) {
		align = CellStyle.VERTICAL_BOTTOM;
		isVer = true;
	}

	if (!isVer)
		setAlignment(align);
	else
		setVerticalAlignment(align);
}

Select Data Format

public void onSelect$dateFormat(SelectEvent event) {
	String format = (String) dateFormat.getSelectedItem().getValue();
	setCellFormat(format);
}

View complete source of ZUML cellStyle.zul

View complete source of composer CellStyleComposer.java

Version History

Version	Date	Content
2.1.0	May, 2011	Vertical Alignment

All source code listed in this book is at Github.