com.gembox.spreadsheet

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

All Methods
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 `null` if no AutoFilter is active.
`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 `true`; Otherwise it will be ignored. Supported in XLSX only.
`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 `null` if no SortState is active.
`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 `null` if no AutoFilter is active.
`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 `null` if no SortState is active.
`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.

Parameters:: name -
The name of the Table.; range -
The Table cell range.; hasHeaders -
Specifies whether the range contains a header row.
Returns:: Newly added Table.

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.

Parameters:: name -
The name of the Table.; range -
The Table cell range.; hasHeaders -
Specifies whether the range contains a header row.
Returns:: Newly added Table.

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.

getComments

public ExcelCommentCollection getComments()

Gets comments in this sheet.

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.

getHeadersFooters

public SheetHeaderFooter getHeadersFooters()

Gets headers/footers for the sheet.

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.

getName

public String getName()

Gets worksheet name.

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.

getParent

public ExcelFile getParent()

Gets the parent ExcelFile of this ExcelWorksheet instance.

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.

getRow

public ExcelRow getRow(String name)

Gets the row with the specified name from this worksheet.

This method has the same effect as using worksheet.getRows().get(name)

Parameters:: name -
The name of the row.

getRows

public ExcelRowCollection getRows()

Gets collection of all rows (ExcelRow) in the worksheet.

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

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.

Returns:: The active SortState or null if no SortState is active.

getTabColor

public SpreadsheetColor getTabColor()

Gets the worksheet tab color.

Supported only in XLSX and HTML.

Returns:: The worksheet tab color.

getTable

public Table getTable(int index)

Gets the Table at the specified index from the collection of this worksheet.

This method has the same effect as using worksheet.getTables().get(index)

Parameters:: index -
The zero-based index of the Table.
Returns:: The Table at the specified index.

getTable

public Table getTable(String name)

Gets Table described by name from the collection of this worksheet.

This method has the same effect as using worksheet.getTables().get(name)

Parameters:: name -
Name of the table.
Returns:: The Table with the specified name.

getTables

public TableCollection getTables()

Gets the collection of all Tables contained in this worksheet.

Returns:: The collection of all Tables contained in this worksheet.

getType

public SheetType getType()

Gets worksheet type.

Returns:: The worksheet type.

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)

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.

Parameters:: value -
Sets the active SortState. Returns null if no SortState is active.

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.