public final class ExcelFile extends Object
ExcelWorksheet
)
and workbook related properties and methods.Constructor and Description |
---|
ExcelFile()
Initializes an empty (no worksheets) instance of the ExcelFile class.
|
Modifier and Type | Method and Description |
---|---|
Style |
addStyle(String name)
Adds an empty
Style instance to the CellStyleCollection of this workbook. |
ExcelWorksheet |
addWorksheet(String worksheetName)
Adds an empty worksheet to the end of the worksheet collection of this workbook.
|
static char |
autoDetectCsvSeparator(InputStream stream)
Autodetects CSV separator.
|
static char |
autoDetectCsvSeparator(InputStreamReader sr,
int rowsToTest,
char[] possibleSeparators)
Autodetects CSV separator.
|
void |
calculate()
Calculates formula for every excel cell in all worksheets.
|
ExcelFile |
clone()
Makes a deep clone of this
ExcelFile instance. |
CalculationOptions |
getCalculationOptions()
Gets the calculation engine options.
|
DefinedNameCollection |
getDefinedNames()
Gets the collection of defined names that are not directly related with one cell range.
|
DocumentProperties |
getDocumentProperties()
Gets
DocumentProperties of this ExcelFile . |
int |
getGroupMethodsAffectedCellsLimit()
Maximum number of affected cells in group get method.
|
PivotCacheCollection |
getPivotCaches()
Gets the collection of
PivotCache s defined in this ExcelFile instance. |
WorkbookProtection |
getProtectionSettings()
Gets workbook protection properties.
|
Style |
getStyle(BuiltInCellStyleName styleName)
Gets the
Style from the styles of this workbook that corresponds to given BuiltInCellStyleName . |
Style |
getStyle(String styleName)
Gets the
Style from the styles of this workbook that corresponds to given name. |
CellStyleCollection |
getStyles()
|
ExcelWorksheet |
getWorksheet(int index)
Gets the worksheet from the worksheet collection of this workbook with the specified index.
|
ExcelWorksheet |
getWorksheet(String name)
Gets the worksheet from the worksheet collection of this workbook with the specified name.
|
ExcelWorksheetCollection |
getWorksheets()
Collection of all worksheets (
ExcelWorksheet ) in a workbook. |
boolean |
isAutomaticFormulaUpdate()
Gets a value indicating whether the formula address references are automatically updated.
|
boolean |
isProtected()
Gets the workbook protection flag.
|
boolean |
isUse1904DateSystem()
Gets whether 1904 date system is used.
|
static ExcelFile |
load(InputStream stream)
Loads a spreadsheet from the specified stream.
|
static ExcelFile |
load(InputStream stream,
LoadOptions options)
Loads a spreadsheet from the specified stream.
|
static ExcelFile |
load(Reader reader,
CsvLoadOptions options)
Loads a spreadsheet from CSV data using the specified reader.
|
static ExcelFile |
load(String path)
Loads a spreadsheet from a file with the specified path.
|
static ExcelFile |
load(String path,
LoadOptions options)
Loads a spreadsheet from a file with the specified path.
|
boolean |
removeStyle(BuiltInCellStyleName name)
Removes built-in style with the specified name from the workbook.
|
boolean |
removeStyle(String name)
Removes style with the specified name from the workbook.
|
boolean |
removeStyle(Style style)
Removes the specified style from the workbook.
|
void |
removeWorksheet(int worksheetIndex)
Removes the worksheet from the worksheet collection of this workbook with the specified index.
|
void |
removeWorksheet(String worksheetName)
Removes the worksheet from the worksheet collection of this workbook with the specified name.
|
void |
save(OutputStream stream,
SaveOptions options)
Saves the spreadsheet to the specified stream.
|
void |
save(String path)
Saves the spreadsheet to a file with the specified path.
|
void |
save(String path,
SaveOptions options)
Saves the spreadsheet to a file with the specified path.
|
void |
save(Writer writer,
CsvSaveOptions options)
Save the spreadsheet to CSV format using the specified writer.
|
void |
setAutomaticFormulaUpdate(boolean automaticFormulaUpdate)
Sets a value indicating whether the formula address references are automatically updated.
|
void |
setGroupMethodsAffectedCellsLimit(int value)
Maximum number of affected cells in group set methods.
|
void |
setProtected(boolean isProtected)
Sets the workbook protection flag.
|
void |
setUse1904DateSystem(boolean value)
Sets whether 1904 date system is used.
|
static boolean |
supportsType(Class type)
Gets a value indicating whether the objects of specified type can be assigned
to
ExcelCell's value field. |
public ExcelFile()
To add new worksheets to a blank file use one of addWorksheet(String)
, getWorksheets()
, ExcelWorksheetCollection.add(String)
or ExcelWorksheetCollection.add(SheetType, String)
methods.
To load / read a spreadsheet from a file or a stream, use static ExcelFile.load
methods.
To save / write a spreadsheet to a file or a stream use ExcelFile.save
methods.
public Style addStyle(String name)
Style
instance to the CellStyleCollection
of this workbook.
This method has the same effect as using file.
getStyles()
.add(name)
name
- The name of new Style
instance.Style
instance that is added to this collection.IllegalArgumentException
- Parameter name
cannot be null or empty.IllegalStateException
- Style with name name
is already contained in the workbook.CellStyleCollection.add(String)
public ExcelWorksheet addWorksheet(String worksheetName)
This method has the same effect as using file.
getWorksheets()
.add(worksheetName)
If this is the first worksheet added to the collection the
active worksheet
is set to this worksheet.
worksheetName
- Worksheet name.IllegalArgumentException
- Worksheet name is not unique.ExcelWorksheetCollection.add(String)
public static char autoDetectCsvSeparator(InputStream stream) throws IOException
This method counts the number of occurrences of the most common separators (',', ';', '\t') and returns one that has occurred the most times in first 5 rows.
stream
- Input Stream.IOException
public static char autoDetectCsvSeparator(InputStreamReader sr, int rowsToTest, char[] possibleSeparators) throws IOException
This method counts the number of occurrences of chars that are specified in the array possibleSeparators, and returns one that has occurred the most times in first rowsToTest rows of StreamReader sr.
sr
- Input StreamReader.rowsToTest
- Number of rows to test.possibleSeparators
- Chars that can be CSV separators.IOException
public void calculate()
public CalculationOptions getCalculationOptions()
public DefinedNameCollection getDefinedNames()
public DocumentProperties getDocumentProperties()
DocumentProperties
of this ExcelFile
.public int getGroupMethodsAffectedCellsLimit()
If user tries to modify all cells in a group which has more cells than specified limit, exception will be thrown. This property was introduced to prevent users from accidentally modifying millions of cells which results in a long delay, a large memory allocation and a big resulting file. You can set this limit to value which suits your needs (minimum is 5).
public PivotCacheCollection getPivotCaches()
Gets the collection of PivotCache
s defined in this ExcelFile
instance.
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 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
PivotCache
s defined in this ExcelFile
instance.PivotCacheCollection for examples on pivot tables
,
Create Excel Pivot Tables in Javapublic WorkbookProtection getProtectionSettings()
public Style getStyle(BuiltInCellStyleName styleName)
Style
from the styles of this workbook that corresponds to given BuiltInCellStyleName
.
This method has the same effect as using file.
getStyles()
.get(styleName)
If CellStyleCollection
doesn't contain correspondent style, then new style will be added and returned.
styleName
- The BuiltInCellStyleName
of the Style
.Style
that corresponds to given BuiltInCellStyleName
.CellStyleCollection.get(BuiltInCellStyleName)
public Style getStyle(String styleName)
Style
from the styles of this workbook that corresponds to given name.
This method has the same effect as using file.
getStyles()
.get(styleName)
If CellStyleCollection
doesn't contain correspondent style, then new style will be added and returned.
styleName
- The name of the Style
.Style
that corresponds to given name.CellStyleCollection.get(String)
public CellStyleCollection getStyles()
Style
s defined for this ExcelFile
instance.
Conceptually, cell formatting is divided into following groups:
CellStyle.setNumberFormat(java.lang.String)
.
CellStyle.setHorizontalAlignment(com.gembox.spreadsheet.HorizontalAlignmentStyle)
, CellStyle.setVerticalAlignment(com.gembox.spreadsheet.VerticalAlignmentStyle)
, CellStyle.setIndent(int)
, CellStyle.setRotation(int)
, CellStyle.setTextVertical(boolean)
, CellStyle.setWrapText(boolean)
and CellStyle.setShrinkToFit(boolean)
.
CellStyle.setFont(com.gembox.spreadsheet.ExcelFont)
.
CellStyle.setBorders(com.gembox.spreadsheet.CellBorders)
.
CellStyle.setFillPattern(com.gembox.spreadsheet.ExcelFillPattern)
.
CellStyle.setLocked(boolean)
and CellStyle.setFormulaHidden(boolean)
.
Additional CellStyle
methods not associated with any formatting group are:
CellStyle.getName()
- name of the referenced workbook style
.
CellStyle.setQuotePrefix(boolean)
- true
to store numeric value of a cell as text; otherwise, false
.
CellStyle.isDefault()
- true
if referenced
workbook style
is default (
CellStyleCollection.getNormal()
) and there are no additional modifications of cell formatting; otherwise, false
.
Workbook
contains a set of master styles
which can be referenced by multiple cells.
Workbook
must always contain at least one master style
which cannot be removed and is, by default, referenced by all cells. This default style is
CellStyleCollection.getNormal()
.
Workbook style
can either be built-in or user-defined. Built-in style is accessible from workbook styles
via BuiltInCellStyleName
enumeration.
Cell formatting group (Number, Alignment, Font, Border, Fill or Protection) (and its associated methods) is resolved from referenced
workbook (master) style
, unless cell formatting group or its associated method is modified.
Cell formatting is available for one or more cells through AbstractRange.setStyle(com.gembox.spreadsheet.CellStyle)
method which is available on ExcelCell
and CellRange
types.
Cell formatting specified on ExcelColumn
and ExcelRow
types through ExcelColumnRowBase.setStyle(com.gembox.spreadsheet.CellStyle)
method is simply propagated to cell formatting of its ExcelColumnRowBase.getCells()
.
For performance reasons, cell formatting on CellRange
is resolved based just on its top-left cell formatting, except borders which are resolved based on corner cells depending on border side.
Setting cell formatting method on CellRange
is propagated to each cell in a range.
To set workbook (master) style
to one or more cells, simply assign it to AbstractRange.setStyle(com.gembox.spreadsheet.CellStyle)
method.
Preferable way to modify formatting method of multiple cells is to get CellRange
to which all those cells belong, and use AbstractRange.setStyle(com.gembox.spreadsheet.CellStyle)
method of that range to make the modification.
If modifying multiple formatting properties of a CellRange
, without preserving unmodified formatting properties, preferable way is to create new instance of CellStyle
, make modifications on it, and assign it using AbstractRange.setStyle(com.gembox.spreadsheet.CellStyle)
method of that range.
GemBox.Spreadsheet internally takes care not to allocate unnecessary cells when formatting a range of cells (for example, when formatting all worksheet cells
) and to cache formatting information of equally formatted cells, at the appropriate time, to reduce memory footprint.
public ExcelWorksheet getWorksheet(int index)
This method has the same effect as using file.
getWorksheets()
.get(index)
index
- The zero-based index of the worksheet.ExcelWorksheetCollection.get(int)
public ExcelWorksheet getWorksheet(String name)
This method has the same effect as using file.
getWorksheets()
.get(name)
name
- The name of the worksheet.ExcelWorksheetCollection.get(String)
public ExcelWorksheetCollection getWorksheets()
ExcelWorksheet
) in a workbook.ExcelWorksheet
public boolean isAutomaticFormulaUpdate()
If you use address references in a formula, those references will be automatically updated if you add or delete rows or columns and those changes affect the address reference in some way.
If this field is false
, formula address references will not update automatically.
This should be used only for performance reasons when working on large files.
Default value of this field is true
.
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 workbook protection, consult Microsoft Excel documentation.
ExcelWorksheet.isProtected()
public boolean isUse1904DateSystem()
Default value is false
.
For more information on 1904 date system, consult Microsoft Excel documentation.
public static ExcelFile load(InputStream stream) throws IOException
load(InputStream, LoadOptions)
method.stream
- The stream from which to load a spreadsheet.IOException
public static ExcelFile load(InputStream stream, LoadOptions options) throws IOException
Following code demonstrates different ways of loading
// Loads a spreadsheet from a file with the specified path
ExcelFile ef = ExcelFile.load(fileName);
try (FileInputStream stream = new FileInputStream(fileName)) {
// Loads a spreadsheet from the specified stream
ef = ExcelFile.load(stream, LoadOptions.getXlsxDefault());
}
// Loads a spreadsheet using specified options
XlsxLoadOptions loadOptions = new XlsxLoadOptions();
loadOptions.setPreserveUnsupportedFeatures(false);
ef = ExcelFile.load(fileName, loadOptions);
stream
- The stream from which to load a spreadsheet.options
- The loading options which can be used to define settings for load operation.IOException
public static ExcelFile load(Reader reader, CsvLoadOptions options) throws IOException
reader
- The reader which is used to load a spreadsheet.options
- The CSV loading options which can be used to define settings for load operation.IOException
public static ExcelFile load(String path) throws IOException
File extension that is extracted from the path
is used to create the appropriate LoadOptions
derived class instance that is then passed to the load(String, LoadOptions)
method.
Following file extensions are supported:
Following code demonstrates different ways of loading
// Loads a spreadsheet from a file with the specified path
ExcelFile ef = ExcelFile.load(fileName);
try (FileInputStream stream = new FileInputStream(fileName)) {
// Loads a spreadsheet from the specified stream
ef = ExcelFile.load(stream, LoadOptions.getXlsxDefault());
}
// Loads a spreadsheet using specified options
XlsxLoadOptions loadOptions = new XlsxLoadOptions();
loadOptions.setPreserveUnsupportedFeatures(false);
ef = ExcelFile.load(fileName, loadOptions);
path
- The path from which to load a spreadsheet.IOException
public static ExcelFile load(String path, LoadOptions options) throws IOException
Following code demonstrates different ways of loading
// Loads a spreadsheet from a file with the specified path
ExcelFile ef = ExcelFile.load(fileName);
try (FileInputStream stream = new FileInputStream(fileName)) {
// Loads a spreadsheet from the specified stream
ef = ExcelFile.load(stream, LoadOptions.getXlsxDefault());
}
// Loads a spreadsheet using specified options
XlsxLoadOptions loadOptions = new XlsxLoadOptions();
loadOptions.setPreserveUnsupportedFeatures(false);
ef = ExcelFile.load(fileName, loadOptions);
path
- The path from which to load a spreadsheet.options
- The loading options which can be used to define settings for load operation.IOException
public boolean removeStyle(BuiltInCellStyleName name)
This method has the same effect as using file.
getStyles()
.remove(name)
name
- The name of the built-in style which should be removed from the workbook.true
if style is successfully removed; otherwise, false
. This method also returns false
if style was not found in the workbook.IllegalStateException
- 'Normal
' style cannot be removed from the workbook.CellStyleCollection.remove(BuiltInCellStyleName)
public boolean removeStyle(String name)
This method has the same effect as using file.
getStyles()
.remove(name)
name
- The name of the style which should be removed from the workbook.true
if style is successfully removed; otherwise, false
. This method also returns false
if style was not found in the workbook.IllegalStateException
- 'Normal
' style cannot be removed from the workbook.CellStyleCollection.remove(String)
public boolean removeStyle(Style style)
This method has the same effect as using file.
getStyles()
.remove(style)
style
- The style to remove from the workbook.true
if style
is successfully removed; otherwise, false
. This method also returns false
if style
was not found in the workbook.CellStyleCollection.remove(Style)
public void removeWorksheet(int worksheetIndex)
This method has the same effect as using file.
getWorksheets()
.remove(worksheetIndex)
worksheetIndex
- The zero-based index of the worksheet.ExcelWorksheetCollection.remove(int)
public void removeWorksheet(String worksheetName)
This method has the same effect as using file.
getWorksheets()
.remove(worksheetName)
worksheetName
- Worksheet name.ExcelWorksheetCollection.remove(String)
public final void save(OutputStream stream, SaveOptions options) throws IOException
Following code demonstrates different ways of saving ExcelFile to specified file format.
ExcelFile ef = ExcelFile.load(fileName);
// Saves the spreadsheet to a file with the specified path
ef.save("Report.xlsx");
try (FileOutputStream stream = new FileOutputStream("Report.ods")) {
// Saves file to the stream
ef.save(stream, SaveOptions.getOdsDefault());
}
// Saves the file using specified save options
HtmlSaveOptions htmlSaveOptions = new HtmlSaveOptions();
htmlSaveOptions.setSelectionType(SelectionType.ENTIRE_FILE);
ef.save("Report.html", htmlSaveOptions);
stream
- The stream to which to save the spreadsheet.options
- The saving options which can be used to define settings for save operation.IOException
public final void save(String path) throws IOException
path
is used to create the appropriate SaveOptions
derived class instance that is then passed to the save(String, SaveOptions)
method.
Following file extensions are supported:
Following code demonstrates different ways of saving ExcelFile to specified file format.
ExcelFile ef = ExcelFile.load(fileName);
// Saves the spreadsheet to a file with the specified path
ef.save("Report.xlsx");
try (FileOutputStream stream = new FileOutputStream("Report.ods")) {
// Saves file to the stream
ef.save(stream, SaveOptions.getOdsDefault());
}
// Saves the file using specified save options
HtmlSaveOptions htmlSaveOptions = new HtmlSaveOptions();
htmlSaveOptions.setSelectionType(SelectionType.ENTIRE_FILE);
ef.save("Report.html", htmlSaveOptions);
path
- The path to which to save the spreadsheet.IllegalArgumentException
- If file extension is not specified or not supported. If file extension is not recognized, use save(String, SaveOptions)
method overload instead.IOException
public final void save(String path, SaveOptions options) throws IOException
Following code demonstrates different ways of saving ExcelFile to specified file format.
ExcelFile ef = ExcelFile.load(fileName);
// Saves the spreadsheet to a file with the specified path
ef.save("Report.xlsx");
try (FileOutputStream stream = new FileOutputStream("Report.ods")) {
// Saves file to the stream
ef.save(stream, SaveOptions.getOdsDefault());
}
// Saves the file using specified save options
HtmlSaveOptions htmlSaveOptions = new HtmlSaveOptions();
htmlSaveOptions.setSelectionType(SelectionType.ENTIRE_FILE);
ef.save("Report.html", htmlSaveOptions);
path
- The path to which to save the spreadsheet.options
- The saving options which can be used to define settings for save operation.IOException
public final void save(Writer writer, CsvSaveOptions options) throws IOException
writer
- The writer which is used to save the spreadsheet.options
- The CSV saving options which can be used to define settings for save operation.IOException
public void setAutomaticFormulaUpdate(boolean automaticFormulaUpdate)
If you use address references in a formula, those references will be automatically updated if you add or delete rows or columns and those changes affect the address reference in some way.
If this field is false
, formula address references will not update automatically.
This should be used only for performance reasons when working on large files.
Default value of this field is true
.
public void setGroupMethodsAffectedCellsLimit(int value)
If user tries to modify all cells in a group which has more cells than specified limit, exception will be thrown. This property was introduced to prevent users from accidentally modifying millions of cells which results in a long delay, a large memory allocation and a big resulting file. You can set this limit to value which suits your needs (minimum is 5).
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 workbook protection, consult Microsoft Excel documentation.
ExcelWorksheet.setProtected(boolean)
public void setUse1904DateSystem(boolean value)
Default value is false
.
For more information on 1904 date system, consult Microsoft Excel documentation.
public static boolean supportsType(Class type)
ExcelCell's value
field.
Currently supported types are:
type
- Queried type.true
if the specified type is supported; otherwise, false
.© GemBox Ltd. — All rights reserved.