Change Cell's Style and Text Format"
From Documentation
| Line 22: | Line 22: | ||
===Clone Cell Style=== | ===Clone Cell Style=== | ||
| − | To keep original cell style and only modify part of style, we can use the <javadoc method="cloneStyleFrom(org.zkoss.poi.ss.usermodel.CellStyle)">org.zkoss.poi.ss.usermodel.CellStyle</javadoc> to clone a cell style. | + | To keep original cell style and only modify part of style, we can use the <javadoc directory="zss" method="cloneStyleFrom(org.zkoss.poi.ss.usermodel.CellStyle)">org.zkoss.poi.ss.usermodel.CellStyle</javadoc> to clone a cell style. |
<source lang="java" high="2,3"> | <source lang="java" high="2,3"> | ||
| Line 34: | Line 34: | ||
===Cell Style=== | ===Cell Style=== | ||
====Color==== | ====Color==== | ||
| − | We get color index from<javadoc method="getFillForegroundColor()">org.zkoss.poi.ss.usermodel.CellStyle</javadoc>. Usually we use color in #RRGGBB format. We can translate string to index <javadoc method="rgbToIndex(org.zkoss.zss.model.Book,java.lang.String)">org.zkoss.zss.model.impl.BookHelper</javadoc> | + | We get color index from<javadoc directory="zss" method="getFillForegroundColor()">org.zkoss.poi.ss.usermodel.CellStyle</javadoc>. Usually we use color in #RRGGBB format. We can translate string to index <javadoc directory="zss" method="rgbToIndex(org.zkoss.zss.model.Book,java.lang.String)">org.zkoss.zss.model.impl.BookHelper</javadoc> |
| − | 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 directory="zss" method="setFillForegroundColor(java.lang.Short)">org.zkoss.poi.ss.usermodel.CellStyle</javadoc>, and use <javadoc directory="zss" 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"> | ||
public void setCellColor(String color) { | public void setCellColor(String color) { | ||
| Line 63: | Line 63: | ||
====Alignment==== | ====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. | + | We can get cell's alignment information by <javadoc directory="zss" method="getAlignment()">org.zkoss.poi.ss.usermodel.CellStyle</javadoc>, and use <javadoc directory="zss" 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"> | ||
| Line 87: | Line 87: | ||
====Border==== | ====Border==== | ||
| − | We can set border by <javadoc method="setBorders(java.lang.Short, org.zkoss.poi.ss.usermodel.BorderStyle, java.lang.String)">org.zkoss.zss.model.Range</javadoc> | + | We can set border by <javadoc directory="zss" method="setBorders(java.lang.Short, org.zkoss.poi.ss.usermodel.BorderStyle, java.lang.String)">org.zkoss.zss.model.Range</javadoc> |
<source lang="java" high="3,5,"> | <source lang="java" high="3,5,"> | ||
| Line 126: | Line 126: | ||
===Font Style=== | ===Font Style=== | ||
| − | From <javadoc method="getFontIndex()">org.zkoss.poi.ss.usermodel.CellStyle</javadoc>, we get font index in the book, then we can get Font from Book.getFontAt() <javadoc method="getFontAt(java.lang.String)">org.zkoss.zss.model.Book</javadoc> | + | From <javadoc directory="zss" method="getFontIndex()">org.zkoss.poi.ss.usermodel.CellStyle</javadoc>, we get font index in the book, then we can get Font from Book.getFontAt() <javadoc directory="zss" method="getFontAt(java.lang.String)">org.zkoss.zss.model.Book</javadoc> |
| − | We can get or create Font by <javadoc method="getOrCreateFont(org.zkoss.zss.model.Book, java.lang.Sort, org.zkoss.poi.ss.usermodel.Color, java.lang.Short, java.lang.String, java.lang.Boolean, java.lang.Boolean, java.lang.Short, java.lang.Byte)">org.zkoss.zss.model.impl.BookHelper</javadoc> | + | We can get or create Font by <javadoc directory="zss" method="getOrCreateFont(org.zkoss.zss.model.Book, java.lang.Sort, org.zkoss.poi.ss.usermodel.Color, java.lang.Short, java.lang.String, java.lang.Boolean, java.lang.Boolean, java.lang.Short, java.lang.Byte)">org.zkoss.zss.model.impl.BookHelper</javadoc> |
====Font family==== | ====Font family==== | ||
| Line 158: | Line 158: | ||
====Font size==== | ====Font size==== | ||
| − | We can get font height by <javadoc method="getFontHeight()">org.zkoss.poi.ss.usermodel.Font</javadoc> , 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 | + | We can get font height by <javadoc directory="zss" method="getFontHeight()">org.zkoss.poi.ss.usermodel.Font</javadoc> , 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 |
<source lang="java" high="2"> | <source lang="java" high="2"> | ||
short getFontHeight(int fontSize) { | short getFontHeight(int fontSize) { | ||
| Line 165: | Line 165: | ||
</source> | </source> | ||
| − | After we get the font height to set, we can use <javadoc method="getOrCreateFont(org.zkoss.zss.model.Book, java.lang.Sort, org.zkoss.poi.ss.usermodel.Color, java.lang.Short, java.lang.String, java.lang.Boolean, java.lang.Boolean, java.lang.Short, java.lang.Byte)">org.zkoss.zss.model.impl.BookHelper</javadoc>. | + | After we get the font height to set, we can use <javadoc directory="zss" method="getOrCreateFont(org.zkoss.zss.model.Book, java.lang.Sort, org.zkoss.poi.ss.usermodel.Color, java.lang.Short, java.lang.String, java.lang.Boolean, java.lang.Boolean, java.lang.Short, java.lang.Byte)">org.zkoss.zss.model.impl.BookHelper</javadoc>. |
<source lang="java" high="9,11, 16,17"> | <source lang="java" high="9,11, 16,17"> | ||
| Line 193: | Line 193: | ||
====Bold==== | ====Bold==== | ||
| − | We can get font bold weight by <javadoc method="getBoldweight()">org.zkoss.poi.ss.usermodel.Font</javadoc> | + | We can get font bold weight by <javadoc directory="zss" method="getBoldweight()">org.zkoss.poi.ss.usermodel.Font</javadoc> |
<source lang="java" high="11,13,20,21"> | <source lang="java" high="11,13,20,21"> | ||
void setFontBold(boolean isBold) { | void setFontBold(boolean isBold) { | ||
| Line 224: | Line 224: | ||
====Italic==== | ====Italic==== | ||
| − | We can know whether font use italic or not by <javadoc method="getItalic()">org.zkoss.poi.ss.usermodel.Font</javadoc> | + | We can know whether font use italic or not by <javadoc directory="zss" method="getItalic()">org.zkoss.poi.ss.usermodel.Font</javadoc> |
<source lang="java" high="11,13,19,20"> | <source lang="java" high="11,13,19,20"> | ||
void setItalic(boolean isItalic) { | void setItalic(boolean isItalic) { | ||
| Line 254: | Line 254: | ||
====Underline==== | ====Underline==== | ||
| − | We can get font underline information by <javadoc method="getUnderline()">org.zkoss.poi.ss.usermodel.Font</javadoc> | + | We can get font underline information by <javadoc directory="zss" method="getUnderline()">org.zkoss.poi.ss.usermodel.Font</javadoc> |
<source lang="java" high="12,14,20,21"> | <source lang="java" high="12,14,20,21"> | ||
| Line 286: | Line 286: | ||
====Strikethrough==== | ====Strikethrough==== | ||
| − | We can know whether font use strikethrough or not by <javadoc method="getStrikeout()">org.zkoss.poi.ss.usermodel.Font</javadoc> | + | We can know whether font use strikethrough or not by <javadoc directory="zss" method="getStrikeout()">org.zkoss.poi.ss.usermodel.Font</javadoc> |
<source lang="java" high="12,14,20,21"> | <source lang="java" high="12,14,20,21"> | ||
void setStrikethrough(boolean isStrikethrough) { | void setStrikethrough(boolean isStrikethrough) { | ||
Revision as of 00:59, 20 December 2010
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(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();
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 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();
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(Book, Sort, Color, Short, String, Boolean, Boolean, Short, Byte).
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);
}
View complete source of ZUML cellStyle.zul
View complete source of composer CellStyleComposer.java
Version History
| Version | Date | Content |
|---|---|---|
All source code listed in this book is at Github.