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

  • Constructor Details

    • CellOperationUtil

      public CellOperationUtil()

  • Method Details

    • 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.

      Visibility effect:

      • If widthPx is 0, it hides the columns implicitly.
      • If widthPx is a positive value, it unhides the columns automatically.
      • This operation affects entire columns even if the range only contains some cells.
      • The column width will be marked as custom (manually set) and disables "best fit".
      • It may trigger re-refresh of cell contents (e.g., changing displayed number precision).
      Parameters:
      range - the target range
      widthPx - the column width in pixels. A value of 0 hides the columns, while a positive value unhides them and sets their width.

    • 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