Package io.keikai.api

Class SheetOperationUtil

java.lang.Object
io.keikai.api.SheetOperationUtil
public class SheetOperationUtil extends Object
The utility to help UI to deal with user's sheet operation of a Range. This utility is the default implementation for handling a spreadsheet, it is also the example for calling Range APIs
Since:
3.0.0
Author:
dennis

  • Constructor Details

    • SheetOperationUtil

      public SheetOperationUtil()

  • Method Details

    • toggleAutoFilter

      public static void toggleAutoFilter(Range range)
      Toggles AutoFilter on or off
      Parameters:
      range - the range to toggle

    • resetAutoFilter

      public static void resetAutoFilter(Range range)
      Resets AutoFilter
      Parameters:
      range - the range to reset

    • applyAutoFilter

      public static void applyAutoFilter(Range range)
      Re-apply autofilter
      Parameters:
      range - the range to apply

    • addPicture

      public static Picture addPicture(Range range, org.zkoss.image.AImage image)
      Add picture to the range
      Parameters:
      range - the range to add picture to
      image - the image

    • addPicture

      public static Picture addPicture(Range range, byte[] binary, Picture.Format format, int widthPx, int heightPx)
      Add picture to the range
      Parameters:
      range - the range to add picture
      binary - the image binary data
      format - the image format
      widthPx - the width of image to place
      heightPx - the height of image to place

    • addPicture

      public static Picture addPicture(Range range, SheetAnchor anchor, byte[] binary, Picture.Format format)
      Add picture to the range
      Parameters:
      range - the range to add picture
      anchor - the picture location
      binary - the image binary data
      format - the image format

    • getPictureFormat

      public static Picture.Format getPictureFormat(org.zkoss.image.AImage image)
      Gets the picture format
      Parameters:
      image - the image
      Returns:
      image format, or null if doens't support

    • movePicture

      public static void movePicture(Range range, Picture picture, int rowIndex, int columnIndex)
      Move a picture to specified row and column.
      Parameters:
      range - a range represented the sheet that contains the picture
      picture - the picture to move
      rowIndex - destination row index, 0-based
      columnIndex - destination column index, 0-based

    • deletePicture

      public static void deletePicture(Range range, Picture picture)
      Delete a picture
      Parameters:
      range - a range that represents the sheet that contains the picture
      picture - the picture to delete

    • hasPicture

      public static boolean hasPicture(Range range, Picture picture)
      Returns whether the range of sheet has the picture
      Parameters:
      range -
      picture -
      Returns:
      true if the range has, otherwise false

    • addChart

      public static Chart addChart(Range range, Chart.Type type, Chart.Grouping grouping, Chart.LegendPosition pos)
      Adds a chart to specified sheet range in pre-defined position and size.
      Position: the same row as the specified range, 2 columns right to the specified range
      Size: 7 rows, 7 columns
      Parameters:
      range - the range of source data
      type - the chart type
      grouping - the grouping type
      pos - the legend position type

    • addChart

      public static Chart addChart(String name, Range range, Chart.Type type, Chart.Grouping grouping, Chart.LegendPosition pos)
      Adds a chart to specified sheet range in pre-defined position and size.
      Position: the same row as the specified range, 2 columns right to the specified range
      Size: 7 rows, 7 columns
      Parameters:
      name - the name of chart
      range - the range of source data
      type - the chart type
      grouping - the grouping type
      pos - the legend position type
      Since:
      6.2.0

    • addChart

      public static Chart addChart(Range range, SheetAnchor anchor, Chart.Type type, Chart.Grouping grouping, Chart.LegendPosition pos)
      Adds a chart at the specified location
      Parameters:
      range - the range of source data
      anchor - the chart location on a sheet
      type - the chart type
      grouping - the grouping type
      pos - the legend position type

    • addChart

      public static Chart addChart(String name, Range range, SheetAnchor anchor, Chart.Type type, Chart.Grouping grouping, Chart.LegendPosition pos)
      Adds a chart at the specified location
      Parameters:
      name - the name of chart
      range - the range of source data
      anchor - the chart location on a sheet
      type - the chart type
      grouping - the grouping type
      pos - the legend position type
      Since:
      6.2.0

    • toChartAnchor

      public static SheetAnchor toChartAnchor(Range range)
      Create a anchor by range's row/column data
      Parameters:
      range - the range for chart.
      Returns:
      a new anchor

    • moveChart

      public static void moveChart(Range range, Chart chart, int rowIndex, int columnIndex)
      Move a chart to specified row and column.
      Parameters:
      range - the range that represents the sheet contains the chart to move.
      chart - the chart to move
      rowIndex - destination row index, 0-based.
      columnIndex - destination column index, 0-based.

    • deleteChart

      public static void deleteChart(Range range, Chart chart)
      Delete a chart if the specified Range has the specified chart.
      Parameters:
      range - the range that represents the sheet contains the chart to delete.
      chart - the chart to delete.

    • hasChart

      public static boolean hasChart(Range range, Chart chart)
      Returns whether the range of sheet has the chart.
      Parameters:
      range -
      chart -
      Returns:
      true if the range has, otherwise false

    • protectSheet

      public static void protectSheet(Range range, String password, String newpasswrod)

    • protectSheet

      public static void protectSheet(Range range, String password, boolean allowSelectingLockedCells, boolean allowSelectingUnlockedCells, boolean allowFormattingCells, boolean allowFormattingColumns, boolean allowFormattingRows, boolean allowInsertColumns, boolean allowInsertRows, boolean allowInsertingHyperlinks, boolean allowDeletingColumns, boolean allowDeletingRows, boolean allowSorting, boolean allowFiltering, boolean allowUsingPivotTables, boolean drawingObjects, boolean scenarios)

    • unprotectSheet

      public static boolean unprotectSheet(Range range, String password)

    • displaySheetGridlines

      public static void displaySheetGridlines(Range range, boolean enable)
      Enables/disables to show a sheet's gridlines.
      Parameters:
      range - the sheet range to be applied
      enable - true for enable

    • addSheet

      public static void addSheet(Range range, String prefix)
      Add a new sheet to this book
      Parameters:
      range - the range to be applied
      prefix - the sheet name prefix, it will produce a new name with this prefix and a counter.

    • createSheet

      public static void createSheet(Range range, String name)
      Add a new sheet as the last sheet to this book
      Parameters:
      range - the Book to be applied
      name - the sheet name, it must not be the same as another sheet name in this book, otherwise it takes no effect.

    • renameSheet

      public static void renameSheet(Range range, String newname)
      Rename a sheet
      Parameters:
      range - the sheet range to be applied
      newname - the new name of the sheet, it must not same as another sheet name in this book

    • setSheetOrder

      public static void setSheetOrder(Range range, int pos)
      Sets a sheet's position order, used to move a sheet
      Parameters:
      range - the sheet range to be applied
      pos - the sheet position

    • deleteSheet

      public static void deleteSheet(Range range)
      Deletes the sheet, notice that it prevents you from deleting the last sheet
      Parameters:
      range - the sheet range to be applied

    • copySheet

      public static void copySheet(Range range)
      Copy the sheet with the naming pattern "ORIGINAL_SHEET_NAME (n)".
      Parameters:
      range - the sheet range to be applied

    • toFilledAnchor

      public static SheetAnchor toFilledAnchor(Sheet sheet, int row, int column, int widthPx, int heightPx)
      return a SheetAnchor based on a cell and a picture width and height
      Parameters:
      sheet - target sheet where the anchor locates
      row - 0-based row index
      column - 0-base column index
      widthPx - a picture's width in pixel
      heightPx - a picture's height in pixel
      Returns:
      a SheetAnchor