Class HSSFSheet
-
Field Summary
FieldsModifier and TypeFieldDescriptionprotected final InternalWorkbookprotected final HSSFWorkbookstatic final intUsed for compile-time optimization.Fields inherited from interface org.apache.poi.ss.usermodel.Sheet
BottomMargin, FooterMargin, HeaderMargin, LeftMargin, PANE_LOWER_LEFT, PANE_LOWER_RIGHT, PANE_UPPER_LEFT, PANE_UPPER_RIGHT, RightMargin, TopMargin -
Constructor Summary
ConstructorsModifierConstructorDescriptionprotectedHSSFSheet(HSSFWorkbook workbook) Creates new HSSFSheet - called by HSSFWorkbook to create a sheet from scratch.protectedHSSFSheet(HSSFWorkbook workbook, InternalSheet sheet) Creates an HSSFSheet representing the given Sheet object. -
Method Summary
Modifier and TypeMethodDescriptionintaddMergedRegion(CellRangeAddress region) Adds a merged region of cells on a sheet.intAdds a merged region of cells (hence those cells form one).voidaddValidationData(DataValidation dataValidation) Creates a data validation objectvoidautoSizeColumn(int column) Adjusts the column width to fit the contents.voidautoSizeColumn(int column, boolean useMergedCells) Adjusts the column width to fit the contents.Creates the top-level drawing patriarch.voidcreateFreezePane(int colSplit, int rowSplit) Creates a split (freezepane).voidcreateFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow) Creates a split (freezepane).createRow(int rownum) Create a new row within the sheet and return the high level representationvoidcreateSplitPane(int xSplitPos, int ySplitPos, int leftmostColumn, int topRow, int activePane) Creates a split pane.voiddumpDrawingRecords(boolean fat, PrintWriter pw) Aggregates the drawing records and dumps the escher record hierarchy to the standard output.protected HSSFCommentfindCellComment(int row, int column) Return location of the active cell, e.g.booleanwhether alternate expression evaluation is onbooleanwhether alternative formula entry is onbooleanshow automatic page breaks or notReturns cell comment for the specified row and columnReturns all cell comments on this sheet.int[]Retrieves all the vertical page breaksintgetColumnOutlineLevel(int columnIndex) Returns the column outline level.getColumnStyle(int column) Returns the HSSFCellStyle that applies to the given (0 based) column, or null if no style has been set for that columnintgetColumnWidth(int columnIndex) get the width (in units of 1/256th of a character width )floatgetColumnWidthInPixels(int column) get the width in pixelReturns the list of DataValidation in the sheet.intget the default column width for the sheet (if the columns do not define their own width) in charactersshortget the default row height for the sheet (if the rows do not define their own height) in twips (1/20 of a point)floatget the default row height for the sheet (if the rows do not define their own height) in points.booleanget whether sheet is a dialog sheet or notbooleanget whether to display the guts or notReturns the agregate escher records for this sheet, it there is one.This will hold any graphics or charts for the sheet.intGets the first row on the sheetbooleanfit to page option is onGets the user model for the default document footer.booleanWhether a record must be inserted or not at generation to indicate that formula must be recalculated when workbook is opened.Gets the user model for the default document header.booleanDetermine whether printed output for this sheet will be horizontally centered.getHyperlink(int row, int column) Get a Hyperlink in this sheet anchored at row, columngetHyperlink(CellAddress addr) Get a Hyperlink in this sheet located in a cell specified by {code addr}Get a list of Hyperlinks in this sheetintGets the number last row on the sheet.shortThe left col in the visible view when the sheet is first viewed after opening it in a viewerdoublegetMargin(short margin) Gets the size of the margin in inches.getMergedRegion(int index) Returns the merged region at the specified indexReturns the list of merged regions.intreturns the number of merged regionsbooleanAnswer whether object protection is enabled or disabledReturns the information regarding the currently configured pane (split or freeze).shortintReturns the number of physically defined rows (NOT the number of rows in the sheet)Gets the print setup object.booleanAnswer whether protection is enabled or disabledGets the repeating columns used when printing the sheet, as found in File->PageSetup->Sheet.Gets the repeating rows used when printing the sheet, as found in File->PageSetup->Sheet.getRow(int rowIndex) Returns the logical row (not physical) 0-based.int[]Retrieves all the horizontal page breaksbooleanget if row summaries appear below detail in the outlinebooleanget if col summaries appear right of the detail in the outlinebooleanAnswer whether scenario protection is enabled or disabledThe 'Conditional Formatting' facet for this SheetReturns the name of this sheetshortThe top row in the visible view when the sheet is first viewed after opening it in a viewerbooleanDetermine whether printed output for this sheet will be vertically centered.Return the parent workbookvoidgroupColumn(int fromColumn, int toColumn) Create an outline for the provided column range.voidgroupRow(int fromRow, int toRow) Tie a range of cell together so that they can be collapsed or expandedprotected voidinsertChartRecords(List<Record> records) booleanisActive()booleanisColumnBroken(int column) Determines if there is a page break at the indicated columnbooleanisColumnHidden(int columnIndex) Get the hidden state for a given column.booleanReturns if formulas are displayed.booleanReturns if gridlines are displayed.booleanReturns if RowColHeadings are displayed.booleanGets the flag indicating whether the window should show 0 (zero) in cells containing zero value.booleanget whether gridlines are printed.booleanReturns whether gridlines are printed.booleanReturns whether row and column headings are printed.booleanWhether the text is displayed in right-to-left mode in the windowbooleanisRowBroken(int row) Determines if there is a page break at the indicated rowbooleanNote - this is not the same as whether the sheet is focused (isActive)iterator()Alias forrowIterator()to allow foreach loopsprotected voidcheck whether the data of sheet can be serializedvoidprotectSheet(String password) Sets the protection enabled as well as the passwordremoveArrayFormula(Cell cell) Remove a Array Formula from this sheet.voidremoveColumnBreak(int column) Removes a page break at the indicated columnprotected voidRemove the underlying HyperlinkRecord from this sheetprotected voidremoveHyperlink(HSSFHyperlink link) Remove the underlying HyperlinkRecord from this sheet.voidremoveMergedRegion(int index) removes a merged region of cells (hence letting them free)voidremoveMergedRegions(Collection<Integer> indices) Removes a number of merged regions of cells (hence letting them free)voidRemove a row from this sheet.voidremoveRowBreak(int row) Removes the page break at the indicated rowReturns an iterator of the physical rowsvoidsetActive(boolean sel) Sets whether sheet is selected.voidsetActiveCell(CellAddress address) Sets location of the active cellvoidsetAlternativeExpression(boolean b) whether alternate expression evaluation is onvoidsetAlternativeFormula(boolean b) whether alternative formula entry is onsetArrayFormula(String formula, CellRangeAddress range) Sets array formula to specified region for result.voidsetAutobreaks(boolean b) show automatic page breaks or notsetAutoFilter(CellRangeAddress range) Enable filtering for a range of cellsvoidsetColumnBreak(int column) Sets a page break at the indicated column.voidsetColumnGroupCollapsed(int columnNumber, boolean collapsed) Expands or collapses a column group.voidsetColumnHidden(int columnIndex, boolean hidden) Get the visibility state for a given column.voidsetColumnWidth(int columnIndex, int width) Set the width (in units of 1/256th of a character width)voidsetDefaultColumnStyle(int column, CellStyle style) Sets the default column style for a given column.voidsetDefaultColumnWidth(int width) set the default column width for the sheet (if the columns do not define their own width) in charactersvoidsetDefaultRowHeight(short 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)voidsetDefaultRowHeightInPoints(float height) set the default row height for the sheet (if the rows do not define their own height) in pointsvoidsetDialog(boolean b) set whether sheet is a dialog sheet or notvoidsetDisplayFormulas(boolean show) Sets whether the formulas are shown in a viewer.voidsetDisplayGridlines(boolean show) Sets whether the gridlines are shown in a viewer.voidsetDisplayGuts(boolean b) set whether to display the guts or notvoidsetDisplayRowColHeadings(boolean show) Sets whether the RowColHeadings are shown in a viewer.voidsetDisplayZeros(boolean value) Set whether the window should show 0 (zero) in cells containing zero value.voidsetFitToPage(boolean b) fit to page option is onvoidsetForceFormulaRecalculation(boolean value) Control if Excel should be asked to recalculate all formulas on this sheet when the workbook is opened.voidsetGridsPrinted(boolean value) set whether gridlines printed.voidsetHorizontallyCenter(boolean value) determines whether the output is horizontally centered on the page.voidsetMargin(short margin, double size) Sets the size of the margin in inches.voidsetPrintGridlines(boolean show) Turns on or off the printing of gridlines.voidsetPrintRowAndColumnHeadings(boolean show) Turns on or off the printing of row and column headings.voidsetRepeatingColumns(CellRangeAddress columnRangeRef) Sets the repeating columns used when printing the sheet, as found in File->PageSetup->Sheet.voidsetRepeatingRows(CellRangeAddress rowRangeRef) Sets the repeating rows used when printing the sheet, as found in File->PageSetup->Sheet.voidsetRightToLeft(boolean value) Sets whether the worksheet is displayed from right to left instead of from left to right.voidsetRowBreak(int row) Sets a page break at the indicated row Breaks occur above the specified row and left of the specified column inclusive.voidsetRowGroupCollapsed(int rowIndex, boolean collapse) Set view state of a grouped range of rowsvoidsetRowSumsBelow(boolean b) set if row summaries appear below detail in the outlinevoidsetRowSumsRight(boolean b) set if col summaries appear right of the detail in the outlinevoidsetSelected(boolean sel) Sets whether sheet is selected.voidsetVerticallyCenter(boolean value) determines whether the output is vertically centered on the page.voidsetZoom(int scale) Window zoom magnification for current view representing percent values.voidsetZoom(int numerator, int denominator) Sets the zoom magnification for the sheet.voidshiftColumns(int startColumn, int endColumn, int n) Shifts columns in range [startColumn, endColumn] for n places to the right.protected voidshiftMerged(int startRow, int endRow, int n, boolean isRow) Deprecated.POI 3.15 beta 2.voidshiftRows(int startRow, int endRow, int n) Shifts rows between startRow and endRow n number of rows.voidshiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight) Shifts rows between startRow and endRow n number of rows.voidshiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight, boolean moveComments) Shifts rows between startRow and endRow n number of rows.voidshowInPane(int toprow, int leftcol) Sets desktop window pane display area, when the file is first opened in a viewer.voidungroupColumn(int fromColumn, int toColumn) Ungroup a range of columns that were previously groupedvoidungroupRow(int fromRow, int toRow) Ungroup a range of rows that were previously groupedprotected voidvalidateColumn(int column) Runs a bounds check for column numbersvoidVerify that merged regions do not intersect multi-cell array formulas and no merged regions intersect another merged region in this sheet.protected voidvalidateRow(int row) Runs a bounds check for row numbersMethods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, waitMethods inherited from interface java.lang.Iterable
forEach, spliterator
-
Field Details
-
INITIAL_CAPACITY
public static final int INITIAL_CAPACITYUsed for compile-time optimization. This is the initial size for the collection of rows. It is currently set to 20. If you generate larger sheets you may benefit by setting this to a higher number and recompiling a custom edition of HSSFSheet. -
_book
-
_workbook
-
-
Constructor Details
-
HSSFSheet
Creates new HSSFSheet - called by HSSFWorkbook to create a sheet from scratch. You should not be calling this from application code (its protected anyhow).- Parameters:
workbook- - The HSSF Workbook object associated with the sheet.- See Also:
-
HSSFSheet
Creates an HSSFSheet representing the given Sheet object. Should only be called by HSSFWorkbook when reading in an exisiting file.- Parameters:
workbook- - The HSSF Workbook object associated with the sheet.sheet- - lowlevel Sheet object this sheet will represent- See Also:
-
-
Method Details
-
preSerialize
protected void preSerialize()check whether the data of sheet can be serialized -
getWorkbook
Return the parent workbook- Specified by:
getWorkbookin interfaceSheet- Returns:
- the parent workbook
-
createRow
Create a new row within the sheet and return the high level representation -
removeRow
Remove a row from this sheet. All cells contained in the row are removed as well -
getRow
Returns the logical row (not physical) 0-based. If you ask for a row that is not defined you get a null. This is to say row 4 represents the fifth row on a sheet. -
getPhysicalNumberOfRows
public int getPhysicalNumberOfRows()Returns the number of physically defined rows (NOT the number of rows in the sheet)- Specified by:
getPhysicalNumberOfRowsin interfaceSheet- Returns:
- the number of physically defined rows in this sheet
-
getFirstRowNum
public int getFirstRowNum()Gets the first row on the sheet- Specified by:
getFirstRowNumin interfaceSheet- Returns:
- the number of the first logical row on the sheet, zero based
-
getLastRowNum
public int getLastRowNum()Gets the number last row on the sheet. Owing to idiosyncrasies in the excel file format, if the result of calling this method is zero, you can't tell if that means there are zero rows on the sheet, or one at position zero. For that case, additionally callgetPhysicalNumberOfRows()to tell if there is a row at position zero or not.- Specified by:
getLastRowNumin interfaceSheet- Returns:
- the number of the last row contained in this sheet, zero based.
-
getDataValidations
Description copied from interface:SheetReturns the list of DataValidation in the sheet.- Specified by:
getDataValidationsin interfaceSheet- Returns:
- list of DataValidation in the sheet
-
addValidationData
Creates a data validation object- Specified by:
addValidationDatain interfaceSheet- Parameters:
dataValidation- The Data validation object settings
-
setColumnHidden
public void setColumnHidden(int columnIndex, boolean hidden) Get the visibility state for a given column.- Specified by:
setColumnHiddenin interfaceSheet- Parameters:
columnIndex- - the column to get (0-based)hidden- - the visiblity state of the column
-
isColumnHidden
public boolean isColumnHidden(int columnIndex) Get the hidden state for a given column.- Specified by:
isColumnHiddenin interfaceSheet- Parameters:
columnIndex- - the column to set (0-based)- Returns:
- hidden -
falseif the column is visible
-
setColumnWidth
public void setColumnWidth(int columnIndex, int width) Set the width (in units of 1/256th of a character width)The maximum column width for an individual cell is 255 characters. This value represents the number of characters that can be displayed in a cell that is formatted with the standard font (first font in the workbook).
Character width is defined as the maximum digit width of the numbers
0, 1, 2, ... 9as rendered using the default font (first font in the workbook).Unless you are using a very special font, the default character is '0' (zero), this is true for Arial (default font font in HSSF) and Calibri (default font in XSSF)
Please note, that the width set by this method includes 4 pixels of margin padding (two on each side), plus 1 pixel padding for the gridlines (Section 3.3.1.12 of the OOXML spec). This results is a slightly less value of visible characters than passed to this method (approx. 1/2 of a character).
To compute the actual number of visible characters, Excel uses the following formula (Section 3.3.1.12 of the OOXML spec):
width = Truncate([{Number of Visible Characters} * {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256Using the Calibri font as an example, the maximum digit width of 11 point font size is 7 pixels (at 96 dpi). If you set a column width to be eight characters wide, e.g.
setColumnWidth(columnIndex, 8*256), then the actual value of visible characters (the value shown in Excel) is derived from the following equation:Truncate([numChars*7+5]/7*256)/256 = 8;which gives
7.29.- Specified by:
setColumnWidthin interfaceSheet- Parameters:
columnIndex- - the column to set (0-based)width- - the width in units of 1/256th of a character width- Throws:
IllegalArgumentException- if width > 255*256 (the maximum column width in Excel is 255 characters)
-
getColumnWidth
public int getColumnWidth(int columnIndex) get the width (in units of 1/256th of a character width )- Specified by:
getColumnWidthin interfaceSheet- Parameters:
columnIndex- - the column to set (0-based)- Returns:
- width - the width in units of 1/256th of a character width
-
getColumnWidthInPixels
public float getColumnWidthInPixels(int column) Description copied from interface:Sheetget the width in pixelPlease note, that this method works correctly only for workbooks with the default font size (Arial 10pt for .xls and Calibri 11pt for .xlsx). If the default font is changed the column width can be stretched
- Specified by:
getColumnWidthInPixelsin interfaceSheet- Parameters:
column- - the column to set (0-based)- Returns:
- width in pixels
-
getDefaultColumnWidth
public int getDefaultColumnWidth()get the default column width for the sheet (if the columns do not define their own width) in characters- Specified by:
getDefaultColumnWidthin interfaceSheet- Returns:
- default column width
-
setDefaultColumnWidth
public void setDefaultColumnWidth(int width) set the default column width for the sheet (if the columns do not define their own width) in characters- Specified by:
setDefaultColumnWidthin interfaceSheet- Parameters:
width- default column width
-
getDefaultRowHeight
public short 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)- Specified by:
getDefaultRowHeightin interfaceSheet- Returns:
- default row height
-
getDefaultRowHeightInPoints
public float getDefaultRowHeightInPoints()get the default row height for the sheet (if the rows do not define their own height) in points.- Specified by:
getDefaultRowHeightInPointsin interfaceSheet- Returns:
- default row height in points
-
setDefaultRowHeight
public void setDefaultRowHeight(short 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)- Specified by:
setDefaultRowHeightin interfaceSheet- Parameters:
height- default row height
-
setDefaultRowHeightInPoints
public void setDefaultRowHeightInPoints(float height) set the default row height for the sheet (if the rows do not define their own height) in points- Specified by:
setDefaultRowHeightInPointsin interfaceSheet- Parameters:
height- default row height
-
getColumnStyle
Returns the HSSFCellStyle that applies to the given (0 based) column, or null if no style has been set for that column- Specified by:
getColumnStylein interfaceSheet
-
isGridsPrinted
public boolean isGridsPrinted()get whether gridlines are printed.- Returns:
- true if printed
-
setGridsPrinted
public void setGridsPrinted(boolean value) set whether gridlines printed.- Parameters:
value- false if not printed.
-
addMergedRegion
Adds a merged region of cells on a sheet.- Specified by:
addMergedRegionin interfaceSheet- Parameters:
region- to merge- Returns:
- index of this region
- Throws:
IllegalArgumentException- if region contains fewer than 2 cellsIllegalStateException- if region intersects with a multi-cell array formulaIllegalStateException- if region intersects with an existing region on this sheet
-
addMergedRegionUnsafe
Adds a merged region of cells (hence those cells form one). Skips validation. It is possible to create overlapping merged regions or create a merged region that intersects a multi-cell array formula with this formula, which may result in a corrupt workbook. To check for merged regions overlapping array formulas or other merged regions after addMergedRegionUnsafe has been called, callvalidateMergedRegions(), which runs in O(n^2) time.- Specified by:
addMergedRegionUnsafein interfaceSheet- Parameters:
region- to merge- Returns:
- index of this region
- Throws:
IllegalArgumentException- if region contains fewer than 2 cells
-
validateMergedRegions
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.- Specified by:
validateMergedRegionsin interfaceSheet- Throws:
IllegalStateException- if region intersects with a multi-cell array formulaIllegalStateException- if at least one region intersects with another merged region in this sheet
-
setForceFormulaRecalculation
public void setForceFormulaRecalculation(boolean value) Control if Excel should be asked to recalculate all formulas on this sheet when the workbook is opened.Calculating the formula values with
FormulaEvaluatoris the recommended solution, but this may be used for certain cases where evaluation in POI is not possible.It is recommended to force recalcuation of formulas on workbook level using
Workbook.setForceFormulaRecalculation(boolean)to ensure that all cross-worksheet formuals and external dependencies are updated.- Specified by:
setForceFormulaRecalculationin interfaceSheet- Parameters:
value- true if the application will perform a full recalculation of this worksheet values when the workbook is opened- See Also:
-
getForceFormulaRecalculation
public boolean getForceFormulaRecalculation()Whether a record must be inserted or not at generation to indicate that formula must be recalculated when workbook is opened.- Specified by:
getForceFormulaRecalculationin interfaceSheet- Returns:
- true if an uncalced record must be inserted or not at generation
-
setVerticallyCenter
public void setVerticallyCenter(boolean value) determines whether the output is vertically centered on the page.- Specified by:
setVerticallyCenterin interfaceSheet- Parameters:
value- true to vertically center, false otherwise.
-
getVerticallyCenter
public boolean getVerticallyCenter()Determine whether printed output for this sheet will be vertically centered.- Specified by:
getVerticallyCenterin interfaceSheet
-
setHorizontallyCenter
public void setHorizontallyCenter(boolean value) determines whether the output is horizontally centered on the page.- Specified by:
setHorizontallyCenterin interfaceSheet- Parameters:
value- true to horizontally center, false otherwise.
-
getHorizontallyCenter
public boolean getHorizontallyCenter()Determine whether printed output for this sheet will be horizontally centered.- Specified by:
getHorizontallyCenterin interfaceSheet
-
setRightToLeft
public void setRightToLeft(boolean value) Sets whether the worksheet is displayed from right to left instead of from left to right.- Specified by:
setRightToLeftin interfaceSheet- Parameters:
value- true for right to left, false otherwise.
-
isRightToLeft
public boolean isRightToLeft()Whether the text is displayed in right-to-left mode in the window- Specified by:
isRightToLeftin interfaceSheet- Returns:
- whether the text is displayed in right-to-left mode in the window
-
removeMergedRegion
public void removeMergedRegion(int index) removes a merged region of cells (hence letting them free)- Specified by:
removeMergedRegionin interfaceSheet- Parameters:
index- of the region to unmerge
-
removeMergedRegions
Removes a number of merged regions of cells (hence letting them free)- Specified by:
removeMergedRegionsin interfaceSheet- Parameters:
indices- A set of the regions to unmerge
-
getNumMergedRegions
public int getNumMergedRegions()returns the number of merged regions- Specified by:
getNumMergedRegionsin interfaceSheet- Returns:
- number of merged regions
-
getMergedRegion
Description copied from interface:SheetReturns the merged region at the specified index- Specified by:
getMergedRegionin interfaceSheet- Returns:
- the merged region at the specified index
-
getMergedRegions
Description copied from interface:SheetReturns the list of merged regions.- Specified by:
getMergedRegionsin interfaceSheet- Returns:
- the list of merged regions
-
rowIterator
Description copied from interface:SheetReturns an iterator of the physical rows- Specified by:
rowIteratorin interfaceSheet- 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. Call getRowNum() on each row if you care which one it is.
-
iterator
Alias forrowIterator()to allow foreach loops -
setAlternativeExpression
public void setAlternativeExpression(boolean b) whether alternate expression evaluation is on- Parameters:
b- alternative expression evaluation or not
-
setAlternativeFormula
public void setAlternativeFormula(boolean b) whether alternative formula entry is on- Parameters:
b- alternative formulas or not
-
setAutobreaks
public void setAutobreaks(boolean b) show automatic page breaks or not- Specified by:
setAutobreaksin interfaceSheet- Parameters:
b- whether to show auto page breaks
-
setDialog
public void setDialog(boolean b) set whether sheet is a dialog sheet or not- Parameters:
b- isDialog or not
-
setDisplayGuts
public void setDisplayGuts(boolean b) set whether to display the guts or not- Specified by:
setDisplayGutsin interfaceSheet- Parameters:
b- guts or no guts (or glory)
-
setFitToPage
public void setFitToPage(boolean b) fit to page option is on- Specified by:
setFitToPagein interfaceSheet- Parameters:
b- fit or not
-
setRowSumsBelow
public void setRowSumsBelow(boolean b) set if row summaries appear below detail in the outline- Specified by:
setRowSumsBelowin interfaceSheet- Parameters:
b- below or not
-
setRowSumsRight
public void setRowSumsRight(boolean b) set if col summaries appear right of the detail in the outline- Specified by:
setRowSumsRightin interfaceSheet- Parameters:
b- right or not
-
getAlternateExpression
public boolean getAlternateExpression()whether alternate expression evaluation is on- Returns:
- alternative expression evaluation or not
-
getAlternateFormula
public boolean getAlternateFormula()whether alternative formula entry is on- Returns:
- alternative formulas or not
-
getAutobreaks
public boolean getAutobreaks()show automatic page breaks or not- Specified by:
getAutobreaksin interfaceSheet- Returns:
- whether to show auto page breaks
-
getDialog
public boolean getDialog()get whether sheet is a dialog sheet or not- Returns:
- isDialog or not
-
getDisplayGuts
public boolean getDisplayGuts()get whether to display the guts or not- Specified by:
getDisplayGutsin interfaceSheet- Returns:
- guts or no guts (or glory)
-
isDisplayZeros
public boolean isDisplayZeros()Gets the flag indicating whether the window should show 0 (zero) in cells containing zero value. When false, cells with zero value appear blank instead of showing the number zero.In Excel 2003 this option can be changed in the Options dialog on the View tab.
- Specified by:
isDisplayZerosin interfaceSheet- Returns:
- whether all zero values on the worksheet are displayed
-
setDisplayZeros
public void setDisplayZeros(boolean value) Set whether the window should show 0 (zero) in cells containing zero value. When false, cells with zero value appear blank instead of showing the number zero.In Excel 2003 this option can be set in the Options dialog on the View tab.
- Specified by:
setDisplayZerosin interfaceSheet- Parameters:
value- whether to display or hide all zero values on the worksheet
-
getFitToPage
public boolean getFitToPage()fit to page option is on- Specified by:
getFitToPagein interfaceSheet- Returns:
- fit or not
-
getRowSumsBelow
public boolean getRowSumsBelow()get if row summaries appear below detail in the outline- Specified by:
getRowSumsBelowin interfaceSheet- Returns:
- below or not
-
getRowSumsRight
public boolean getRowSumsRight()get if col summaries appear right of the detail in the outline- Specified by:
getRowSumsRightin interfaceSheet- Returns:
- right or not
-
isPrintGridlines
public boolean isPrintGridlines()Returns whether gridlines are printed.- Specified by:
isPrintGridlinesin interfaceSheet- Returns:
- Gridlines are printed
- See Also:
-
setPrintGridlines
public void setPrintGridlines(boolean show) Turns on or off the printing of gridlines.- Specified by:
setPrintGridlinesin interfaceSheet- Parameters:
show- boolean to turn on or off the printing of gridlines- See Also:
-
isPrintRowAndColumnHeadings
public boolean isPrintRowAndColumnHeadings()Returns whether row and column headings are printed.- Specified by:
isPrintRowAndColumnHeadingsin interfaceSheet- Returns:
- row and column headings are printed
-
setPrintRowAndColumnHeadings
public void setPrintRowAndColumnHeadings(boolean show) Turns on or off the printing of row and column headings.- Specified by:
setPrintRowAndColumnHeadingsin interfaceSheet- Parameters:
show- boolean to turn on or off the printing of row and column headings
-
getPrintSetup
Gets the print setup object.- Specified by:
getPrintSetupin interfaceSheet- Returns:
- The user model for the print setup object.
-
getHeader
Description copied from interface:SheetGets the user model for the default document header.Note that XSSF offers more kinds of document headers than HSSF does
-
isSelected
public boolean isSelected()Note - this is not the same as whether the sheet is focused (isActive)- Specified by:
isSelectedin interfaceSheet- Returns:
trueif this sheet is currently selected
-
setSelected
public void setSelected(boolean sel) Sets whether sheet is selected.- Specified by:
setSelectedin interfaceSheet- Parameters:
sel- Whether to select the sheet or deselect the sheet.- See Also:
-
isActive
public boolean isActive()- Returns:
trueif this sheet is currently focused
-
setActive
public void setActive(boolean sel) Sets whether sheet is selected.- Parameters:
sel- Whether to select the sheet or deselect the sheet.
-
getMargin
public double getMargin(short margin) Gets the size of the margin in inches. -
setMargin
public void setMargin(short margin, double size) Sets the size of the margin in inches. -
getProtect
public boolean getProtect()Answer whether protection is enabled or disabled- Specified by:
getProtectin interfaceSheet- Returns:
- true => protection enabled; false => protection disabled
-
getPassword
public short getPassword()- Returns:
- hashed password
-
getObjectProtect
public boolean getObjectProtect()Answer whether object protection is enabled or disabled- Returns:
- true => protection enabled; false => protection disabled
-
getScenarioProtect
public boolean getScenarioProtect()Answer whether scenario protection is enabled or disabled- Specified by:
getScenarioProtectin interfaceSheet- Returns:
- true => protection enabled; false => protection disabled
-
protectSheet
Sets the protection enabled as well as the password- Specified by:
protectSheetin interfaceSheet- Parameters:
password- to set for protection. Passnullto remove protection
-
setZoom
public void setZoom(int numerator, int denominator) Sets the zoom magnification for the sheet. The zoom is expressed as a fraction. For example to express a zoom of 75% use 3 for the numerator and 4 for the denominator.- Parameters:
numerator- The numerator for the zoom magnification.denominator- The denominator for the zoom magnification.- See Also:
-
setZoom
public void setZoom(int scale) Window zoom magnification for current view representing percent values. Valid values range from 10 to 400. Horizontal & Vertical scale together. For example:10 - 10% 20 - 20% ... 100 - 100% ... 400 - 400%
- Specified by:
setZoomin interfaceSheet- Parameters:
scale- window zoom magnification- Throws:
IllegalArgumentException- if scale is invalid
-
getTopRow
public short getTopRow()The top row in the visible view when the sheet is first viewed after opening it in a viewer -
getLeftCol
public short getLeftCol()The left col in the visible view when the sheet is first viewed after opening it in a viewer- Specified by:
getLeftColin interfaceSheet- Returns:
- short indicating the rownum (0 based) of the top row
-
showInPane
public void showInPane(int toprow, int leftcol) Sets desktop window pane display area, when the file is first opened in a viewer.- Specified by:
showInPanein interfaceSheet- Parameters:
toprow- the top row to show in desktop window paneleftcol- the left column to show in desktop window pane
-
shiftMerged
protected void shiftMerged(int startRow, int endRow, int n, boolean isRow) Deprecated.POI 3.15 beta 2. UseRowShifter.shiftMergedRegions(int, int, int).Shifts, grows, or shrinks the merged regions due to a row shift- Parameters:
startRow- the start-index of the rows to shift, zero-basedendRow- the end-index of the rows to shift, zero-basedn- how far to shift, negative to shift upisRow- unused, kept for backwards compatibility
-
shiftRows
public void shiftRows(int startRow, int endRow, int n) Shifts rows between startRow and endRow n number of rows. If you use a negative number, it will shift rows up. Code ensures that rows don't wrap around.Calls
shiftRows(startRow, endRow, n, false, false);Additionally shifts merged regions that are completely defined in these rows (ie. merged 2 cells on a row to be shifted).
-
shiftRows
public void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight) Shifts rows between startRow and endRow n number of rows. If you use a negative number, it will shift rows up. Code ensures that rows don't wrap aroundAdditionally shifts merged regions that are completely defined in these rows (ie. merged 2 cells on a row to be shifted). All merged regions that are completely overlaid by shifting will be deleted.
TODO Might want to add bounds checking here
- Specified by:
shiftRowsin interfaceSheet- Parameters:
startRow- the row to start shiftingendRow- the row to end shiftingn- the number of rows to shiftcopyRowHeight- whether to copy the row height during the shiftresetOriginalRowHeight- whether to set the original row's height to the default
-
shiftRows
public void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight, boolean moveComments) Shifts rows between startRow and endRow n number of rows. If you use a negative number, it will shift rows up. Code ensures that rows don't wrap aroundAdditionally shifts merged regions that are completely defined in these rows (ie. merged 2 cells on a row to be shifted).
TODO Might want to add bounds checking here
- Parameters:
startRow- the row to start shiftingendRow- the row to end shiftingn- the number of rows to shiftcopyRowHeight- whether to copy the row height during the shiftresetOriginalRowHeight- whether to set the original row's height to the defaultmoveComments- whether to move comments at the same time as the cells they are attached to
-
shiftColumns
Shifts columns in range [startColumn, endColumn] for n places to the right. For n < 0, it will shift columns left. Additionally adjusts formulas. Probably should also process other features (hyperlinks, comments...) in the way analog to shiftRows method- Specified by:
shiftColumnsin interfaceSheet- Parameters:
startRow- the row to start shiftingendRow- the row to end shiftingn- the number of rows to shift
-
insertChartRecords
-
createFreezePane
public void createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow) Creates a split (freezepane). Any existing freezepane or split pane is overwritten.If both colSplit and rowSplit are zero then the existing freeze pane is removed
- Specified by:
createFreezePanein interfaceSheet- Parameters:
colSplit- Horizonatal position of split.rowSplit- Vertical position of split.leftmostColumn- Left column visible in right pane.topRow- Top row visible in bottom pane
-
createFreezePane
public void createFreezePane(int colSplit, int rowSplit) Creates a split (freezepane). Any existing freezepane or split pane is overwritten.If both colSplit and rowSplit are zero then the existing freeze pane is removed
- Specified by:
createFreezePanein interfaceSheet- Parameters:
colSplit- Horizonatal position of split.rowSplit- Vertical position of split.
-
createSplitPane
public void createSplitPane(int xSplitPos, int ySplitPos, int leftmostColumn, int topRow, int activePane) Creates a split pane. Any existing freezepane or split pane is overwritten.- Specified by:
createSplitPanein interfaceSheet- Parameters:
xSplitPos- Horizonatal position of split (in 1/20th of a point).ySplitPos- Vertical position of split (in 1/20th of a point).topRow- Top row visible in bottom paneleftmostColumn- Left column visible in right pane.activePane- Active pane. One of: PANE_LOWER_RIGHT, PANE_UPPER_RIGHT, PANE_LOWER_LEFT, PANE_UPPER_LEFT- See Also:
-
getPaneInformation
Returns the information regarding the currently configured pane (split or freeze).- Specified by:
getPaneInformationin interfaceSheet- Returns:
- null if no pane configured, or the pane information.
-
setDisplayGridlines
public void setDisplayGridlines(boolean show) Sets whether the gridlines are shown in a viewer.- Specified by:
setDisplayGridlinesin interfaceSheet- Parameters:
show- whether to show gridlines or not
-
isDisplayGridlines
public boolean isDisplayGridlines()Returns if gridlines are displayed.- Specified by:
isDisplayGridlinesin interfaceSheet- Returns:
- whether gridlines are displayed
-
setDisplayFormulas
public void setDisplayFormulas(boolean show) Sets whether the formulas are shown in a viewer.- Specified by:
setDisplayFormulasin interfaceSheet- Parameters:
show- whether to show formulas or not
-
isDisplayFormulas
public boolean isDisplayFormulas()Returns if formulas are displayed.- Specified by:
isDisplayFormulasin interfaceSheet- Returns:
- whether formulas are displayed
-
setDisplayRowColHeadings
public void setDisplayRowColHeadings(boolean show) Sets whether the RowColHeadings are shown in a viewer.- Specified by:
setDisplayRowColHeadingsin interfaceSheet- Parameters:
show- whether to show RowColHeadings or not
-
isDisplayRowColHeadings
public boolean isDisplayRowColHeadings()Returns if RowColHeadings are displayed.- Specified by:
isDisplayRowColHeadingsin interfaceSheet- Returns:
- whether RowColHeadings are displayed
-
setRowBreak
public void setRowBreak(int row) Sets a page break at the indicated row Breaks occur above the specified row and left of the specified column inclusive.For example,
sheet.setColumnBreak(2);breaks the sheet into two parts with columns A,B,C in the first and D,E,... in the second. Simuilar,sheet.setRowBreak(2);breaks the sheet into two parts with first three rows (rownum=1...3) in the first part and rows starting with rownum=4 in the second.- Specified by:
setRowBreakin interfaceSheet- Parameters:
row- the row to break, inclusive
-
isRowBroken
public boolean isRowBroken(int row) Description copied from interface:SheetDetermines if there is a page break at the indicated row- Specified by:
isRowBrokenin interfaceSheet- Parameters:
row- FIXME: Document this!- Returns:
trueif there is a page break at the indicated row
-
removeRowBreak
public void removeRowBreak(int row) Removes the page break at the indicated row- Specified by:
removeRowBreakin interfaceSheet- Parameters:
row- The 0-based index of the row.
-
getRowBreaks
public int[] getRowBreaks()Description copied from interface:SheetRetrieves all the horizontal page breaks- Specified by:
getRowBreaksin interfaceSheet- Returns:
- row indexes of all the horizontal page breaks, never
null
-
getColumnBreaks
public int[] getColumnBreaks()Description copied from interface:SheetRetrieves all the vertical page breaks- Specified by:
getColumnBreaksin interfaceSheet- Returns:
- column indexes of all the vertical page breaks, never
null
-
setColumnBreak
public void setColumnBreak(int column) Sets a page break at the indicated column. Breaks occur above the specified row and left of the specified column inclusive.For example,
sheet.setColumnBreak(2);breaks the sheet into two parts with columns A,B,C in the first and D,E,... in the second. Simuilar,sheet.setRowBreak(2);breaks the sheet into two parts with first three rows (rownum=1...3) in the first part and rows starting with rownum=4 in the second.- Specified by:
setColumnBreakin interfaceSheet- Parameters:
column- the column to break, inclusive
-
isColumnBroken
public boolean isColumnBroken(int column) Determines if there is a page break at the indicated column- Specified by:
isColumnBrokenin interfaceSheet- Parameters:
column- FIXME: Document this!- Returns:
- FIXME: Document this!
-
removeColumnBreak
public void removeColumnBreak(int column) Removes a page break at the indicated column- Specified by:
removeColumnBreakin interfaceSheet- Parameters:
column- The index of the column for which to remove a page-break, zero-based
-
validateRow
protected void validateRow(int row) Runs a bounds check for row numbers- Parameters:
row- the index of the row to validate, zero-based
-
validateColumn
protected void validateColumn(int column) Runs a bounds check for column numbers- Parameters:
column- the index of the column to validate, zero-based
-
dumpDrawingRecords
Aggregates the drawing records and dumps the escher record hierarchy to the standard output. -
getDrawingEscherAggregate
Returns the agregate escher records for this sheet, it there is one. -
getDrawingPatriarch
This will hold any graphics or charts for the sheet.- Specified by:
getDrawingPatriarchin interfaceSheet- Returns:
- the top-level drawing patriarch, if there is one, else returns null
-
createDrawingPatriarch
Creates the top-level drawing patriarch.This may then be used to add graphics or charts.
Note that this will normally have the effect of removing any existing drawings on this sheet.
- Specified by:
createDrawingPatriarchin interfaceSheet- Returns:
- The new patriarch.
-
setColumnGroupCollapsed
public void setColumnGroupCollapsed(int columnNumber, boolean collapsed) Expands or collapses a column group.- Specified by:
setColumnGroupCollapsedin interfaceSheet- Parameters:
columnNumber- One of the columns in the group.collapsed- true = collapse group, false = expand group.
-
groupColumn
public void groupColumn(int fromColumn, int toColumn) Create an outline for the provided column range.- Specified by:
groupColumnin interfaceSheet- Parameters:
fromColumn- beginning of the column range.toColumn- end of the column range.
-
ungroupColumn
public void ungroupColumn(int fromColumn, int toColumn) Description copied from interface:SheetUngroup a range of columns that were previously grouped- Specified by:
ungroupColumnin interfaceSheet- Parameters:
fromColumn- start column (0-based)toColumn- end column (0-based)
-
groupRow
public void groupRow(int fromRow, int toRow) Tie a range of cell together so that they can be collapsed or expanded -
ungroupRow
public void ungroupRow(int fromRow, int toRow) Description copied from interface:SheetUngroup a range of rows that were previously grouped- Specified by:
ungroupRowin interfaceSheet- Parameters:
fromRow- start row (0-based)toRow- end row (0-based)
-
setRowGroupCollapsed
public void setRowGroupCollapsed(int rowIndex, boolean collapse) Description copied from interface:SheetSet view state of a grouped range of rows- Specified by:
setRowGroupCollapsedin interfaceSheet- Parameters:
rowIndex- start row of a grouped range of rows (0-based)collapse- whether to expand/collapse the detail rows
-
setDefaultColumnStyle
Sets the default column style for a given column. POI will only apply this style to new cells added to the sheet.- Specified by:
setDefaultColumnStylein interfaceSheet- Parameters:
column- the column indexstyle- the style to set
-
autoSizeColumn
public void autoSizeColumn(int column) Adjusts the column width to fit the contents.This process can be relatively slow on large sheets, so this should normally only be called once per column, at the end of your processing.
- Specified by:
autoSizeColumnin interfaceSheet- Parameters:
column- the column index
-
autoSizeColumn
public void autoSizeColumn(int column, boolean useMergedCells) Adjusts the column width to fit the contents.This process can be relatively slow on large sheets, so this should normally only be called once per column, at the end of your processing.
You can specify whether the content of merged cells should be considered or ignored. Default is to ignore merged cells.
- Specified by:
autoSizeColumnin interfaceSheet- Parameters:
column- the column indexuseMergedCells- whether to use the contents of merged cells when calculating the width of the column
-
getCellComment
Returns cell comment for the specified row and column- Specified by:
getCellCommentin interfaceSheet- Returns:
- cell comment or
nullif not found
-
getHyperlink
Get a Hyperlink in this sheet anchored at row, column- Specified by:
getHyperlinkin interfaceSheet- Parameters:
row- The index of the row of the hyperlink, zero-basedcolumn- the index of the column of the hyperlink, zero-based- Returns:
- hyperlink if there is a hyperlink anchored at row, column; otherwise returns null
-
getHyperlink
Get a Hyperlink in this sheet located in a cell specified by {code addr}- Specified by:
getHyperlinkin interfaceSheet- Parameters:
addr- The address of the cell containing the hyperlink- Returns:
- hyperlink if there is a hyperlink anchored at
addr; otherwise returnsnull - Since:
- POI 3.15 beta 3
-
getHyperlinkList
Get a list of Hyperlinks in this sheet- Specified by:
getHyperlinkListin interfaceSheet- Returns:
- Hyperlinks for the sheet
-
removeHyperlink
Remove the underlying HyperlinkRecord from this sheet. If multiple HSSFHyperlinks refer to the same HyperlinkRecord, all HSSFHyperlinks will be removed.- Parameters:
link- the HSSFHyperlink wrapper around the HyperlinkRecord to remove
-
removeHyperlink
Remove the underlying HyperlinkRecord from this sheet- Parameters:
link- the underlying HyperlinkRecord to remove from this sheet
-
getSheetConditionalFormatting
Description copied from interface:SheetThe 'Conditional Formatting' facet for this Sheet- Specified by:
getSheetConditionalFormattingin interfaceSheet- Returns:
- conditional formatting rule for this sheet
-
getSheetName
Returns the name of this sheet- Specified by:
getSheetNamein interfaceSheet- Returns:
- the name of this sheet
-
setArrayFormula
Description copied from interface:SheetSets array formula to specified region for result.Note if there are shared formulas this will invalidate any
FormulaEvaluatorinstances based on this workbook- Specified by:
setArrayFormulain interfaceSheet- Parameters:
formula- text representation of the formularange- Region of array formula for result.- Returns:
- the
CellRangeof cells affected by this change
-
removeArrayFormula
Description copied from interface:SheetRemove a Array Formula from this sheet. All cells contained in the Array Formula range are removed as well- Specified by:
removeArrayFormulain interfaceSheet- Parameters:
cell- any cell within Array Formula range- Returns:
- the
CellRangeof cells affected by this change
-
getDataValidationHelper
- Specified by:
getDataValidationHelperin interfaceSheet
-
setAutoFilter
Description copied from interface:SheetEnable filtering for a range of cells- Specified by:
setAutoFilterin interfaceSheet- Parameters:
range- the range of cells to filter
-
findCellComment
-
getCellComments
Returns all cell comments on this sheet.- Specified by:
getCellCommentsin interfaceSheet- Returns:
- A map of each Comment in the sheet, keyed on the cell address where the comment is located.
-
getRepeatingRows
Description copied from interface:SheetGets the repeating rows used when printing the sheet, as found in File->PageSetup->Sheet.Repeating rows cover a range of contiguous rows, e.g.:
Sheet1!$1:$1 Sheet2!$5:$8
TheCellRangeAddressreturned contains a column part which spans all columns, and a row part which specifies the contiguous range of repeating rows.If the Sheet does not have any repeating rows defined, null is returned.
- Specified by:
getRepeatingRowsin interfaceSheet- Returns:
- an
CellRangeAddresscontaining the repeating rows for the Sheet, or null.
-
getRepeatingColumns
Description copied from interface:SheetGets the repeating columns used when printing the sheet, as found in File->PageSetup->Sheet.Repeating columns cover a range of contiguous columns, e.g.:
Sheet1!$A:$A Sheet2!$C:$F
TheCellRangeAddressreturned contains a row part which spans all rows, and a column part which specifies the contiguous range of repeating columns.If the Sheet does not have any repeating columns defined, null is returned.
- Specified by:
getRepeatingColumnsin interfaceSheet- Returns:
- an
CellRangeAddresscontaining the repeating columns for the Sheet, or null.
-
setRepeatingRows
Description copied from interface:SheetSets the repeating rows used when printing the sheet, as found in File->PageSetup->Sheet.Repeating rows cover a range of contiguous rows, e.g.:
Sheet1!$1:$1 Sheet2!$5:$8
The parameterCellRangeAddressshould specify a column part which spans all columns, and a row part which specifies the contiguous range of repeating rows, e.g.:sheet.setRepeatingRows(CellRangeAddress.valueOf("2:3"));A null parameter value indicates that repeating rows should be removed from the Sheet:sheet.setRepeatingRows(null);
- Specified by:
setRepeatingRowsin interfaceSheet- Parameters:
rowRangeRef- aCellRangeAddresscontaining the repeating rows for the Sheet, or null.
-
setRepeatingColumns
Description copied from interface:SheetSets the repeating columns used when printing the sheet, as found in File->PageSetup->Sheet.Repeating columns cover a range of contiguous columns, e.g.:
Sheet1!$A:$A Sheet2!$C:$F
The parameterCellRangeAddressshould specify a row part which spans all rows, and a column part which specifies the contiguous range of repeating columns, e.g.:sheet.setRepeatingColumns(CellRangeAddress.valueOf("B:C"));A null parameter value indicates that repeating columns should be removed from the Sheet:sheet.setRepeatingColumns(null);
- Specified by:
setRepeatingColumnsin interfaceSheet- Parameters:
columnRangeRef- aCellRangeAddresscontaining the repeating columns for the Sheet, or null.
-
getColumnOutlineLevel
public int getColumnOutlineLevel(int columnIndex) Returns the column outline level. Increased as you put it into more groups (outlines), reduced as you take it out of them.- Specified by:
getColumnOutlineLevelin interfaceSheet
-
getActiveCell
Return location of the active cell, e.g.A1.- Specified by:
getActiveCellin interfaceSheet- Returns:
- the location of the active cell.
-
setActiveCell
Sets location of the active cell- Specified by:
setActiveCellin interfaceSheet- Parameters:
address- the location of the active cell, e.g.A1.
-