Change Cell's Style and Text Format"
From Documentation
m |
m |
||
| Line 241: | Line 241: | ||
</source> | </source> | ||
====Italic==== | ====Italic==== | ||
| + | <source lang="java" > | ||
| + | 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); | ||
| + | } | ||
| + | } | ||
| + | } | ||
| + | } | ||
| + | </source> | ||
====Underline==== | ====Underline==== | ||
====Strikethrough==== | ====Strikethrough==== | ||
Revision as of 09:17, 17 November 2010
| 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
//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
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
Strikethrough
All source code listed in this book is at Github.