Package org.apache.poi.ss.util

Class CellUtil

java.lang.Object

org.apache.poi.ss.util.CellUtil

public final class CellUtil
extends Object

Various utility functions that make working with a cells and rows easier. The various methods that deal with style's allow you to create your CellStyles as you need them. When you apply a style change to a cell, the code will attempt to see if a style already exists that meets your needs. If not, then it will create a new style. This is to prevent creating too many styles. there is an upper limit in Excel on the number of styles that can be supported.

Field Summary

Fields

Modifier and Type	Field	Description
static final String	ALIGNMENT	Deprecated. as of POI 5.4.0.
static final String	BORDER_BOTTOM	Deprecated. as of POI 5.4.0.
static final String	BORDER_LEFT	Deprecated. as of POI 5.4.0.
static final String	BORDER_RIGHT	Deprecated. as of POI 5.4.0.
static final String	BORDER_TOP	Deprecated. as of POI 5.4.0.
static final String	BOTTOM_BORDER_COLOR	Deprecated. as of POI 5.4.0.
static final String	DATA_FORMAT	Deprecated. as of POI 5.4.0.
static final String	FILL_BACKGROUND_COLOR	Deprecated. as of POI 5.4.0.
static final String	FILL_BACKGROUND_COLOR_COLOR	Deprecated. as of POI 5.4.0.
static final String	FILL_FOREGROUND_COLOR	Deprecated. as of POI 5.4.0.
static final String	FILL_FOREGROUND_COLOR_COLOR	Deprecated. as of POI 5.4.0.
static final String	FILL_PATTERN	Deprecated. as of POI 5.4.0.
static final String	FONT	Deprecated. as of POI 5.4.0.
static final String	HIDDEN	Deprecated. as of POI 5.4.0.
static final String	INDENTION	Deprecated. as of POI 5.4.0.
static final String	LEFT_BORDER_COLOR	Deprecated. as of POI 5.4.0.
static final String	LOCKED	Deprecated. as of POI 5.4.0.
static final String	QUOTE_PREFIXED	Deprecated. as of POI 5.4.0.
static final String	RIGHT_BORDER_COLOR	Deprecated. as of POI 5.4.0.
static final String	ROTATION	Deprecated. as of POI 5.4.0.
static final String	SHRINK_TO_FIT	Deprecated. as of POI 5.4.0.
static final String	TOP_BORDER_COLOR	Deprecated. as of POI 5.4.0.
static final String	VERTICAL_ALIGNMENT	Deprecated. as of POI 5.4.0.
static final String	WRAP_TEXT	Deprecated. as of POI 5.4.0.

Method Summary

Modifier and Type	Method	Description
static void	cloneStyle(CellStyle src, CellStyle dest, Workbook destWorkbook)	Clones the style from one cell to another.
static void	copyCell(Cell srcCell, Cell destCell, CellCopyPolicy policy, CellCopyContext context)	Copy cell value, formula and style, from srcCell per cell copy policy If srcCell is null, clears the cell value and cell style per cell copy policy.
static Cell	createCell(Row row, int column, String value)	Create a cell, and give it a value.
static Cell	createCell(Row row, int column, String value, CellStyle style)	Creates a cell, gives it a value, and applies a style if provided
static Cell	getCell(Row row, int columnIndex)	Get a specific cell from a row.
static EnumMap<CellPropertyType,Object>	getFormatProperties(CellStyle style)	Returns a map containing the format properties of the given cell style.
static Row	getRow(int rowIndex, Sheet sheet)	Get a row from the spreadsheet, and create it if it doesn't exist.
static void	setAlignment(Cell cell, HorizontalAlignment align)	Take a cell, and align it.
static void	setCellStyleProperties(Cell cell, Map<String,Object> properties)	Deprecated. as of POI 5.4.0.
static void	setCellStylePropertiesEnum(Cell cell, Map<CellPropertyType,Object> properties)	This method attempts to find an existing CellStyle that matches the cell's current style plus styles properties in properties.
static void	setCellStyleProperty(Cell cell, String propertyName, Object propertyValue)	Deprecated. as of POI 5.4.0.
static void	setCellStyleProperty(Cell cell, CellPropertyType property, Object propertyValue)	This method attempts to find an existing CellStyle that matches the cell's current style plus a single style property propertyName with value propertyValue.
static void	setFont(Cell cell, Font font)	Take a cell, and apply a font to it
static void	setVerticalAlignment(Cell cell, VerticalAlignment align)	Take a cell, and vertically align it.
static Cell	translateUnicodeValues(Cell cell)	Looks for text in the cell that should be unicode, like α and provides the unicode version of it.

Methods inherited from class java.lang.Object

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

Field Details

ALIGNMENT

@Deprecated
@Removal(version="7.0.0")
public static final String ALIGNMENT

Deprecated.

as of POI 5.4.0. Use CellPropertyType.ALIGNMENT instead.

See Also:

• Constant Field Values

BORDER_BOTTOM

@Deprecated
@Removal(version="7.0.0")
public static final String BORDER_BOTTOM

Deprecated.

as of POI 5.4.0. Use CellPropertyType.BORDER_BOTTOM instead.

See Also:

• Constant Field Values

BORDER_LEFT

@Deprecated
@Removal(version="7.0.0")
public static final String BORDER_LEFT

Deprecated.

as of POI 5.4.0. Use CellPropertyType.BORDER_LEFT instead.

See Also:

• Constant Field Values

BORDER_RIGHT

@Deprecated
@Removal(version="7.0.0")
public static final String BORDER_RIGHT

Deprecated.

as of POI 5.4.0. Use CellPropertyType.BORDER_RIGHT instead.

See Also:

• Constant Field Values

BORDER_TOP

@Deprecated
@Removal(version="7.0.0")
public static final String BORDER_TOP

Deprecated.

as of POI 5.4.0. Use CellPropertyType.BORDER_TOP instead.

See Also:

• Constant Field Values

BOTTOM_BORDER_COLOR

@Deprecated
@Removal(version="7.0.0")
public static final String BOTTOM_BORDER_COLOR

Deprecated.

as of POI 5.4.0. Use CellPropertyType.BORDER_BOTTOM instead.

See Also:

• Constant Field Values

LEFT_BORDER_COLOR

@Deprecated
@Removal(version="7.0.0")
public static final String LEFT_BORDER_COLOR

Deprecated.

as of POI 5.4.0. Use CellPropertyType.BOTTOM_BORDER_COLOR instead.

See Also:

• Constant Field Values

RIGHT_BORDER_COLOR

@Deprecated
@Removal(version="7.0.0")
public static final String RIGHT_BORDER_COLOR

Deprecated.

as of POI 5.4.0. Use CellPropertyType.RIGHT_BORDER_COLOR instead.

See Also:

• Constant Field Values

TOP_BORDER_COLOR

@Deprecated
@Removal(version="7.0.0")
public static final String TOP_BORDER_COLOR

Deprecated.

as of POI 5.4.0. Use CellPropertyType.TOP_BORDER_COLOR instead.

See Also:

• Constant Field Values

DATA_FORMAT

@Deprecated
@Removal(version="7.0.0")
public static final String DATA_FORMAT

Deprecated.

as of POI 5.4.0. Use CellPropertyType.DATA_FORMAT instead.

See Also:

• Constant Field Values

FILL_BACKGROUND_COLOR

@Deprecated
@Removal(version="7.0.0")
public static final String FILL_BACKGROUND_COLOR

Deprecated.

as of POI 5.4.0. Use CellPropertyType.FILL_BACKGROUND_COLOR instead.

See Also:

• Constant Field Values

FILL_FOREGROUND_COLOR

@Deprecated
@Removal(version="7.0.0")
public static final String FILL_FOREGROUND_COLOR

Deprecated.

as of POI 5.4.0. Use CellPropertyType.FILL_FOREGROUND_COLOR instead.

See Also:

• Constant Field Values

FILL_BACKGROUND_COLOR_COLOR

@Deprecated
@Removal(version="7.0.0")
public static final String FILL_BACKGROUND_COLOR_COLOR

Deprecated.

as of POI 5.4.0. Use CellPropertyType.FILL_BACKGROUND_COLOR_COLOR instead.

See Also:

• Constant Field Values

FILL_FOREGROUND_COLOR_COLOR

@Deprecated
@Removal(version="7.0.0")
public static final String FILL_FOREGROUND_COLOR_COLOR

Deprecated.

as of POI 5.4.0. Use CellPropertyType.FILL_FOREGROUND_COLOR_COLOR instead.

See Also:

• Constant Field Values

FILL_PATTERN

@Deprecated
@Removal(version="7.0.0")
public static final String FILL_PATTERN

Deprecated.

as of POI 5.4.0. Use CellPropertyType.FILL_PATTERN instead.

See Also:

• Constant Field Values

FONT

@Deprecated
@Removal(version="7.0.0")
public static final String FONT

Deprecated.

as of POI 5.4.0. Use CellPropertyType.FONT instead.

See Also:

• Constant Field Values

HIDDEN

@Deprecated
@Removal(version="7.0.0")
public static final String HIDDEN

Deprecated.

as of POI 5.4.0. Use CellPropertyType.HIDDEN instead.

See Also:

• Constant Field Values

INDENTION

@Deprecated
@Removal(version="7.0.0")
public static final String INDENTION

Deprecated.

as of POI 5.4.0. Use CellPropertyType.INDENTION instead.

See Also:

• Constant Field Values

LOCKED

@Deprecated
@Removal(version="7.0.0")
public static final String LOCKED

Deprecated.

as of POI 5.4.0. Use CellPropertyType.LOCKED instead.

See Also:

• Constant Field Values

ROTATION

@Deprecated
@Removal(version="7.0.0")
public static final String ROTATION

Deprecated.

as of POI 5.4.0. Use CellPropertyType.ROTATION instead.

See Also:

• Constant Field Values

VERTICAL_ALIGNMENT

@Deprecated
@Removal(version="7.0.0")
public static final String VERTICAL_ALIGNMENT

Deprecated.

as of POI 5.4.0. Use CellPropertyType.VERTICAL_ALIGNMENT instead.

See Also:

• Constant Field Values

WRAP_TEXT

@Deprecated
@Removal(version="7.0.0")
public static final String WRAP_TEXT

Deprecated.

as of POI 5.4.0. Use CellPropertyType.WRAP_TEXT instead.

See Also:

• Constant Field Values

SHRINK_TO_FIT

@Deprecated
@Removal(version="7.0.0")
public static final String SHRINK_TO_FIT

Deprecated.

as of POI 5.4.0. Use CellPropertyType.SHRINK_TO_FIT instead.

See Also:

• Constant Field Values

QUOTE_PREFIXED

@Deprecated
@Removal(version="7.0.0")
public static final String QUOTE_PREFIXED

Deprecated.

as of POI 5.4.0. Use CellPropertyType.QUOTE_PREFIXED instead.

See Also:

• Constant Field Values

Method Details

getRow

public static Row getRow(int rowIndex,
 Sheet sheet)

Get a row from the spreadsheet, and create it if it doesn't exist.

Parameters:

rowIndex - The 0 based row number

sheet - The sheet that the row is part of.

Returns:

The row indicated by the rowCounter

getCell

public static Cell getCell(Row row,
 int columnIndex)

Get a specific cell from a row. If the cell doesn't exist, then create it.

Parameters:

row - The row that the cell is part of

columnIndex - The column index that the cell is in.

Returns:

The cell indicated by the column.

createCell

public static Cell createCell(Row row,
 int column,
 String value,
 CellStyle style)

Creates a cell, gives it a value, and applies a style if provided

Parameters:

row - the row to create the cell in

column - the column index to create the cell in

value - The value of the cell

style - If the style is not null, then set

Returns:

A new Cell

createCell

public static Cell createCell(Row row,
 int column,
 String value)

Create a cell, and give it a value.

Parameters:

row - the row to create the cell in

column - the column index to create the cell in

value - The value of the cell

Returns:

A new Cell.

copyCell

@Beta
public static void copyCell(Cell srcCell,
 Cell destCell,
 CellCopyPolicy policy,
 CellCopyContext context)

Copy cell value, formula and style, from srcCell per cell copy policy If srcCell is null, clears the cell value and cell style per cell copy policy. Note that if you are copying from a source cell from a different type of then you may need to disable style copying in the CellCopyPolicy (HSSF styles are not compatible with XSSF styles, for instance). This does not shift references in formulas. The copyRowFrom method on XSSFRow and HSSFRow does attempt to shift references in formulas.

Parameters:

srcCell - The cell to take value, formula and style from

destCell - The cell to copy to

policy - The policy for copying the information, see CellCopyPolicy

context - The context for copying, see CellCopyContext

Throws:

IllegalArgumentException - if copy cell style and srcCell is from a different workbook

IllegalStateException - if srcCell hyperlink is not an instance of Duplicatable

Since:

5.2.0

setAlignment

public static void setAlignment(Cell cell,
 HorizontalAlignment align)

Take a cell, and align it.

This is superior to cell.getCellStyle().setAlignment(align) because this method will not modify the CellStyle object that may be referenced by multiple cells. Instead, this method will search for existing CellStyles that match the desired CellStyle, creating a new CellStyle with the desired style if no match exists.

Parameters:

cell - the cell to set the alignment for

align - the horizontal alignment to use.

Since:

3.15 beta 3

See Also:

• for alignment options

setVerticalAlignment

public static void setVerticalAlignment(Cell cell,
 VerticalAlignment align)

Take a cell, and vertically align it. This is superior to cell.getCellStyle().setVerticalAlignment(align) because this method will not modify the CellStyle object that may be referenced by multiple cells. Instead, this method will search for existing CellStyles that match the desired CellStyle, creating a new CellStyle with the desired style if no match exists.

Parameters:

cell - the cell to set the alignment for

align - the vertical alignment to use.

Since:

3.15 beta 3

See Also:

• for alignment options

setFont

public static void setFont(Cell cell,
 Font font)

Take a cell, and apply a font to it

Parameters:

cell - the cell to set the alignment for

font - The Font that you want to set.

Throws:

IllegalArgumentException - if font and cell do not belong to the same workbook

setCellStyleProperties

@Deprecated
@Removal(version="7.0.0")
public static void setCellStyleProperties(Cell cell,
 Map<String,Object> properties)

Deprecated.

as of POI 5.4.0. See setCellStylePropertiesEnum(Cell, Map)

This method attempts to find an existing CellStyle that matches the cell's current style plus styles properties in properties. A new style is created if the workbook does not contain a matching style.

Modifies the cell style of cell without affecting other cells that use the same style.

This is necessary because Excel has an upper limit on the number of styles that it supports.

This function is more efficient than multiple calls to setCellStyleProperty(Cell, CellPropertyType, Object) if adding multiple cell styles.

For performance reasons, if this is the only cell in a workbook that uses a cell style, this method does NOT remove the old style from the workbook.

Parameters:

cell - The cell to change the style of

properties - The properties to be added to a cell style, as {property: propertyValue}.

Since:

3.14 beta 2

setCellStylePropertiesEnum

public static void setCellStylePropertiesEnum(Cell cell,
 Map<CellPropertyType,Object> properties)

This method attempts to find an existing CellStyle that matches the cell's current style plus styles properties in properties. A new style is created if the workbook does not contain a matching style.

Modifies the cell style of cell without affecting other cells that use the same style.

This is necessary because Excel has an upper limit on the number of styles that it supports.

This function is more efficient than multiple calls to setCellStyleProperty(Cell, CellPropertyType, Object) if adding multiple cell styles.

For performance reasons, if this is the only cell in a workbook that uses a cell style, this method does NOT remove the old style from the workbook.

Parameters:

cell - The cell to change the style of

properties - The properties to be added to a cell style, as {property: propertyValue}.

Since:

5.4.0

setCellStyleProperty

public static void setCellStyleProperty(Cell cell,
 CellPropertyType property,
 Object propertyValue)

This method attempts to find an existing CellStyle that matches the cell's current style plus a single style property propertyName with value propertyValue. A new style is created if the workbook does not contain a matching style.

Modifies the cell style of cell without affecting other cells that use the same style.

If setting more than one cell style property on a cell, use setCellStylePropertiesEnum(Cell, Map), which is faster and does not add unnecessary intermediate CellStyles to the workbook.

Parameters:

cell - The cell that is to be changed.

property - The name of the property that is to be changed.

propertyValue - The value of the property that is to be changed.

Throws:

NullPointerException - if cell or property is null

Since:

5.4.0

setCellStyleProperty

@Deprecated
@Removal(version="7.0.0")
public static void setCellStyleProperty(Cell cell,
 String propertyName,
 Object propertyValue)

Deprecated.

as of POI 5.4.0. See setCellStyleProperty(Cell, CellPropertyType, Object)

This method attempts to find an existing CellStyle that matches the cell's current style plus a single style property propertyName with value propertyValue. A new style is created if the workbook does not contain a matching style.

Modifies the cell style of cell without affecting other cells that use the same style.

If setting more than one cell style property on a cell, use setCellStylePropertiesEnum(Cell, Map), which is faster and does not add unnecessary intermediate CellStyles to the workbook.

Parameters:

cell - The cell that is to be changed.

propertyName - The name of the property that is to be changed.

propertyValue - The value of the property that is to be changed.

getFormatProperties

public static EnumMap<CellPropertyType,Object> getFormatProperties(CellStyle style)

Returns a map containing the format properties of the given cell style. The returned map is not tied to style, so subsequent changes to style will not modify the map, and changes to the returned map will not modify the cell style. The returned map is mutable.

Parameters:

style - cell style

Returns:

map of format properties (CellPropertyType -> Object)

Since:

5.5.0

See Also:

• setFormatProperties(CellStyle, Workbook, Map)

cloneStyle

@Internal
public static void cloneStyle(CellStyle src,
 CellStyle dest,
 Workbook destWorkbook)

Clones the style from one cell to another. For internal use only. Users should use the cloneStyleFrom method on CellStyle instead.

Parameters:

src - source cell style

dest - destination cell style

destWorkbook - destination workbook (can be null but some font info will not be copied if null is passed)

Throws:

IllegalArgumentException - if source or destination styles are null

Since:

5.5.0

translateUnicodeValues

public static Cell translateUnicodeValues(Cell cell)

Looks for text in the cell that should be unicode, like α and provides the unicode version of it.

Parameters:

cell - The cell to check for unicode values

Returns:

translated to unicode