Apache POI - Common 5.3.0 · org.apache.poi.ss.util.SheetUtil

Modifier and Type	Field and Description
public static final int	DEFAULT_CHAR_WIDTH Which default char-width to use if the font-system is unavailable.
private static final char	defaultChar Excel measures columns in units of 1/256th of a character width but the docs say nothing about what particular character is used.
private static final FormulaEvaluator	dummyEvaluator Dummy formula evaluator that does nothing.
private static final double	fontHeightMultiple This is the multiple that the font height is scaled by when determining the boundary of rotated text.
private static FontRenderContext	fontRenderContext drawing context to measure text
private static boolean	ignoreMissingFontSystem A system property which can be enabled to not fail when the font-system is not available on the current machine

Access	Constructor and Description
public	SheetUtil()

Method Summary

Modifier and Type	Method and Description
public static boolean	Returns: true if computing the size for this Font will succeed, false otherwise canComputeColumnWidth(Font The Font that is used in the Cell font) Check if the Fonts are installed correctly so that Java can compute the size of columns.
private static void	copyAttributes(Font font, AttributedString str, int startIdx, int endIdx) Copy text attributes from the supplied Font to Java2D AttributedString
public static Cell	Returns: cell at the given location, or null if not defined getCell(Sheet The workbook sheet to look at. sheet, int The 0-based index of the row. rowIx, int The 0-based index of the cell. colIx) Return the cell, without taking account of merged regions.
public static double	Returns: the width in pixels or -1 if cell is empty getCellWidth(Cell the cell whose width is to be calculated cell, int the width of a single character defaultCharWidth, DataFormatter formatter used to prepare the text to be measured formatter, boolean whether to use merged cells useMergedCells) Deprecated since POI 5.2.5, it is better to pass defaultCharWidth as a float Compute width of a single cell
public static double	Returns: the width in pixels or -1 if cell is empty getCellWidth(Cell the cell whose width is to be calculated cell, float the width of a single character defaultCharWidth, DataFormatter formatter used to prepare the text to be measured formatter, boolean whether to use merged cells useMergedCells) Compute width of a single cell
public static double	Returns: the width in pixels or -1 if cell is empty getCellWidth(Cell the cell whose width is to be calculated cell, int the width of a single character defaultCharWidth, DataFormatter formatter used to prepare the text to be measured formatter, boolean whether to use merged cells useMergedCells, List<CellRangeAddress> The list of merged regions as received via cell.getSheet().getMergedRegions() mergedRegions) Deprecated since POI 5.2.5, it is better to pass defaultCharWidth as a float Compute width of a single cell This method receives the list of merged regions as querying it from the cell/sheet is time-consuming and thus caching the list across cells speeds up certain operations considerably.
public static double	Returns: the width in pixels or -1 if cell is empty getCellWidth(Cell the cell whose width is to be calculated cell, float the width of a single character defaultCharWidth, DataFormatter formatter used to prepare the text to be measured formatter, boolean whether to use merged cells useMergedCells, List<CellRangeAddress> The list of merged regions as received via cell.getSheet().getMergedRegions() mergedRegions) Compute width of a single cell This method receives the list of merged regions as querying it from the cell/sheet is time-consuming and thus caching the list across cells speeds up certain operations considerably.
private static double	Returns: the best fit cell width getCellWidth(float the width of a character using the default font in a workbook defaultCharWidth, int the number of columns that is spanned by the cell (1 if the cell is not part of a merged region) colspan, CellStyle the cell style, which contains text rotation and indention information needed to compute the cell width style, double the minimum best-fit width. This algorithm will only return values greater than or equal to the minimum width. minWidth, AttributedString the text contained in the cell str) Calculate the best-fit width for a cell If a merged cell spans multiple columns, evenly distribute the column width among those columns
public static Cell	Returns: cell at the given location, its base merged cell, or null if not defined getCellWithMerges(Sheet The workbook sheet to look at. sheet, int The 0-based index of the row. rowIx, int The 0-based index of the cell. colIx) Return the cell, taking account of merged regions.
public static double	Returns: the width in pixels or -1 if all cells are empty getColumnWidth(Sheet the sheet to calculate sheet, int 0-based index of the column column, boolean whether to use merged cells useMergedCells) Compute width of a column and return the result.
public static double	Returns: the width in pixels or -1 if cell is empty getColumnWidth(Sheet the sheet to calculate sheet, int 0-based index of the column column, boolean whether to use merged cells useMergedCells, int 0-based index of the first row to consider (inclusive) firstRow, int 0-based index of the last row to consider (inclusive) lastRow) Compute width of a column based on a subset of the rows and return the result.
private static double	Returns: the width in pixels or -1 if cell is empty getColumnWidthForRow(Row the row that contains the cell of interest row, int the column number of the cell whose width is to be calculated column, float the width of a single character defaultCharWidth, DataFormatter formatter used to prepare the text to be measured formatter, boolean whether to use merged cells useMergedCells, List<CellRangeAddress> mergedRegions) Compute width of a single cell in a row Convenience method for `getCellWidth`
public static int	Returns: default character width in pixels getDefaultCharWidth(final Workbook the workbook to get the default character width from wb) Deprecated since POI 5.2.5, it is recommended to switch to `getDefaultCharWidthAsFloat(Workbook)`. Get default character width using the Workbook's default font.
public static float	Returns: default character width in pixels (as a float) getDefaultCharWidthAsFloat(final Workbook the workbook to get the default character width from wb) Get default character width using the Workbook's default font.
protected static FontRenderContext	getFontRenderContext()
protected static boolean	isIgnoreMissingFontSystem()
protected static void	setFontRenderContext(FontRenderContext fontRenderContext)
protected static void	setIgnoreMissingFontSystem(boolean value)

Inherited from java.lang.Object:: clone equals finalize getClass hashCode notify notifyAll toString wait wait wait

DEFAULT_CHAR_WIDTH	back to summary
public static final int DEFAULT_CHAR_WIDTH Which default char-width to use if the font-system is unavailable.

defaultChar	back to summary
private static final char defaultChar Excel measures columns in units of 1/256th of a character width but the docs say nothing about what particular character is used. '0' looks to be a good choice.

dummyEvaluator	back to summary
private static final FormulaEvaluator dummyEvaluator Dummy formula evaluator that does nothing. YK: The only reason of having this class is that `org.apache.poi.ss.usermodel.DataFormatter#formatCellValue(org.apache.poi.ss.usermodel.Cell)` returns formula string for formula cells. Dummy evaluator makes it to format the cached formula result. See Bugzilla #50021

fontHeightMultiple	back to summary
private static final double fontHeightMultiple This is the multiple that the font height is scaled by when determining the boundary of rotated text.

fontRenderContext	back to summary
private static FontRenderContext fontRenderContext drawing context to measure text

ignoreMissingFontSystem	back to summary
private static boolean ignoreMissingFontSystem A system property which can be enabled to not fail when the font-system is not available on the current machine

SheetUtil	back to summary
public SheetUtil()

Method Detail

canComputeColumnWidth	back to summary
public static boolean canComputeColumnWidth(Font font) Check if the Fonts are installed correctly so that Java can compute the size of columns. If a Cell uses a Font which is not available on the operating system then Java may fail to return useful Font metrics and thus lead to an auto-computed size of 0. This method allows to check if computing the sizes for a given Font will succeed or not. Parameters font:Font The Font that is used in the Cell Returns:boolean true if computing the size for this Font will succeed, false otherwise

canComputeColumnWidth

back to summary

public static boolean canComputeColumnWidth(Font font)

Check if the Fonts are installed correctly so that Java can compute the size of columns. If a Cell uses a Font which is not available on the operating system then Java may fail to return useful Font metrics and thus lead to an auto-computed size of 0. This method allows to check if computing the sizes for a given Font will succeed or not.

Parameters

font:Font: The Font that is used in the Cell

Returns:boolean

true if computing the size for this Font will succeed, false otherwise

copyAttributes	back to summary
private static void copyAttributes(Font font, AttributedString str, int startIdx, int endIdx) Copy text attributes from the supplied Font to Java2D AttributedString

getCell back to summary

getCell	back to summary
public static Cell getCell(Sheet sheet, int rowIx, int colIx) Return the cell, without taking account of merged regions. Use `getCellWithMerges(Sheet, int, int)` if you want the top left cell from merged regions instead when the reference is a merged cell. Use this where you want to know if the given cell is explicitly defined or not. Parameters sheet:Sheet The workbook sheet to look at. rowIx:int The 0-based index of the row. colIx:int The 0-based index of the cell. Returns:Cell cell at the given location, or null if not defined Exceptions NullPointerException: if sheet is null

public static Cell getCell(Sheet sheet, int rowIx, int colIx)

Return the cell, without taking account of merged regions.

Use getCellWithMerges(Sheet, int, int) if you want the top left cell from merged regions instead when the reference is a merged cell.

Use this where you want to know if the given cell is explicitly defined or not.

Parameters

sheet:Sheet: The workbook sheet to look at.
rowIx:int: The 0-based index of the row.
colIx:int: The 0-based index of the cell.

Returns:Cell

cell at the given location, or null if not defined

Exceptions

NullPointerException:: if sheet is null

getCellWidth	back to summary
public static double getCellWidth(Cell cell, int defaultCharWidth, DataFormatter formatter, boolean useMergedCells) Deprecated since POI 5.2.5, it is better to pass defaultCharWidth as a float Compute width of a single cell Parameters cell:Cell the cell whose width is to be calculated defaultCharWidth:int the width of a single character formatter:DataFormatter formatter used to prepare the text to be measured useMergedCells:boolean whether to use merged cells Returns:double the width in pixels or -1 if cell is empty

getCellWidth

back to summary

public static double getCellWidth(Cell cell, int defaultCharWidth, DataFormatter formatter, boolean useMergedCells)

Deprecated

since POI 5.2.5, it is better to pass defaultCharWidth as a float

Compute width of a single cell

Parameters

cell:Cell: the cell whose width is to be calculated
defaultCharWidth:int: the width of a single character
formatter:DataFormatter: formatter used to prepare the text to be measured
useMergedCells:boolean: whether to use merged cells

Returns:double

the width in pixels or -1 if cell is empty

getCellWidth	back to summary
public static double getCellWidth(Cell cell, float defaultCharWidth, DataFormatter formatter, boolean useMergedCells) Compute width of a single cell Parameters cell:Cell the cell whose width is to be calculated defaultCharWidth:float the width of a single character formatter:DataFormatter formatter used to prepare the text to be measured useMergedCells:boolean whether to use merged cells Returns:double the width in pixels or -1 if cell is empty Since POI 5.2.5

getCellWidth

back to summary

public static double getCellWidth(Cell cell, float defaultCharWidth, DataFormatter formatter, boolean useMergedCells)

Compute width of a single cell

Parameters

cell:Cell: the cell whose width is to be calculated
defaultCharWidth:float: the width of a single character
formatter:DataFormatter: formatter used to prepare the text to be measured
useMergedCells:boolean: whether to use merged cells

Returns:double

the width in pixels or -1 if cell is empty

Since

POI 5.2.5

getCellWidth	back to summary
public static double getCellWidth(Cell cell, int defaultCharWidth, DataFormatter formatter, boolean useMergedCells, List<CellRangeAddress> mergedRegions) Deprecated since POI 5.2.5, it is better to pass defaultCharWidth as a float Compute width of a single cell This method receives the list of merged regions as querying it from the cell/sheet is time-consuming and thus caching the list across cells speeds up certain operations considerably. Parameters cell:Cell the cell whose width is to be calculated defaultCharWidth:int the width of a single character formatter:DataFormatter formatter used to prepare the text to be measured useMergedCells:boolean whether to use merged cells mergedRegions:List<CellRangeAddress> The list of merged regions as received via cell.getSheet().getMergedRegions() Returns:double the width in pixels or -1 if cell is empty

getCellWidth

back to summary

public static double getCellWidth(Cell cell, int defaultCharWidth, DataFormatter formatter, boolean useMergedCells, List<CellRangeAddress> mergedRegions)

Deprecated

since POI 5.2.5, it is better to pass defaultCharWidth as a float

Compute width of a single cell This method receives the list of merged regions as querying it from the cell/sheet is time-consuming and thus caching the list across cells speeds up certain operations considerably.

Parameters

cell:Cell: the cell whose width is to be calculated
defaultCharWidth:int: the width of a single character
formatter:DataFormatter: formatter used to prepare the text to be measured
useMergedCells:boolean: whether to use merged cells
mergedRegions:List<CellRangeAddress>: The list of merged regions as received via cell.getSheet().getMergedRegions()

Returns:double

the width in pixels or -1 if cell is empty

getCellWidth	back to summary
public static double getCellWidth(Cell cell, float defaultCharWidth, DataFormatter formatter, boolean useMergedCells, List<CellRangeAddress> mergedRegions) Compute width of a single cell This method receives the list of merged regions as querying it from the cell/sheet is time-consuming and thus caching the list across cells speeds up certain operations considerably. Parameters cell:Cell the cell whose width is to be calculated defaultCharWidth:float the width of a single character formatter:DataFormatter formatter used to prepare the text to be measured useMergedCells:boolean whether to use merged cells mergedRegions:List<CellRangeAddress> The list of merged regions as received via cell.getSheet().getMergedRegions() Returns:double the width in pixels or -1 if cell is empty Since POI 5.2.5

getCellWidth

back to summary

public static double getCellWidth(Cell cell, float defaultCharWidth, DataFormatter formatter, boolean useMergedCells, List<CellRangeAddress> mergedRegions)

Parameters

cell:Cell: the cell whose width is to be calculated
defaultCharWidth:float: the width of a single character
formatter:DataFormatter: formatter used to prepare the text to be measured
useMergedCells:boolean: whether to use merged cells
mergedRegions:List<CellRangeAddress>: The list of merged regions as received via cell.getSheet().getMergedRegions()

Returns:double

the width in pixels or -1 if cell is empty

Since

POI 5.2.5

getCellWidth	back to summary
private static double getCellWidth(float defaultCharWidth, int colspan, CellStyle style, double minWidth, AttributedString str) Calculate the best-fit width for a cell If a merged cell spans multiple columns, evenly distribute the column width among those columns Parameters defaultCharWidth:float the width of a character using the default font in a workbook colspan:int the number of columns that is spanned by the cell (1 if the cell is not part of a merged region) style:CellStyle the cell style, which contains text rotation and indention information needed to compute the cell width minWidth:double the minimum best-fit width. This algorithm will only return values greater than or equal to the minimum width. str:AttributedString the text contained in the cell Returns:double the best fit cell width

getCellWidth

back to summary

private static double getCellWidth(float defaultCharWidth, int colspan, CellStyle style, double minWidth, AttributedString str)

Calculate the best-fit width for a cell If a merged cell spans multiple columns, evenly distribute the column width among those columns

Parameters

defaultCharWidth:float: the width of a character using the default font in a workbook
colspan:int: the number of columns that is spanned by the cell (1 if the cell is not part of a merged region)
style:CellStyle: the cell style, which contains text rotation and indention information needed to compute the cell width
minWidth:double: the minimum best-fit width. This algorithm will only return values greater than or equal to the minimum width.
str:AttributedString: the text contained in the cell

Returns:double

the best fit cell width

getCellWithMerges	back to summary
public static Cell getCellWithMerges(Sheet sheet, int rowIx, int colIx) Return the cell, taking account of merged regions. Allows you to find the cell who's contents are shown in a given position in the sheet. If the cell at the given co-ordinates is a merged cell, this will return the primary (top-left) most cell of the merged region. If the cell at the given co-ordinates is not in a merged region, then will return the cell itself. If there is no cell defined at the given co-ordinates, will return null. Parameters sheet:Sheet The workbook sheet to look at. rowIx:int The 0-based index of the row. colIx:int The 0-based index of the cell. Returns:Cell cell at the given location, its base merged cell, or null if not defined Exceptions NullPointerException: if sheet is null

getCellWithMerges

back to summary

public static Cell getCellWithMerges(Sheet sheet, int rowIx, int colIx)

Return the cell, taking account of merged regions. Allows you to find the cell who's contents are shown in a given position in the sheet.

If the cell at the given co-ordinates is a merged cell, this will return the primary (top-left) most cell of the merged region.

If the cell at the given co-ordinates is not in a merged region, then will return the cell itself.

If there is no cell defined at the given co-ordinates, will return null.

Parameters

sheet:Sheet: The workbook sheet to look at.
rowIx:int: The 0-based index of the row.
colIx:int: The 0-based index of the cell.

Returns:Cell

cell at the given location, its base merged cell, or null if not defined

Exceptions

NullPointerException:: if sheet is null

getColumnWidth	back to summary
public static double getColumnWidth(Sheet sheet, int column, boolean useMergedCells) Compute width of a column and return the result. Note that this fall can fail if you do not have the right fonts installed in your OS. Parameters sheet:Sheet the sheet to calculate column:int 0-based index of the column useMergedCells:boolean whether to use merged cells Returns:double the width in pixels or -1 if all cells are empty

getColumnWidth

back to summary

public static double getColumnWidth(Sheet sheet, int column, boolean useMergedCells)

Compute width of a column and return the result. Note that this fall can fail if you do not have the right fonts installed in your OS.

Parameters

sheet:Sheet: the sheet to calculate
column:int: 0-based index of the column
useMergedCells:boolean: whether to use merged cells

Returns:double

the width in pixels or -1 if all cells are empty

getColumnWidth	back to summary
public static double getColumnWidth(Sheet sheet, int column, boolean useMergedCells, int firstRow, int lastRow) Compute width of a column based on a subset of the rows and return the result. Note that this fall can fail if you do not have the right fonts installed in your OS. Parameters sheet:Sheet the sheet to calculate column:int 0-based index of the column useMergedCells:boolean whether to use merged cells firstRow:int 0-based index of the first row to consider (inclusive) lastRow:int 0-based index of the last row to consider (inclusive) Returns:double the width in pixels or -1 if cell is empty

getColumnWidth

back to summary

public static double getColumnWidth(Sheet sheet, int column, boolean useMergedCells, int firstRow, int lastRow)

Compute width of a column based on a subset of the rows and return the result. Note that this fall can fail if you do not have the right fonts installed in your OS.

Parameters

sheet:Sheet: the sheet to calculate
column:int: 0-based index of the column
useMergedCells:boolean: whether to use merged cells
firstRow:int: 0-based index of the first row to consider (inclusive)
lastRow:int: 0-based index of the last row to consider (inclusive)

Returns:double

the width in pixels or -1 if cell is empty

getColumnWidthForRow back to summary

getColumnWidthForRow	back to summary
private static double getColumnWidthForRow(Row row, int column, float defaultCharWidth, DataFormatter formatter, boolean useMergedCells, List<CellRangeAddress> mergedRegions) Compute width of a single cell in a row Convenience method for `getCellWidth` Parameters row:Row the row that contains the cell of interest column:int the column number of the cell whose width is to be calculated defaultCharWidth:float the width of a single character formatter:DataFormatter formatter used to prepare the text to be measured useMergedCells:boolean whether to use merged cells Returns:double the width in pixels or -1 if cell is empty

private static double getColumnWidthForRow(Row row, int column, float defaultCharWidth, DataFormatter formatter, boolean useMergedCells, List<CellRangeAddress> mergedRegions)

Compute width of a single cell in a row Convenience method for getCellWidth

Parameters

row:Row: the row that contains the cell of interest
column:int: the column number of the cell whose width is to be calculated
defaultCharWidth:float: the width of a single character
formatter:DataFormatter: formatter used to prepare the text to be measured
useMergedCells:boolean: whether to use merged cells

Returns:double

the width in pixels or -1 if cell is empty

getDefaultCharWidth back to summary

getDefaultCharWidth	back to summary
public static int getDefaultCharWidth(final Workbook wb) Deprecated since POI 5.2.5, it is recommended to switch to `getDefaultCharWidthAsFloat(Workbook)`. Get default character width using the Workbook's default font. Note that this can fail if your OS does not have the right fonts installed. Parameters wb:Workbook the workbook to get the default character width from Returns:int default character width in pixels Annotations @Internal

public static int getDefaultCharWidth(final Workbook wb)

Deprecated

since POI 5.2.5, it is recommended to switch to getDefaultCharWidthAsFloat(Workbook).

Get default character width using the Workbook's default font. Note that this can fail if your OS does not have the right fonts installed.

Parameters

wb:Workbook: the workbook to get the default character width from

Returns:int

default character width in pixels

Annotations

@Internal

getDefaultCharWidthAsFloat	back to summary
public static float getDefaultCharWidthAsFloat(final Workbook wb) Get default character width using the Workbook's default font. Note that this can fail if your OS does not have the right fonts installed. Parameters wb:Workbook the workbook to get the default character width from Returns:float default character width in pixels (as a float) Annotations @Internal Since POI 5.2.5

getDefaultCharWidthAsFloat

back to summary

public static float getDefaultCharWidthAsFloat(final Workbook wb)

Get default character width using the Workbook's default font. Note that this can fail if your OS does not have the right fonts installed.

Parameters

wb:Workbook: the workbook to get the default character width from

Returns:float

default character width in pixels (as a float)

Annotations

@Internal

Since

POI 5.2.5

getFontRenderContext	back to summary
protected static FontRenderContext getFontRenderContext()

isIgnoreMissingFontSystem	back to summary
protected static boolean isIgnoreMissingFontSystem()

setFontRenderContext	back to summary
protected static void setFontRenderContext(FontRenderContext fontRenderContext)

setIgnoreMissingFontSystem	back to summary
protected static void setIgnoreMissingFontSystem(boolean value)

public Class SheetUtil

Field Summary

Constructor Summary

Method Summary

Field Detail

Constructor Detail

Method Detail