org.apache.poi.hssf.usermodel
Class HSSFSheet

java.lang.Object
  extended by org.apache.poi.hssf.usermodel.HSSFSheet
All Implemented Interfaces:
java.lang.Iterable<Row>, Sheet

public final class HSSFSheet
extends java.lang.Object
implements Sheet

High level representation of a worksheet.

Author:
Andrew C. Oliver (acoliver at apache dot org), Glen Stampoultzis (glens at apache.org), Libin Roman (romal at vistaportal.com), Shawn Laubach (slaubach at apache dot org) (Just a little), Jean-Pierre Paris (jean-pierre.paris at m4x dot org) (Just a little, too), Yegor Kozlov (yegor at apache.org) (Autosizing columns), Josh Micich, Petr Udalau(Petr.Udalau at exigenservices.com) - set/remove array formulas

Field Summary
protected  InternalWorkbook _book
           
protected  HSSFWorkbook _workbook
           
static int INITIAL_CAPACITY
          Used 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
protected HSSFSheet(HSSFWorkbook workbook)
          Creates new HSSFSheet - called by HSSFWorkbook to create a sheet from scratch.
protected HSSFSheet(HSSFWorkbook workbook, InternalSheet sheet)
          Creates an HSSFSheet representing the given Sheet object.
 
Method Summary
 int addMergedRegion(CellRangeAddress region)
          adds a merged region of cells (hence those cells form one)
 int addMergedRegion(Region region)
          Deprecated. (Aug-2008) use CellRangeAddress instead of Region
 void addValidationData(DataValidation dataValidation)
          Creates a data validation object
 void autoSizeColumn(int column)
          Adjusts the column width to fit the contents.
 void autoSizeColumn(int column, boolean useMergedCells)
          Adjusts the column width to fit the contents.
 HSSFPatriarch createDrawingPatriarch()
          Creates the top-level drawing patriarch.
 void createFreezePane(int colSplit, int rowSplit)
          Creates a split (freezepane).
 void createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow)
          Creates a split (freezepane).
 HSSFRow createRow(int rownum)
          Create a new row within the sheet and return the high level representation
 void createSplitPane(int xSplitPos, int ySplitPos, int leftmostColumn, int topRow, int activePane)
          Creates a split pane.
 void dumpDrawingRecords(boolean fat)
          Aggregates the drawing records and dumps the escher record hierarchy to the standard output.
 boolean getAlternateExpression()
          whether alternate expression evaluation is on
 boolean getAlternateFormula()
          whether alternative formula entry is on
 boolean getAutobreaks()
          show automatic page breaks or not
 HSSFComment getCellComment(int row, int column)
          Returns cell comment for the specified row and column
 int[] getColumnBreaks()
          Retrieves all the vertical page breaks
 HSSFCellStyle getColumnStyle(int column)
          Returns the HSSFCellStyle that applies to the given (0 based) column, or null if no style has been set for that column
 int getColumnWidth(int columnIndex)
          get the width (in units of 1/256th of a character width )
 short getColumnWidth(short columnIndex)
          Deprecated. (Sep 2008) use getColumnWidth(int)
 DataValidationHelper getDataValidationHelper()
           
 int getDefaultColumnWidth()
          get the default column width for the sheet (if the columns do not define their own width) in characters
 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)
 float getDefaultRowHeightInPoints()
          get the default row height for the sheet (if the rows do not define their own height) in points.
 boolean getDialog()
          get whether sheet is a dialog sheet or not
 boolean getDisplayGuts()
          get whether to display the guts or not
 EscherAggregate getDrawingEscherAggregate()
          Returns the agregate escher records for this sheet, it there is one.
 HSSFPatriarch getDrawingPatriarch()
          Returns the top-level drawing patriach, if there is one.
 int getFirstRowNum()
          Gets the first row on the sheet
 boolean getFitToPage()
          fit to page option is on
 HSSFFooter getFooter()
          Gets the user model for the default document footer.
 boolean getForceFormulaRecalculation()
          Whether a record must be inserted or not at generation to indicate that formula must be recalculated when workbook is opened.
 HSSFHeader getHeader()
          Gets the user model for the default document header.
 boolean getHorizontallyCenter()
          Determine whether printed output for this sheet will be horizontally centered.
 int getLastRowNum()
          Gets the number last row on the sheet.
 short getLeftCol()
          The left col in the visible view when the sheet is first viewed after opening it in a viewer
 double getMargin(short margin)
          Gets the size of the margin in inches.
 CellRangeAddress getMergedRegion(int index)
          Returns the merged region at the specified index
 Region getMergedRegionAt(int index)
          Deprecated. (Aug-2008) use getMergedRegion(int)
 int getNumMergedRegions()
          returns the number of merged regions
 boolean getObjectProtect()
          Answer whether object protection is enabled or disabled
 PaneInformation getPaneInformation()
          Returns the information regarding the currently configured pane (split or freeze).
 short getPassword()
           
 int getPhysicalNumberOfRows()
          Returns the number of physically defined rows (NOT the number of rows in the sheet)
 HSSFPrintSetup getPrintSetup()
          Gets the print setup object.
 boolean getProtect()
          Answer whether protection is enabled or disabled
 HSSFRow getRow(int rowIndex)
          Returns the logical row (not physical) 0-based.
 int[] getRowBreaks()
          Retrieves all the horizontal page breaks
 boolean getRowSumsBelow()
          get if row summaries appear below detail in the outline
 boolean getRowSumsRight()
          get if col summaries appear right of the detail in the outline
 boolean getScenarioProtect()
          Answer whether scenario protection is enabled or disabled
 HSSFSheetConditionalFormatting getSheetConditionalFormatting()
          The 'Conditional Formatting' facet for this Sheet
 java.lang.String getSheetName()
          Returns the name of this sheet
 short getTopRow()
          The top row in the visible view when the sheet is first viewed after opening it in a viewer
 boolean getVerticallyCenter()
          Determine whether printed output for this sheet will be vertically centered.
 boolean getVerticallyCenter(boolean value)
          Deprecated. (Mar-2008) use getVerticallyCenter() instead
 HSSFWorkbook getWorkbook()
          Return the parent workbook
 void groupColumn(int fromColumn, int toColumn)
          Create an outline for the provided column range.
 void groupColumn(short fromColumn, short toColumn)
          Deprecated. (Sep 2008) use groupColumn(int, int)
 void groupRow(int fromRow, int toRow)
          Tie a range of cell together so that they can be collapsed or expanded
protected  void insertChartRecords(java.util.List<Record> records)
           
 boolean isActive()
           
 boolean isColumnBroken(int column)
          Determines if there is a page break at the indicated column
 boolean isColumnHidden(int columnIndex)
          Get the hidden state for a given column.
 boolean isColumnHidden(short columnIndex)
          Deprecated. (Sep 2008) use isColumnHidden(int)
 boolean isDisplayFormulas()
          Returns if formulas are displayed.
 boolean isDisplayGridlines()
          Returns if gridlines are displayed.
 boolean isDisplayRowColHeadings()
          Returns if RowColHeadings are displayed.
 boolean isDisplayZeros()
          Gets the flag indicating whether the window should show 0 (zero) in cells containing zero value.
 boolean isGridsPrinted()
          get whether gridlines are printed.
 boolean isPrintGridlines()
          Returns whether gridlines are printed.
 boolean isRightToLeft()
          Whether the text is displayed in right-to-left mode in the window
 boolean isRowBroken(int row)
          Determines if there is a page break at the indicated row
 boolean isSelected()
          Note - this is not the same as whether the sheet is focused (isActive)
 java.util.Iterator<Row> iterator()
          Alias for rowIterator() to allow foreach loops
 void protectSheet(java.lang.String password)
          Sets the protection enabled as well as the password
 CellRange<HSSFCell> removeArrayFormula(Cell cell)
          Remove a Array Formula from this sheet.
 void removeColumnBreak(int column)
          Removes a page break at the indicated column
 void removeMergedRegion(int index)
          removes a merged region of cells (hence letting them free)
 void removeRow(Row row)
          Remove a row from this sheet.
 void removeRowBreak(int row)
          Removes the page break at the indicated row
 java.util.Iterator<Row> rowIterator()
          Returns an iterator of the physical rows
 void setActive(boolean sel)
          Sets whether sheet is selected.
 void setAlternativeExpression(boolean b)
          whether alternate expression evaluation is on
 void setAlternativeFormula(boolean b)
          whether alternative formula entry is on
 CellRange<HSSFCell> setArrayFormula(java.lang.String formula, CellRangeAddress range)
          Sets array formula to specified region for result.
 void setAutobreaks(boolean b)
          show automatic page breaks or not
 HSSFAutoFilter setAutoFilter(CellRangeAddress range)
          Enable filtering for a range of cells
 void setColumnBreak(int column)
          Sets a page break at the indicated column.
 void setColumnGroupCollapsed(int columnNumber, boolean collapsed)
          Expands or collapses a column group.
 void setColumnGroupCollapsed(short columnNumber, boolean collapsed)
          Deprecated. (Sep 2008) use setColumnGroupCollapsed(int, boolean)
 void setColumnHidden(int columnIndex, boolean hidden)
          Get the visibility state for a given column.
 void setColumnHidden(short columnIndex, boolean hidden)
          Deprecated. (Sep 2008) use setColumnHidden(int, boolean)
 void setColumnWidth(int columnIndex, int width)
          Set the width (in units of 1/256th of a character width)
 void setColumnWidth(short columnIndex, short width)
          Deprecated. (Sep 2008) use setColumnWidth(int, int)
 void setDefaultColumnStyle(int column, CellStyle style)
          Sets the default column style for a given column.
 void setDefaultColumnWidth(int width)
          set the default column width for the sheet (if the columns do not define their own width) in characters
 void setDefaultColumnWidth(short width)
          Deprecated. (Sep 2008) use setDefaultColumnWidth(int)
 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)
 void setDefaultRowHeightInPoints(float height)
          set the default row height for the sheet (if the rows do not define their own height) in points
 void setDialog(boolean b)
          set whether sheet is a dialog sheet or not
 void setDisplayFormulas(boolean show)
          Sets whether the formulas are shown in a viewer.
 void setDisplayGridlines(boolean show)
          Sets whether the gridlines are shown in a viewer.
 void setDisplayGuts(boolean b)
          set whether to display the guts or not
 void setDisplayRowColHeadings(boolean show)
          Sets whether the RowColHeadings are shown in a viewer.
 void setDisplayZeros(boolean value)
          Set whether the window should show 0 (zero) in cells containing zero value.
 void setFitToPage(boolean b)
          fit to page option is on
 void setForceFormulaRecalculation(boolean value)
          Control if Excel should be asked to recalculate all formulas on this sheet when the workbook is opened.
 void setGridsPrinted(boolean value)
          set whether gridlines printed.
 void setHorizontallyCenter(boolean value)
          determines whether the output is horizontally centered on the page.
 void setMargin(short margin, double size)
          Sets the size of the margin in inches.
 void setPrintGridlines(boolean newPrintGridlines)
          Turns on or off the printing of gridlines.
 void setRightToLeft(boolean value)
          Sets whether the worksheet is displayed from right to left instead of from left to right.
 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.
 void setRowGroupCollapsed(int rowIndex, boolean collapse)
          Set view state of a groupped range of rows
 void setRowSumsBelow(boolean b)
          set if row summaries appear below detail in the outline
 void setRowSumsRight(boolean b)
          set if col summaries appear right of the detail in the outline
 void setSelected(boolean sel)
          Sets whether sheet is selected.
 void setVerticallyCenter(boolean value)
          determines whether the output is vertically centered on the page.
 void setZoom(int numerator, int denominator)
          Sets the zoom magnification for the sheet.
protected  void shiftMerged(int startRow, int endRow, int n, boolean isRow)
          Shifts the merged regions left or right depending on mode
 void shiftRows(int startRow, int endRow, int n)
          Shifts rows between startRow and endRow n number of rows.
 void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight)
          Shifts rows between startRow and endRow n number of rows.
 void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight, boolean moveComments)
          Shifts rows between startRow and endRow n number of rows.
 void showInPane(short toprow, short leftcol)
          Sets desktop window pane display area, when the file is first opened in a viewer.
 void ungroupColumn(int fromColumn, int toColumn)
          Ungroup a range of columns that were previously groupped
 void ungroupColumn(short fromColumn, short toColumn)
          Deprecated. (Sep 2008) use ungroupColumn(int, int)
 void ungroupRow(int fromRow, int toRow)
          Ungroup a range of rows that were previously groupped
protected  void validateColumn(int column)
          Runs a bounds check for column numbers
protected  void validateRow(int row)
          Runs a bounds check for row numbers
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Field Detail

INITIAL_CAPACITY

public static final int INITIAL_CAPACITY
Used 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.

See Also:
Constant Field Values

_book

protected final InternalWorkbook _book

_workbook

protected final HSSFWorkbook _workbook
Constructor Detail

HSSFSheet

protected HSSFSheet(HSSFWorkbook workbook)
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:
HSSFWorkbook.createSheet()

HSSFSheet

protected HSSFSheet(HSSFWorkbook workbook,
                    InternalSheet sheet)
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:
HSSFWorkbook.createSheet()
Method Detail

getWorkbook

public HSSFWorkbook getWorkbook()
Return the parent workbook

Specified by:
getWorkbook in interface Sheet
Returns:
the parent workbook

createRow

public HSSFRow createRow(int rownum)
Create a new row within the sheet and return the high level representation

Specified by:
createRow in interface Sheet
Parameters:
rownum - row number
Returns:
High level HSSFRow object representing a row in the sheet
See Also:
HSSFRow, removeRow(org.apache.poi.ss.usermodel.Row)

removeRow

public void removeRow(Row row)
Remove a row from this sheet. All cells contained in the row are removed as well

Specified by:
removeRow in interface Sheet
Parameters:
row - representing a row to remove.

getRow

public HSSFRow getRow(int rowIndex)
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.

Specified by:
getRow in interface Sheet
Parameters:
rowIndex - row to get
Returns:
HSSFRow representing the row number or null if its not defined on the sheet

getPhysicalNumberOfRows

public int getPhysicalNumberOfRows()
Returns the number of physically defined rows (NOT the number of rows in the sheet)

Specified by:
getPhysicalNumberOfRows in interface Sheet
Returns:
the number of physically defined rows in this sheet

getFirstRowNum

public int getFirstRowNum()
Gets the first row on the sheet

Specified by:
getFirstRowNum in interface Sheet
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 call getPhysicalNumberOfRows() to tell if there is a row at position zero or not.

Specified by:
getLastRowNum in interface Sheet
Returns:
the number of the last row contained in this sheet, zero based.

addValidationData

public void addValidationData(DataValidation dataValidation)
Creates a data validation object

Specified by:
addValidationData in interface Sheet
Parameters:
dataValidation - The Data validation object settings

setColumnHidden

public void setColumnHidden(short columnIndex,
                            boolean hidden)
Deprecated. (Sep 2008) use setColumnHidden(int, boolean)


isColumnHidden

public boolean isColumnHidden(short columnIndex)
Deprecated. (Sep 2008) use isColumnHidden(int)


setColumnWidth

public void setColumnWidth(short columnIndex,
                           short width)
Deprecated. (Sep 2008) use setColumnWidth(int, int)


getColumnWidth

public short getColumnWidth(short columnIndex)
Deprecated. (Sep 2008) use getColumnWidth(int)


setDefaultColumnWidth

public void setDefaultColumnWidth(short width)
Deprecated. (Sep 2008) use setDefaultColumnWidth(int)


setColumnHidden

public void setColumnHidden(int columnIndex,
                            boolean hidden)
Get the visibility state for a given column.

Specified by:
setColumnHidden in interface Sheet
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:
isColumnHidden in interface Sheet
Parameters:
columnIndex - - the column to set (0-based)
Returns:
hidden - false if 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, ... 9 as 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)/256

Using 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:
setColumnWidth in interface Sheet
Parameters:
columnIndex - - the column to set (0-based)
width - - the width in units of 1/256th of a character width
Throws:
java.lang.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:
getColumnWidth in interface Sheet
Parameters:
columnIndex - - the column to set (0-based)
Returns:
width - the width in units of 1/256th of a character width

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:
getDefaultColumnWidth in interface Sheet
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:
setDefaultColumnWidth in interface Sheet
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:
getDefaultRowHeight in interface Sheet
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:
getDefaultRowHeightInPoints in interface Sheet
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:
setDefaultRowHeight in interface Sheet
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:
setDefaultRowHeightInPoints in interface Sheet
Parameters:
height - default row height

getColumnStyle

public HSSFCellStyle getColumnStyle(int column)
Returns the HSSFCellStyle that applies to the given (0 based) column, or null if no style has been set for that column

Specified by:
getColumnStyle in interface Sheet

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

public int addMergedRegion(Region region)
Deprecated. (Aug-2008) use CellRangeAddress instead of Region


addMergedRegion

public int addMergedRegion(CellRangeAddress region)
adds a merged region of cells (hence those cells form one)

Specified by:
addMergedRegion in interface Sheet
Parameters:
region - (rowfrom/colfrom-rowto/colto) to merge
Returns:
index of this region

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 FormulaEvaluator is 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:
setForceFormulaRecalculation in interface Sheet
Parameters:
value - true if the application will perform a full recalculation of this worksheet values when the workbook is opened
See Also:
Workbook.setForceFormulaRecalculation(boolean)

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:
getForceFormulaRecalculation in interface Sheet
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:
setVerticallyCenter in interface Sheet
Parameters:
value - true to vertically center, false otherwise.

getVerticallyCenter

public boolean getVerticallyCenter(boolean value)
Deprecated. (Mar-2008) use getVerticallyCenter() instead

TODO: Boolean not needed, remove after next release


getVerticallyCenter

public boolean getVerticallyCenter()
Determine whether printed output for this sheet will be vertically centered.

Specified by:
getVerticallyCenter in interface Sheet

setHorizontallyCenter

public void setHorizontallyCenter(boolean value)
determines whether the output is horizontally centered on the page.

Specified by:
setHorizontallyCenter in interface Sheet
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:
getHorizontallyCenter in interface Sheet

setRightToLeft

public void setRightToLeft(boolean value)
Sets whether the worksheet is displayed from right to left instead of from left to right.

Specified by:
setRightToLeft in interface Sheet
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:
isRightToLeft in interface Sheet
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:
removeMergedRegion in interface Sheet
Parameters:
index - of the region to unmerge

getNumMergedRegions

public int getNumMergedRegions()
returns the number of merged regions

Specified by:
getNumMergedRegions in interface Sheet
Returns:
number of merged regions

getMergedRegionAt

public Region getMergedRegionAt(int index)
Deprecated. (Aug-2008) use getMergedRegion(int)


getMergedRegion

public CellRangeAddress getMergedRegion(int index)
Description copied from interface: Sheet
Returns the merged region at the specified index

Specified by:
getMergedRegion in interface Sheet
Returns:
the merged region at the specified index

rowIterator

public java.util.Iterator<Row> rowIterator()
Description copied from interface: Sheet
Returns an iterator of the physical rows

Specified by:
rowIterator in interface Sheet
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

public java.util.Iterator<Row> iterator()
Alias for rowIterator() to allow foreach loops

Specified by:
iterator in interface java.lang.Iterable<Row>

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:
setAutobreaks in interface Sheet
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:
setDisplayGuts in interface Sheet
Parameters:
b - guts or no guts (or glory)

setFitToPage

public void setFitToPage(boolean b)
fit to page option is on

Specified by:
setFitToPage in interface Sheet
Parameters:
b - fit or not

setRowSumsBelow

public void setRowSumsBelow(boolean b)
set if row summaries appear below detail in the outline

Specified by:
setRowSumsBelow in interface Sheet
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:
setRowSumsRight in interface Sheet
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:
getAutobreaks in interface Sheet
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:
getDisplayGuts in interface Sheet
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:
isDisplayZeros in interface Sheet
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:
setDisplayZeros in interface Sheet
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:
getFitToPage in interface Sheet
Returns:
fit or not

getRowSumsBelow

public boolean getRowSumsBelow()
get if row summaries appear below detail in the outline

Specified by:
getRowSumsBelow in interface Sheet
Returns:
below or not

getRowSumsRight

public boolean getRowSumsRight()
get if col summaries appear right of the detail in the outline

Specified by:
getRowSumsRight in interface Sheet
Returns:
right or not

isPrintGridlines

public boolean isPrintGridlines()
Returns whether gridlines are printed.

Specified by:
isPrintGridlines in interface Sheet
Returns:
Gridlines are printed
See Also:
to check if printing of gridlines is turned on or off

setPrintGridlines

public void setPrintGridlines(boolean newPrintGridlines)
Turns on or off the printing of gridlines.

Specified by:
setPrintGridlines in interface Sheet
Parameters:
newPrintGridlines - boolean to turn on or off the printing of gridlines
See Also:
Sheet.setPrintGridlines(boolean)

getPrintSetup

public HSSFPrintSetup getPrintSetup()
Gets the print setup object.

Specified by:
getPrintSetup in interface Sheet
Returns:
The user model for the print setup object.

getHeader

public HSSFHeader getHeader()
Description copied from interface: Sheet
Gets the user model for the default document header.

Note that XSSF offers more kinds of document headers than HSSF does

Specified by:
getHeader in interface Sheet
Returns:
the document header. Never null

getFooter

public HSSFFooter getFooter()
Description copied from interface: Sheet
Gets the user model for the default document footer.

Note that XSSF offers more kinds of document footers than HSSF does.

Specified by:
getFooter in interface Sheet
Returns:
the document footer. Never null

isSelected

public boolean isSelected()
Note - this is not the same as whether the sheet is focused (isActive)

Specified by:
isSelected in interface Sheet
Returns:
true if this sheet is currently selected

setSelected

public void setSelected(boolean sel)
Sets whether sheet is selected.

Specified by:
setSelected in interface Sheet
Parameters:
sel - Whether to select the sheet or deselect the sheet.
See Also:
Workbook.setActiveSheet(int)

isActive

public boolean isActive()
Returns:
true if 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.

Specified by:
getMargin in interface Sheet
Parameters:
margin - which margin to get
Returns:
the size of the margin

setMargin

public void setMargin(short margin,
                      double size)
Sets the size of the margin in inches.

Specified by:
setMargin in interface Sheet
Parameters:
margin - which margin to get
size - the size of the margin

getProtect

public boolean getProtect()
Answer whether protection is enabled or disabled

Specified by:
getProtect in interface Sheet
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:
getScenarioProtect in interface Sheet
Returns:
true => protection enabled; false => protection disabled

protectSheet

public void protectSheet(java.lang.String password)
Sets the protection enabled as well as the password

Specified by:
protectSheet in interface Sheet
Parameters:
password - to set for protection. Pass null to 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.

Specified by:
setZoom in interface Sheet
Parameters:
numerator - The numerator for the zoom magnification.
denominator - The denominator for the zoom magnification.

getTopRow

public short getTopRow()
The top row in the visible view when the sheet is first viewed after opening it in a viewer

Specified by:
getTopRow in interface Sheet
Returns:
short indicating the rownum (0 based) of the top row

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:
getLeftCol in interface Sheet
Returns:
short indicating the rownum (0 based) of the top row

showInPane

public void showInPane(short toprow,
                       short leftcol)
Sets desktop window pane display area, when the file is first opened in a viewer.

Specified by:
showInPane in interface Sheet
Parameters:
toprow - the top row to show in desktop window pane
leftcol - the left column to show in desktop window pane

shiftMerged

protected void shiftMerged(int startRow,
                           int endRow,
                           int n,
                           boolean isRow)
Shifts the merged regions left or right depending on mode

TODO: MODE , this is only row specific

Parameters:
startRow -
endRow -
n -
isRow -

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).

Specified by:
shiftRows in interface Sheet
Parameters:
startRow - the row to start shifting
endRow - the row to end shifting
n - the number of rows to shift

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 around

Additionally 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

Specified by:
shiftRows in interface Sheet
Parameters:
startRow - the row to start shifting
endRow - the row to end shifting
n - the number of rows to shift
copyRowHeight - whether to copy the row height during the shift
resetOriginalRowHeight - 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 around

Additionally 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 shifting
endRow - the row to end shifting
n - the number of rows to shift
copyRowHeight - whether to copy the row height during the shift
resetOriginalRowHeight - whether to set the original row's height to the default
moveComments - whether to move comments at the same time as the cells they are attached to

insertChartRecords

protected void insertChartRecords(java.util.List<Record> records)

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:
createFreezePane in interface Sheet
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:
createFreezePane in interface Sheet
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:
createSplitPane in interface Sheet
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 pane
leftmostColumn - 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:
Sheet.PANE_LOWER_LEFT, Sheet.PANE_LOWER_RIGHT, Sheet.PANE_UPPER_LEFT, Sheet.PANE_UPPER_RIGHT

getPaneInformation

public PaneInformation getPaneInformation()
Returns the information regarding the currently configured pane (split or freeze).

Specified by:
getPaneInformation in interface Sheet
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:
setDisplayGridlines in interface Sheet
Parameters:
show - whether to show gridlines or not

isDisplayGridlines

public boolean isDisplayGridlines()
Returns if gridlines are displayed.

Specified by:
isDisplayGridlines in interface Sheet
Returns:
whether gridlines are displayed

setDisplayFormulas

public void setDisplayFormulas(boolean show)
Sets whether the formulas are shown in a viewer.

Specified by:
setDisplayFormulas in interface Sheet
Parameters:
show - whether to show formulas or not

isDisplayFormulas

public boolean isDisplayFormulas()
Returns if formulas are displayed.

Specified by:
isDisplayFormulas in interface Sheet
Returns:
whether formulas are displayed

setDisplayRowColHeadings

public void setDisplayRowColHeadings(boolean show)
Sets whether the RowColHeadings are shown in a viewer.

Specified by:
setDisplayRowColHeadings in interface Sheet
Parameters:
show - whether to show RowColHeadings or not

isDisplayRowColHeadings

public boolean isDisplayRowColHeadings()
Returns if RowColHeadings are displayed.

Specified by:
isDisplayRowColHeadings in interface Sheet
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:
setRowBreak in interface Sheet
Parameters:
row - the row to break, inclusive

isRowBroken

public boolean isRowBroken(int row)
Description copied from interface: Sheet
Determines if there is a page break at the indicated row

Specified by:
isRowBroken in interface Sheet
Parameters:
row - FIXME: Document this!
Returns:
true if 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:
removeRowBreak in interface Sheet

getRowBreaks

public int[] getRowBreaks()
Description copied from interface: Sheet
Retrieves all the horizontal page breaks

Specified by:
getRowBreaks in interface Sheet
Returns:
row indexes of all the horizontal page breaks, never null

getColumnBreaks

public int[] getColumnBreaks()
Description copied from interface: Sheet
Retrieves all the vertical page breaks

Specified by:
getColumnBreaks in interface Sheet
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:
setColumnBreak in interface Sheet
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:
isColumnBroken in interface Sheet
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:
removeColumnBreak in interface Sheet
Parameters:
column -

validateRow

protected void validateRow(int row)
Runs a bounds check for row numbers

Parameters:
row -

validateColumn

protected void validateColumn(int column)
Runs a bounds check for column numbers

Parameters:
column -

dumpDrawingRecords

public void dumpDrawingRecords(boolean fat)
Aggregates the drawing records and dumps the escher record hierarchy to the standard output.


createDrawingPatriarch

public HSSFPatriarch createDrawingPatriarch()
Creates the top-level drawing patriarch. This will have the effect of removing any existing drawings on this sheet. This may then be used to add graphics or charts

Specified by:
createDrawingPatriarch in interface Sheet
Returns:
The new patriarch.

getDrawingEscherAggregate

public EscherAggregate getDrawingEscherAggregate()
Returns the agregate escher records for this sheet, it there is one. WARNING - calling this will trigger a parsing of the associated escher records. Any that aren't supported (such as charts and complex drawing types) will almost certainly be lost or corrupted when written out.


getDrawingPatriarch

public HSSFPatriarch getDrawingPatriarch()
Returns the top-level drawing patriach, if there is one. This will hold any graphics or charts for the sheet. WARNING - calling this will trigger a parsing of the associated escher records. Any that aren't supported (such as charts and complex drawing types) will almost certainly be lost or corrupted when written out. Only use this with simple drawings, otherwise call createDrawingPatriarch() and start from scratch!


setColumnGroupCollapsed

public void setColumnGroupCollapsed(short columnNumber,
                                    boolean collapsed)
Deprecated. (Sep 2008) use setColumnGroupCollapsed(int, boolean)


groupColumn

public void groupColumn(short fromColumn,
                        short toColumn)
Deprecated. (Sep 2008) use groupColumn(int, int)


ungroupColumn

public void ungroupColumn(short fromColumn,
                          short toColumn)
Deprecated. (Sep 2008) use ungroupColumn(int, int)


setColumnGroupCollapsed

public void setColumnGroupCollapsed(int columnNumber,
                                    boolean collapsed)
Expands or collapses a column group.

Specified by:
setColumnGroupCollapsed in interface Sheet
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:
groupColumn in interface Sheet
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: Sheet
Ungroup a range of columns that were previously groupped

Specified by:
ungroupColumn in interface Sheet
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

Specified by:
groupRow in interface Sheet
Parameters:
fromRow - start row (0-based)
toRow - end row (0-based)

ungroupRow

public void ungroupRow(int fromRow,
                       int toRow)
Description copied from interface: Sheet
Ungroup a range of rows that were previously groupped

Specified by:
ungroupRow in interface Sheet
Parameters:
fromRow - start row (0-based)
toRow - end row (0-based)

setRowGroupCollapsed

public void setRowGroupCollapsed(int rowIndex,
                                 boolean collapse)
Description copied from interface: Sheet
Set view state of a groupped range of rows

Specified by:
setRowGroupCollapsed in interface Sheet
Parameters:
rowIndex - start row of a groupped range of rows (0-based)
collapse - whether to expand/collapse the detail rows

setDefaultColumnStyle

public void setDefaultColumnStyle(int column,
                                  CellStyle style)
Sets the default column style for a given column. POI will only apply this style to new cells added to the sheet.

Specified by:
setDefaultColumnStyle in interface Sheet
Parameters:
column - the column index
style - 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:
autoSizeColumn in interface Sheet
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:
autoSizeColumn in interface Sheet
Parameters:
column - the column index
useMergedCells - whether to use the contents of merged cells when calculating the width of the column

getCellComment

public HSSFComment getCellComment(int row,
                                  int column)
Returns cell comment for the specified row and column

Specified by:
getCellComment in interface Sheet
Returns:
cell comment or null if not found

getSheetConditionalFormatting

public HSSFSheetConditionalFormatting getSheetConditionalFormatting()
Description copied from interface: Sheet
The 'Conditional Formatting' facet for this Sheet

Specified by:
getSheetConditionalFormatting in interface Sheet
Returns:
conditional formatting rule for this sheet

getSheetName

public java.lang.String getSheetName()
Returns the name of this sheet

Specified by:
getSheetName in interface Sheet
Returns:
the name of this sheet

setArrayFormula

public CellRange<HSSFCell> setArrayFormula(java.lang.String formula,
                                           CellRangeAddress range)
Description copied from interface: Sheet
Sets array formula to specified region for result.

Specified by:
setArrayFormula in interface Sheet
Parameters:
formula - text representation of the formula
range - Region of array formula for result.
Returns:
the CellRange of cells affected by this change

removeArrayFormula

public CellRange<HSSFCell> removeArrayFormula(Cell cell)
Description copied from interface: Sheet
Remove a Array Formula from this sheet. All cells contained in the Array Formula range are removed as well

Specified by:
removeArrayFormula in interface Sheet
Parameters:
cell - any cell within Array Formula range
Returns:
the CellRange of cells affected by this change

getDataValidationHelper

public DataValidationHelper getDataValidationHelper()
Specified by:
getDataValidationHelper in interface Sheet

setAutoFilter

public HSSFAutoFilter setAutoFilter(CellRangeAddress range)
Description copied from interface: Sheet
Enable filtering for a range of cells

Specified by:
setAutoFilter in interface Sheet
Parameters:
range - the range of cells to filter


Copyright 2012 The Apache Software Foundation or its licensors, as applicable.