Package loci.poi.hssf.usermodel
Class HSSFSheet
java.lang.Object
loci.poi.hssf.usermodel.HSSFSheet
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)
-
Field Summary
FieldsModifier and TypeFieldDescriptionprotected Workbookstatic final shortstatic final intUsed for compile-time optimization.static final shortstatic final bytestatic final bytestatic final bytestatic final bytestatic final shortstatic final shortprotected HSSFWorkbook -
Constructor Summary
ConstructorsModifierConstructorDescriptionprotectedHSSFSheet(HSSFWorkbook workbook) Creates new HSSFSheet - called by HSSFWorkbook to create a sheet from scratch.protectedHSSFSheet(HSSFWorkbook workbook, Sheet sheet) Creates an HSSFSheet representing the given Sheet object. -
Method Summary
Modifier and TypeMethodDescriptionintaddMergedRegion(Region region) adds a merged region of cells (hence those cells form one)voidautoSizeColumn(short column) Adjusts the column width to fit the contents.Creates the toplevel 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) Aggregates the drawing records and dumps the escher record hierarchy to the standard output.booleanwhether alternate expression evaluation is onbooleanwhether alternative formula entry is onbooleanshow automatic page breaks or notgetCellComment(int row, int column) Returns cell comment for the specified row and columnshort[]Retrieves all the vertical page breaksshortgetColumnWidth(short column) get the width (in units of 1/256th of a character width )shortget 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 notintgets the first row on the sheetbooleanfit to page option is onGets the user model for the document footer.Gets the user model for the document header.booleanDetermine whether printed output for this sheet will be horizontally centered.intgets the last row on the sheetshortThe 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.getMergedRegionAt(int index) gets the region at a particular indexintreturns 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 phsyically defined rows (NOT the number of rows in the sheet)Gets the print setup object.booleanAnswer whether protection is enabled or disabledgetRow(int rownum) 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 disabledprotected SheetgetSheet()used internally in the API to get the low level Sheet record represented by this Object.shortThe top row in the visible view when the sheet is first viewed after opening it in a viewerbooleangetVerticallyCenter(boolean value) Determine whether printed output for this sheet will be vertically centered.voidgroupColumn(short fromColumn, short toColumn) Create an outline for the provided column range.voidgroupRow(int fromRow, int toRow) protected voidinsertChartRecords(List records) booleanisColumnBroken(short column) Determines if there is a page break at the indicated columnbooleanisColumnHidden(short column) Get the hidden state for a given column.booleanReturns if formulas are displayed.booleanReturns if gridlines are displayed.booleanReturns if RowColHeadings are displayed.booleanget whether gridlines are printed.booleanReturns whether gridlines are printed.booleanisRowBroken(int row) Determines if there is a page break at the indicated rowvoidprotectSheet(String password) Sets the protection enabled as well as the passwordvoidremoveColumnBreak(short column) Removes a page break at the indicated columnvoidremoveMergedRegion(int index) removes a merged region of cells (hence letting them free)voidRemove a row from this sheet.voidremoveRowBreak(int row) Removes the page break at the indicated rowvoidsetAlternativeExpression(boolean b) whether alternate expression evaluation is onvoidsetAlternativeFormula(boolean b) whether alternative formula entry is onvoidsetAutobreaks(boolean b) show automatic page breaks or notvoidsetColumnBreak(short column) Sets a page break at the indicated columnvoidsetColumnGroupCollapsed(short columnNumber, boolean collapsed) Expands or collapses a column group.voidsetColumnHidden(short column, boolean hidden) Get the visibility state for a given column.voidsetColumnWidth(short column, short width) set the width (in units of 1/256th of a character width)voidsetDefaultColumnStyle(short column, HSSFCellStyle style) Sets the default column style for a given column.voidsetDefaultColumnWidth(short 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.voidsetFitToPage(boolean b) fit to page option is onvoidsetGridsPrinted(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 newPrintGridlines) Turns on or off the printing of gridlines.voidsetProtect(boolean protect) Deprecated.use protectSheet(String, boolean, boolean)voidsetRowBreak(int row) Sets a page break at the indicated rowvoidsetRowGroupCollapsed(int row, boolean collapse) voidsetRowSumsBelow(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 numerator, int denominator) Sets the zoom magnication for the sheet.protected voidshiftMerged(int startRow, int endRow, int n, boolean isRow) Shifts the merged regions left or right depending on modevoidshiftRows(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.voidshowInPane(short toprow, short leftcol) Sets desktop window pane display area, when the file is first opened in a viewer.voidungroupColumn(short fromColumn, short toColumn) voidungroupRow(int fromRow, int toRow) protected voidvalidateColumn(short column) Runs a bounds check for column numbersprotected voidvalidateRow(int row) Runs a bounds check for row numbers
-
Field Details
-
LeftMargin
public static final short LeftMargin- See Also:
-
RightMargin
public static final short RightMargin- See Also:
-
TopMargin
public static final short TopMargin- See Also:
-
BottomMargin
public static final short BottomMargin- See Also:
-
PANE_LOWER_RIGHT
public static final byte PANE_LOWER_RIGHT- See Also:
-
PANE_UPPER_RIGHT
public static final byte PANE_UPPER_RIGHT- See Also:
-
PANE_LOWER_LEFT
public static final byte PANE_LOWER_LEFT- See Also:
-
PANE_UPPER_LEFT
public static final byte PANE_UPPER_LEFT- See Also:
-
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.- See Also:
-
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
-
createRow
Create a new row within the sheet and return the high level representation- Parameters:
rownum- row number- Returns:
- High level HSSFRow object representing a row in the sheet
- See Also:
-
removeRow
Remove a row from this sheet. All cells contained in the row are removed as well- Parameters:
row- representing a row to remove.
-
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.- Parameters:
rownum- row to get- Returns:
- HSSFRow representing the rownumber or null if its not defined on the sheet
-
getPhysicalNumberOfRows
public int getPhysicalNumberOfRows()Returns the number of phsyically defined rows (NOT the number of rows in the sheet) -
getFirstRowNum
public int getFirstRowNum()gets the first row on the sheet- Returns:
- the number of the first logical row on the sheet
-
getLastRowNum
public int getLastRowNum()gets the last row on the sheet- Returns:
- last row contained n this sheet.
-
setColumnHidden
public void setColumnHidden(short column, boolean hidden) Get the visibility state for a given column.- Parameters:
column- - the column to get (0-based)hidden- - the visiblity state of the column
-
isColumnHidden
public boolean isColumnHidden(short column) Get the hidden state for a given column.- Parameters:
column- - the column to set (0-based)- Returns:
- hidden - the visiblity state of the column
-
setColumnWidth
public void setColumnWidth(short column, short width) set the width (in units of 1/256th of a character width)- Parameters:
column- - the column to set (0-based)width- - the width in units of 1/256th of a character width
-
getColumnWidth
public short getColumnWidth(short column) get the width (in units of 1/256th of a character width )- Parameters:
column- - the column to set (0-based)- Returns:
- width - the width in units of 1/256th of a character width
-
getDefaultColumnWidth
public short getDefaultColumnWidth()get the default column width for the sheet (if the columns do not define their own width) in characters- Returns:
- 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)- 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.- Returns:
- default row height in points
-
setDefaultColumnWidth
public void setDefaultColumnWidth(short width) set the default column width for the sheet (if the columns do not define their own width) in characters- Parameters:
width- default column width
-
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)- 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- Parameters:
height- default row height
-
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 (hence those cells form one)- Parameters:
region- (rowfrom/colfrom-rowto/colto) to merge- Returns:
- index of this region
-
setVerticallyCenter
public void setVerticallyCenter(boolean value) determines whether the output is vertically centered on the page.- Parameters:
value- true to vertically center, false otherwise.
-
getVerticallyCenter
public boolean getVerticallyCenter(boolean value) Determine whether printed output for this sheet will be vertically centered. -
setHorizontallyCenter
public void setHorizontallyCenter(boolean value) determines whether the output is horizontally centered on the page.- Parameters:
value- true to horizontally center, false otherwise.
-
getHorizontallyCenter
public boolean getHorizontallyCenter()Determine whether printed output for this sheet will be horizontally centered. -
removeMergedRegion
public void removeMergedRegion(int index) removes a merged region of cells (hence letting them free)- Parameters:
index- of the region to unmerge
-
getNumMergedRegions
public int getNumMergedRegions()returns the number of merged regions- Returns:
- number of merged regions
-
getMergedRegionAt
gets the region at a particular index- Parameters:
index- of the region to fetch- Returns:
- the merged region (simple eh?)
-
rowIterator
- 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.
-
getSheet
used internally in the API to get the low level Sheet record represented by this Object.- Returns:
- Sheet - low level representation of this HSSFSheet.
-
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- 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- Parameters:
b- guts or no guts (or glory)
-
setFitToPage
public void setFitToPage(boolean b) fit to page option is on- Parameters:
b- fit or not
-
setRowSumsBelow
public void setRowSumsBelow(boolean b) set if row summaries appear below detail in the outline- Parameters:
b- below or not
-
setRowSumsRight
public void setRowSumsRight(boolean b) set if col summaries appear right of the detail in the outline- 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- 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- Returns:
- guts or no guts (or glory)
-
getFitToPage
public boolean getFitToPage()fit to page option is on- Returns:
- fit or not
-
getRowSumsBelow
public boolean getRowSumsBelow()get if row summaries appear below detail in the outline- Returns:
- below or not
-
getRowSumsRight
public boolean getRowSumsRight()get if col summaries appear right of the detail in the outline- Returns:
- right or not
-
isPrintGridlines
public boolean isPrintGridlines()Returns whether gridlines are printed.- Returns:
- Gridlines are printed
-
setPrintGridlines
public void setPrintGridlines(boolean newPrintGridlines) Turns on or off the printing of gridlines.- Parameters:
newPrintGridlines- boolean to turn on or off the printing of gridlines
-
getPrintSetup
Gets the print setup object.- Returns:
- The user model for the print setup object.
-
getHeader
Gets the user model for the document header.- Returns:
- The Document header.
-
setSelected
public void setSelected(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.- 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.- Parameters:
margin- which margin to getsize- the size of the margin
-
getProtect
public boolean getProtect()Answer whether protection is enabled or disabled- 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- Returns:
- true => protection enabled; false => protection disabled
-
setProtect
public void setProtect(boolean protect) Deprecated.use protectSheet(String, boolean, boolean)Sets the protection on enabled or disabled- Parameters:
protect- true => protection enabled; false => protection disabled
-
protectSheet
Sets the protection enabled as well as the password- Parameters:
password- to set for protection
-
setZoom
public void setZoom(int numerator, int denominator) Sets the zoom magnication 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.
-
getTopRow
public short getTopRow()The top row in the visible view when the sheet is first viewed after opening it in a viewer- 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- 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.- 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) Shifts the merged regions left or right depending on modeTODO: 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).
- Parameters:
startRow- the row to start shiftingendRow- the row to end shiftingn- 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 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 default
-
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.- Parameters:
colSplit- Horizonatal position of split.rowSplit- Vertical position of split.topRow- Top row visible in bottom paneleftmostColumn- Left column visible in right pane.
-
createFreezePane
public void createFreezePane(int colSplit, int rowSplit) Creates a split (freezepane). Any existing freezepane or split pane is overwritten.- 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.- 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).- 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.- Parameters:
show- whether to show gridlines or not
-
isDisplayGridlines
public boolean isDisplayGridlines()Returns if gridlines are displayed.- Returns:
- whether gridlines are displayed
-
setDisplayFormulas
public void setDisplayFormulas(boolean show) Sets whether the formulas are shown in a viewer.- Parameters:
show- whether to show formulas or not
-
isDisplayFormulas
public boolean isDisplayFormulas()Returns if formulas are displayed.- Returns:
- whether formulas are displayed
-
setDisplayRowColHeadings
public void setDisplayRowColHeadings(boolean show) Sets whether the RowColHeadings are shown in a viewer.- Parameters:
show- whether to show RowColHeadings or not
-
isDisplayRowColHeadings
public boolean isDisplayRowColHeadings()Returns if RowColHeadings are displayed.- Returns:
- whether RowColHeadings are displayed
-
setRowBreak
public void setRowBreak(int row) Sets a page break at the indicated row- Parameters:
row- FIXME: Document this!
-
isRowBroken
public boolean isRowBroken(int row) Determines if there is a page break at the indicated row- Parameters:
row- FIXME: Document this!- Returns:
- FIXME: Document this!
-
removeRowBreak
public void removeRowBreak(int row) Removes the page break at the indicated row- Parameters:
row-
-
getRowBreaks
public int[] getRowBreaks()Retrieves all the horizontal page breaks- Returns:
- all the horizontal page breaks, or null if there are no row page breaks
-
getColumnBreaks
public short[] getColumnBreaks()Retrieves all the vertical page breaks- Returns:
- all the vertical page breaks, or null if there are no column page breaks
-
setColumnBreak
public void setColumnBreak(short column) Sets a page break at the indicated column- Parameters:
column-
-
isColumnBroken
public boolean isColumnBroken(short column) Determines if there is a page break at the indicated column- Parameters:
column- FIXME: Document this!- Returns:
- FIXME: Document this!
-
removeColumnBreak
public void removeColumnBreak(short column) Removes a page break at the indicated column- Parameters:
column-
-
validateRow
protected void validateRow(int row) Runs a bounds check for row numbers- Parameters:
row-
-
validateColumn
protected void validateColumn(short 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
Creates the toplevel drawing patriarch. This will have the effect of removing any existing drawings on this sheet.- Returns:
- The new patriarch.
-
setColumnGroupCollapsed
public void setColumnGroupCollapsed(short columnNumber, boolean collapsed) Expands or collapses a column group.- Parameters:
columnNumber- One of the columns in the group.collapsed- true = collapse group, false = expand group.
-
groupColumn
public void groupColumn(short fromColumn, short toColumn) Create an outline for the provided column range.- Parameters:
fromColumn- beginning of the column range.toColumn- end of the column range.
-
ungroupColumn
public void ungroupColumn(short fromColumn, short toColumn) -
groupRow
public void groupRow(int fromRow, int toRow) -
ungroupRow
public void ungroupRow(int fromRow, int toRow) -
setRowGroupCollapsed
public void setRowGroupCollapsed(int row, boolean collapse) -
setDefaultColumnStyle
Sets the default column style for a given column. POI will only apply this style to new cells added to the sheet.- Parameters:
column- the column indexstyle- the style to set
-
autoSizeColumn
public void autoSizeColumn(short 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.- Parameters:
column- the column index
-
getCellComment
Returns cell comment for the specified row and column- Returns:
- cell comment or
nullif not found
-