Apache POI - Common 5.3.0 · org.apache.poi.ss.usermodel.Sheet

Modifier and Type	Field and Description
public static final short	BottomMargin
public static final short	FooterMargin
public static final short	HeaderMargin
public static final short	LeftMargin
public static final byte	PANE_LOWER_LEFT
public static final byte	PANE_LOWER_RIGHT
public static final byte	PANE_UPPER_LEFT
public static final byte	PANE_UPPER_RIGHT
public static final short	RightMargin
public static final short	TopMargin

Method Summary

Modifier and Type	Method and Description
public int	Returns: index of this region addMergedRegion(CellRangeAddress (rowfrom/colfrom-rowto/colto) to merge region) Adds a merged region of cells (hence those cells form one)
public int	Returns: index of this region addMergedRegionUnsafe(CellRangeAddress to merge region) Adds a merged region of cells (hence those cells form one).
public void	addValidationData(DataValidation The Data validation object settings dataValidation) Creates a data validation object
public void	autoSizeColumn(int the column index column) Adjusts the column width to fit the contents.
public void	autoSizeColumn(int the column index column, boolean whether to use the contents of merged cells when calculating the width of the column useMergedCells) Adjusts the column width to fit the contents.
public Drawing<?>	Returns: The new drawing patriarch. createDrawingPatriarch() Creates the top-level drawing patriarch.
public void	createFreezePane(int Horizontal position of split. colSplit, int Vertical position of split. rowSplit, int Left column visible in right pane. leftmostColumn, int Top row visible in bottom pane topRow) Creates a split (freezepane).
public void	createFreezePane(int Horizontal position of split. colSplit, int Vertical position of split. rowSplit) Creates a split (freezepane).
public Row	Returns: high level Row object representing a row in the sheet createRow(int row number rownum) Create a new row within the sheet and return the high level representation
public void	createSplitPane(int Horizontal position of split (in 1/20th of a point). xSplitPos, int Vertical position of split (in 1/20th of a point). ySplitPos, int Left column visible in right pane. leftmostColumn, int Top row visible in bottom pane topRow, int Active pane. One of: PANE_LOWER_RIGHT, PANE_UPPER_RIGHT, PANE_LOWER_LEFT, PANE_UPPER_LEFT activePane) Deprecated use `createSplitPane(int, int, int, int, PaneType)` Creates a split pane.
public void	createSplitPane(int Horizontal position of split (in 1/20th of a point). xSplitPos, int Vertical position of split (in 1/20th of a point). ySplitPos, int Left column visible in right pane. leftmostColumn, int Top row visible in bottom pane topRow, PaneType Active pane. activePane) Creates a split pane.
public CellAddress	Returns: the location of the active cell. getActiveCell() Return location of the active cell, e.g. `A1`.
public boolean	Returns: `true` if the sheet displays Automatic Page Breaks. getAutobreaks() Flag indicating whether the sheet displays Automatic Page Breaks.
public Comment	Returns: cell comment or `null` if not found getCellComment(CellAddress ref) Returns cell comment for the specified location
public Map<CellAddress, ? extends Comment>	Returns: A map of each Comment in the sheet, keyed on the cell address where the comment is located. getCellComments() Returns all cell comments on this sheet.
public int[]	Returns: all the vertical page breaks, or null if there are no column page breaks getColumnBreaks() Retrieves all the vertical page breaks
public int	getColumnOutlineLevel(int columnIndex) Returns the column outline level.
public CellStyle	getColumnStyle(int column) Returns the CellStyle that applies to the given (0 based) column, or null if no style has been set for that column
public int	Returns: width - the width in units of 1/256th of a character width getColumnWidth(int - the column to get (0-based) columnIndex) get the width (in units of 1/256th of a character width ) Character width is defined as the maximum digit width of the numbers `0, 1, 2, ...`
public float	Returns: width in pixels getColumnWidthInPixels(int - the column to set (0-based) columnIndex) get the width in pixel Please note, that this method works correctly only for workbooks with the default font size (Arial 10pt for .xls and Calibri 11pt for .xlsx).
public DataValidationHelper	getDataValidationHelper()
public List<? extends DataValidation>	Returns: list of DataValidation in the sheet getDataValidations() Returns the list of DataValidation in the sheet.
public int	Returns: default column width measured in characters getDefaultColumnWidth() Get the default column width for the sheet (if the columns do not define their own width) in characters
public short	Returns: default row height measured in twips (1/20 of a point) getDefaultRowHeight() Get the default row height for the sheet (if the rows do not define their own height) in twips (1/20 of a point)
public float	Returns: default row height in points getDefaultRowHeightInPoints() Get the default row height for the sheet (if the rows do not define their own height) in points.
public boolean	Returns: boolean - guts or no guts getDisplayGuts() Get whether to display the guts or not, default value is true
public Drawing<?>	Returns: a SpreadsheetML drawing getDrawingPatriarch() Return the sheet's existing drawing, or null if there isn't yet one.
public int	Returns: the number of the first logical row on the sheet (0-based) or -1 if no row exists getFirstRowNum() Gets the first row on the sheet.
public boolean	Returns: `true` if the Fit to Page print option is enabled. getFitToPage() Flag indicating whether the Fit to Page print option is enabled.
public Footer	Returns: the document footer. Never `null` getFooter() Gets the user model for the default document footer.
public boolean	Returns: true if the Sheet has the recalculate-flag set. getForceFormulaRecalculation() Whether Excel will be asked to recalculate all formulas in this sheet when the workbook is opened.
public Header	Returns: the document header. Never `null` getHeader() Gets the user model for the default document header.
public boolean	getHorizontallyCenter() Determine whether printed output for this sheet will be horizontally centered.
public Hyperlink	Returns: hyperlink if there is a hyperlink anchored at row, column; otherwise returns null getHyperlink(int The 0-based index of the row to look at. row, int The 0-based index of the column to look at. column) Get a Hyperlink in this sheet anchored at row, column
public Hyperlink	Returns: hyperlink if there is a hyperlink anchored at `addr`; otherwise returns `null` getHyperlink(CellAddress The address of the cell containing the hyperlink addr) Get a Hyperlink in this sheet located in a cell specified by {code addr}
public List<? extends Hyperlink>	Returns: Hyperlinks for the sheet getHyperlinkList() Get a list of Hyperlinks in this sheet
public int	Returns: last row contained on this sheet (0-based) or -1 if no row exists getLastRowNum() Gets the last row on the sheet Note: rows which had content before and were set to empty later might still be counted as rows by Excel and Apache POI, so the result of this method will include such rows and thus the returned value might be higher than expected!
public short	Returns: short indicating the rownum (0 based) of the top row getLeftCol() The left col in the visible view when the sheet is first viewed after opening it in a viewer
public double	Returns: the size of the margin getMargin(short which margin to get margin) Deprecated use `getMargin(PageMargin)` Gets the size of the margin in inches.
public double	Returns: the size of the margin getMargin(PageMargin which margin to get margin) Gets the size of the margin in inches.
public CellRangeAddress	Returns: the merged region at the specified index getMergedRegion(int index) Returns the merged region at the specified index
public List<CellRangeAddress>	Returns: the list of merged regions getMergedRegions() Returns the list of merged regions.
public int	Returns: number of merged regions getNumMergedRegions() Returns the number of merged regions
public PaneInformation	Returns: null if no pane configured, or the pane information. getPaneInformation() Returns the information regarding the currently configured pane (split or freeze)
public int	Returns: the number of physically defined rows in this sheet getPhysicalNumberOfRows() Returns the number of physically defined rows (NOT the number of rows in the sheet)
public PrintSetup	Returns: The user model for the print setup object. getPrintSetup() Gets the print setup object.
public boolean	Returns: true => protection enabled; false => protection disabled getProtect() Answer whether protection is enabled or disabled
public CellRangeAddress	Returns: an `CellRangeAddress` containing the repeating columns for the Sheet, or null. getRepeatingColumns() Gets the repeating columns used when printing the sheet, as found in File->PageSetup->Sheet.
public CellRangeAddress	Returns: an `CellRangeAddress` containing the repeating rows for the Sheet, or null. getRepeatingRows() Gets the repeating rows used when printing the sheet, as found in File->PageSetup->Sheet.
public Row	Returns: Row representing the row-number or null if its not defined on the sheet getRow(int row to get (0-based) rownum) Returns the logical row (not physical) 0-based.
public int[]	Returns: all the horizontal page breaks, or null if there are no row page breaks getRowBreaks() Retrieves all the horizontal page breaks
public boolean	Returns: `true` if row summaries appear below detail in the outline getRowSumsBelow() Flag indicating whether summary rows appear below detail in an outline, when applying an outline.
public boolean	Returns: `true` if col summaries appear right of the detail in the outline getRowSumsRight() Flag indicating whether summary columns appear to the right of detail in an outline, when applying an outline.
public boolean	Returns: true => protection enabled; false => protection disabled getScenarioProtect() Answer whether scenario protection is enabled or disabled
public SheetConditionalFormatting	Returns: conditional formatting rule for this sheet getSheetConditionalFormatting() The 'Conditional Formatting' facet for this `Sheet`
public String	Returns: the name of this sheet getSheetName() Returns the name of this sheet
public short	Returns: short indicating the rownum (0 based) of the top row getTopRow() The top row in the visible view when the sheet is first viewed after opening it in a viewer
public boolean	getVerticallyCenter() Determine whether printed output for this sheet will be vertically centered.
public Workbook	Returns: the parent workbook getWorkbook() Return the parent workbook
public void	groupColumn(int beginning of the column range. fromColumn, int end of the column range. toColumn) Create an outline for the provided column range.
public void	groupRow(int start row (0-based) fromRow, int end row (0-based) toRow) Tie a range of rows together so that they can be collapsed or expanded
public boolean	Returns: FIXME: Document this! isColumnBroken(int FIXME: Document this! column) Determines if there is a page break at the indicated column
public boolean	Returns: hidden - `false` if the column is visible isColumnHidden(int - the column to set (0-based) columnIndex) Get the hidden state for a given column
public boolean	Returns: whether formulas are displayed isDisplayFormulas() Returns if formulas are displayed
public boolean	Returns: whether gridlines are displayed isDisplayGridlines() Returns if gridlines are displayed
public boolean	Returns: whether RowColHeadings are displayed isDisplayRowColHeadings() Returns if RowColHeadings are displayed.
public boolean	Returns: whether all zero values on the worksheet are displayed isDisplayZeros() Gets the flag indicating whether the window should show 0 (zero) in cells containing zero value.
public boolean	Returns: `true` if this sheet prints gridlines. isPrintGridlines() Gets the flag indicating whether this sheet displays the lines between rows and columns to make editing and reading easier.
public boolean	Returns: `true` if this sheet prints row and column headings. isPrintRowAndColumnHeadings() Gets the flag indicating whether this sheet prints the row and column headings when printing.
public boolean	Returns: whether the text is displayed in right-to-left mode in the window isRightToLeft() Whether the text is displayed in right-to-left mode in the window
public boolean	Returns: FIXME: Document this! isRowBroken(int FIXME: Document this! row) Determines if there is a page break at the indicated row
public boolean	Returns: `true` if this sheet is currently selected isSelected() Note - this is not the same as whether the sheet is focused (isActive)
public default Iterator<Row>	iterator() Implements java.lang.Iterable.iterator. Alias for `rowIterator()` to allow foreach loops
public void	protectSheet(String to set for protection. Pass `null` to remove protection password) Sets the protection enabled as well as the password
public CellRange<? extends Cell>	Returns: the `CellRange` of cells affected by this change removeArrayFormula(Cell any cell within Array Formula range cell) Remove a Array Formula from this sheet.
public void	removeColumnBreak(int The 0-based index of the column. column) Removes a page break at the indicated column
public void	removeMergedRegion(int of the region to unmerge index) Removes a merged region of cells (hence letting them free)
public void	removeMergedRegions(Collection<Integer> A set of the regions to unmerge indices) Removes a number of merged regions of cells (hence letting them free)
public void	removeRow(Row representing a row to remove. row) Remove a row from this sheet.
public void	removeRowBreak(int The 0-based index of the row. row) Removes the page break at the indicated row
public Iterator<Row>	Returns: an iterator of the PHYSICAL rows. Meaning the 3rd element may not be the third row if say for instance the second row is undefined. rowIterator() Returns an iterator of the physical rows
public void	setActiveCell(CellAddress the location of the active cell, e.g. `A1`. address) Sets location of the active cell
public CellRange<? extends Cell>	Returns: the `CellRange` of cells affected by this change setArrayFormula(String text representation of the formula formula, CellRangeAddress Region of array formula for result. range) Sets array formula to specified region for result.
public void	setAutobreaks(boolean `true` if the sheet displays Automatic Page Breaks. value) Flag indicating whether the sheet displays Automatic Page Breaks.
public AutoFilter	setAutoFilter(CellRangeAddress the range of cells to filter range) Enable filtering for a range of cells
public void	setColumnBreak(int the column to break, inclusive column) Sets a page break at the indicated column.
public void	setColumnGroupCollapsed(int One of the columns in the group. columnNumber, boolean true = collapse group, false = expand group. collapsed) Expands or collapses a column group.
public void	setColumnHidden(int - the column to get (0-based) columnIndex, boolean - the visibility state of the column hidden) Set the visibility state for a given column
public void	setColumnWidth(int - the column to set (0-based) columnIndex, int - the width in units of 1/256th of a character width width) Set the width (in units of 1/256th of a character width) The maximum column width for an individual cell is 255 characters.
public void	setDefaultColumnStyle(int the column index column, CellStyle the style to set style) Sets the default column style for a given column.
public void	setDefaultColumnWidth(int default column width measured in characters width) Set the default column width for the sheet (if the columns do not define their own width) in characters
public void	setDefaultRowHeight(short default row height measured in twips (1/20 of a point) height) Set the default row height for the sheet (if the rows do not define their own height) in twips (1/20 of a point)
public void	setDefaultRowHeightInPoints(float default row height height) Set the default row height for the sheet (if the rows do not define their own height) in points
public void	setDisplayFormulas(boolean whether to show formulas or not show) Sets whether the formulas are shown in a viewer
public void	setDisplayGridlines(boolean whether to show gridlines or not show) Sets whether the gridlines are shown in a viewer
public void	setDisplayGuts(boolean - guts or no guts value) Set whether to display the guts or not
public void	setDisplayRowColHeadings(boolean whether to show RowColHeadings or not show) Sets whether the RowColHeadings are shown in a viewer
public void	setDisplayZeros(boolean whether to display or hide all zero values on the worksheet value) Set whether the window should show 0 (zero) in cells containing zero value.
public void	setFitToPage(boolean `true` if the Fit to Page print option is enabled. value) Flag indicating whether the Fit to Page print option is enabled.
public void	setForceFormulaRecalculation(boolean true if the application will perform a full recalculation of this worksheet values when the workbook is opened value) Control if Excel should be asked to recalculate all formulas on this sheet when the workbook is opened.
public void	setHorizontallyCenter(boolean true to horizontally center, false otherwise. value) Determines whether the output is horizontally centered on the page.
public void	setMargin(short which margin to set margin, double the size of the margin size) Deprecated use `setMargin(PageMargin, double)` instead Sets the size of the margin in inches.
public void	setMargin(PageMargin which margin to set margin, double the size of the margin size) Sets the size of the margin in inches.
public void	setPrintGridlines(boolean `true` if this sheet should print gridlines. show) Sets the flag indicating whether this sheet should print the lines between rows and columns to make editing and reading easier.
public void	setPrintRowAndColumnHeadings(boolean `true` if this sheet should print row and column headings. show) Sets the flag indicating whether this sheet should print row and columns headings when printing.
public void	setRepeatingColumns(CellRangeAddress a `CellRangeAddress` containing the repeating columns for the Sheet, or null. columnRangeRef) Sets the repeating columns used when printing the sheet, as found in File->PageSetup->Sheet.
public void	setRepeatingRows(CellRangeAddress a `CellRangeAddress` containing the repeating rows for the Sheet, or null. rowRangeRef) Sets the repeating rows used when printing the sheet, as found in File->PageSetup->Sheet.
public void	setRightToLeft(boolean true for right to left, false otherwise. value) Sets whether the worksheet is displayed from right to left instead of from left to right.
public void	setRowBreak(int the row to break, inclusive row) Sets a page break at the indicated row Breaks occur above the specified row and left of the specified column inclusive.
public void	setRowGroupCollapsed(int start row of a grouped range of rows (0-based) row, boolean whether to expand/collapse the detail rows collapse) Set view state of a grouped range of rows
public void	setRowSumsBelow(boolean `true` if row summaries appear below detail in the outline value) Flag indicating whether summary rows appear below detail in an outline, when applying an outline.
public void	setRowSumsRight(boolean `true` if col summaries appear right of the detail in the outline value) Flag indicating whether summary columns appear to the right of detail in an outline, when applying an outline.
public void	setSelected(boolean `true` if this sheet is selected value) Sets a flag indicating whether this sheet is selected.
public void	setVerticallyCenter(boolean true to vertically center, false otherwise. value) Determines whether the output is vertically centered on the page.
public void	setZoom(int window zoom magnification scale) Window zoom magnification for current view representing percent values.
public void	shiftColumns(int the column to start shifting startColumn, int the column to end shifting endColumn, int the number of columns to shift n) Shifts columns between startColumn and endColumn, n number of columns.
public void	shiftRows(int the row to start shifting startRow, int the row to end shifting endRow, int the number of rows to shift n) Shifts rows between startRow and endRow n number of rows.
public void	shiftRows(int the row to start shifting startRow, int the row to end shifting endRow, int the number of rows to shift n, boolean whether to copy the row height during the shift copyRowHeight, boolean whether to set the original row's height to the default resetOriginalRowHeight) Shifts rows between startRow and endRow n number of rows.
public void	showInPane(int the top row to show in desktop window pane topRow, int the left column to show in desktop window pane leftCol) Sets desktop window pane display area, when the file is first opened in a viewer.
public default Spliterator<Row>	Returns: a spliterator of the PHYSICAL rows. Meaning the 3rd element may not be the third row if say for instance the second row is undefined. spliterator() Overrides default java.lang.Iterable.spliterator. Returns a spliterator of the physical rows
public void	ungroupColumn(int start column (0-based) fromColumn, int end column (0-based) toColumn) Ungroup a range of columns that were previously grouped
public void	ungroupRow(int start row (0-based) fromRow, int end row (0-based) toRow) Ungroup a range of rows that were previously grouped
public void	validateMergedRegions() Verify that merged regions do not intersect multi-cell array formulas and no merged regions intersect another merged region in this sheet.

Inherited from java.lang.Iterable:: forEach

public Interface Sheet

Field Summary

Method Summary