Class CellUtil
- java.lang.Object
-
- org.apache.poi.ss.util.CellUtil
-
public final class CellUtil extends java.lang.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 java.lang.String
ALIGNMENT
static java.lang.String
BORDER_BOTTOM
static java.lang.String
BORDER_LEFT
static java.lang.String
BORDER_RIGHT
static java.lang.String
BORDER_TOP
static java.lang.String
BOTTOM_BORDER_COLOR
static java.lang.String
DATA_FORMAT
static java.lang.String
FILL_BACKGROUND_COLOR
static java.lang.String
FILL_BACKGROUND_COLOR_COLOR
static java.lang.String
FILL_FOREGROUND_COLOR
static java.lang.String
FILL_FOREGROUND_COLOR_COLOR
static java.lang.String
FILL_PATTERN
static java.lang.String
FONT
static java.lang.String
HIDDEN
static java.lang.String
INDENTION
static java.lang.String
LEFT_BORDER_COLOR
static java.lang.String
LOCKED
static java.lang.String
QUOTE_PREFIXED
static java.lang.String
RIGHT_BORDER_COLOR
static java.lang.String
ROTATION
static java.lang.String
SHRINK_TO_FIT
static java.lang.String
TOP_BORDER_COLOR
static java.lang.String
VERTICAL_ALIGNMENT
static java.lang.String
WRAP_TEXT
-
Method Summary
All Methods Static Methods Concrete Methods Modifier and Type Method Description 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, java.lang.String value)
Create a cell, and give it a value.static Cell
createCell(Row row, int column, java.lang.String value, CellStyle style)
Creates a cell, gives it a value, and applies a style if providedstatic Cell
getCell(Row row, int columnIndex)
Get a specific cell from a row.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, java.util.Map<java.lang.String,java.lang.Object> properties)
This method attempts to find an existing CellStyle that matches thecell
's current style plus styles properties inproperties
.static void
setCellStyleProperty(Cell cell, java.lang.String propertyName, java.lang.Object propertyValue)
This method attempts to find an existing CellStyle that matches thecell
's current style plus a single style propertypropertyName
with valuepropertyValue
.static void
setFont(Cell cell, Font font)
Take a cell, and apply a font to itstatic 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.
-
-
-
Field Detail
-
ALIGNMENT
public static final java.lang.String ALIGNMENT
- See Also:
- Constant Field Values
-
BORDER_BOTTOM
public static final java.lang.String BORDER_BOTTOM
- See Also:
- Constant Field Values
-
BORDER_LEFT
public static final java.lang.String BORDER_LEFT
- See Also:
- Constant Field Values
-
BORDER_RIGHT
public static final java.lang.String BORDER_RIGHT
- See Also:
- Constant Field Values
-
BORDER_TOP
public static final java.lang.String BORDER_TOP
- See Also:
- Constant Field Values
-
BOTTOM_BORDER_COLOR
public static final java.lang.String BOTTOM_BORDER_COLOR
- See Also:
- Constant Field Values
-
LEFT_BORDER_COLOR
public static final java.lang.String LEFT_BORDER_COLOR
- See Also:
- Constant Field Values
-
RIGHT_BORDER_COLOR
public static final java.lang.String RIGHT_BORDER_COLOR
- See Also:
- Constant Field Values
-
TOP_BORDER_COLOR
public static final java.lang.String TOP_BORDER_COLOR
- See Also:
- Constant Field Values
-
DATA_FORMAT
public static final java.lang.String DATA_FORMAT
- See Also:
- Constant Field Values
-
FILL_BACKGROUND_COLOR
public static final java.lang.String FILL_BACKGROUND_COLOR
- See Also:
- Constant Field Values
-
FILL_FOREGROUND_COLOR
public static final java.lang.String FILL_FOREGROUND_COLOR
- See Also:
- Constant Field Values
-
FILL_BACKGROUND_COLOR_COLOR
public static final java.lang.String FILL_BACKGROUND_COLOR_COLOR
- See Also:
- Constant Field Values
-
FILL_FOREGROUND_COLOR_COLOR
public static final java.lang.String FILL_FOREGROUND_COLOR_COLOR
- See Also:
- Constant Field Values
-
FILL_PATTERN
public static final java.lang.String FILL_PATTERN
- See Also:
- Constant Field Values
-
FONT
public static final java.lang.String FONT
- See Also:
- Constant Field Values
-
HIDDEN
public static final java.lang.String HIDDEN
- See Also:
- Constant Field Values
-
INDENTION
public static final java.lang.String INDENTION
- See Also:
- Constant Field Values
-
LOCKED
public static final java.lang.String LOCKED
- See Also:
- Constant Field Values
-
ROTATION
public static final java.lang.String ROTATION
- See Also:
- Constant Field Values
-
VERTICAL_ALIGNMENT
public static final java.lang.String VERTICAL_ALIGNMENT
- See Also:
- Constant Field Values
-
WRAP_TEXT
public static final java.lang.String WRAP_TEXT
- See Also:
- Constant Field Values
-
SHRINK_TO_FIT
public static final java.lang.String SHRINK_TO_FIT
- See Also:
- Constant Field Values
-
QUOTE_PREFIXED
public static final java.lang.String QUOTE_PREFIXED
- See Also:
- Constant Field Values
-
-
Method Detail
-
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 numbersheet
- 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 ofcolumnIndex
- The column index that the cell is in.- Returns:
- The cell indicated by the column.
-
createCell
public static Cell createCell(Row row, int column, java.lang.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 incolumn
- the column index to create the cell invalue
- The value of the cellstyle
- If the style is not null, then set- Returns:
- A new Cell
-
createCell
public static Cell createCell(Row row, int column, java.lang.String value)
Create a cell, and give it a value.- Parameters:
row
- the row to create the cell incolumn
- the column index to create the cell invalue
- 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 theCellCopyPolicy
(HSSF styles are not compatible with XSSF styles, for instance). This does not shift references in formulas. ThecopyRowFrom
method onXSSFRow
andHSSFRow
does attempt to shift references in formulas.- Parameters:
srcCell
- The cell to take value, formula and style fromdestCell
- The cell to copy topolicy
- The policy for copying the information, seeCellCopyPolicy
context
- The context for copying, seeCellCopyContext
- Throws:
java.lang.IllegalArgumentException
- if copy cell style and srcCell is from a different workbookjava.lang.IllegalStateException
- if srcCell hyperlink is not an instance ofDuplicatable
- Since:
- POI 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 foralign
- the horizontal alignment to use.- Since:
- POI 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 foralign
- the vertical alignment to use.- Since:
- POI 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 forfont
- The Font that you want to set.- Throws:
java.lang.IllegalArgumentException
- iffont
andcell
do not belong to the same workbook
-
setCellStyleProperties
public static void setCellStyleProperties(Cell cell, java.util.Map<java.lang.String,java.lang.Object> properties)
This method attempts to find an existing CellStyle that matches the
cell
's current style plus styles properties inproperties
. 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, String, 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 ofproperties
- The properties to be added to a cell style, as {propertyName: propertyValue}.- Since:
- POI 3.14 beta 2
-
setCellStyleProperty
public static void setCellStyleProperty(Cell cell, java.lang.String propertyName, java.lang.Object propertyValue)
This method attempts to find an existing CellStyle that matches the
cell
's current style plus a single style propertypropertyName
with valuepropertyValue
. 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
setCellStyleProperties(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.
-
-