Package io.keikai.api

Class CellOperationUtil

java.lang.Object

io.keikai.api.CellOperationUtil

public class CellOperationUtil
extends Object

The utility to help UI to deal with user's cell operation of a Range. This utility is the default implementation for handling user operations for cells, it is also the example for calling Range APIs

Since:

3.0.0

Author:

dennis

Nested Class Summary

Nested Classes

Modifier and Type	Class	Description
static interface	CellOperationUtil.CellStyleApplier	Interface for help apply cell style
static interface	CellOperationUtil.CellStyleApplierEx	Interface for help apply whole row,column style

Constructor Summary

Constructors

Constructor	Description
CellOperationUtil()

Method Summary

Modifier and Type	Method	Description
static void	applyAlignment(Range range, CellStyle.Alignment alignment)	Apply alignment to cells in the range
static void	applyBackColor(Range range, String htmlColor)	Apply back-color to cells in the range
static void	applyBorder(Range range, Range.ApplyBorderType applyType, CellStyle.BorderType borderType, String htmlColor)	Apply border to cells in the range
static void	applyCellStyle(Range range, CellOperationUtil.CellStyleApplier applyer)	Apply style according to the cell style applier
static void	applyDataFormat(Range range, String format)	Apply data-format to cells in the range
static void	applyFillColor(Range range, String htmlColor)	Apply fill-color to cells in the range
static void	applyFontBoldweight(Range range, Font.Boldweight boldweight)	Apply font bold-weight to cells in the range
static void	applyFontColor(Range range, String htmlColor)	Apply font color to cells in the range
static void	applyFontHeightPoints(Range range, int fontHeightPoints)	Apply font height to cells in the range, it will also enlarge the row height if row height is smaller than font height
static void	applyFontItalic(Range range, boolean italic)	Apply font italic to cells in the range
static void	applyFontName(Range range, String fontName)	Apply font to cells in the range
static void	applyFontStrikeout(Range range, boolean strikeout)	Apply font strike-out to cells in the range
static void	applyFontTypeOffset(Range range, Font.TypeOffset offset)	Apply font typeOffset to cells in the range
static void	applyFontUnderline(Range range, Font.Underline underline)	Apply font underline to cells in the range
static void	applyHyperlink(Range range, Hyperlink.HyperlinkType type, String address, String label)
static void	applyIndentionOffset(Range range, int offset)	Apply indention to cells in the range
static void	applyLocked(Range range, boolean locked)	Apply the locked status only to cells in the range
static void	applyRotation(Range range, int rotation)	Apply alignment to cells in the range
static void	applyVerticalAlignment(Range range, CellStyle.VerticalAlignment alignment)	Apply vertical-alignment to cells in the range
static void	applyWrapText(Range range, boolean wraptext)	Apply text-warp to cells in the range
static void	autoFill(Range src, Range dest, Range.AutoFillType type)	Fills data from source range to destination range automatically upon auto fill type
static void	clearAll(Range range)	Clear all
static void	clearContents(Range range)	Clear contents
static void	clearStyles(Range range)	Clear style
static Range	cut(Range src, Range dest)	Cuts data and style from src to destination
static void	delete(Range range, Range.DeleteShift shift)	Delete cells of the range.
static void	deleteColumn(Range range)	Delete columns to the range.
static void	deleteRow(Range range)	Delete rows of the range.
static void	fitFontHeightPoints(Range range)	Detect the highest words in each row.
static CellOperationUtil.CellStyleApplier	getAligmentApplier(CellStyle.Alignment alignment)
static int	getAutoFitHeight(Sheet sheet, int row, int col)	Calculate the autoFit cell height according its data size.
static CellOperationUtil.CellStyleApplier	getBackColorApplier(Color color)
static CellOperationUtil.CellStyleApplier	getDataFormatApplier(String format)
static CellOperationUtil.CellStyleApplier	getFillColorApplier(Color color)
static CellOperationUtil.CellStyleApplier	getFontBoldweightApplier(Font.Boldweight boldweight)
static CellOperationUtil.CellStyleApplier	getFontColorApplier(Color color)
static CellOperationUtil.CellStyleApplier	getFontHeightPointsApplier(int fontHeightPoints)
static CellOperationUtil.CellStyleApplier	getFontItalicApplier(boolean italic)
static CellOperationUtil.CellStyleApplier	getFontNameApplier(String fontName)
static CellOperationUtil.CellStyleApplier	getFontStrikeoutApplier(boolean strikeout)
static CellOperationUtil.CellStyleApplier	getFontTypeOffsetApplier(Font.TypeOffset offset)
static CellOperationUtil.CellStyleApplier	getFontUnderlineApplier(Font.Underline underline)
static CellOperationUtil.CellStyleApplier	getIndentionApplier(int offset)
static CellOperationUtil.CellStyleApplier	getRichTextFontBoldweightApplier(Font.Boldweight boldweight)
static CellOperationUtil.CellStyleApplier	getRichTextFontColorApplier(Color color)
static CellOperationUtil.CellStyleApplier	getRichTextFontHeightPointsApplier(int heightPoints)
static CellOperationUtil.CellStyleApplier	getRichTextFontItalicApplier(boolean italic)
static CellOperationUtil.CellStyleApplier	getRichTextFontNameApplier(String fontName)
static CellOperationUtil.CellStyleApplier	getRichTextFontStrikeoutApplier(boolean strikeout)
static CellOperationUtil.CellStyleApplier	getRichTextFontTypeOffsetApplier(Font.TypeOffset offset)
static CellOperationUtil.CellStyleApplier	getRichTextFontUnderlineApplier(Font.Underline underline)
static CellOperationUtil.CellStyleApplier	getRotationApplier(short rotation)
static CellOperationUtil.CellStyleApplier	getVerticalAligmentApplier(CellStyle.VerticalAlignment alignment)
static CellOperationUtil.CellStyleApplier	getWrapTextApplier(boolean wraptext)
static void	hide(Range range)	Hide the range.
static void	insert(Range range, Range.InsertShift shift, Range.InsertCopyOrigin copyOrigin)	Insert cells to the range.
static void	insertColumn(Range range)	Insert columns to the range.
static void	insertRow(Range range)	Insert rows to the range.
static void	merge(Range range, boolean across)	merge the range
static Range	paste(Range src, Range dest)	Paste data and style from src to destination
static Range	pasteAllExceptBorder(Range src, Range dest)	Paste all (except border) from src to destination
static Range	pasteFormula(Range src, Range dest)	Paste formula only from src to destination
static Range	pasteSpecial(Range src, Range dest, Range.PasteType pasteType, Range.PasteOperation pasteOperation, boolean skipBlank, boolean transpose)	Paste according the argument from src to destination
static Range	pasteTranspose(Range src, Range dest)	Paste and transpose from src to destination
static Range	pasteValue(Range src, Range dest)	Paste value only from src to destination
static void	setColumnWidth(Range range, int widthPx)	Sets the column width
static void	setRowHeight(Range range, int heightPx)	Sets the row height and make it as custom modified
static void	setRowHeight(Range range, int heightPx, boolean isCustom)	Sets the row height and provide a custom flag, a custom flag to indicate this height was set by user or system.
static void	shift(Range range, int rowOffset, int colOffset)	Shifts/moves cells with a offset row and column
static void	sort(Range range, boolean desc)	Sort range
static void	sort(Range range, Range index1, boolean desc1, Range.SortDataOption dataOption1, Range index2, boolean desc2, Range.SortDataOption dataOption2, Range index3, boolean desc3, Range.SortDataOption dataOption3, boolean header, boolean matchCase, boolean sortByRows)	Sort range
static void	toggleMergeCenter(Range range)	Toggle merge/unmerge of the range, if merging it will also set alignment to center
static void	unhide(Range range)	Unhide the range.
static void	unmerge(Range range)	Unmerge the range

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

CellOperationUtil

public CellOperationUtil()

Method Detail

cut

public static Range cut(Range src,
                        Range dest)

Cuts data and style from src to destination

Parameters:

src - source range

dest - destination range

Returns:

a Range contains the final pasted range. paste to a protected sheet will always cause paste return null.

paste

public static Range paste(Range src,
                          Range dest)

Paste data and style from src to destination

Parameters:

src - source range

dest - destination range

Returns:

a Range contains the final pasted range. paste to a protected sheet will always cause paste return null.

pasteFormula

public static Range pasteFormula(Range src,
                                 Range dest)

Paste formula only from src to destination

Parameters:

src - source range

dest - destination range

Returns:

a Range contains the final pasted range. paste to a protected sheet will always cause paste return null.

pasteValue

public static Range pasteValue(Range src,
                               Range dest)

Paste value only from src to destination

Parameters:

src - source range

dest - destination range

Returns:

a Range contains the final pasted range. paste to a protected sheet will always cause paste return null.

pasteAllExceptBorder

public static Range pasteAllExceptBorder(Range src,
                                         Range dest)

Paste all (except border) from src to destination

Parameters:

src - source range

dest - destination range

Returns:

a Range contains the final pasted range. paste to a protected sheet will always cause paste return null.

pasteTranspose

public static Range pasteTranspose(Range src,
                                   Range dest)

Paste and transpose from src to destination

Parameters:

src - source range

dest - destination range

Returns:

a Range contains the final pasted range. paste to a protected sheet will always cause paste return null.

pasteSpecial

public static Range pasteSpecial(Range src,
                                 Range dest,
                                 Range.PasteType pasteType,
                                 Range.PasteOperation pasteOperation,
                                 boolean skipBlank,
                                 boolean transpose)

Paste according the argument from src to destination

Parameters:

src - source range

dest - destination range

pasteType - paste type

pasteOperation - paste operation

skipBlank - skip blank

transpose - transpose

Returns:

a Range contains the final pasted range. paste to a protected sheet will always cause paste return null.

getFontNameApplier

public static CellOperationUtil.CellStyleApplier getFontNameApplier(String fontName)

applyFontName

public static void applyFontName(Range range,
                                 String fontName)

Apply font to cells in the range

Parameters:

range - range to be applied

fontName - the font name

getRichTextFontNameApplier

public static CellOperationUtil.CellStyleApplier getRichTextFontNameApplier(String fontName)

applyFontHeightPoints

public static void applyFontHeightPoints(Range range,
                                         int fontHeightPoints)

Apply font height to cells in the range, it will also enlarge the row height if row height is smaller than font height

Parameters:

range - range to be applied

fontHeightPoints - the font height in point

fitFontHeightPoints

public static void fitFontHeightPoints(Range range)

Detect the highest words in each row. It will change row height if needed.

Parameters:

range - range to be detected

getFontHeightPointsApplier

public static CellOperationUtil.CellStyleApplier getFontHeightPointsApplier(int fontHeightPoints)

getRichTextFontHeightPointsApplier

public static CellOperationUtil.CellStyleApplier getRichTextFontHeightPointsApplier(int heightPoints)

getFontBoldweightApplier

public static CellOperationUtil.CellStyleApplier getFontBoldweightApplier(Font.Boldweight boldweight)

applyFontBoldweight

public static void applyFontBoldweight(Range range,
                                       Font.Boldweight boldweight)

Apply font bold-weight to cells in the range

Parameters:

range - the range to be applied

boldweight - the font bold-weight

getRichTextFontBoldweightApplier

public static CellOperationUtil.CellStyleApplier getRichTextFontBoldweightApplier(Font.Boldweight boldweight)

getFontItalicApplier

public static CellOperationUtil.CellStyleApplier getFontItalicApplier(boolean italic)

applyFontItalic

public static void applyFontItalic(Range range,
                                   boolean italic)

Apply font italic to cells in the range

Parameters:

range - the range to be applied

italic - the font italic

getRichTextFontItalicApplier

public static CellOperationUtil.CellStyleApplier getRichTextFontItalicApplier(boolean italic)

getFontStrikeoutApplier

public static CellOperationUtil.CellStyleApplier getFontStrikeoutApplier(boolean strikeout)

applyFontStrikeout

public static void applyFontStrikeout(Range range,
                                      boolean strikeout)

Apply font strike-out to cells in the range

Parameters:

range - the range to be applied

strikeout - font strike-out

getRichTextFontStrikeoutApplier

public static CellOperationUtil.CellStyleApplier getRichTextFontStrikeoutApplier(boolean strikeout)

getFontUnderlineApplier

public static CellOperationUtil.CellStyleApplier getFontUnderlineApplier(Font.Underline underline)

applyFontUnderline

public static void applyFontUnderline(Range range,
                                      Font.Underline underline)

Apply font underline to cells in the range

Parameters:

range - the range to be applied

underline - font underline

getRichTextFontUnderlineApplier

public static CellOperationUtil.CellStyleApplier getRichTextFontUnderlineApplier(Font.Underline underline)

getFontColorApplier

public static CellOperationUtil.CellStyleApplier getFontColorApplier(Color color)

applyFontColor

public static void applyFontColor(Range range,
                                  String htmlColor)

Apply font color to cells in the range

Parameters:

range - the range to be applied.

htmlColor - the color by html color syntax(#rgb-hex-code, e.x #FF00FF)

getRichTextFontColorApplier

public static CellOperationUtil.CellStyleApplier getRichTextFontColorApplier(Color color)

getBackColorApplier

public static CellOperationUtil.CellStyleApplier getBackColorApplier(Color color)

getFillColorApplier

public static CellOperationUtil.CellStyleApplier getFillColorApplier(Color color)

applyBackColor

public static void applyBackColor(Range range,
                                  String htmlColor)

Apply back-color to cells in the range

Parameters:

range - the range to be applied

htmlColor - the color by html color syntax(#rgb-hex-code, e.x #FF00FF)

applyFillColor

public static void applyFillColor(Range range,
                                  String htmlColor)

Apply fill-color to cells in the range

Parameters:

range - the range to be applied

htmlColor - the color by html color syntax(#rgb-hex-code, e.x #FF00FF)

getDataFormatApplier

public static CellOperationUtil.CellStyleApplier getDataFormatApplier(String format)

applyDataFormat

public static void applyDataFormat(Range range,
                                   String format)

Apply data-format to cells in the range

Parameters:

range - the range to be applied

format - the data format

getAligmentApplier

public static CellOperationUtil.CellStyleApplier getAligmentApplier(CellStyle.Alignment alignment)

applyAlignment

public static void applyAlignment(Range range,
                                  CellStyle.Alignment alignment)

Apply alignment to cells in the range

Parameters:

range - the range to be applied

alignment - the alignement

getVerticalAligmentApplier

public static CellOperationUtil.CellStyleApplier getVerticalAligmentApplier(CellStyle.VerticalAlignment alignment)

applyVerticalAlignment

public static void applyVerticalAlignment(Range range,
                                          CellStyle.VerticalAlignment alignment)

Apply vertical-alignment to cells in the range

Parameters:

range - the range to be applied

alignment - vertical alignment

applyCellStyle

public static void applyCellStyle(Range range,
                                  CellOperationUtil.CellStyleApplier applyer)

Apply style according to the cell style applier

Parameters:

range - the range to be applied

applyer -

applyBorder

public static void applyBorder(Range range,
                               Range.ApplyBorderType applyType,
                               CellStyle.BorderType borderType,
                               String htmlColor)

Apply border to cells in the range

Parameters:

range - the range to be applied

applyType - the apply type

borderType - the border type

htmlColor - the color of border(#rgb-hex-code, e.x #FF00FF)

toggleMergeCenter

public static void toggleMergeCenter(Range range)

Toggle merge/unmerge of the range, if merging it will also set alignment to center

Parameters:

range - the range to be applied

merge

public static void merge(Range range,
                         boolean across)

merge the range

Parameters:

range - the range to be merge

across - true if merge horizontally

unmerge

public static void unmerge(Range range)

Unmerge the range

Parameters:

range - the range to be unmerge

getWrapTextApplier

public static CellOperationUtil.CellStyleApplier getWrapTextApplier(boolean wraptext)

applyWrapText

public static void applyWrapText(Range range,
                                 boolean wraptext)

Apply text-warp to cells in the range

Parameters:

range - the range to be applied

wraptext - wrap text or not

applyHyperlink

public static void applyHyperlink(Range range,
                                  Hyperlink.HyperlinkType type,
                                  String address,
                                  String label)

clearContents

public static void clearContents(Range range)

Clear contents

Parameters:

range - the range to be cleared.

clearStyles

public static void clearStyles(Range range)

Clear style

Parameters:

range - the range to be cleared

clearAll

public static void clearAll(Range range)

Clear all

Parameters:

range - the range to be cleared

insert

public static void insert(Range range,
                          Range.InsertShift shift,
                          Range.InsertCopyOrigin copyOrigin)

Insert cells to the range. To insert a row, you have to call Range.toRowRange() first, to insert a column, you have to call Range.toColumnRange() first.

Parameters:

range - the range to insert new cells

shift - the shift direction of original cells

copyOrigin - copy the format from nearby cells when inserting new cells

insertRow

public static void insertRow(Range range)

Insert rows to the range.

Parameters:

range - the range to insert new rows

insertColumn

public static void insertColumn(Range range)

Insert columns to the range.

Parameters:

range - the range to insert new rows

delete

public static void delete(Range range,
                          Range.DeleteShift shift)

Delete cells of the range. To delete a row, you have to call Range.toRowRange() first, to delete a column, you have to call Range.toColumnRange() first.

Parameters:

range - the range to delete

shift - the shift direction when deleting.

deleteRow

public static void deleteRow(Range range)

Delete rows of the range.

Parameters:

range - the range to delete rows

deleteColumn

public static void deleteColumn(Range range)

Delete columns to the range.

Parameters:

range - the range to delete columns

sort

public static void sort(Range range,
                        boolean desc)

Sort range

Parameters:

range - the range to sort

desc - true for descent, false for ascent

sort

public static void sort(Range range,
                        Range index1,
                        boolean desc1,
                        Range.SortDataOption dataOption1,
                        Range index2,
                        boolean desc2,
                        Range.SortDataOption dataOption2,
                        Range index3,
                        boolean desc3,
                        Range.SortDataOption dataOption3,
                        boolean header,
                        boolean matchCase,
                        boolean sortByRows)

Sort range

Parameters:

range - the range to sort

desc1 - true for descent, false for ascent

hide

public static void hide(Range range)

Hide the range. To hide a row, you have to call Range.toRowRange() first, to hide a column, you have to call Range.toColumnRange()

Parameters:

range - the range to hide

unhide

public static void unhide(Range range)

Unhide the range. To unhide a row, you have to call Range.toRowRange() first, to unhide a column, you have to call Range.toColumnRange()

Parameters:

range - the range to un-hide

shift

public static void shift(Range range,
                         int rowOffset,
                         int colOffset)

Shifts/moves cells with a offset row and column

Parameters:

range - the range to shift

rowOffset - the row offset

colOffset - the column offset

autoFill

public static void autoFill(Range src,
                            Range dest,
                            Range.AutoFillType type)

Fills data from source range to destination range automatically upon auto fill type

Parameters:

src - the source range

dest - the destination range

type - the fill type, currently only support AutoFillType.DEFAULT, AutoFillType.COPY, AutoFillType.FORMAT, AutoFillType.VALUES

setRowHeight

public static void setRowHeight(Range range,
                                int heightPx)

Sets the row height and make it as custom modified

Parameters:

range -

heightPx -

setRowHeight

public static void setRowHeight(Range range,
                                int heightPx,
                                boolean isCustom)

Sets the row height and provide a custom flag, a custom flag to indicate this height was set by user or system.

Parameters:

range -

heightPx -

isCustom -

Since:

3.0.1

setColumnWidth

public static void setColumnWidth(Range range,
                                  int widthPx)

Sets the column width

Parameters:

range -

widthPx -

getFontTypeOffsetApplier

public static CellOperationUtil.CellStyleApplier getFontTypeOffsetApplier(Font.TypeOffset offset)

applyFontTypeOffset

public static void applyFontTypeOffset(Range range,
                                       Font.TypeOffset offset)

Apply font typeOffset to cells in the range

Parameters:

range - the range to be applied

offset - font type offset (super, sub)

getRichTextFontTypeOffsetApplier

public static CellOperationUtil.CellStyleApplier getRichTextFontTypeOffsetApplier(Font.TypeOffset offset)

getRotationApplier

public static CellOperationUtil.CellStyleApplier getRotationApplier(short rotation)

applyRotation

public static void applyRotation(Range range,
                                 int rotation)

Apply alignment to cells in the range

Parameters:

range - the range to be applied

rotation - the rotation degree(255 means vertical text)

Since:

3.8.0

getIndentionApplier

public static CellOperationUtil.CellStyleApplier getIndentionApplier(int offset)

applyIndentionOffset

public static void applyIndentionOffset(Range range,
                                        int offset)

Apply indention to cells in the range

Parameters:

range - the range to be applied

offset - the relative value to indent. Value greater than 0 means increasing indent. On the other hand, value less than 0 means decreasing indent.

Since:

3.8.0

getAutoFitHeight

public static int getAutoFitHeight(Sheet sheet,
                                   int row,
                                   int col)

Calculate the autoFit cell height according its data size. No resize when the whole cell has no content.
Note: be aware of that in different JDK vendor, version, or OS, the FontMetrics calculates a different width value of the same text, so the result on UI may look different when the server is running on these differences which are mentioned early. Not recommended using this function to update the cell height. Please use Spreadsheet.autoFitRowHeight(int, int).

Parameters:

sheet -

row - index of row

col - index of column

Returns:

height (px)

Since:

5.9.0

applyLocked

public static void applyLocked(Range range,
                               boolean locked)

Apply the locked status only to cells in the range

Parameters:

range - the range to be applied

locked - true to lock

Since:

6.0.0