Change Cell's Style and Text Format"

From Documentation
Line 304: Line 304:
  
 
We can know whether font use strikethrough or not by Font.getStrikeout()
 
We can know whether font use strikethrough or not by Font.getStrikeout()
−
<source lang="java" >
+
<source lang="java" high="12,14,20,21">
 
void setStrikethrough(boolean isStrikethrough) {
 
void setStrikethrough(boolean isStrikethrough) {
 
Rect rect = getSelection();
 
Rect rect = getSelection();
Line 325: Line 325:
 
CellStyle newStyle = cloneStyle(cell.getCellStyle(), book);
 
CellStyle newStyle = cloneStyle(cell.getCellStyle(), book);
 
newStyle.setFont(newFont);
 
newStyle.setFont(newFont);
−
 
 
Ranges.range(sheet, row, col).setStyle(newStyle);
 
Ranges.range(sheet, row, col).setStyle(newStyle);
 
}
 
}

Revision as of 06:09, 18 November 2010


DocumentationZK Spreadsheet EssentialsWorking with ZK SpreadsheetConfigure and Control ZK SpreadsheetChange Cell's Style and Text Format
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 

void setBorder(String border) {
	//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);
			}
		}
	}
}

Font size

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

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

After we get the font height to set, we can use BookHelper.getOrCreateFont() to create font. 

void setFontSize(short fontHeight) {
	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.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 font bold weight by Font.getBoldweight() 

void setFontBold(boolean isBold) {
	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());
					
			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 font use italic or not by Font.getItalic() 

void setItalic(boolean isItalic) {
	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());
					
			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 font underline information by Font.getUnderline() 

void setUnderline(boolean isUnderline){
	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());
				
			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 font use strikethrough or not by Font.getStrikeout() 

void setStrikethrough(boolean isStrikethrough) {
	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());
					
			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);
			}
		}
	}
}

Version History

Last Update : 2010/11/18


Version Date Content
     


All source code listed in this book is at Github.


Last Update : 2010/11/18

Copyright © Potix Corporation. This article is licensed under GNU Free Documentation License.


Retrieved from "https://www.zkoss.org/_w/index.php?title=ZK_Spreadsheet_Essentials/Working_with_ZK_Spreadsheet/Configure_and_Control_ZK_Spreadsheet/Change_Cell%27s_Style_and_Text_Format&oldid=11062"