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 Summary

Constructors

Constructor and Description
SheetOperationUtil()

Method Summary

Modifier and Type	Method and Description
static Chart	addChart(Range range, Chart.Type type, Chart.Grouping grouping, Chart.LegendPosition pos) Adds chart to range
static Chart	addChart(Range range, SheetAnchor anchor, Chart.Type type, Chart.Grouping grouping, Chart.LegendPosition pos) Adds chart to range
static Picture	addPicture(Range range, org.zkoss.image.AImage image) Add picture to the range
static Picture	addPicture(Range range, byte[] binary, Picture.Format format, int widthPx, int heightPx) Add picture to the range
static Picture	addPicture(Range range, SheetAnchor anchor, byte[] binary, Picture.Format format) Add picture to the range
static void	addSheet(Range range, String prefix) Add a new sheet to this book
static void	applyAutoFilter(Range range) Re-apply autofilter
static void	CopySheet(Range range) Copy the sheet with the naming pattern "ORIGINAL_FILENAME (n)".
static void	createSheet(Range range, String name) Add a new sheet to this book
static void	deleteChart(Range range, Chart chart) Delete a chart.
static void	deletePicture(Range range, Picture picture) Delete a picture
static void	deleteSheet(Range range) Deletes the sheet, notice that it prevents you from deleting the last sheet
static void	displaySheetGridlines(Range range, boolean enable) Enables/disables sheet gridlines.
static Picture.Format	getPictureFormat(org.zkoss.image.AImage image) Gets the picture format
static boolean	hasChart(Range range, Chart chart) Determine whether the range of sheet has the chart.
static boolean	hasPicture(Range range, Picture picture) Determine whether the range of sheet has the picture
static void	moveChart(Range range, Chart chart, int rowIndex, int columnIndex) Move a chart to specified row and column.
static void	movePicture(Range range, Picture picture, int rowIndex, int columnIndex) Move a picture to specified row and column.
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)
static void	protectSheet(Range range, String password, String newpasswrod)
static void	renameSheet(Range range, String newname) Rename a sheet
static void	resetAutoFilter(Range range) Resets autofilter
static void	setSheetOrder(Range range, int pos) Sets the sheet order
static SheetAnchor	toChartAnchor(Range range) Create a anchor by range's row/column data
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
static void	toggleAutoFilter(Range range) Toggles autofilter on or off
static boolean	unprotectSheet(Range range, String password)

Methods inherited from class java.lang.Object

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

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)

Determine 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 chart to range

Parameters:

range - the range to add chart

data - the chart 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 chart to range

Parameters:

range - the range to add chart

anchor - the chart location

data - the chart data

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.

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)

Determine 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 sheet gridlines.

Parameters:

range - the 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 selection a new name with this prefix and a counter.

createSheet

public static void createSheet(Range range,
                               String name)

Add a new sheet to this book

Parameters:

range - the range to be applied

name - the sheet name, it must not same as another sheet name in this book

renameSheet

public static void renameSheet(Range range,
                               String newname)

Rename a sheet

Parameters:

range - the 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 the sheet order

Parameters:

range - the 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 range to be applied

CopySheet

public static void CopySheet(Range range)

Copy the sheet with the naming pattern "ORIGINAL_FILENAME (n)".

Parameters:

range - the 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