Class ExcelWorksheet
- java.lang.Object
- com.gembox.spreadsheet.ExcelWorksheet
public final class ExcelWorksheet
Excel worksheet is a table with additional properties, identified by a unique name.
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 ExcelWorksheet#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.");
Method Summary
Modifer 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. Named ranges are used to represent cells, ranges of cells, formulas or constant values. |
void | addNamedRange(String name, CellRange range, boolean globalName) Adds a new named range to this worksheet. Named ranges are used to represent cells, ranges of cells, formulas or constant values. |
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. Supported in XLSX and XLS only. |
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). Not supported in ODS. |
ExcelEmbeddedObjectCollection | getEmbeddedObjects() Gets the worksheet embedded objects. Supported only in XLSX. |
AutoFilter | getFilter() Gets the active AutoFilter. Returns |
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. Supported picture formats are listed in ExcelPictureFormat. |
PivotTableCollection | getPivotTables() Gets the collection of all PivotTables contained in this worksheet. |
ExcelPrintOptions | getPrintOptions() Contains MS Excel print and print related options. |
ProtectedRangeCollection | getProtectedRanges() Gets the protected ranges. Supported in XLSX only. |
WorksheetProtection | getProtectionSettings() Gets worksheet protection properties. This settings will be used only if ExcelWorksheet#isProtected is |
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. Returns |
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 Tables 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). Not supported in ODS. |
void | setFilter(AutoFilter value) Gets the active AutoFilter. Returns |
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. Returns |
void | setTabColor(SpreadsheetColor value) Sets the worksheet tab color. |
void | setVisibility(SheetVisibility visibility) Sets the worksheet visibility. |
Method Detail
addDataValidation
public boolean addDataValidation(DataValidation dataValidation)
Add a data validation to this worksheet.
This method has the same effect as using worksheet.#getDataValidations() () getDataValidations()}.add(dataValidation)
- Parameters:
- dataValidation -
Add a data validation to this worksheet.
addNamedRange
public void addNamedRange(String name, CellRange range)
Adds a new named range to this worksheet. Named ranges are used to represent cells, ranges of cells, formulas or constant values.
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.
- Parameters:
- name -
The user-defined name.
- range -
The range to be referred by name.
addNamedRange
public void addNamedRange(String name, CellRange range, boolean globalName)
Adds a new named range to this worksheet. Named ranges are used to represent cells, ranges of cells, formulas or constant values.
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.
- Parameters:
- name -
The user-defined name.
- range -
The range to be referred by name.
- globalName -
sets the range as global if set to true
addPivotTable
public PivotTable addPivotTable(PivotCache pivotCache, String tableName, ExcelCell topLeftCell)
Adds a new 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)}
- Parameters:
- 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.
- Returns:
Newly created PivotTable.
addPivotTable
public PivotTable addPivotTable(PivotCache pivotCache, String tableName, String cellReference)
Adds a new PivotTable to the PivotTableCollection of this worksheet.
This method has the same effect as using worksheet.getPivotTables().add(pivotCache, tableName, cellReference)
- Parameters:
- 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.
- Returns:
Newly created PivotTable.
addTable
public Table addTable(String name, CellRange range, boolean hasHeaders)
Adds a new 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.
addTable
public Table addTable(String name, String range, boolean hasHeaders)
Adds a new 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.
calculate
public void calculate()
Calculates formula for every excel cell in this worksheet.
calculateMaxUsedColumns
public int calculateMaxUsedColumns()
Iterates all rows and finds maximum number of used columns.
- Returns:
Maximum number of occupied columns in this sheet.
clear
public void clear()
Clears this worksheet.
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
- Each ExcelRow instance in #getRows
- Each ExcelColumn instance in #getColumns
- Each ExcelCell instance in #getCells
- Each ExcelPicture instance in #getPictures
- Each ExcelChart instance in #getCharts
- Each HorizontalPageBreak instance in #getHorizontalPageBreaks
- Each VerticalPageBreak instance in #getVerticalPageBreaks()
- Each NamedRange instance in #getNamedRanges
- Each SpreadsheetHyperlink instance in #getHyperlinks
deleteBackgroundPicture
public void deleteBackgroundPicture()
Removes the worksheet background picture.
getBackgroundPictureFormat
public ExcelPictureFormat getBackgroundPictureFormat()
Gets the worksheet background picture format.
- Returns:
The worksheet background picture format.
getBackgroundPictureStream
public InputStream getBackgroundPictureStream()
Gets the worksheet background picture stream.
- Returns:
The worksheet background picture stream.
getCell
public final ExcelCell getCell(int rowIndex, int columnIndex)
Gets excel cell at the specified position from this worksheet.
This method has the same effect as using worksheet.getCells().get(rowIndex, columnIndex)
- Parameters:
- rowIndex -
The zero-based relative row position.
- columnIndex -
The zero-based relative column position.
getCell
public final ExcelCell getCell(String contextName)
Gets excel cell with the specified full name from this worksheet.
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.).
- Parameters:
- contextName -
Full name of the cell.
getCells
public CellRange getCells()
Gets all the cells in the sheet.
- Returns:
All the cells in the sheet.
getCharts
public ExcelChartCollection getCharts()
Gets the worksheet charts.
- Returns:
The worksheet charts.
getColumn
public ExcelColumn getColumn(int index)
Gets the column with the specified index from this worksheet.
This method has the same effect as using worksheet.getColumns().get(index)
- Parameters:
- index -
The zero-based index of the column.
getColumn
public ExcelColumn getColumn(String name)
Gets the column with the specified name from this worksheet.
This method has the same effect as using worksheet.getColumns().get(name)
- Parameters:
- name -
The name of the column.
getColumns
public ExcelColumnCollection getColumns()
Gets collection of all columns (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 ExcelWorksheet#calculateMaxUsedColumns method.
getConditionalFormatting
public ConditionalFormatRuleCollection getConditionalFormatting()
Gets the conditional formatting rules applied on cell ranges of this sheet.
Currently supported in XLSX and XLS only.
- Returns:
The conditional formatting rules applied on cell ranges of this sheet.
getDataValidations
public DataValidationCollection getDataValidations()
Gets the collection that expresses all data validation information for cells in a sheet which have data validation features applied. Supported in XLSX and XLS only.
getDefaultColumnWidth
public int getDefaultColumnWidth()
Gets the default column width in 1/256th of the width of the zero character in default font.
This value is used as width for columns which don't have column width explicitly set.
- Returns:
The default column width in 1/256th of the width of the zero character in default font.
getDefaultRowHeight
public int getDefaultRowHeight()
Gets the default row height in twips (1/20th of a point). Not supported in ODS.
This value is used as height for rows which don't have row height explicitly set.
- Returns:
The default row height in twips (1/20th of a point).
getEmbeddedObjects
public ExcelEmbeddedObjectCollection getEmbeddedObjects()
Gets the worksheet embedded objects. Supported only in XLSX.
- Returns:
The worksheet embedded objects.
getFilter
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.
- Returns:
The active AutoFilter or
null
if no AutoFilter is active.
getHorizontalPageBreaks
public HorizontalPageBreakCollection getHorizontalPageBreaks()
Gets collection of all horizontal page breaks (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);
getHyperlinks
public SpreadsheetHyperlinkCollection getHyperlinks()
Gets hyperlinks in this sheet.
Supported only in xlsx and xls.
getIgnoredErrors
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.
- Returns:
The ignored errors applied on cell ranges of this sheet.
getIndex
public int getIndex()
Gets worksheet zero-based index.
getNamedRange
public NamedRange getNamedRange(int index)
Gets the NamedRange at the specified index from this worksheet.
This method has the same effect as using worksheet.getNamedRanges().get(index)
- Parameters:
- index -
Range index.
getNamedRange
public NamedRange getNamedRange(String name)
Gets the NamedRange with the specified name from this worksheet.
This method has the same effect as using worksheet.getNamedRanges().get(name)
- Parameters:
- name -
Range name.
getNamedRanges
public NamedRangeCollection getNamedRanges()
Gets 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.
getPanes
public WorksheetPanes getPanes()
Gets the panes in the worksheet.
If there are no split or freeze panes in the worksheet, the value is null.
getPictures
public ExcelPictureCollection getPictures()
Gets the worksheet pictures. Supported picture formats are listed in ExcelPictureFormat.
- Returns:
The worksheet pictures.
getPivotTables
public PivotTableCollection getPivotTables()
Gets the collection of all PivotTables 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.
- External - database, OLAP cube, textual file, web query, etc.
- Other - multiple consolidation ranges in the workbook, another Pivot 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:
- Scheme - data source fields/columns, its data types, etc.
- Data - actual data source records.
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:
- Report Filter Area represented by PivotTable#getPageFields collection.
- Row Axis represented by PivotTable#getRowFields collection.
- Column Axis represented by PivotTable#getColumnFields collection.
- Values Area represented by PivotTable#getDataFields collection.
Each (non-calculated) PivotField can contain zero or more PivotItems 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 PivotFields and calculated PivotItems 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 PivotFields. 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 PivotTables which reference the same PivotTable#getPivotCache.
Since adding/removing calculated PivotFields means changing the data scheme, which is defined in PivotCache, actual modification is made in PivotCache and propagated to all PivotTables which are referencing it.
PivotFields can be re-ordered in a specific area using the PivotAreaFieldCollection#move(int, int) method. PivotItems 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 PivotFields, PivotItems 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:
- Relative difference of average salary between GemBox and other companies grouped over departments (and genders).
- Relative difference of max salary between GemBox and other companies grouped over departments (and genders).
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
- Returns:
The collection of all PivotTables contained in this worksheet.
getPrintOptions
public ExcelPrintOptions getPrintOptions()
Contains MS Excel print and print related options.
getProtectedRanges
public ProtectedRangeCollection getProtectedRanges()
Gets the protected ranges. Supported in XLSX only.
- Returns:
The protected ranges.
getProtectionSettings
public WorksheetProtection getProtectionSettings()
Gets worksheet protection properties. This settings will be used only if ExcelWorksheet#isProtected is true
; Otherwise it will be ignored. Supported in XLSX only.
getRow
public ExcelRow getRow(int index)
Gets the row with the specified index from this worksheet.
This method has the same effect as using worksheet.getRows().get(index)
- Parameters:
- index -
The zero-based index of the row.
getSelectedRanges
public CellRangeCollection getSelectedRanges()
Gets the selected ranges of cells in the worksheet.
Supported only in XLSX.
- Returns:
The selected ranges of cells in the worksheet.
getSort
public SortState getSort()
getTabColor
public SpreadsheetColor getTabColor()
Gets the worksheet tab color.
Supported only in XLSX and HTML.
- Returns:
The worksheet tab color.
getTables
public TableCollection getTables()
Gets the collection of all Tables contained in this worksheet.
- Returns:
The collection of all Tables contained in this worksheet.
getUsedCellRange
public CellRange getUsedCellRange(boolean ignoreCellsWithoutValue)
Calculates cell range that is used in the worksheet.
- Parameters:
- ignoreCellsWithoutValue -
Specifies if allocated cells with ExcelCell#getValue set to
null
are ignored. - Returns:
Cell range that is used in the worksheet.
getVerticalPageBreaks
public VerticalPageBreakCollection getVerticalPageBreaks()
Gets collection of all vertical page breaks (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);
getViewOptions
public ExcelViewOptions getViewOptions()
Contains MS Excel display and view related options.
getVisibility
public SheetVisibility getVisibility()
Gets the worksheet visibility.
This value is available only in XLSX file format.
hasHeadersFooters
public boolean hasHeadersFooters()
Returns true if worksheet has headers/footers.
hasSplitOrFreezePanes
public boolean hasSplitOrFreezePanes()
Returns true if worksheet has split or freeze panes.
isFilterMode
public boolean isFilterMode()
Gets a value indicating whether the worksheet is in filter mode.
Supported only in XLSX.
- Returns:
true
if the worksheet is in filter mode; otherwise,false
.
isProtected
public boolean isProtected()
Gets the worksheet protection flag.
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.
removeColumn
public void removeColumn(int columnIndex)
Removes the column with the specified index from this worksheet.
This method has the same effect as using worksheet.getColumns().remove(index)
- Parameters:
- columnIndex -
The zero-based index of the column.
removeRow
public void removeRow(int rowIndex)
Removes the row with the specified index from this worksheet.
This method has the same effect as using worksheet.getRows().get(rowIndex)
- Parameters:
- rowIndex -
The zero-based index of the row.
setBackgroundPicture
public void setBackgroundPicture(InputStream pictureStream, ExcelPictureFormat pictureFormat)
Sets the worksheet background picture.
- Parameters:
- pictureStream -
The picture's stream.
- pictureFormat -
The picture's format.
- Throws:
- IOException
setBackgroundPicture
public void setBackgroundPicture(String picturePath)
Sets the worksheet background picture.
- Parameters:
- picturePath -
The picture's path.
- Throws:
- IOException
setDefaultColumnWidth
public void setDefaultColumnWidth(int defaultColumnWidth)
Sets the default column width in 1/256th of the width of the zero character in default font.
This value is used as width for columns which don't have column width explicitly set.
- Parameters:
- defaultColumnWidth -
The default column width in 1/256th of the width of the zero character in default font.
setDefaultRowHeight
public void setDefaultRowHeight(int defaultRowHeight)
Gets the default row height in twips (1/20th of a point). Not supported in ODS.
This value is used as height for rows which don't have row height explicitly set.
- Parameters:
- defaultRowHeight -
The default row height in twips (1/20th of a point).
setFilter
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.
- Parameters:
- value -
The active AutoFilter or
null
if no AutoFilter is active.
setHeadersFooters
public void setHeadersFooters(SheetHeaderFooter value)
Sets headers/footers for the sheet.
- Parameters:
- value -
Sets headers/footers for the sheet.
setName
public void setName(String value)
Sets worksheet name.
If not unique (worksheet with that name already exists in ExcelFile.getWorksheets collection) exception is thrown.
- Parameters:
- value -
Sets worksheet name.
setPanes
public void setPanes(WorksheetPanes value)
Sets the panes in the worksheet.
- Parameters:
- value -
Sets the panes in the worksheet.
setProtected
public void setProtected(boolean isProtected)
Sets the worksheet protection flag.
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.
- Parameters:
- isProtected -
Sets the worksheet protection flag.
setSort
public void setSort(SortState value)
setTabColor
public void setTabColor(SpreadsheetColor value)
Sets the worksheet tab color.
Supported only in XLSX and HTML.
- Parameters:
- value -
The worksheet tab color.
setVisibility
public void setVisibility(SheetVisibility visibility)
Sets the worksheet visibility.
Workbook must contain at least one visible sheet.
This value is available only in XLSX file format.
- Parameters:
- visibility -
Sets the worksheet visibility.