Change Cell's Style and Text Format"

From Documentation
m (correct highlight (via JWB))
 
(37 intermediate revisions by 5 users not shown)
Line 1: Line 1:
 
{{ZKSpreadsheetEssentialsPageHeader}}
 
{{ZKSpreadsheetEssentialsPageHeader}}
 +
 +
{{Deprecated|url=http://books.zkoss.org/wiki/ZK_Spreadsheet_Essentials}}
 +
 +
  
 
__TOC__
 
__TOC__
  
 
===Purpose===
 
===Purpose===
ZK Spreadsheet support various cell style and font style.
+
ZK Spreadsheet supports various cell style and font style.
  
 
{| border="1"
 
{| border="1"
Line 14: Line 18:
 
||Border / Border Color || Cause of browser limitation, only solid/dashed/dotted border are supported now.
 
||Border / Border Color || Cause of browser limitation, only solid/dashed/dotted border are supported now.
 
|-
 
|-
||Horizontal Alignment || Vertical Alignment has not implemented yet
+
||Alignment || Horizontal, Vertical Alignment
 
|-
 
|-
 
||Text Wrap & Overflow ||
 
||Text Wrap & Overflow ||
 
|-
 
|-
||Horizontal Merged Cell || Vertical Merged Cell has not implemented yet
+
|| Merged Cell || Horizontal/Vertical Merged Cell
 +
|-
 +
|| Data Formats || Format cell
 
|}
 
|}
  
 
===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" highlight="2,3">
 
CellStyle cloneStyle(CellStyle srcStyle, Book book) {
 
CellStyle cloneStyle(CellStyle srcStyle, Book book) {
 
CellStyle newStyle =  book.createCellStyle();
 
CellStyle newStyle =  book.createCellStyle();
Line 34: Line 40:
 
===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 from <javadoc directory="zss" method="getFillForegroundColorColor()">org.zkoss.poi.ss.usermodel.CellStyle</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" highlight="5,11,16,17">
 
public void setCellColor(String color) {
 
public void setCellColor(String color) {
 
Rect rect = getSelection();
 
Rect rect = getSelection();
Sheet sheet = spreadsheet.getSelectedSheet();
+
Worksheet sheet = spreadsheet.getSelectedSheet();
 
Book book = spreadsheet.getBook();
 
Book book = spreadsheet.getBook();
short colorIndex = BookHelper.rgbToIndex(book, color);
+
final Color newColor = BookHelper.HTMLToColor(book, color);
 
 
 
for (int row = rect.getTop(); row <= rect.getBottom(); row++) {
 
for (int row = rect.getTop(); row <= rect.getBottom(); row++) {
Line 48: Line 54:
 
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 Color srcColor = cellStyle.getFillForegroundColorColor();
 
 
if (srcColor != colorIndex) {
+
if (!Objects.equals(newColor, srcColor)) {
 
CellStyle newStyle = cloneStyle(cellStyle, book);
 
CellStyle newStyle = cloneStyle(cellStyle, book);
 
newStyle.cloneStyleFrom(cellStyle);
 
newStyle.cloneStyleFrom(cellStyle);
newStyle.setFillForegroundColor(colorIndex);
+
BookHelper.setFillForegroundColor(newStyle, newColor);//use CellStyle.setFillForegroundColor
 
Ranges.range(sheet, row, col).setStyle(newStyle);
 
Ranges.range(sheet, row, col).setStyle(newStyle);
 
}
 
}
Line 63: Line 69:
 
====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.
+
=====Horizontal alignment=====
 +
We can get the cell's horizontal 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 horizontal  alignment.
  
<source lang="java" high="9,12,13">
+
<source lang="java" highlight="9,12,13">
 
public void setAlignment(short alignment) {
 
public void setAlignment(short alignment) {
 
Rect rect = getSelection();
 
Rect rect = getSelection();
Sheet sheet = spreadsheet.getSelectedSheet();
+
Worksheet 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++) {
Line 78: Line 85:
 
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);
 +
}
 +
}
 +
}
 +
}
 +
</source>
 +
 +
=====VerticalAlignment alignment=====
 +
We can get the cell's vertical alignment information by <javadoc directory="zss" method="getVerticalAlignment()">org.zkoss.poi.ss.usermodel.CellStyle</javadoc>, and use <javadoc directory="zss"  method="setVerticalAlignment(java.lang.Short)">org.zkoss.poi.ss.usermodel.CellStyle</javadoc> to set vertical alignment.
 +
 +
<source lang="java" highlight="9,12,13">
 +
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);
 
Ranges.range(sheet, row, col).setStyle(newStyle);
 
}
 
}
Line 87: Line 117:
 
====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 the 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" highlight="3,5,">
 
void setBorder(String border) {
 
void setBorder(String border) {
 
//Border color
 
//Border color
Line 97: Line 127:
 
 
 
Rect rect = getSelection();
 
Rect rect = getSelection();
Sheet sheet = spreadsheet.getSelectedSheet();
+
Worksheet sheet = spreadsheet.getSelectedSheet();
 
int lCol = rect.getLeft();
 
int lCol = rect.getLeft();
 
int rCol = rect.getRight();
 
int rCol = rect.getRight();
Line 126: Line 156:
 
===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====
  
<source lang="java" high="8,11, 17,18">
+
<source lang="java" highlight="8,11, 17,18">
 
void setFontFamily(String fontName) {
 
void setFontFamily(String fontName) {
 
Rect rect = getSelection();
 
Rect rect = getSelection();
Sheet sheet = spreadsheet.getSelectedSheet();
+
Worksheet 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++) {
Line 158: Line 188:
 
====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 the font height by using <javadoc directory="zss" method="getFontHeight()">org.zkoss.poi.ss.usermodel.Font</javadoc> , 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  
<source lang="java" high="2">
+
<source lang="java" highlight="2">
 
short getFontHeight(int fontSize) {
 
short getFontHeight(int fontSize) {
 
return (short) (fontSize * 20);
 
return (short) (fontSize * 20);
Line 165: Line 195:
 
</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" highlight="9,11, 16,17">
 
void setFontSize(short fontHeight) {
 
void setFontSize(short fontHeight) {
 
Rect rect = getSelection();
 
Rect rect = getSelection();
Sheet sheet = spreadsheet.getSelectedSheet();
+
Worksheet 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++) {
Line 193: Line 223:
 
====Bold====
 
====Bold====
  
We can get font bold weight by <javadoc method="getBoldweight()">org.zkoss.poi.ss.usermodel.Font</javadoc>
+
We can get the font bold weight by employing <javadoc directory="zss" method="getBoldweight()">org.zkoss.poi.ss.usermodel.Font</javadoc>
<source lang="java" high="11,13,20,21">
+
<source lang="java" highlight="11,13,20,21">
 
void setFontBold(boolean isBold) {
 
void setFontBold(boolean isBold) {
 
Rect rect = getSelection();
 
Rect rect = getSelection();
Sheet sheet = spreadsheet.getSelectedSheet();
+
Worksheet 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++) {
Line 224: Line 254:
 
====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 the font uses 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" highlight="11,13,19,20">
 
void setItalic(boolean isItalic) {
 
void setItalic(boolean isItalic) {
 
Rect rect = getSelection();
 
Rect rect = getSelection();
Sheet sheet = spreadsheet.getSelectedSheet();
+
Worksheet 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++) {
Line 254: Line 284:
 
====Underline====
 
====Underline====
  
We can get font underline information by <javadoc method="getUnderline()">org.zkoss.poi.ss.usermodel.Font</javadoc>
+
We can get the font underline information by using <javadoc directory="zss" method="getUnderline()">org.zkoss.poi.ss.usermodel.Font</javadoc>
  
<source lang="java" high="12,14,20,21">
+
<source lang="java" highlight="12,14,20,21">
 
void setUnderline(boolean isUnderline){
 
void setUnderline(boolean isUnderline){
 
Rect rect = getSelection();
 
Rect rect = getSelection();
Sheet sheet = spreadsheet.getSelectedSheet();
+
Worksheet sheet = spreadsheet.getSelectedSheet();
 
Book book = spreadsheet.getBook();
 
Book book = spreadsheet.getBook();
 
 
Line 286: Line 316:
 
====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 the font uses 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" highlight="12,14,20,21">
 
void setStrikethrough(boolean isStrikethrough) {
 
void setStrikethrough(boolean isStrikethrough) {
 
Rect rect = getSelection();
 
Rect rect = getSelection();
Sheet sheet = spreadsheet.getSelectedSheet();
+
Worksheet sheet = spreadsheet.getSelectedSheet();
 
Book book = spreadsheet.getBook();
 
Book book = spreadsheet.getBook();
 
 
Line 313: Line 343:
 
}
 
}
 
}
 
}
 +
</source>
 +
 +
===Data Format===
 +
Get data format using <code>CellStyle.getDataFormat()</code> and set new data format using <code>CellStyle.setDataFormat(format)</code>
 +
 +
<source lang="java" highlight="11,16,17">
 +
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);
 +
}
 +
}
 +
}
 +
}
 
</source>
 
</source>
  
 
===Composer===
 
===Composer===
====Current selection====
+
====Current Selection====
<source lang="java" high="3">
+
<source lang="java" highlight="3">
 
Rect selection;
 
Rect selection;
 
public void onCellSelection$spreadsheet(CellSelectionEvent event) {
 
public void onCellSelection$spreadsheet(CellSelectionEvent event) {
Line 324: Line 381:
 
</source>
 
</source>
  
====Select font family====
+
====Select Font Family====
<source lang="java" high="4">
+
<source lang="java" highlight="4">
 
Combobox fontFamily;
 
Combobox fontFamily;
 
public void onSelect$fontFamily() {
 
public void onSelect$fontFamily() {
Line 333: Line 390:
 
</source>
 
</source>
  
====Select font size====
+
====Select Font Size====
<source lang="java" high="4">
+
<source lang="java" highlight="4">
 
Combobox fontSize
 
Combobox fontSize
 
public void onSelect$fontSize() {
 
public void onSelect$fontSize() {
Line 342: Line 399:
 
</source>
 
</source>
  
====Select font color====
+
====Select Font Color====
  
<source lang="java" high="4">
+
<source lang="java" highlight="4">
 
Colorbox fontColor;
 
Colorbox fontColor;
 
public void onChange$fontColor() {
 
public void onChange$fontColor() {
Line 352: Line 409:
 
</source>
 
</source>
  
====Set font bold====
+
====Set Font Bold====
<source lang="java" high="5">
+
<source lang="java" highlight="5">
 
boolean isBold;
 
boolean isBold;
 
Toolbarbutton boldBtn;
 
Toolbarbutton boldBtn;
Line 362: Line 419:
 
</source>
 
</source>
  
====Set font italic====
+
====Set Font Italic====
<source lang="java" high="5">
+
<source lang="java" highlight="5">
 
boolean isItalic;
 
boolean isItalic;
 
Toolbarbutton italicBtn;
 
Toolbarbutton italicBtn;
Line 372: Line 429:
 
</source>
 
</source>
  
====Set font underline====
+
====Set Font Underline====
<source lang="java" high="5">
+
<source lang="java" highlight="5">
 
boolean isUnderline;
 
boolean isUnderline;
 
Toolbarbutton underlineBtn;
 
Toolbarbutton underlineBtn;
Line 382: Line 439:
 
</source>
 
</source>
  
====Set font strikethrough====
+
====Set Font Strikethrough====
<source lang="java" high="5">
+
<source lang="java" highlight="5">
 
boolean isStrikethrough;
 
boolean isStrikethrough;
 
Toolbarbutton strikethroughBtn;
 
Toolbarbutton strikethroughBtn;
Line 392: Line 449:
 
</source>
 
</source>
  
====Select cell color====
+
====Select Cell Color====
<source lang="java" high="4">
+
<source lang="java" highlight="4, 21,22, 24">
 
Colorbox cellColor;
 
Colorbox cellColor;
 
public void onChange$cellColor() {
 
public void onChange$cellColor() {
Line 399: Line 456:
 
setCellColor(color);
 
setCellColor(color);
 
}
 
}
</source>
 
  
====Set cell alignment====
+
public void setCellColor(String color) {
<source lang="java" high="20">
+
    Rect rect = getSelection();
Toolbarbutton alignLeftBtn;
+
    Worksheet sheet = spreadsheet.getSelectedSheet();
Toolbarbutton alignCenterBtn;
+
    Book book = spreadsheet.getBook();
Toolbarbutton alignRightBtn;
+
    final Color newColor = BookHelper.HTMLToColor(book, color);
public void onAlignHorizontalClick(ForwardEvent event) {
+
   
 +
    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);
 +
            }
 +
        }
 +
    }
 +
}</source>
 +
 
 +
====Set Cell Alignment====
 +
<source lang="java" highlight="24,26">
 +
public void onAlignClick(ForwardEvent event) {
 
String alignStr = (String) event.getData();
 
String alignStr = (String) event.getData();
  
 
short align = CellStyle.ALIGN_GENERAL;
 
short align = CellStyle.ALIGN_GENERAL;
 +
boolean isVer = false;
 
if (alignStr.equals("left")) {
 
if (alignStr.equals("left")) {
 
align = CellStyle.ALIGN_LEFT;
 
align = CellStyle.ALIGN_LEFT;
}
+
} else if (alignStr.equals("center")) {
 
 
if (alignStr.equals("center")) {
 
 
align = CellStyle.ALIGN_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 (alignStr.equals("right")) {
+
if (!isVer)
align = CellStyle.ALIGN_RIGHT;
+
setAlignment(align);
}
+
else
 +
setVerticalAlignment(align);
 +
}
 +
</source>
  
setAlignment(align);
+
====Select Data Format====
 +
<source lang="java" highlight="3">
 +
public void onSelect$dateFormat(SelectEvent event) {
 +
String format = (String) dateFormat.getSelectedItem().getValue();
 +
setCellFormat(format);
 
}
 
}
 
</source>
 
</source>
 +
  
 
View complete source of ZUML [https://code.google.com/p/zkbooks/source/browse/trunk/zssessentials/examples/WebContent/config/cellStyle.zul cellStyle.zul]
 
View complete source of ZUML [https://code.google.com/p/zkbooks/source/browse/trunk/zssessentials/examples/WebContent/config/cellStyle.zul cellStyle.zul]
Line 434: Line 528:
 
{| border='1px' | width="100%"
 
{| border='1px' | width="100%"
 
! Version !! Date !! Content
 
! Version !! Date !! Content
 +
|-
 +
| 2.1.0
 +
| May, 2011
 +
| Vertical Alignment
 
|-
 
|-
 
| &nbsp;
 
| &nbsp;

Latest revision as of 12:54, 19 January 2022


Change Cell's Style and Text Format



Stop.png 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

Last Update : 2022/01/19


Version Date Content
2.1.0 May, 2011 Vertical Alignment
     


All source code listed in this book is at Github.


Last Update : 2022/01/19

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