public final class ExcelWorksheet extends Object
Worksheet in Microsoft Excel has limited size.
A specific cell (ExcelCell
) can be accessed either trough
getCell(int, int)
, getCell(String)
,
ExcelRow.getCells
,
ExcelColumn.getCells
or
ExcelWorksheet.getCells
method.
Whichever method used, there are two distinct methods of getting a cell reference; using name
and using index. For example, full name of cell in top left corner of a worksheet is "A1". Translated
to indexes, same cell would be 0,0 (zero row and zero column). If using
ExcelRow.getCells
or
ExcelColumn.getCells
to access a
specific cell, only partial name or partial index must be used, providing unknown column or row information.
Note that the cells are internally allocated in rows and not in columns. ExcelColumn objects are created only if they have non-standard width or style, or they are accessed directly. So, while ExcelRowCollection.size shows number of rows occupied with data, ExcelColumnCollection.size does not say which Column is the last one occupied with data!
If you want to read all data in a sheet, use
ExcelRow.getAllocatedCells()
method.
If you want to find last column occupied with data, use
calculateMaxUsedColumns()
method.
Look at following code for cell referencing examples:
ExcelWorksheet ws = excelFile.getWorksheets().getActiveWorksheet();
ws.getCell("B2").setValue("Cell B2.");
ws.getCell(6, 0).setValue("Cell in row 7 and column A.");
ws.getRow(2).getCell(0).setValue("Cell in row 3 and column A.");
ws.getRow("4").getCell("B").setValue("Cell in row 4 and column B.");
ws.getColumn(2).getCell(4).setValue("Cell in column C and row 5.");
ws.getColumn("AA").getCell("6").setValue("Cell in AA column and row 6.");
ExcelRow
,
ExcelColumn
,
ExcelCell
Modifier and Type | Method and Description |
---|---|
boolean |
addDataValidation(DataValidation dataValidation)
Add a data validation to this worksheet.
|
void |
addNamedRange(String name,
CellRange range)
Adds a new named range to this worksheet.
|
void |
addNamedRange(String name,
CellRange range,
boolean globalName)
Adds a new named range to this worksheet.
|
PivotTable |
addPivotTable(PivotCache pivotCache,
String tableName,
ExcelCell topLeftCell)
Adds a new
PivotTable to the PivotTableCollection of this worksheet. |
PivotTable |
addPivotTable(PivotCache pivotCache,
String tableName,
String cellReference)
Adds a new
PivotTable to the PivotTableCollection of this worksheet. |
Table |
addTable(String name,
CellRange range,
boolean hasHeaders)
Adds a new
Table to the table collection of this worksheet. |
Table |
addTable(String name,
String range,
boolean hasHeaders)
Adds a new
Table to the table collection of this worksheet. |
void |
calculate()
Calculates formula for every excel cell in this worksheet.
|
int |
calculateMaxUsedColumns()
Iterates all rows and finds maximum number of used columns.
|
void |
clear()
Clears this worksheet.
|
void |
deleteBackgroundPicture()
Removes the worksheet background picture.
|
ExcelPictureFormat |
getBackgroundPictureFormat()
Gets the worksheet background picture format.
|
InputStream |
getBackgroundPictureStream()
Gets the worksheet background picture stream.
|
ExcelCell |
getCell(int rowIndex,
int columnIndex)
Gets excel cell at the specified position from this worksheet.
|
ExcelCell |
getCell(String contextName)
Gets excel cell with the specified full name from this worksheet.
|
CellRange |
getCells()
Gets all the cells in the sheet.
|
ExcelChartCollection |
getCharts()
Gets the worksheet charts.
|
ExcelColumn |
getColumn(int index)
Gets the column with the specified index from this worksheet.
|
ExcelColumn |
getColumn(String name)
Gets the column with the specified name from this worksheet.
|
ExcelColumnCollection |
getColumns()
Gets collection of all columns (
ExcelColumn ) in the worksheet. |
ExcelCommentCollection |
getComments()
Gets comments in this sheet.
|
ConditionalFormatRuleCollection |
getConditionalFormatting()
Gets the conditional formatting rules applied on cell ranges of this sheet.
|
DataValidationCollection |
getDataValidations()
Gets the collection that expresses all data validation information for cells in a sheet which have data validation features applied.
|
int |
getDefaultColumnWidth()
Gets the default column width in 1/256th of the width of the zero character in default font.
|
int |
getDefaultRowHeight()
Gets the default row height in twips (1/20th of a point).
|
ExcelEmbeddedObjectCollection |
getEmbeddedObjects()
Gets the worksheet embedded objects.
|
AutoFilter |
getFilter()
Gets the active
AutoFilter . |
SheetHeaderFooter |
getHeadersFooters()
Gets headers/footers for the sheet.
|
HorizontalPageBreakCollection |
getHorizontalPageBreaks()
Gets collection of all horizontal page breaks
(
HorizontalPageBreak ) in the worksheet. |
SpreadsheetHyperlinkCollection |
getHyperlinks()
Gets hyperlinks in this sheet.
|
IgnoredErrorCollection |
getIgnoredErrors()
Gets the ignored errors applied on cell ranges of this sheet.
|
int |
getIndex()
Gets worksheet zero-based index.
|
String |
getName()
Gets worksheet name.
|
NamedRange |
getNamedRange(int index)
Gets the
NamedRange at the specified index from this worksheet. |
NamedRange |
getNamedRange(String name)
Gets the
NamedRange with the specified name from this worksheet. |
NamedRangeCollection |
getNamedRanges()
Gets
NamedRangeCollection
containing descriptive names which are used to represent cells, ranges of cells,
formulas, or constant values. |
WorksheetPanes |
getPanes()
Gets the panes in the worksheet.
|
ExcelFile |
getParent()
Gets the parent
ExcelFile of this ExcelWorksheet instance. |
ExcelPictureCollection |
getPictures()
Gets the worksheet pictures.
|
PivotTableCollection |
getPivotTables()
Gets the collection of all
PivotTable s contained in this worksheet. |
ExcelPrintOptions |
getPrintOptions()
Contains MS Excel print and print related options.
|
ProtectedRangeCollection |
getProtectedRanges()
Gets the protected ranges.
|
WorksheetProtection |
getProtectionSettings()
Gets worksheet protection properties.
|
ExcelRow |
getRow(int index)
Gets the row with the specified index from this worksheet.
|
ExcelRow |
getRow(String name)
Gets the row with the specified name from this worksheet.
|
ExcelRowCollection |
getRows()
Gets collection of all rows (
ExcelRow ) in the worksheet. |
CellRangeCollection |
getSelectedRanges()
Gets the selected ranges of cells in the worksheet.
|
SortState |
getSort()
Gets the active
SortState . |
SpreadsheetColor |
getTabColor()
Gets the worksheet tab color.
|
Table |
getTable(int index)
Gets the
Table at the specified index from the collection of this worksheet. |
Table |
getTable(String name)
Gets
Table described by name from the collection of this worksheet. |
TableCollection |
getTables()
Gets the collection of all
Table s contained in this worksheet. |
SheetType |
getType()
Gets worksheet type.
|
CellRange |
getUsedCellRange(boolean ignoreCellsWithoutValue)
Calculates cell range that is used in the worksheet.
|
VerticalPageBreakCollection |
getVerticalPageBreaks()
Gets collection of all vertical page breaks
(
VerticalPageBreak ) in the worksheet. |
ExcelViewOptions |
getViewOptions()
Contains MS Excel display and view related options.
|
SheetVisibility |
getVisibility()
Gets the worksheet visibility.
|
boolean |
hasHeadersFooters()
Returns true if worksheet has headers/footers.
|
boolean |
hasSplitOrFreezePanes()
Returns true if worksheet has split or freeze panes.
|
boolean |
isFilterMode()
Gets a value indicating whether the worksheet is in filter mode.
|
boolean |
isProtected()
Gets the worksheet protection flag.
|
void |
removeColumn(int columnIndex)
Removes the column with the specified index from this worksheet.
|
void |
removeRow(int rowIndex)
Removes the row with the specified index from this worksheet.
|
void |
setBackgroundPicture(InputStream pictureStream,
ExcelPictureFormat pictureFormat)
Sets the worksheet background picture.
|
void |
setBackgroundPicture(String picturePath)
Sets the worksheet background picture.
|
void |
setDefaultColumnWidth(int defaultColumnWidth)
Sets the default column width in 1/256th of the width of the zero character in default font.
|
void |
setDefaultRowHeight(int defaultRowHeight)
Gets the default row height in twips (1/20th of a point).
|
void |
setFilter(AutoFilter value)
Gets the active
AutoFilter . |
void |
setHeadersFooters(SheetHeaderFooter value)
Sets headers/footers for the sheet.
|
void |
setName(String value)
Sets worksheet name.
|
void |
setPanes(WorksheetPanes value)
Sets the panes in the worksheet.
|
void |
setProtected(boolean isProtected)
Sets the worksheet protection flag.
|
void |
setSort(SortState value)
Sets the active
SortState . |
void |
setTabColor(SpreadsheetColor value)
Sets the worksheet tab color.
|
void |
setVisibility(SheetVisibility visibility)
Sets the worksheet visibility.
|
public boolean addDataValidation(DataValidation dataValidation)
This method has the same effect as using worksheet.
getDataValidations()
() getDataValidations()}.add(dataValidation)
public void addNamedRange(String name, CellRange range)
This method has the same effect as using worksheet.
getNamedRanges()
.add(name, range)
You can use the labels of columns and rows on a worksheet to refer to the cells within those columns and rows. Or you can create descriptive names to represent cells, ranges of cells, formulas, or constant values. Labels can be used in formulas that refer to data on the same worksheet; if you want to represent a range on another worksheet, use a name. You can also create 3-D names that represent the same cell or range of cells across multiple worksheets.
name
- The user-defined name.range
- The range to be referred by name.ExcelCell formula
,
NamedRangeCollection.add(String, CellRange)
public void addNamedRange(String name, CellRange range, boolean globalName)
This method has the same effect as using worksheet.
getNamedRanges()
.add(name, range, globalName)
You can use the labels of columns and rows on a worksheet to refer to the cells within those columns and rows. Or you can create descriptive names to represent cells, ranges of cells, formulas, or constant values. Labels can be used in formulas that refer to data on the same worksheet; if you want to represent a range on another worksheet, use a name. You can also create 3-D names that represent the same cell or range of cells across multiple worksheets.
name
- The user-defined name.range
- The range to be referred by name.globalName
- sets the range as global if set to trueExcelCell formula
,
NamedRangeCollection.add(String, CellRange, boolean)
public PivotTable addPivotTable(PivotCache pivotCache, String tableName, ExcelCell topLeftCell)
PivotTable
to the PivotTableCollection of this worksheet.
This method has the same effect as using worksheet.
getPivotTables()
.PivotTableCollection.add(PivotCache, String, ExcelCell)
add(pivotCache, tableName, topLeftCell)}
pivotCache
- The PivotCache
on which the new PivotTable
is based.tableName
- The name of the new PivotTable
.topLeftCell
- The location where PivotTable
will be placed in the sheet.PivotTable
.NullPointerException
- pivotCache
can't be null
.IllegalArgumentException
- pivotCache
must belong to the parent workbook.IllegalArgumentException
- tableName
can't be null or empty string.NullPointerException
- topLeftCell
can't be null
.IllegalArgumentException
- topLeftCell
must belong to the parent sheet.PivotTableCollection.add(PivotCache, String, String)
,
Create Excel Pivot Tables in Javapublic PivotTable addPivotTable(PivotCache pivotCache, String tableName, String cellReference)
PivotTable
to the PivotTableCollection of this worksheet.
This method has the same effect as using worksheet.
getPivotTables()
.add(pivotCache, tableName, cellReference)
pivotCache
- The PivotCache
on which the new PivotTable
is based.tableName
- The name of the new PivotTable
.cellReference
- The location where PivotTable
will be placed in the sheet.PivotTable
.NullPointerException
- pivotCache
can't be null
.IllegalArgumentException
- pivotCache
must belong to the parent workbook.IllegalArgumentException
- tableName
can't be null
or empty string.IllegalArgumentException
- cellReference
can't be null
or empty string.PivotTableCollection.add(PivotCache, String, String)
,
Create Excel Pivot Tables in Javapublic Table addTable(String name, CellRange range, boolean hasHeaders)
Table
to the table collection of this worksheet.
This method has the same effect as using worksheet.
getTables()
.add(name, range, hasHeaders)
Method creates and adds a new table to the worksheet.
If hasHeaders
is true
first row in specified range will be used as header row; otherwise, table's range will be expanded by inserting a header row.
name
- The name of the Table
.range
- The Table
cell range.hasHeaders
- Specifies whether the range contains a header row.Table
.NullPointerException
- range
is null.IllegalArgumentException
- range
doesn't belong to the parent worksheet.IllegalStateException
- Table
or DefinedName
with same name already exists.TableCollection.add(String, CellRange, boolean)
public Table addTable(String name, String range, boolean hasHeaders)
Table
to the table collection of this worksheet.
This method has the same effect as using worksheet.
getTables()
.add(name, range, hasHeaders)
Method creates and adds a new table to the worksheet.
If hasHeaders
is true
first row in specified range will be used as header row; otherwise, table's range will be expanded by inserting a header row.
name
- The name of the Table
.range
- The Table
cell range.hasHeaders
- Specifies whether the range contains a header row.Table
.TableCollection.add(String, String, boolean)
public void calculate()
public int calculateMaxUsedColumns()
public void clear()
References to following members will become invalid after calling this method. If they are stored in external variables, you will need to get them again from worksheet.
For example:
DataValidationCollection dataValidations = worksheet.getDataValidations();
worksheet.clear();
// dataValidations variable is no longer associated with worksheet, you need to do following to reacquire it
dataValidations = worksheet.getDataValidations();
Here is the list:
getDataValidations()
ExcelRow
instance in getRows()
ExcelColumn
instance in getColumns()
ExcelCell
instance in getCells()
ExcelPicture
instance in getPictures()
ExcelChart
instance in getCharts()
HorizontalPageBreak
instance in getHorizontalPageBreaks()
VerticalPageBreak
instance in getVerticalPageBreaks()
NamedRange
instance in getNamedRanges()
SpreadsheetHyperlink
instance in getHyperlinks()
public void deleteBackgroundPicture()
public ExcelPictureFormat getBackgroundPictureFormat()
public InputStream getBackgroundPictureStream()
public final ExcelCell getCell(int rowIndex, int columnIndex)
This method has the same effect as using worksheet.
getCells()
.get(rowIndex, columnIndex)
rowIndex
- The zero-based relative row position.columnIndex
- The zero-based relative column position.CellRange.get(int, int)
public final ExcelCell getCell(String contextName)
This method has the same effect as using worksheet.
getCells()
.get(contextName)
RangeIndexingMode.Rectangular
full name of
the cell must be used (for example; "A1", "D7", etc.).
contextName
- Full name of the cell.CellRange.get(String)
public CellRange getCells()
public ExcelChartCollection getCharts()
public ExcelColumn getColumn(int index)
This method has the same effect as using worksheet.
getColumns()
.get(index)
index
- The zero-based index of the column.ExcelColumnCollection.get(int)
public ExcelColumn getColumn(String name)
This method has the same effect as using worksheet.
getColumns()
.get(name)
name
- The name of the column.ExcelRowColumnCollectionBaseGeneric.get(String)
public ExcelColumnCollection getColumns()
ExcelColumn
) in the worksheet.
Note that the cells are internally allocated in rows and not in columns. ExcelColumn objects are created only if they have non-standard width or style, or they are accessed directly. So, while ExcelRowCollection.Count shows number of rows occupied with data, ExcelColumnCollection.Count does not say which Column is the last one occupied with data!
If you want to read all data in a sheet, use
ExcelRow.getAllocatedCells
method.
If you want to find last column occupied with data, use
calculateMaxUsedColumns()
method.
public ExcelCommentCollection getComments()
public ConditionalFormatRuleCollection getConditionalFormatting()
Gets the conditional formatting rules applied on cell ranges of this sheet.
Currently supported in XLSX and XLS only.
public DataValidationCollection getDataValidations()
public int getDefaultColumnWidth()
This value is used as width for columns which don't have column width
explicitly set.
1/256th of the width of the zero character in default font
.public int getDefaultRowHeight()
This value is used as height for rows which don't have row height
explicitly set.
twips (1/20th of a point)
.ExcelRow.getHeight()
public ExcelEmbeddedObjectCollection getEmbeddedObjects()
public AutoFilter getFilter()
Gets the active AutoFilter
.
Returns null
if no AutoFilter
is active.
AutoFilter
is used to store filter settings and to apply filter on a range of cells.
Active AutoFilter
is loaded from and saved to XLSX file.
Use com.gembox.spreadsheet.CellRange.filter
methods to create or get an AutoFilter
instance.
AutoFilter
or null
if no AutoFilter
is active.public SheetHeaderFooter getHeadersFooters()
public HorizontalPageBreakCollection getHorizontalPageBreaks()
HorizontalPageBreak
) in the worksheet.
Following code demonstrates how to add PageBreaks to ExcelWorksheet.
int columnsPerPage = 6, rowsPerPage = 30;
int usedColumnsCount = ws.calculateMaxUsedColumns();
for (int i = columnsPerPage; i < usedColumnsCount; i += columnsPerPage)
ws.getVerticalPageBreaks().add(i);
for (int i = rowsPerPage; i < ws.getRows().size(); i += rowsPerPage)
ws.getHorizontalPageBreaks().add(i);
public SpreadsheetHyperlinkCollection getHyperlinks()
Supported only in xlsx and xls.
public IgnoredErrorCollection getIgnoredErrors()
Gets the ignored errors applied on cell ranges of this sheet.
Currently supported in XLSX.
A cell is considered to have an error condition when it meets one of the conditions specified in the IgnoredError.getErrorTypes()
member.
For example, if a cell is formatted as text but contains a numeric value, this is considered to be a potential error because the number won't be treated as a number, for example, in calculations.
Note that this is simply a guess by the implementing application, and a recommendation to the user.
Cells with the errors specified in IgnoredErrorTypes
may have perfectly valid reasons for being in such a state, for example a cell formatted as text which contains numeric Postal Codes or Order numbers.
It is useful to format these cells as text so that leading zeros remain as part of the value instead of being removed.
public int getIndex()
public String getName()
public NamedRange getNamedRange(int index)
NamedRange
at the specified index from this worksheet.
This method has the same effect as using worksheet.
getNamedRanges()
.get(index)
index
- Range index.NamedRangeCollection.get(int)
public NamedRange getNamedRange(String name)
NamedRange
with the specified name from this worksheet.
This method has the same effect as using worksheet.
getNamedRanges()
.get(name)
name
- Range name.NamedRangeCollection.get(String)
public NamedRangeCollection getNamedRanges()
NamedRangeCollection
containing descriptive names which are used to represent cells, ranges of cells,
formulas, or constant values.
You can use the labels of columns and rows on a worksheet to refer to the cells within those columns and rows. Or you can create descriptive names to represent cells, ranges of cells, formulas, or constant values. Labels can be used in formulas that refer to data on the same worksheet; if you want to represent a range on another worksheet, use a name. You can also create 3-D names that represent the same cell or range of cells across multiple worksheets.
public WorksheetPanes getPanes()
If there are no split or freeze panes in the worksheet, the value is null.
public ExcelFile getParent()
ExcelFile
of this ExcelWorksheet
instance.public ExcelPictureCollection getPictures()
ExcelPictureFormat
.for an example
,
ExcelPicture
public PivotTableCollection getPivotTables()
Gets the collection of all PivotTable
s contained in this worksheet.
Currently supported in XLSX only.
*Pivot Tables are used to arrange and aggregate complicated data and drill down on details.
Source of data for Pivot Table can be:
Worksheet
- cell range
, named range
, table, etc.
GemBox.Spreadsheet currently supports creating only worksheet source
.
All other sources are supported through preservation (to correctly round-trip to XLSX format).
Source of the data for Pivot Table contains following information:
Pivot Table does not directly reference its data source, but instead it references a cache of a data source. Cache of a data source contains an in-memory copy of data source scheme and, optionally, data and is stored in a workbook which may contain multiple caches of various sources of data.
Cache can be updated/refreshed from a data source automatically (for example, when opening a workbook) or on request. Cache is used to provide better performance of Pivot Table functionality which requires frequent access to data records and their values and to enable sharing the same data source between multiple Pivot Tables.
GemBox.Spreadsheet represents cache with a PivotCache
type.
Cache source is represented with a PivotCacheSource
type (and its sub-types) and can be accessed from a PivotCache.getCacheSource()
method.
PivotCache
instances are stored in a ExcelFile.getPivotCaches()
collection.
PivotCache
currently doesn't store the actual cached records (it stores only scheme - fields and their properties), but instead Microsoft Excel is instructed to update the record cache from the data source when opening a workbook.
PivotTable
is created from a PivotCache
with the specified name and the specified cell in the worksheet where it is placed using the PivotTableCollection.add(PivotCache, String, String)
methods.
PivotTable
contains the PivotFields
collection which PivotField
elements can be moved
into the following Pivot Table areas:
PivotTable.getPageFields()
collection.
PivotTable.getRowFields()
collection.
PivotTable.getColumnFields()
collection.
PivotTable.getDataFields()
collection.
Each (non-calculated) PivotField
can contain zero or more PivotItem
s which are used for filtering and grouping by a specific value.
Except using fields defined in Pivot Table data source (more precisely, in PivotTable.getPivotCache()
) which cannot be removed, Pivot Table can also define calculated PivotField
s and calculated PivotItem
s which use a formula to specify their values.
Calculated PivotField
can be added using a PivotFieldCollection.addCalculated(String, String)
method and removed using a PivotFieldCollection.removeCalculated(String)
method. Method PivotField.isCalculated()
tells if a field is calculated or is a field from the data source.
Calculated PivotField
is automatically moved to a Values Area (PivotTable.getDataFields()
collection) and only Values Area can contain calculated PivotField
s.
Calculated PivotItem
can be added using a PivotItemCollection.addCalculated(String, String)
method and removed using a PivotItemCollection.removeCalculated(String)
method. Method PivotItem.isCalculated()
tells if an item is calculated.
If calculated PivotField
is added/removed in one PivotTable
, then it is also added/removed from all other PivotTable
s which reference the same PivotTable.getPivotCache()
.
Since adding/removing calculated PivotField
s means changing the data scheme, which is defined in PivotCache
, actual modification is made in PivotCache
and propagated to all PivotTable
s which are referencing it.
PivotField
s can be re-ordered in a specific area using the PivotAreaFieldCollection.move(int, int)
method. PivotItem
s can be re-ordered using the PivotItemCollection.move(int, int)
method.
GemBox.Spreadsheet currently does not provide functionality to read/calculate the actual data from the PivotTable
.
It provides the functionality to arrange and customize PivotField
s, PivotItem
s and PivotTable
appearance.
Because of that PivotTable
data is not rendered/written to other output formats like HTML. When output XLSX file with Pivot Table is opened in Microsoft Excel application, its data is automatically retrieved and calculated from the data source by Microsoft Excel.
Following code demonstrates how to create and customize PivotTable
using GemBox.Spreadsheet component to get the following information out of the sample records:
This information might help you decide if working for GemBox is worth it if you are a man or a woman and in which department would it be the best to work (considering department salaries of other companies).
Input workbook can be downloaded from PivotTableData.xlsx
PivotTable
s contained in this worksheet.PivotTableCollection for an example on pivot tables
,
Create Excel Pivot Tables in Javapublic ExcelPrintOptions getPrintOptions()
public ProtectedRangeCollection getProtectedRanges()
public WorksheetProtection getProtectionSettings()
isProtected()
is true
;
Otherwise it will be ignored.
Supported in XLSX only.public ExcelRow getRow(int index)
This method has the same effect as using worksheet.
getRows()
.get(index)
index
- The zero-based index of the row.ExcelRowCollection.get(int)
public ExcelRow getRow(String name)
This method has the same effect as using worksheet.
getRows()
.get(name)
name
- The name of the row.ExcelRowColumnCollectionBaseGeneric.get(String)
public ExcelRowCollection getRows()
ExcelRow
) in the worksheet.public CellRangeCollection getSelectedRanges()
Gets the selected ranges of cells in the worksheet.
Supported only in XLSX.
public SortState getSort()
Gets the active SortState
.
Returns null
if no SortState
is active.
SortState
is used to store sort settings and to apply sort on a range of cells.
Active SortState
is loaded from and saved to XLSX file.
Use CellRange.sort(boolean)
method to create or get a SortState
instance.
SortState
or null
if no SortState
is active.SortState for an example
,
Excel Sorting examplepublic SpreadsheetColor getTabColor()
Gets the worksheet tab color.
Supported only in XLSX and HTML.
public Table getTable(int index)
Table
at the specified index from the collection of this worksheet.
This method has the same effect as using worksheet.
getTables()
.get(index)
index
- The zero-based index of the Table
.Table
at the specified index.TableCollection.get(int)
public Table getTable(String name)
Table
described by name from the collection of this worksheet.
This method has the same effect as using worksheet.
getTables()
.get(name)
name
- Name of the table.Table
with the specified name.TableCollection.get(String)
public TableCollection getTables()
Table
s contained in this worksheet.Table
s contained in this worksheet.public SheetType getType()
public CellRange getUsedCellRange(boolean ignoreCellsWithoutValue)
ignoreCellsWithoutValue
- Specifies if allocated cells with ExcelCell.getValue()
set to null
are ignored.public VerticalPageBreakCollection getVerticalPageBreaks()
VerticalPageBreak
) in the worksheet.
Following code demonstrates how to add PageBreaks to ExcelWorksheet.
int columnsPerPage = 6, rowsPerPage = 30;
int usedColumnsCount = ws.calculateMaxUsedColumns();
for (int i = columnsPerPage; i < usedColumnsCount; i += columnsPerPage)
ws.getVerticalPageBreaks().add(i);
for (int i = rowsPerPage; i < ws.getRows().size(); i += rowsPerPage)
ws.getHorizontalPageBreaks().add(i);
public ExcelViewOptions getViewOptions()
public SheetVisibility getVisibility()
This value is available only in XLSX file format.
public boolean hasHeadersFooters()
public boolean hasSplitOrFreezePanes()
public boolean isFilterMode()
Gets a value indicating whether the worksheet is in filter mode.
Supported only in XLSX.
true
if the worksheet is in filter mode; otherwise, false
.public boolean isProtected()
This value is simply written to Excel file and has no effect on the behavior of this library. For more information on worksheet protection, consult Microsoft Excel documentation.
ExcelFile.isProtected
public void removeColumn(int columnIndex)
This method has the same effect as using worksheet.
getColumns()
.remove(index)
columnIndex
- The zero-based index of the column.ExcelColumnCollection.remove(int)
public void removeRow(int rowIndex)
This method has the same effect as using worksheet.
getRows()
.get(rowIndex)
rowIndex
- The zero-based index of the row.ExcelRowCollection.remove(int)
public void setBackgroundPicture(InputStream pictureStream, ExcelPictureFormat pictureFormat) throws IOException
pictureStream
- The picture's stream.pictureFormat
- The picture's format.IOException
public void setBackgroundPicture(String picturePath) throws IOException
picturePath
- The picture's path.IOException
public void setDefaultColumnWidth(int defaultColumnWidth)
This value is used as width for columns which don't have column width
explicitly set.
defaultColumnWidth
- The default column width in 1/256th of the width of the zero character in default font
.ExcelColumn.getWidth()
public void setDefaultRowHeight(int defaultRowHeight)
This value is used as height for rows which don't have row height
explicitly set.
defaultRowHeight
- The default row height in twips (1/20th of a point)
.ExcelRow.getHeight()
public void setFilter(AutoFilter value)
Gets the active AutoFilter
.
Returns null
if no AutoFilter
is active.
AutoFilter
is used to store filter settings and to apply filter on a range of cells.
Active AutoFilter
is loaded from and saved to XLSX file.
Use CellRange.filter()
methods to create or get an AutoFilter
instance.
value
- The active AutoFilter
or null
if no AutoFilter
is active.IllegalArgumentException
- Filter range doesn't belong to this worksheet.AutoFilter for an example
,
Excel AutoFiltering examplepublic void setHeadersFooters(SheetHeaderFooter value)
public void setName(String value)
If not unique (worksheet with that name already exists in
ExcelFile.getWorksheets
collection) exception is thrown.
IllegalArgumentException
- Worksheet name is not unique.public void setPanes(WorksheetPanes value)
public void setProtected(boolean isProtected)
This value is simply written to Excel file and has no effect on the behavior of this library. For more information on worksheet protection, consult Microsoft Excel documentation.
ExcelFile.setProtected
public void setSort(SortState value)
Sets the active SortState
.
Returns null
if no SortState
is active.
SortState
is used to store sort settings and to apply sort on a range of cells.
Active SortState
is loaded from and saved to XLSX file.
Use CellRange.sort(boolean)
method to create or get a SortState
instance.
IllegalArgumentException
- Sort range doesn't belong to this worksheet.SortState for an example
,
Excel Sorting examplepublic void setTabColor(SpreadsheetColor value)
Sets the worksheet tab color.
Supported only in XLSX and HTML.
value
- The worksheet tab color.public void setVisibility(SheetVisibility visibility)
Workbook must contain at least one visible sheet.
This value is available only in XLSX file format.
© GemBox Ltd. — All rights reserved.