Package net.sf.jett.util

Class SheetUtil

java.lang.Object

net.sf.jett.util.SheetUtil

public class SheetUtil
extends java.lang.Object

The SheetUtil utility class provides methods for Sheet, Row, and Cell manipulation.

Author:

Randy Gettman

Constructor Summary

Constructors

Constructor	Description
SheetUtil()

Method Summary

Modifier and Type	Method	Description
static void	clearBlock(org.apache.poi.ss.usermodel.Sheet sheet, Block block, WorkbookContext context)	Blanks out all Cells found inside the given Block on the given Sheet.
static Block	copyBlock(org.apache.poi.ss.usermodel.Sheet sheet, TagContext tagContext, Block block, WorkbookContext context, int numBlocksAway)	Copies an entire Block the given number of blocks away on the given Sheet.
static org.apache.poi.ss.usermodel.CellStyle	createCellStyle(org.apache.poi.ss.usermodel.Workbook workbook, short alignment, short borderBottom, short borderLeft, short borderRight, short borderTop, java.lang.String dataFormat, boolean wrapText, org.apache.poi.ss.usermodel.Color fillBackgroundColor, org.apache.poi.ss.usermodel.Color fillForegroundColor, short fillPattern, short verticalAlignment, short indention, short rotationDegrees, org.apache.poi.ss.usermodel.Color bottomBorderColor, org.apache.poi.ss.usermodel.Color leftBorderColor, org.apache.poi.ss.usermodel.Color rightBorderColor, org.apache.poi.ss.usermodel.Color topBorderColor, boolean locked, boolean hidden)	Creates a new CellStyle for the given Workbook, with the given attributes.
static org.apache.poi.ss.usermodel.Font	createFont(org.apache.poi.ss.usermodel.Workbook workbook, short fontBoldweight, boolean fontItalic, org.apache.poi.ss.usermodel.Color fontColor, java.lang.String fontName, short fontHeightInPoints, byte fontUnderline, boolean fontStrikeout, int fontCharset, short fontTypeOffset)	Creates a new Font for the given Workbook, with the given attributes.
static void	deleteBlock(org.apache.poi.ss.usermodel.Sheet sheet, TagContext tagContext, Block block, WorkbookContext context)	Removes all Cells found inside the given Block on the given Sheet.
static java.lang.String	getCellKey(org.apache.poi.ss.usermodel.Cell cell)	Returns a String that can reference the given Cell.
static java.lang.String	getCellLocation(org.apache.poi.ss.usermodel.Cell cell)	Returns a String formatted in the following way:
static org.apache.poi.ss.usermodel.Color	getColor(org.apache.poi.ss.usermodel.Workbook workbook, java.lang.String value)	Determines the proper POI Color, given a string value that could be a color name, e.g.
static java.lang.String	getColorHexString(org.apache.poi.ss.usermodel.Color color)	Get the hex string that represents the Color.
static int	getLastPopulatedColIndex(org.apache.poi.ss.usermodel.Sheet sheet)	Determine the last populated column and return its 0-based index.
static Block	getShiftEndingAncestor(Block block, int numVertCells, int numHorizCells)	Walk up the Block tree until a "shift ending" ancestor is found, or until the tree has been exhausted.
static java.lang.String	getTagLocationWithHierarchy(Tag tag)	Returns a String formatted in the following way:
static void	groupColumns(org.apache.poi.ss.usermodel.Sheet sheet, int begin, int end, boolean collapse)	Group all columns on the sheet between the "begin" and "end" indices, inclusive.
static void	groupRows(org.apache.poi.ss.usermodel.Sheet sheet, int begin, int end, boolean collapse)	Group all rows on the sheet between the "begin" and "end" indices, inclusive.
static boolean	isCellBlank(org.apache.poi.ss.usermodel.Sheet sheet, int rowNum, int colNum)	Determines whether the Cell on the given Sheet at the given row and column indexes is blank: either it doesn't exist, or it exists and the cell type is blank.
static boolean	isCellImmaterial(org.apache.poi.ss.usermodel.Sheet sheet, int rowNum, int colNum)	Determines whether the Cell on the given Sheet at the given row and column indexes is immaterial: either it doesn't exist, or it exists and the cell type is blank.
static void	removeBlock(org.apache.poi.ss.usermodel.Sheet sheet, TagContext tagContext, Block block, WorkbookContext context)	Removes the given Block of Cells from the given Sheet.
static java.lang.String	safeSetSheetName(org.apache.poi.ss.usermodel.Workbook workbook, int index, java.lang.String newName)	Sets the name of the indicated Sheet in the workbook to a safe, legal sheet name.
static java.lang.Object	setCellValue(WorkbookContext context, org.apache.poi.ss.usermodel.Cell cell, java.lang.Object value)	Sets the cell value on the given Cell to the given value, regardless of data type.
static java.lang.Object	setCellValue(WorkbookContext context, org.apache.poi.ss.usermodel.Cell cell, java.lang.Object value, org.apache.poi.ss.usermodel.RichTextString origRichString)	Sets the cell value on the given Cell to the given value, regardless of data type.
static void	setUpBlockForImplicitCollectionAccess(org.apache.poi.ss.usermodel.Sheet sheet, Block block, java.util.List<java.lang.String> collExprs, java.util.List<java.lang.String> itemNames)	Replace all occurrences of the given collection expression name with the given item name, in preparation for implicit collections processing loops.
static void	setUpSheetForImplicitCloningAccess(org.apache.poi.ss.usermodel.Sheet sheet, java.util.List<java.lang.String> collExprs, java.util.List<java.lang.String> itemNames)	Replace all occurrences of the given collection expression name with the given item name, in the entire Sheet, in preparation for implicit cloning processing loops.
static void	shiftForBlock(org.apache.poi.ss.usermodel.Sheet sheet, TagContext tagContext, Block block, WorkbookContext context, int numBlocksAway)	Shifts Cells out of the way.
static java.lang.String	takePastEndAction(org.apache.poi.ss.usermodel.Sheet sheet, java.util.List<java.lang.String> pastEndRefs, java.lang.String replacementValue)	Takes the "replace value" PastEndAction on the entire Sheet - sheet name, header/footer, and all Cells on it.
static void	takePastEndAction(org.apache.poi.ss.usermodel.Sheet sheet, Block block, java.util.List<java.lang.String> pastEndRefs, PastEndAction pastEndAction, java.lang.String replacementValue)	Takes the given PastEndAction on all Cells found inside the given Block on the given Sheet that contain any of the given expressions.

Methods inherited from class java.lang.Object

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

Constructor Detail

SheetUtil

public SheetUtil()

Method Detail

getLastPopulatedColIndex

public static int getLastPopulatedColIndex(org.apache.poi.ss.usermodel.Sheet sheet)

Determine the last populated column and return its 0-based index.

Parameters:

sheet - The Sheet on which to determine the last populated column.

Returns:

The 0-based index of the last populated column (-1 if the Sheet is empty).

setCellValue

public static java.lang.Object setCellValue(WorkbookContext context,
                                            org.apache.poi.ss.usermodel.Cell cell,
                                            java.lang.Object value)

Sets the cell value on the given Cell to the given value, regardless of data type.

Parameters:

context - The WorkbookContext; access to the CellStyleCache and FontCache is used.

cell - The Cell on which to set the value.

value - The value.

Returns:

The actual value set in the Cell.

setCellValue

public static java.lang.Object setCellValue(WorkbookContext context,
                                            org.apache.poi.ss.usermodel.Cell cell,
                                            java.lang.Object value,
                                            org.apache.poi.ss.usermodel.RichTextString origRichString)

Sets the cell value on the given Cell to the given value, regardless of data type.

Parameters:

context - The WorkbookContext; access to the CellStyleCache and FontCache is used.

cell - The Cell on which to set the value.

value - The value.

origRichString - The original RichTextString, to be used to set the CellStyle if the value isn't some kind of string (String or RichTextString).

Returns:

The actual value set in the Cell.

isCellImmaterial

public static boolean isCellImmaterial(org.apache.poi.ss.usermodel.Sheet sheet,
                                       int rowNum,
                                       int colNum)

Determines whether the Cell on the given Sheet at the given row and column indexes is immaterial: either it doesn't exist, or it exists and the cell type is blank. That is, whether the cell doesn't exist, is blank, or is empty, and its cell style is the default.

Parameters:

sheet - The Sheet.

rowNum - The 0-based row index.

colNum - The 0-based column index.

Returns:

Whether the Cell is blank.

Since:

0.9.1

isCellBlank

public static boolean isCellBlank(org.apache.poi.ss.usermodel.Sheet sheet,
                                  int rowNum,
                                  int colNum)

Determines whether the Cell on the given Sheet at the given row and column indexes is blank: either it doesn't exist, or it exists and the cell type is blank. That is, whether the cell doesn't exist, is blank, or is empty.

Parameters:

sheet - The Sheet.

rowNum - The 0-based row index.

colNum - The 0-based column index.

Returns:

Whether the Cell is blank.

getCellKey

public static java.lang.String getCellKey(org.apache.poi.ss.usermodel.Cell cell)

Returns a String that can reference the given Cell.

Parameters:

cell - The Cell.

Returns:

A string in the format "sheet!A1".

deleteBlock

public static void deleteBlock(org.apache.poi.ss.usermodel.Sheet sheet,
                               TagContext tagContext,
                               Block block,
                               WorkbookContext context)

Removes all Cells found inside the given Block on the given Sheet.

Parameters:

sheet - The Sheet on which to delete a Block.

tagContext - A TagContext.

block - The Block of Cells to delete.

context - The WorkbookContext.

clearBlock

public static void clearBlock(org.apache.poi.ss.usermodel.Sheet sheet,
                              Block block,
                              WorkbookContext context)

Blanks out all Cells found inside the given Block on the given Sheet.

Parameters:

sheet - The Sheet on which to clear a Block

block - The Block of Cells to clear.

context - The WorkbookContext.

takePastEndAction

public static java.lang.String takePastEndAction(org.apache.poi.ss.usermodel.Sheet sheet,
                                                 java.util.List<java.lang.String> pastEndRefs,
                                                 java.lang.String replacementValue)

Takes the "replace value" PastEndAction on the entire Sheet - sheet name, header/footer, and all Cells on it.

Parameters:

sheet - The Sheet on which to replace expressions.

pastEndRefs - A List of strings identifying which expressions represent collection access beyond the end of the collection.

replacementValue - The value with which to replace those expressions.

Returns:

The sheet name after replacement. It may change because the sheet name can be changed by this method.

Since:

0.9.1

takePastEndAction

public static void takePastEndAction(org.apache.poi.ss.usermodel.Sheet sheet,
                                     Block block,
                                     java.util.List<java.lang.String> pastEndRefs,
                                     PastEndAction pastEndAction,
                                     java.lang.String replacementValue)

Takes the given PastEndAction on all Cells found inside the given Block on the given Sheet that contain any of the given expressions.

Parameters:

sheet - The Sheet on which to take a PastEndAction on a Block.

block - The Block of Cells.

pastEndRefs - A List of strings identifying which expressions represent collection access beyond the end of the collection.

pastEndAction - An enumerated value representing the action to take on such a cell/expression that references collection access beyond the end of the collection.

replacementValue - If the past end action is to replace expressions, then this is the value with which to replace those expressions, else this is ignored.

See Also:

PastEndAction

removeBlock

public static void removeBlock(org.apache.poi.ss.usermodel.Sheet sheet,
                               TagContext tagContext,
                               Block block,
                               WorkbookContext context)

Removes the given Block of Cells from the given Sheet.

Parameters:

sheet - The Sheet on which to remove the block.

tagContext - A TagContext.

block - The Block to remove.

context - The WorkbookContext.

getShiftEndingAncestor

public static Block getShiftEndingAncestor(Block block,
                                           int numVertCells,
                                           int numHorizCells)

Walk up the Block tree until a "shift ending" ancestor is found, or until the tree has been exhausted. Optionally, grow/shrink parent blocks encountered until the "shift ending" ancestor is found. (The "shift ending" ancestor is not grown/shrunk). The "shift ending" ancestor is defined as an ancestor Block that is either a different direction than the original Block or is larger than the original Block along the other direction (that is, larger in height for Horizontal blocks, or larger in width for Vertical blocks).

Parameters:

block - The Block to search for ancestors.

numVertCells - The number of cells to grow each parent vertically until the "shift ending" ancestor is found, or shrink if numCells is negative.

numHorizCells - The number of cells to grow each parent horizontally until the "shift ending" ancestor is found, or shrink if numCells is negative.

Returns:

The closest "shift ending" ancestor Block.

shiftForBlock

public static void shiftForBlock(org.apache.poi.ss.usermodel.Sheet sheet,
                                 TagContext tagContext,
                                 Block block,
                                 WorkbookContext context,
                                 int numBlocksAway)

Shifts Cells out of the way.

Parameters:

sheet - The Sheet on which to shift.

tagContext - A TagContext.

block - The Block whose copies will occupy the Cells that will move to make way for the copies.

context - The WorkbookContext.

numBlocksAway - The number of blocks (widths or lengths, depending on the case of block that defines the area of Cells to shift.

copyBlock

public static Block copyBlock(org.apache.poi.ss.usermodel.Sheet sheet,
                              TagContext tagContext,
                              Block block,
                              WorkbookContext context,
                              int numBlocksAway)

Copies an entire Block the given number of blocks away on the given Sheet.

Parameters:

sheet - The Sheet on which to copy.

tagContext - A TagContext.

block - The Block to copy.

context - The WorkbookContext.

numBlocksAway - The number of blocks (widths or lengths, depending on the direction of block), away to copy.

Returns:

The newly copied Block.

setUpBlockForImplicitCollectionAccess

public static void setUpBlockForImplicitCollectionAccess(org.apache.poi.ss.usermodel.Sheet sheet,
                                                         Block block,
                                                         java.util.List<java.lang.String> collExprs,
                                                         java.util.List<java.lang.String> itemNames)

Replace all occurrences of the given collection expression name with the given item name, in preparation for implicit collections processing loops.

Parameters:

sheet - The Sheet on which the Block lies.

block - The Block in which to perform the replacement.

collExprs - The collection expression strings to replace.

itemNames - The item names that replace the collection expressions.

setUpSheetForImplicitCloningAccess

public static void setUpSheetForImplicitCloningAccess(org.apache.poi.ss.usermodel.Sheet sheet,
                                                      java.util.List<java.lang.String> collExprs,
                                                      java.util.List<java.lang.String> itemNames)

Replace all occurrences of the given collection expression name with the given item name, in the entire Sheet, in preparation for implicit cloning processing loops. This doesn't replace any collection expressions in the sheet name; this is assumed to have taken place already, due to unique sheet naming requirements.

Parameters:

sheet - The Sheet.

collExprs - The List of collection expression strings to replace.

itemNames - The List of item names that replace the collection expressions.

Since:

0.9.1

groupRows

public static void groupRows(org.apache.poi.ss.usermodel.Sheet sheet,
                             int begin,
                             int end,
                             boolean collapse)

Group all rows on the sheet between the "begin" and "end" indices, inclusive. Optionally collapse the rows.

Parameters:

sheet - The Sheet on which to group the rows.

begin - The 0-based index of the start row of the group.

end - The 0-based index of the end row of the group.

collapse - Whether to collapse the group.

Since:

0.2.0

groupColumns

public static void groupColumns(org.apache.poi.ss.usermodel.Sheet sheet,
                                int begin,
                                int end,
                                boolean collapse)

Group all columns on the sheet between the "begin" and "end" indices, inclusive. Optionally collapse the columns.

Parameters:

sheet - The Sheet on which to group the columns.

begin - The 0-based index of the start column of the group.

end - The 0-based index of the end column of the group.

collapse - Whether to collapse the group.

Since:

0.2.0

getColorHexString

public static java.lang.String getColorHexString(org.apache.poi.ss.usermodel.Color color)

Get the hex string that represents the Color.

Parameters:

color - A POI Color.

Returns:

The hex string that represents the Color.

Since:

0.5.0

createCellStyle

public static org.apache.poi.ss.usermodel.CellStyle createCellStyle(org.apache.poi.ss.usermodel.Workbook workbook,
                                                                    short alignment,
                                                                    short borderBottom,
                                                                    short borderLeft,
                                                                    short borderRight,
                                                                    short borderTop,
                                                                    java.lang.String dataFormat,
                                                                    boolean wrapText,
                                                                    org.apache.poi.ss.usermodel.Color fillBackgroundColor,
                                                                    org.apache.poi.ss.usermodel.Color fillForegroundColor,
                                                                    short fillPattern,
                                                                    short verticalAlignment,
                                                                    short indention,
                                                                    short rotationDegrees,
                                                                    org.apache.poi.ss.usermodel.Color bottomBorderColor,
                                                                    org.apache.poi.ss.usermodel.Color leftBorderColor,
                                                                    org.apache.poi.ss.usermodel.Color rightBorderColor,
                                                                    org.apache.poi.ss.usermodel.Color topBorderColor,
                                                                    boolean locked,
                                                                    boolean hidden)

Creates a new CellStyle for the given Workbook, with the given attributes. Moved from StyleTag here for 0.5.0.

Parameters:

workbook - A Workbook.

alignment - A short alignment constant.

borderBottom - A short border type constant.

borderLeft - A short border type constant.

borderRight - A short border type constant.

borderTop - A short border type constant.

dataFormat - A data format string.

wrapText - Whether text is wrapped.

fillBackgroundColor - A background Color.

fillForegroundColor - A foreground Color.

fillPattern - A short pattern constant.

verticalAlignment - A short vertical alignment constant.

indention - A short number of indent characters.

rotationDegrees - A short degrees rotation of text.

bottomBorderColor - A border Color object.

leftBorderColor - A border Color object.

rightBorderColor - A border Color object.

topBorderColor - A border Color object.

locked - Whether the cell is locked.

hidden - Whether the cell is hidden.

Returns:

A new CellStyle.

createFont

public static org.apache.poi.ss.usermodel.Font createFont(org.apache.poi.ss.usermodel.Workbook workbook,
                                                          short fontBoldweight,
                                                          boolean fontItalic,
                                                          org.apache.poi.ss.usermodel.Color fontColor,
                                                          java.lang.String fontName,
                                                          short fontHeightInPoints,
                                                          byte fontUnderline,
                                                          boolean fontStrikeout,
                                                          int fontCharset,
                                                          short fontTypeOffset)

Creates a new Font for the given Workbook, with the given attributes. Moved from StyleTag here for 0.5.0.

Parameters:

workbook - A Workbook.

fontBoldweight - A short boldweight constant.

fontItalic - Whether the text is italic.

fontColor - A color Color object.

fontName - A font name.

fontHeightInPoints - A short font height in points.

fontUnderline - A byte underline constant.

fontStrikeout - Whether the font is strikeout.

fontCharset - An int charset constant.

fontTypeOffset - A short type offset constant.

Returns:

A new Font.

getColor

public static org.apache.poi.ss.usermodel.Color getColor(org.apache.poi.ss.usermodel.Workbook workbook,
                                                         java.lang.String value)

Determines the proper POI Color, given a string value that could be a color name, e.g. "aqua", or a hex string, e.g. "#FFCCCC".

Parameters:

workbook - A Workbook, used only to determine whether to create an HSSFColor or an XSSFColor.

value - The color value, which could be one of the 48 pre-defined color names, or a hex value of the format "#RRGGBB".

Returns:

A Color, or null if an invalid color name was given.

getCellLocation

public static java.lang.String getCellLocation(org.apache.poi.ss.usermodel.Cell cell)

Returns a String formatted in the following way:

" at " + cellReference

e.g. " at Sheet2!C3".

Parameters:

cell - The Cell

Returns:

The formatted location string.

Since:

0.7.0

getTagLocationWithHierarchy

public static java.lang.String getTagLocationWithHierarchy(Tag tag)

Returns a String formatted in the following way:

[", at " + tagCellRef + " (originally at " + origCellRef + ")"]+

where each instance represents the parent tag of the tag before it, in a "tag stack trace" kind of way.

Parameters:

tag - The Tag.

Returns:

The formatted location string.

Since:

0.9.0

safeSetSheetName

public static java.lang.String safeSetSheetName(org.apache.poi.ss.usermodel.Workbook workbook,
                                                int index,
                                                java.lang.String newName)

Sets the name of the indicated Sheet in the workbook to a safe, legal sheet name. Invalid characters are replaced with spaces. If a sheet name is already taken, numbers are added as suffixes until a name that isn't taken is found, e.g. "example" -> "example-1" -> "example-2".

Parameters:

workbook - The Workbook in which to set a sheet's name.

index - The 0-based index of the Sheet.

newName - The proposed new name.

Returns:

The actual safe name used to rename the sheet.

Since:

0.9.1