Package io.keikai.api

Class 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 Detail

      • SheetOperationUtil

        public SheetOperationUtil()

    • Method Detail

      • 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​(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

      • 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