BottomMargin, FooterMargin, HeaderMargin, LeftMargin, PANE_LOWER_LEFT, PANE_LOWER_RIGHT, PANE_UPPER_LEFT, PANE_UPPER_RIGHT, RightMargin, TopMargin
Constructor and Description |
---|
SXSSFSheet(SXSSFWorkbook workbook,
XSSFSheet xSheet) |
Modifier and Type | Method and Description |
---|---|
int |
addMergedRegion(CellRangeAddress region)
Adds a merged region of cells (hence those cells form one)
|
int |
addMergedRegionUnsafe(CellRangeAddress region)
Adds a merged region of cells (hence those cells form one)
|
void |
addValidationData(DataValidation dataValidation)
Creates a data validation object
|
boolean |
areAllRowsFlushed()
Are all rows flushed to disk?
|
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.
|
void |
changeRowNum(SXSSFRow row,
int newRowNum) |
SXSSFDrawing |
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).
|
SXSSFRow |
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 |
disableLocking()
Disable sheet protection
|
void |
enableLocking()
Enable sheet protection
|
void |
flushRows()
Flush all rows to disk.
|
void |
flushRows(int remaining)
Specifies how many rows can be accessed at most via getRow().
|
CellAddress |
getActiveCell()
Return location of the active cell, e.g.
|
boolean |
getAutobreaks()
Flag indicating whether the sheet displays Automatic Page Breaks.
|
XSSFComment |
getCellComment(CellAddress ref)
Returns cell comment for the specified row and column
|
java.util.Map<CellAddress,XSSFComment> |
getCellComments()
Returns all cell comments on this sheet.
|
int[] |
getColumnBreaks()
Retrieves all the vertical page breaks
|
int |
getColumnOutlineLevel(int columnIndex)
Returns the column outline level.
|
CellStyle |
getColumnStyle(int column)
Returns the CellStyle that applies to the given
(0 based) column, or null if no style has been
set for that column
|
int |
getColumnWidth(int columnIndex)
get the width (in units of 1/256th of a character width )
|
float |
getColumnWidthInPixels(int columnIndex)
Get the actual column width in pixels
|
DataValidationHelper |
getDataValidationHelper() |
java.util.List<XSSFDataValidation> |
getDataValidations()
Returns the list of DataValidation in the sheet.
|
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 |
getDisplayGuts()
Get whether to display the guts or not,
default value is true
|
XSSFDrawing |
getDrawingPatriarch()
Return the sheet's existing drawing, or null if there isn't yet one.
|
int |
getFirstRowNum()
Gets the first row on the sheet
|
boolean |
getFitToPage()
Flag indicating whether the Fit to Page print option is enabled.
|
Footer |
getFooter()
Gets the user model for the default document footer.
|
boolean |
getForceFormulaRecalculation()
Whether Excel will be asked to recalculate all formulas when the
workbook is opened.
|
Header |
getHeader()
Gets the user model for the default document header.
|
boolean |
getHorizontallyCenter()
Determine whether printed output for this sheet will be horizontally centered.
|
XSSFHyperlink |
getHyperlink(CellAddress addr)
Get a Hyperlink in this sheet located in a cell specified by {code addr}
|
XSSFHyperlink |
getHyperlink(int row,
int column)
Get a Hyperlink in this sheet anchored at row, column
|
java.util.List<XSSFHyperlink> |
getHyperlinkList()
Get a list of Hyperlinks in this sheet
|
int |
getLastFlushedRowNum() |
int |
getLastRowNum()
Gets the 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.
|
java.util.List<CellRangeAddress> |
getMergedRegions()
Returns the list of merged regions.
|
int |
getNumMergedRegions()
Returns the number of merged regions
|
PaneInformation |
getPaneInformation()
Returns the information regarding the currently configured pane (split or freeze)
|
int |
getPhysicalNumberOfRows()
Returns the number of physically defined rows (NOT the number of rows in the sheet)
|
PrintSetup |
getPrintSetup()
Gets the print setup object.
|
boolean |
getProtect()
Answer whether protection is enabled or disabled
|
CellRangeAddress |
getRepeatingColumns()
Gets the repeating columns used when printing the sheet, as found in
File->PageSetup->Sheet.
|
CellRangeAddress |
getRepeatingRows()
Gets the repeating rows used when printing the sheet, as found in
File->PageSetup->Sheet.
|
SXSSFRow |
getRow(int rownum)
Returns the logical row (not physical) 0-based.
|
int[] |
getRowBreaks()
Retrieves all the horizontal page breaks
|
int |
getRowNum(SXSSFRow row) |
boolean |
getRowSumsBelow()
Flag indicating whether summary rows appear below detail in an outline, when applying an outline.
|
boolean |
getRowSumsRight()
Flag indicating whether summary columns appear to the right of detail in an outline, when applying an outline.
|
boolean |
getScenarioProtect()
Answer whether scenario protection is enabled or disabled
|
SheetConditionalFormatting |
getSheetConditionalFormatting()
The 'Conditional Formatting' facet for this Sheet
|
java.lang.String |
getSheetName()
Returns the name of this sheet
|
XSSFColor |
getTabColor() |
short |
getTopRow()
The top row in the visible view when the sheet is
first viewed after opening it in a viewer
|
java.util.Set<java.lang.Integer> |
getTrackedColumnsForAutoSizing()
Get the currently tracked columns for auto-sizing.
|
boolean |
getVerticallyCenter()
Determine whether printed output for this sheet will be vertically centered.
|
SXSSFWorkbook |
getWorkbook()
Return the parent workbook
|
java.io.InputStream |
getWorksheetXMLInputStream() |
void |
groupColumn(int fromColumn,
int toColumn)
Create an outline for the provided column range.
|
void |
groupRow(int fromRow,
int toRow)
Tie a range of rows together so that they can be collapsed or expanded
|
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 |
isColumnTrackedForAutoSizing(int column)
Returns true if column is currently tracked for auto-sizing.
|
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 |
isPrintGridlines()
Returns whether gridlines are printed.
|
boolean |
isPrintRowAndColumnHeadings()
Returns whether row and column headings 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() |
void |
lockAutoFilter(boolean enabled)
Enable or disable Autofilters locking.
|
void |
lockDeleteColumns(boolean enabled)
Enable or disable Deleting columns locking.
|
void |
lockDeleteRows(boolean enabled)
Enable or disable Deleting rows locking.
|
void |
lockFormatCells(boolean enabled)
Enable or disable Formatting cells locking.
|
void |
lockFormatColumns(boolean enabled)
Enable or disable Formatting columns locking.
|
void |
lockFormatRows(boolean enabled)
Enable or disable Formatting rows locking.
|
void |
lockInsertColumns(boolean enabled)
Enable or disable Inserting columns locking.
|
void |
lockInsertHyperlinks(boolean enabled)
Enable or disable Inserting hyperlinks locking.
|
void |
lockInsertRows(boolean enabled)
Enable or disable Inserting rows locking.
|
void |
lockObjects(boolean enabled)
Enable or disable Objects locking.
|
void |
lockPivotTables(boolean enabled)
Enable or disable Pivot Tables locking.
|
void |
lockScenarios(boolean enabled)
Enable or disable Scenarios locking.
|
void |
lockSelectLockedCells(boolean enabled)
Enable or disable Selection of locked cells locking.
|
void |
lockSelectUnlockedCells(boolean enabled)
Enable or disable Selection of unlocked cells locking.
|
void |
lockSort(boolean enabled)
Enable or disable Sort locking.
|
void |
protectSheet(java.lang.String password)
Sets the protection enabled as well as the password
|
CellRange<? extends Cell> |
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 |
removeMergedRegions(java.util.Collection<java.lang.Integer> indices)
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 |
setActiveCell(CellAddress address)
Sets location of the active cell
|
CellRange<? extends Cell> |
setArrayFormula(java.lang.String formula,
CellRangeAddress range)
Sets array formula to specified region for result.
|
void |
setAutobreaks(boolean value)
Flag indicating whether the sheet displays Automatic Page Breaks.
|
AutoFilter |
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 |
setColumnHidden(int columnIndex,
boolean hidden)
Get the visibility state for a given column
|
void |
setColumnWidth(int columnIndex,
int width)
Set the width (in units of 1/256th of a character width)
|
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 |
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 |
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 value)
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 value)
Flag indicating whether the Fit to Page print option is enabled.
|
void |
setForceFormulaRecalculation(boolean value)
Control if Excel should be asked to recalculate all formulas when the
workbook is opened, via the "sheetCalcPr fullCalcOnLoad" option.
|
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 show)
Turns on or off the printing of gridlines.
|
void |
setPrintRowAndColumnHeadings(boolean show)
Turns on or off the printing of row and column headings.
|
void |
setRandomAccessWindowSize(int value)
Specifies how many rows can be accessed at most via getRow().
|
void |
setRepeatingColumns(CellRangeAddress columnRangeRef)
Sets the repeating columns used when printing the sheet, as found in
File->PageSetup->Sheet.
|
void |
setRepeatingRows(CellRangeAddress rowRangeRef)
Sets the repeating rows used when printing the sheet, as found in
File->PageSetup->Sheet.
|
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
|
void |
setRowGroupCollapsed(int row,
boolean collapse)
Set view state of a grouped range of rows.
|
void |
setRowOutlineLevel(int rownum,
int level)
Set row groupings (like groupRow) in a stream-friendly manner
|
void |
setRowSumsBelow(boolean value)
Flag indicating whether summary rows appear below detail in an outline, when applying an outline.
|
void |
setRowSumsRight(boolean value)
Flag indicating whether summary columns appear to the right of detail in an outline, when applying an outline.
|
void |
setSelected(boolean value)
Sets a flag indicating whether this sheet is selected.
|
void |
setTabColor(int colorIndex)
Set background color of the sheet tab
|
void |
setTabColor(XSSFColor color) |
void |
setVerticallyCenter(boolean value)
Determines whether the output is vertically centered on the page.
|
void |
setZoom(int scale)
Window zoom magnification for current view representing percent values.
|
void |
shiftColumns(int startColumn,
int endColumn,
int n)
Shifts columns between startColumn and endColumn, n number of columns.
|
void |
shiftRows(int startRow,
int endRow,
int n)
Not implemented for SXSSFSheets
Shifts rows between startRow and endRow n number of rows.
|
void |
shiftRows(int startRow,
int endRow,
int n,
boolean copyRowHeight,
boolean resetOriginalRowHeight)
Not implemented for SXSSFSheets
Shifts rows between startRow and endRow n number of rows.
|
void |
showInPane(int topRow,
int leftCol)
Sets desktop window pane display area, when the
file is first opened in a viewer.
|
void |
trackAllColumnsForAutoSizing()
Tracks all columns in the sheet for auto-sizing.
|
void |
trackColumnForAutoSizing(int column)
Track a column in the sheet for auto-sizing.
|
void |
trackColumnsForAutoSizing(java.util.Collection<java.lang.Integer> columns)
Track several columns in the sheet for auto-sizing.
|
void |
ungroupColumn(int fromColumn,
int toColumn)
Ungroup a range of columns that were previously groupped
|
void |
ungroupRow(int fromRow,
int toRow)
Ungroup a range of rows that were previously groupped
|
void |
untrackAllColumnsForAutoSizing()
Untracks all columns in the sheet for auto-sizing.
|
boolean |
untrackColumnForAutoSizing(int column)
Removes a column that was previously marked for inclusion in auto-size column tracking.
|
boolean |
untrackColumnsForAutoSizing(java.util.Collection<java.lang.Integer> columns)
Untracks several columns in the sheet for auto-sizing.
|
void |
validateMergedRegions()
Verify that merged regions do not intersect multi-cell array formulas and
no merged regions intersect another merged region in this sheet.
|
public SXSSFSheet(SXSSFWorkbook workbook, XSSFSheet xSheet) throws java.io.IOException
java.io.IOException
public java.io.InputStream getWorksheetXMLInputStream() throws java.io.IOException
java.io.IOException
public java.util.Iterator<Row> iterator()
iterator
in interface java.lang.Iterable<Row>
public SXSSFRow createRow(int rownum)
createRow
in interface Sheet
rownum
- row numberjava.lang.IllegalArgumentException
- If the max. number of rows is exceeded or
a rownum is provided where the row is already flushed to disk.removeRow(Row)
public void removeRow(Row row)
public SXSSFRow getRow(int rownum)
public int getPhysicalNumberOfRows()
getPhysicalNumberOfRows
in interface Sheet
public int getFirstRowNum()
getFirstRowNum
in interface Sheet
public int getLastRowNum()
getLastRowNum
in interface Sheet
public void setColumnHidden(int columnIndex, boolean hidden)
setColumnHidden
in interface Sheet
columnIndex
- - the column to get (0-based)hidden
- - the visiblity state of the columnpublic boolean isColumnHidden(int columnIndex)
isColumnHidden
in interface Sheet
columnIndex
- - the column to set (0-based)false
if the column is visiblepublic void setColumnWidth(int columnIndex, int 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.
setColumnWidth
in interface Sheet
columnIndex
- - the column to set (0-based)width
- - the width in units of 1/256th of a character widthpublic int getColumnWidth(int columnIndex)
getColumnWidth
in interface Sheet
columnIndex
- - the column to set (0-based)public float getColumnWidthInPixels(int columnIndex)
Please note, that this method works correctly only for workbooks with the default font size (Calibri 11pt for .xlsx).
getColumnWidthInPixels
in interface Sheet
columnIndex
- - the column to set (0-based)public void setDefaultColumnWidth(int width)
setDefaultColumnWidth
in interface Sheet
width
- default column width measured in characterspublic int getDefaultColumnWidth()
getDefaultColumnWidth
in interface Sheet
public short getDefaultRowHeight()
getDefaultRowHeight
in interface Sheet
public float getDefaultRowHeightInPoints()
getDefaultRowHeightInPoints
in interface Sheet
public void setDefaultRowHeight(short height)
setDefaultRowHeight
in interface Sheet
height
- default row height measured in twips (1/20 of a point)public void setDefaultRowHeightInPoints(float height)
setDefaultRowHeightInPoints
in interface Sheet
height
- default row heightpublic CellStyle getColumnStyle(int column)
getColumnStyle
in interface Sheet
public int addMergedRegion(CellRangeAddress region)
addMergedRegion
in interface Sheet
region
- (rowfrom/colfrom-rowto/colto) to mergepublic int addMergedRegionUnsafe(CellRangeAddress region)
addMergedRegionUnsafe
in interface Sheet
region
- (rowfrom/colfrom-rowto/colto) to mergepublic void validateMergedRegions()
validateMergedRegions
in interface Sheet
java.lang.IllegalStateException
- if region intersects with a multi-cell array formulajava.lang.IllegalStateException
- if at least one region intersects with another merged region in this sheetpublic void setVerticallyCenter(boolean value)
setVerticallyCenter
in interface Sheet
value
- true to vertically center, false otherwise.public void setHorizontallyCenter(boolean value)
setHorizontallyCenter
in interface Sheet
value
- true to horizontally center, false otherwise.public boolean getHorizontallyCenter()
getHorizontallyCenter
in interface Sheet
public boolean getVerticallyCenter()
getVerticallyCenter
in interface Sheet
public void removeMergedRegion(int index)
removeMergedRegion
in interface Sheet
index
- of the region to unmergepublic void removeMergedRegions(java.util.Collection<java.lang.Integer> indices)
removeMergedRegions
in interface Sheet
indices
- of the regions to unmergepublic int getNumMergedRegions()
getNumMergedRegions
in interface Sheet
public CellRangeAddress getMergedRegion(int index)
getMergedRegions()
than to call
this each time.getMergedRegion
in interface Sheet
public java.util.List<CellRangeAddress> getMergedRegions()
getMergedRegion(int)
each time.getMergedRegions
in interface Sheet
public java.util.Iterator<Row> rowIterator()
rowIterator
in interface Sheet
public void setAutobreaks(boolean value)
setAutobreaks
in interface Sheet
value
- true
if the sheet displays Automatic Page Breaks.public void setDisplayGuts(boolean value)
setDisplayGuts
in interface Sheet
value
- - guts or no gutspublic void setDisplayZeros(boolean value)
setDisplayZeros
in interface Sheet
value
- whether to display or hide all zero values on the worksheetpublic boolean isDisplayZeros()
isDisplayZeros
in interface Sheet
public void setRightToLeft(boolean value)
setRightToLeft
in interface Sheet
value
- true for right to left, false otherwise.public boolean isRightToLeft()
isRightToLeft
in interface Sheet
public void setFitToPage(boolean value)
setFitToPage
in interface Sheet
value
- true
if the Fit to Page print option is enabled.public void setRowSumsBelow(boolean value)
When true a summary row is inserted below the detailed data being summarized and a new outline level is established on that row.
When false a summary row is inserted above the detailed data being summarized and a new outline level is established on that row.
setRowSumsBelow
in interface Sheet
value
- true
if row summaries appear below detail in the outlinepublic void setRowSumsRight(boolean value)
When true a summary column is inserted to the right of the detailed data being summarized and a new outline level is established on that column.
When false a summary column is inserted to the left of the detailed data being summarized and a new outline level is established on that column.
setRowSumsRight
in interface Sheet
value
- true
if col summaries appear right of the detail in the outlinepublic boolean getAutobreaks()
getAutobreaks
in interface Sheet
true
if the sheet displays Automatic Page Breaks.public boolean getDisplayGuts()
getDisplayGuts
in interface Sheet
public boolean getFitToPage()
getFitToPage
in interface Sheet
true
if the Fit to Page print option is enabled.public boolean getRowSumsBelow()
When true a summary row is inserted below the detailed data being summarized and a new outline level is established on that row.
When false a summary row is inserted above the detailed data being summarized and a new outline level is established on that row.
getRowSumsBelow
in interface Sheet
true
if row summaries appear below detail in the outlinepublic boolean getRowSumsRight()
When true a summary column is inserted to the right of the detailed data being summarized and a new outline level is established on that column.
When false a summary column is inserted to the left of the detailed data being summarized and a new outline level is established on that column.
getRowSumsRight
in interface Sheet
true
if col summaries appear right of the detail in the outlinepublic boolean isPrintGridlines()
isPrintGridlines
in interface Sheet
to check if gridlines are displayed on screen
public void setPrintGridlines(boolean show)
setPrintGridlines
in interface Sheet
show
- boolean to turn on or off the printing of gridlinesto display gridlines on screen
public boolean isPrintRowAndColumnHeadings()
isPrintRowAndColumnHeadings
in interface Sheet
public void setPrintRowAndColumnHeadings(boolean show)
setPrintRowAndColumnHeadings
in interface Sheet
show
- boolean to turn on or off the printing of row and column headingspublic PrintSetup getPrintSetup()
getPrintSetup
in interface Sheet
public Header getHeader()
Note that XSSF offers more kinds of document headers than HSSF does
public Footer getFooter()
Note that XSSF offers more kinds of document footers than HSSF does.
public void setSelected(boolean value)
Note: multiple sheets can be selected, but only one sheet can be active at one time.
setSelected
in interface Sheet
value
- true
if this sheet is selectedWorkbook.setActiveSheet(int)
public double getMargin(short margin)
public void setMargin(short margin, double size)
public boolean getProtect()
getProtect
in interface Sheet
public void protectSheet(java.lang.String password)
protectSheet
in interface Sheet
password
- to set for protection. Pass null
to remove protectionpublic boolean getScenarioProtect()
getScenarioProtect
in interface Sheet
public void setZoom(int scale)
10 - 10% 20 - 20% ... 100 - 100% ... 400 - 400%Current view can be Normal, Page Layout, or Page Break Preview.
public short getTopRow()
public short getLeftCol()
getLeftCol
in interface Sheet
public void showInPane(int topRow, int leftCol)
showInPane
in interface Sheet
topRow
- the top row to show in desktop window paneleftCol
- the left column to show in desktop window panepublic void setForceFormulaRecalculation(boolean value)
FormulaEvaluator
is the
recommended solution, but this may be used for certain cases where
evaluation in POI is not possible.setForceFormulaRecalculation
in interface Sheet
value
- true if the application will perform a full recalculation of
this worksheet values when the workbook is openedWorkbook.setForceFormulaRecalculation(boolean)
public boolean getForceFormulaRecalculation()
getForceFormulaRecalculation
in interface Sheet
@NotImplemented public void shiftRows(int startRow, int endRow, int n)
Additionally shifts merged regions that are completely defined in these rows (ie. merged 2 cells on a row to be shifted).
@NotImplemented public void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight)
Additionally 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.
shiftRows
in interface Sheet
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 defaultpublic void createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow)
createFreezePane
in interface Sheet
colSplit
- Horizonatal position of split.rowSplit
- Vertical position of split.leftmostColumn
- Left column visible in right pane.topRow
- Top row visible in bottom panepublic void createFreezePane(int colSplit, int rowSplit)
createFreezePane
in interface Sheet
colSplit
- Horizonatal position of split.rowSplit
- Vertical position of split.public void createSplitPane(int xSplitPos, int ySplitPos, int leftmostColumn, int topRow, int activePane)
createSplitPane
in interface Sheet
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_LEFTSheet.PANE_LOWER_LEFT
,
Sheet.PANE_LOWER_RIGHT
,
Sheet.PANE_UPPER_LEFT
,
Sheet.PANE_UPPER_RIGHT
public PaneInformation getPaneInformation()
getPaneInformation
in interface Sheet
public void setDisplayGridlines(boolean show)
setDisplayGridlines
in interface Sheet
show
- whether to show gridlines or notpublic boolean isDisplayGridlines()
isDisplayGridlines
in interface Sheet
public void setDisplayFormulas(boolean show)
setDisplayFormulas
in interface Sheet
show
- whether to show formulas or notpublic boolean isDisplayFormulas()
isDisplayFormulas
in interface Sheet
public void setDisplayRowColHeadings(boolean show)
setDisplayRowColHeadings
in interface Sheet
show
- whether to show RowColHeadings or notpublic boolean isDisplayRowColHeadings()
isDisplayRowColHeadings
in interface Sheet
public void setRowBreak(int row)
setRowBreak
in interface Sheet
row
- FIXME: Document this!public boolean isRowBroken(int row)
isRowBroken
in interface Sheet
row
- The row to checkpublic void removeRowBreak(int row)
removeRowBreak
in interface Sheet
row
- The row to remove page breaks frompublic int[] getRowBreaks()
getRowBreaks
in interface Sheet
public int[] getColumnBreaks()
getColumnBreaks
in interface Sheet
public void setColumnBreak(int column)
setColumnBreak
in interface Sheet
column
- The column to work onpublic boolean isColumnBroken(int column)
isColumnBroken
in interface Sheet
column
- The column to check for page breakspublic void removeColumnBreak(int column)
removeColumnBreak
in interface Sheet
column
- The column to remove a page break frompublic void setColumnGroupCollapsed(int columnNumber, boolean collapsed)
setColumnGroupCollapsed
in interface Sheet
columnNumber
- One of the columns in the group.collapsed
- true = collapse group, false = expand group.public void groupColumn(int fromColumn, int toColumn)
groupColumn
in interface Sheet
fromColumn
- beginning of the column range.toColumn
- end of the column range.public void ungroupColumn(int fromColumn, int toColumn)
ungroupColumn
in interface Sheet
fromColumn
- start column (0-based)toColumn
- end column (0-based)public void groupRow(int fromRow, int toRow)
Please note the rows being grouped must be in the current window, if the rows are already flushed then groupRow has no effect.
Correct code:
Workbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory
Sheet sh = wb.createSheet();
for (int rownum = 0; rownum < 1000; rownum++) {
Row row = sh.createRow(rownum);
if(rownum == 200) {
sh.groupRow(100, 200);
}
}
Incorrect code:
Workbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory
Sheet sh = wb.createSheet();
for (int rownum = 0; rownum < 1000; rownum++) {
Row row = sh.createRow(rownum);
}
sh.groupRow(100, 200); // the rows in the range [100, 200] are already flushed and groupRows has no effect
public void setRowOutlineLevel(int rownum, int level)
groupRows requires all rows in the group to be in the current window. This is not always practical. Instead use setRowOutlineLevel to explicitly set the group level. Level 1 is the top level group, followed by 2, etc. It is up to the user to ensure that level 2 groups are correctly nested under level 1, etc.
rownum
- index of row to update (0-based)level
- outline level (greater than 0)public void ungroupRow(int fromRow, int toRow)
ungroupRow
in interface Sheet
fromRow
- start row (0-based)toRow
- end row (0-based)public void setRowGroupCollapsed(int row, boolean collapse)
setRowGroupCollapsed
in interface Sheet
row
- start row of a groupped range of rows (0-based)collapse
- whether to expand/collapse the detail rowsjava.lang.RuntimeException
- if collapse is false as this is not implemented for SXSSF.public void setDefaultColumnStyle(int column, CellStyle style)
setDefaultColumnStyle
in interface Sheet
column
- the column indexstyle
- the style to setpublic void trackColumnForAutoSizing(int column)
column
is already tracked, this call does nothing.column
- the column to track for auto-sizingtrackColumnsForAutoSizing(Collection)
,
trackAllColumnsForAutoSizing()
public void trackColumnsForAutoSizing(java.util.Collection<java.lang.Integer> columns)
columns
that are already tracked are ignored by this call.columns
- the columns to track for auto-sizingpublic void trackAllColumnsForAutoSizing()
public boolean untrackColumnForAutoSizing(int column)
column
is not tracked, it will be ignored by this call.column
- the index of the column to track for auto-sizinguntrackColumnsForAutoSizing(Collection)
,
untrackAllColumnsForAutoSizing()
public boolean untrackColumnsForAutoSizing(java.util.Collection<java.lang.Integer> columns)
columns
that is not tracked will be ignored by this call.columns
- the indices of the columns to track for auto-sizingpublic void untrackAllColumnsForAutoSizing()
public boolean isColumnTrackedForAutoSizing(int column)
column
- the index of the column to checkpublic java.util.Set<java.lang.Integer> getTrackedColumnsForAutoSizing()
public void autoSizeColumn(int column)
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.
Special note about SXSSF implementation: You must register the columns you wish to track with
the SXSSFSheet using trackColumnForAutoSizing(int)
or trackAllColumnsForAutoSizing()
.
This is needed because the rows needed to compute the column width may have fallen outside the
random access window and been flushed to disk.
Tracking columns is required even if all rows are in the random access window.
New in POI 3.14 beta 1: auto-sizes columns using cells from current and flushed rows.
autoSizeColumn
in interface Sheet
column
- the column index to auto-sizepublic void autoSizeColumn(int column, boolean useMergedCells)
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.
Special note about SXSSF implementation: You must register the columns you wish to track with
the SXSSFSheet using trackColumnForAutoSizing(int)
or trackAllColumnsForAutoSizing()
.
This is needed because the rows needed to compute the column width may have fallen outside the
random access window and been flushed to disk.
Tracking columns is required even if all rows are in the random access window.
New in POI 3.14 beta 1: auto-sizes columns using cells from current and flushed rows.
autoSizeColumn
in interface Sheet
column
- the column index to auto-sizeuseMergedCells
- whether to use the contents of merged cells when calculating the width of the columnpublic XSSFComment getCellComment(CellAddress ref)
getCellComment
in interface Sheet
null
if not foundpublic java.util.Map<CellAddress,XSSFComment> getCellComments()
getCellComments
in interface Sheet
public XSSFHyperlink getHyperlink(int row, int column)
getHyperlink
in interface Sheet
row
- The 0-base row numbercolumn
- The 0-based column numberpublic XSSFHyperlink getHyperlink(CellAddress addr)
getHyperlink
in interface Sheet
addr
- The address of the cell containing the hyperlinkaddr
; otherwise returns null
public java.util.List<XSSFHyperlink> getHyperlinkList()
getHyperlinkList
in interface Sheet
public XSSFDrawing getDrawingPatriarch()
Sheet.createDrawingPatriarch()
to get or creategetDrawingPatriarch
in interface Sheet
public SXSSFDrawing createDrawingPatriarch()
createDrawingPatriarch
in interface Sheet
public SXSSFWorkbook getWorkbook()
getWorkbook
in interface Sheet
public java.lang.String getSheetName()
getSheetName
in interface Sheet
public boolean isSelected()
isSelected
in interface Sheet
true
if this sheet is currently selectedpublic CellRange<? extends Cell> setArrayFormula(java.lang.String formula, CellRangeAddress range)
setArrayFormula
in interface Sheet
formula
- text representation of the formularange
- Region of array formula for result.CellRange
of cells affected by this changepublic CellRange<? extends Cell> removeArrayFormula(Cell cell)
removeArrayFormula
in interface Sheet
cell
- any cell within Array Formula rangeCellRange
of cells affected by this changepublic DataValidationHelper getDataValidationHelper()
getDataValidationHelper
in interface Sheet
public java.util.List<XSSFDataValidation> getDataValidations()
Sheet
getDataValidations
in interface Sheet
public void addValidationData(DataValidation dataValidation)
addValidationData
in interface Sheet
dataValidation
- The Data validation object settingspublic AutoFilter setAutoFilter(CellRangeAddress range)
setAutoFilter
in interface Sheet
range
- the range of cells to filterpublic SheetConditionalFormatting getSheetConditionalFormatting()
Sheet
getSheetConditionalFormatting
in interface Sheet
public CellRangeAddress getRepeatingRows()
Sheet
Repeating rows cover a range of contiguous rows, e.g.:
Sheet1!$1:$1 Sheet2!$5:$8The
CellRangeAddress
returned 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.
getRepeatingRows
in interface Sheet
CellRangeAddress
containing the repeating rows for the
Sheet, or null.public CellRangeAddress getRepeatingColumns()
Sheet
Repeating columns cover a range of contiguous columns, e.g.:
Sheet1!$A:$A Sheet2!$C:$FThe
CellRangeAddress
returned 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.
getRepeatingColumns
in interface Sheet
CellRangeAddress
containing the repeating columns for
the Sheet, or null.public void setRepeatingRows(CellRangeAddress rowRangeRef)
Sheet
Repeating rows cover a range of contiguous rows, e.g.:
Sheet1!$1:$1 Sheet2!$5:$8The parameter
CellRangeAddress
should 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);
setRepeatingRows
in interface Sheet
rowRangeRef
- a CellRangeAddress
containing the repeating
rows for the Sheet, or null.public void setRepeatingColumns(CellRangeAddress columnRangeRef)
Sheet
Repeating columns cover a range of contiguous columns, e.g.:
Sheet1!$A:$A Sheet2!$C:$FThe parameter
CellRangeAddress
should 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);
setRepeatingColumns
in interface Sheet
columnRangeRef
- a CellRangeAddress
containing the repeating
columns for the Sheet, or null.public void setRandomAccessWindowSize(int value)
public boolean areAllRowsFlushed()
public int getLastFlushedRowNum()
public void flushRows(int remaining) throws java.io.IOException
java.io.IOException
public void flushRows() throws java.io.IOException
java.io.IOException
- If an I/O error occurspublic void changeRowNum(SXSSFRow row, int newRowNum)
public int getRowNum(SXSSFRow row)
public int getColumnOutlineLevel(int columnIndex)
Sheet
getColumnOutlineLevel
in interface Sheet
public CellAddress getActiveCell()
A1
.getActiveCell
in interface Sheet
public void setActiveCell(CellAddress address)
setActiveCell
in interface Sheet
address
- the location of the active cell, e.g. A1
.public XSSFColor getTabColor()
public void setTabColor(XSSFColor color)
public void enableLocking()
public void disableLocking()
public void lockAutoFilter(boolean enabled)
disableLocking()
or enableLocking()
public void lockDeleteColumns(boolean enabled)
disableLocking()
or enableLocking()
public void lockDeleteRows(boolean enabled)
disableLocking()
or enableLocking()
public void lockFormatCells(boolean enabled)
disableLocking()
or enableLocking()
public void lockFormatColumns(boolean enabled)
disableLocking()
or enableLocking()
public void lockFormatRows(boolean enabled)
disableLocking()
or enableLocking()
public void lockInsertColumns(boolean enabled)
disableLocking()
or enableLocking()
public void lockInsertHyperlinks(boolean enabled)
disableLocking()
or enableLocking()
public void lockInsertRows(boolean enabled)
disableLocking()
or enableLocking()
public void lockPivotTables(boolean enabled)
disableLocking()
or enableLocking()
public void lockSort(boolean enabled)
disableLocking()
or enableLocking()
public void lockObjects(boolean enabled)
disableLocking()
or enableLocking()
public void lockScenarios(boolean enabled)
disableLocking()
or enableLocking()
public void lockSelectLockedCells(boolean enabled)
disableLocking()
or enableLocking()
public void lockSelectUnlockedCells(boolean enabled)
disableLocking()
or enableLocking()
public void setTabColor(int colorIndex)
colorIndex
- the indexed color to set, must be a constant from IndexedColors
@NotImplemented public void shiftColumns(int startColumn, int endColumn, int n)
Sheet
shiftColumns
in interface Sheet
startColumn
- the column to start shiftingendColumn
- the column to end shiftingn
- the number of columns to shiftCopyright 2020 The Apache Software Foundation or its licensors, as applicable.