Change Cell's Style and Text Format"

From Documentation
Line 38: Line 38:
 
After we clone cell style, we can modify color by <javadoc method="setFillForegroundColor(java.lang.Short)">org.zkoss.poi.ss.usermodel.CellStyle</javadoc>, and use  <javadoc method="setStyle(org.zkoss.poi.ss.usermodel.CellStyle)">org.zkoss.zss.model.Range</javadoc>to set new style
 
After we clone cell style, we can modify color by <javadoc method="setFillForegroundColor(java.lang.Short)">org.zkoss.poi.ss.usermodel.CellStyle</javadoc>, and use  <javadoc method="setStyle(org.zkoss.poi.ss.usermodel.CellStyle)">org.zkoss.zss.model.Range</javadoc>to set new style
 
<source lang="java" high="5,11,16,17">
 
<source lang="java" high="5,11,16,17">
−
void setCellColor(String color) {
+
public void setCellColor(String color) {
−
Rect rect = getSelection();
+
Rect rect = getSelection();
−
Sheet sheet = spreadsheet.getSelectedSheet();
+
Sheet sheet = spreadsheet.getSelectedSheet();
−
Book book = spreadsheet.getBook();
+
Book book = spreadsheet.getBook();
−
short colorIndex = BookHelper.rgbToIndex(book, color);
+
short colorIndex = BookHelper.rgbToIndex(book, color);
 
 
−
for (int row = rect.getTop(); row <= rect.getBottom(); row++) {
+
for (int row = rect.getTop(); row <= rect.getBottom(); row++) {
−
for (int col = rect.getLeft(); col <= rect.getRight(); col++) {
+
for (int col = rect.getLeft(); col <= rect.getRight(); col++) {
−
Cell cell = Utils.getOrCreateCell(sheet, row, col);
+
Cell cell = Utils.getOrCreateCell(sheet, row, col);
−
CellStyle cellStyle = cell.getCellStyle();
+
CellStyle cellStyle = cell.getCellStyle();
−
final short srcColor = cellStyle.getFillForegroundColor();
+
final short srcColor = cellStyle.getFillForegroundColor();
 
 
−
if (srcColor != colorIndex) {
+
if (srcColor != colorIndex) {
−
CellStyle newStyle = cloneStyle(cellStyle, book);
+
CellStyle newStyle = cloneStyle(cellStyle, book);
−
newStyle.cloneStyleFrom(cellStyle);
+
newStyle.cloneStyleFrom(cellStyle);
−
newStyle.setFillForegroundColor(colorIndex);
+
newStyle.setFillForegroundColor(colorIndex);
−
Ranges.range(sheet, row, col).setStyle(newStyle);
+
Ranges.range(sheet, row, col).setStyle(newStyle);
−
}
 
 
}
 
}
 
}
 
}
 
}
 
}
 +
}
 
</source>
 
</source>
  
 
====Alignment====
 
====Alignment====
  
−
We can get cell's alignment information by CellStyle.getAlignment(), and use CellStyle.setAlignment() to set alignment.
+
We can get cell's alignment information by <javadoc method="getAlignment()">org.zkoss.poi.ss.usermodel.CellStyle</javadoc>, and use <javadoc method="setAlignment(java.lang.Short)">org.zkoss.poi.ss.usermodel.CellStyle</javadoc> to set alignment.
  
 
<source lang="java" high="9,12,13">
 
<source lang="java" high="9,12,13">
−
void setAlignment(short alignment) {
+
public void setAlignment(short alignment) {
−
Rect rect = getSelection();
+
Rect rect = getSelection();
−
Sheet sheet = spreadsheet.getSelectedSheet();
+
Sheet sheet = spreadsheet.getSelectedSheet();
−
Book book = spreadsheet.getBook();
+
Book book = spreadsheet.getBook();
−
for (int row = rect.getTop(); row <= rect.getBottom(); row++) {
+
for (int row = rect.getTop(); row <= rect.getBottom(); row++) {
−
for (int col = rect.getLeft(); col <= rect.getRight(); col++) {
+
for (int col = rect.getLeft(); col <= rect.getRight(); col++) {
  
−
Cell cell = Utils.getOrCreateCell(sheet, row, col);
+
Cell cell = Utils.getOrCreateCell(sheet, row, col);
−
short srcAlign = cell.getCellStyle().getAlignment();
+
short srcAlign = cell.getCellStyle().getAlignment();
−
if (srcAlign != alignment) {
+
if (srcAlign != alignment) {
−
CellStyle newStyle = cloneStyle(cell.getCellStyle(), book);
+
CellStyle newStyle = cloneStyle(cell.getCellStyle(), book);
−
newStyle.setAlignment(alignment);
+
newStyle.setAlignment(alignment);
−
Ranges.range(sheet, row, col).setStyle(newStyle);
+
Ranges.range(sheet, row, col).setStyle(newStyle);
−
}
 
 
}
 
}
 
}
 
}
 
}
 
}
 +
}
 
</source>
 
</source>
  

Revision as of 03:16, 23 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



Purpose

ZK Spreadsheet support 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.
Horizontal Alignment Vertical Alignment has not implemented yet
Text Wrap & Overflow
Horizontal Merged Cell Vertical Merged Cell has not implemented yet

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 index fromCellStyle.getFillForegroundColor(). Usually we use color in #RRGGBB format. We can translate string to index BookHelper.rgbToIndex(Book, String)

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();
	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(Short) to set alignment. 

public 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);
			}
		}
	}
}

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);
}

Set cell alignment

Toolbarbutton alignLeftBtn;
Toolbarbutton alignCenterBtn;
Toolbarbutton alignRightBtn;
public void onAlignHorizontalClick(ForwardEvent event) {
	String alignStr = (String) event.getData();

	short align = CellStyle.ALIGN_GENERAL;
	if (alignStr.equals("left")) {
		align = CellStyle.ALIGN_LEFT;
	}

	if (alignStr.equals("center")) {
		align = CellStyle.ALIGN_CENTER;
	}

	if (alignStr.equals("right")) {
		align = CellStyle.ALIGN_RIGHT;
	}

	setAlignment(align);
}

Version History

Last Update : 2010/11/23


Version Date Content
     


All source code listed in this book is at Github.


Last Update : 2010/11/23

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=11869"