Workbook

Workbook class

Represents a root object to create an Excel spreadsheet.

Constructors

Name	Description
Workbook	Initializes a new instance of the Workbook class. The default file format type is Xlsx. If you want to create other type
Workbook	Initializes a new instance of the Workbook class. The default file format type is Excel97To2003.
Workbook
Workbook	Initializes a new instance of the Workbook class and open a file.
Workbook	Initializes a new instance of the Workbook class and open a file.

Properties

Name	Type	Description
Settings	WorkbookSettings	Represents the workbook settings.
Worksheets	WorksheetCollection	Gets the WorksheetCollection collection in the spreadsheet.
IsLicensed	boolean	Indicates whether license is set.
Colors	Color[]	Returns colors in the palette for the spreadsheet. The palette has 56 entries, each represented by an RGB value.
CountOfStylesInPool	int	Gets number of the styles in the style pool.
DefaultStyle	Style	Gets or sets the default Style object of the workbook. The DefaultStyle property is useful to implement a Style for the
IsDigitallySigned	boolean	Indicates if this spreadsheet is digitally signed.
IsWorkbookProtectedWithPassword	boolean	Indicates whether structure or window is protected with password.
VbaProject	VbaProject	Gets the VbaProject in a spreadsheet.
HasMacro	boolean	Indicates if this spreadsheet contains macro/VBA.
HasRevisions	boolean	Gets if the workbook has any tracked changes
FileName	String	Gets and sets the current file name. If the file is opened by stream and there are some external formula references, ple
CellsDataTableFactory	CellsDataTableFactory	Gets the factory for building ICellsDataTable from custom objects
DataSorter	DataSorter	Gets a DataSorter object to sort data.
Theme	String	Gets the theme name.
BuiltInDocumentProperties	BuiltInDocumentPropertyCollection	Returns a DocumentProperty collection that represents all the built-in document properties of the spreadsheet. A new pro
CustomDocumentProperties	CustomDocumentPropertyCollection	Returns a DocumentProperty collection that represents all the custom document properties of the spreadsheet.
FileFormat	int	Gets and sets the file format. The value of the property is FileFormatType integer constant.
HasCustomFunction	boolean
InterruptMonitor	AbstractInterruptMonitor	Gets and sets the interrupt monitor.
ContentTypeProperties	ContentTypePropertyCollection	Gets the list of ContentTypeProperty objects in the workbook.
CustomXmlParts	CustomXmlPartCollection	Represents a Custom XML Data Storage Part (custom XML data within a package).
DataMashup	DataMashup	Gets mashup data.
RibbonXml	String	Gets and sets the XML file that defines the Ribbon UI.
AbsolutePath	String	Gets and sets the absolute path of the file. Only used for external links.
DataConnections	ExternalConnectionCollection	Gets the ExternalConnection collection.
DataModel	DataModel

Methods

Name	Description
addDigitalSignature	Adds digital signature to an OOXML spreadsheet file (Excel2007 and later).

It’s very simple to clone an Excel file. | | combine | Combines another Workbook object.

The length of colors should be 12. Array index Theme type 0 Backgournd1 1 Text1 2 Backgournd2 3 Text | | copyTheme | Copies the theme from another workbook. | | hasExernalLinks | Indicates whether this workbook contains external links to other data sources.

NOTE: This member is now obsolete. Inste | | updateCustomFunctionDefinition | Updates definition of custom functions.

This method can be used for some special scenarios. For example, if user needs | | updateLinkedDataSource | If this workbook contains external links to other data source, Aspose.Cells will attempt to retrieve the latest data fro | | importXml | Imports/Updates an XML data file into the workbook. | | exportXml | Export XML data linked by the specified XML map. | | setDigitalSignature | Sets digital signature to an spreadsheet file (Excel2007 and later).

Workbook() (1 of 5)

Initializes a new instance of the Workbook class. The default file format type is Xlsx. If you want to create other types of files, please use Workbook(FileFormatType).

Example:

import jpype
import asposecells
jpype.startJVM()
from asposecells.api import *

workbook = Workbook()

Workbook(fileFormatType) (2 of 5)

Initializes a new instance of the Workbook class. The default file format type is Excel97To2003.

Parameter	Type	Description
fileFormatType	int	A FileFormatType value. The new file format.

Example:

import jpype
import asposecells
jpype.startJVM()
from asposecells.api import *

workbook = Workbook(FileFormatType.XLSX)

Workbook(loadOptions) (3 of 5)

Workbook(file) (4 of 5)

Initializes a new instance of the Workbook class and open a file.

Parameter	Type	Description
file	String	The file name.

Workbook(file, loadOptions) (5 of 5)

Initializes a new instance of the Workbook class and open a file.

Parameter	Type	Description
file	String	The file name.
loadOptions	LoadOptions	The load options

Workbook.Settings property

Represents the workbook settings.

Type: WorkbookSettings

Workbook.Worksheets property

Gets the WorksheetCollection collection in the spreadsheet.

Type: WorksheetCollection

Workbook.IsLicensed property

Indicates whether license is set.

Type: boolean

Workbook.Colors property

Returns colors in the palette for the spreadsheet. The palette has 56 entries, each represented by an RGB value.

Type: Color[]

Workbook.CountOfStylesInPool property

Gets number of the styles in the style pool.

Type: int

Workbook.DefaultStyle property

Gets or sets the default Style object of the workbook. The DefaultStyle property is useful to implement a Style for the whole Workbook.

Type: Style

Workbook.IsDigitallySigned property

Indicates if this spreadsheet is digitally signed.

Type: boolean

Workbook.IsWorkbookProtectedWithPassword property

Indicates whether structure or window is protected with password.

Type: boolean

Workbook.VbaProject property

Gets the VbaProject in a spreadsheet.

Type: VbaProject

Workbook.HasMacro property

Indicates if this spreadsheet contains macro/VBA.

Type: boolean

Workbook.HasRevisions property

Gets if the workbook has any tracked changes

Type: boolean

Workbook.FileName property

Gets and sets the current file name. If the file is opened by stream and there are some external formula references, please set the file name.

Type: String

Workbook.CellsDataTableFactory property

Gets the factory for building ICellsDataTable from custom objects

Type: CellsDataTableFactory

Workbook.DataSorter property

Gets a DataSorter object to sort data.

Type: DataSorter

Workbook.Theme property

Gets the theme name.

Type: String

Workbook.BuiltInDocumentProperties property

Returns a DocumentProperty collection that represents all the built-in document properties of the spreadsheet. A new property cannot be added to built-in document properties list. You can only get a built-in property and change its value. The following is the built-in properties name list: Title Subject Author Keywords Comments Template Last Author Revision Number Application Name Last Print Date Creation Date Last Save Time Total Editing Time Number of Pages Number of Words Number of Characters Security Category Format Manager Company Number of Bytes Number of Lines Number of Paragraphs Number of Slides Number of Notes Number of Hidden Slides Number of Multimedia Clips

Type: BuiltInDocumentPropertyCollection

Workbook.CustomDocumentProperties property

Returns a DocumentProperty collection that represents all the custom document properties of the spreadsheet.

Type: CustomDocumentPropertyCollection

Workbook.FileFormat property

Gets and sets the file format. The value of the property is FileFormatType integer constant.

Type: int

Workbook.HasCustomFunction property

Type: boolean

Workbook.InterruptMonitor property

Gets and sets the interrupt monitor.

Type: AbstractInterruptMonitor

Workbook.ContentTypeProperties property

Gets the list of ContentTypeProperty objects in the workbook.

Type: ContentTypePropertyCollection

Workbook.CustomXmlParts property

Represents a Custom XML Data Storage Part (custom XML data within a package).

Type: CustomXmlPartCollection

Workbook.DataMashup property

Gets mashup data.

Type: DataMashup

Workbook.RibbonXml property

Gets and sets the XML file that defines the Ribbon UI.

Type: String

Workbook.AbsolutePath property

Gets and sets the absolute path of the file. Only used for external links.

Type: String

Workbook.DataConnections property

Gets the ExternalConnection collection.

Type: ExternalConnectionCollection

Workbook.DataModel property

Type: DataModel

addDigitalSignature(digitalSignatureCollection)

Adds digital signature to an OOXML spreadsheet file (Excel2007 and later).

Only support adding Xmldsig Digital Signature to an OOXML spreadsheet file

Parameter	Type	Description
digitalSignatureCollection	DigitalSignatureCollection

getDigitalSignature()

Gets digital signature from file.

removePersonalInformation()

Removes personal information.

refreshAll()

dispose()

Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.

parseFormulas(ignoreError)

Parses all formulas which have not been parsed when they were loaded from template file or set to a cell.

Parameter	Type	Description
ignoreError	boolean	Whether ignore error for invalid formula. For one invalid formula, if ignore error then this formula will be ignored and the process will continue to parse other formulas, otherwise exception will be thrown.

startAccessCache(opts)

Starts the session that uses caches to access data.

If the cache of specified data access requires some data models in worksheet to be “read-only”, then corresponding data models in every worksheet in this workbook will be taken as “read-only” and user should not change any of them. After finishing the access to the data, closeAccessCache(int) should be invoked with same options to clear all caches and recover normal access mode.

Parameter	Type	Description
opts	int	A AccessCacheOptions value. options of data access

closeAccessCache(opts)

Closes the session that uses caches to access data.

Parameter	Type	Description
opts	int	A AccessCacheOptions value. options of data access

save(fileName, saveFormat) (1 of 3)

Saves the workbook to the disk.

Parameter	Type	Description
fileName	String	The file name.
saveFormat	int	A SaveFormat value. The save format type.

Example:

workbook = Workbook()
sheets = workbook.getWorksheets()
cells = sheets.get(0).getCells()
cells.get("A1").putValue("Hello world!")
workbook.save("Book1.xls", SaveFormat.EXCEL_97_TO_2003)

save(fileName) (2 of 3)

Save the workbook to the disk.

Parameter	Type	Description
fileName	String

save(fileName, saveOptions) (3 of 3)

Saves the workbook to the disk.

Parameter	Type	Description
fileName	String	The file name.
saveOptions	SaveOptions	The save options.

removeUnusedStyles()

Remove all unused styles.

createStyle() (1 of 2)

Creates a new style.

Returns: Returns a style object.

createStyle(cloneDefaultStyle) (2 of 2)

createBuiltinStyle(type)

Creates built-in style by given type.

Parameter	Type	Description
type	int	A BuiltinStyleType value. The builtin style stype.

Returns: Style object

createCellsColor()

Creates a CellsColor object.

Returns: Returns a CellsColor object.

replace(placeHolder, newValue) (1 of 5)

Replaces a cell’s value with a new string.

Parameter	Type	Description
placeHolder	String	Cell placeholder
newValue	String	String value to replace

Example:

workbook = Workbook()
workbook.replace("AnOldValue", "NewValue")

replace(placeHolder, newValue) (2 of 5)

Replaces a cell’s value with a new integer.

Parameter	Type	Description
placeHolder	String	Cell placeholder
newValue	int	Integer value to replace

Example:

workbook = Workbook()
newValue = 100
workbook.replace("AnOldValue", newValue)

replace(placeHolder, newValue) (3 of 5)

Replaces a cell’s value with a new double.

Parameter	Type	Description
placeHolder	String	Cell placeholder
newValue	float	Double value to replace

Example:

workbook = Workbook()
newValue = 100.0
workbook.replace("AnOldValue", newValue)

replace(placeHolder, newValues, isVertical) (4 of 5)

Replaces a cell’s value with a new string array.

Parameter	Type	Description
placeHolder	String	Cell placeholder
newValues	String[]	String array to replace
isVertical	boolean	True - Vertical, False - Horizontal

Example:

import jpype
import asposecells
jpype.startJVM()
from asposecells.api import *

workbook = Workbook("Book2.xlsx")
workbook.replace("OldString", ["Tom", "Alice", "Jerry"], True)

replace(placeHolder, newValue, options) (5 of 5)

Replaces a cell’s value with a new string.

Parameter	Type	Description
placeHolder	String	Cell placeholder
newValue	String	String value to replace
options	ReplaceOptions	The replace options

copy(source, copyOptions) (1 of 2)

Copies another Workbook object.

It’s very simple to clone an Excel file.

Parameter	Type	Description
source	Workbook	Source Workbook object.
copyOptions	CopyOptions	The options of copying other workbook.

copy(source) (2 of 2)

Copies data from a source Workbook object.

Parameter	Type	Description
source	Workbook	Source Workbook object.

combine(secondWorkbook)

Combines another Workbook object.

Merge Excel, ODS , CSV and other files to one file.

Parameter	Type	Description
secondWorkbook	Workbook	Another Workbook object.

getStyleInPool(index)

Gets the style in the style pool. All styles in the workbook will be gathered into a pool. There is only a simple reference index in the cells.

If the returned style is changed, the style of all cells(which refers to this style) will be changed.

Parameter	Type	Description
index	int	The index.

Returns: The style in the pool corresponds to given index, may be null.

getFonts()

Gets all fonts in the style pool.

getNamedStyle(name)

Gets the named style in the style pool.

Parameter	Type	Description
name	String	name of the style

Returns: named style, maybe null.

mergeNamedStyles(source)

changePalette(color, index)

Changes the palette for the spreadsheet in the specified index.

The palette has 56 entries, each represented by an RGB value. If you set a color which is not in the palette, it will not take effect. So if you want to set a custom color, please change the palette at first. The following is the standard color palette. Color Red Green Blue Black 0 0 0 White 255 255 255 Red 255 0 0 Lime 0 255 0 Blue 0 0 255 Yellow 255 255 0 Magenta 255 0 255 Cyan 0 255 255 Maroon 128 0 0 Green 0 128 0 Navy 0 0 128 Olive 128 128 0 Purple 128 0 128 Teal 0 128 128 Silver 192 192 192 Gray 128 128 128 Color17 153 153 255 Color18 153 51 102 Color19 255 255 204 Color20 204 255 255 Color21 102 0 102 Color22 255 128 128 Color23 0 102 204 Color24 204 204 255 Color25 0 0 128 Color26 255 0 255 Color27 255 255 0 Color28 0 255 255 Color29 128 0 128 Color30 128 0 0 Color31 0 128 128 Color32 0 0 255 Color33 0 204 255 Color34 204 255 255 Color35 204 255 204 Color36 255 255 153 Color37 153 204 255 Color38 255 153 204 Color39 204 153 255 Color40 255 204 153 Color41 51 102 255 Color42 51 204 204 Color43 153 204 0 Color44 255 204 0 Color45 255 153 0 Color46 255 102 0 Color47 102 102 153 Color48 150 150 150 Color49 0 51 102 Color50 51 153 102 Color51 0 51 0 Color52 51 51 0 Color53 153 51 0 Color54 153 51 102 Color55 51 51 153 Color56 51 51 51

Parameter	Type	Description
color	Color	Color structure.
index	int	Palette index, 0 - 55.

isColorInPalette(color)

Checks if a color is in the palette for the spreadsheet.

Parameter	Type	Description
color	Color	Color structure.

Returns: Returns true if this color is in the palette. Otherwise, returns false

calculateFormula() (1 of 3)

Calculates the result of formulas.

For all supported formulas, please see the list at https://docs.aspose.com/display/cellsnet/Supported+Formula+Functions

calculateFormula(ignoreError) (2 of 3)

Calculates the result of formulas.

Parameter	Type	Description
ignoreError	boolean	Indicates if hide the error in calculating formulas. The error may be unsupported function, external links, etc.

calculateFormula(options) (3 of 3)

Calculating formulas in this workbook.

Parameter	Type	Description
options	CalculationOptions	Options for calculation

refreshDynamicArrayFormulas(calculate) (1 of 2)

Refreshes dynamic array formulas(spill into new range of neighboring cells according to current data) Other formulas in the workbook will not be calculated recursively even if they were used by dynamic array formulas.

Parameter	Type	Description
calculate	boolean	Whether calculates and updates cell values for those dynamic array formulas

refreshDynamicArrayFormulas(calculate, copts) (2 of 2)

Refreshes dynamic array formulas(spill into new range of neighboring cells according to current data)

For performance consideration, we do not refresh all dynamic array formulas automatically when the formula itself or the data it references to changed. So user need to call this method manually after those operations which may influence dynamic array formulas, such as importing/setting cell values, inserting/deleting rows/columns/ranges, …etc. For most formulas with functions, calculating the spill range also needs to calculating the formula, so in general true value for “calculate” flag is preferred. If the formula is simple, such as a range reference or array(for example “=C1:E5”, “={1,2;3,4}”, …), simple function on a range or array(for example “=ABS(C1:E5)”, “=1+{1,2;3,4}”, …), and all formulas will be calculated later(such as by calculateFormula(com.aspose.cells.CalculationOptions) ), then using false vlaue for “calculate” flag may avoid the duplicated calculation for the benefit of performance.

Parameter	Type	Description
calculate	boolean	Whether calculates and updates cell values for those dynamic array formulas
copts	CalculationOptions	The options for calculating formulas

getMatchingColor(rawColor)

Find best matching Color in current palette.

Parameter	Type	Description
rawColor	Color	Raw color.

Returns: Best matching color.

setEncryptionOptions(encryptionType, keyLength)

Set Encryption Options.

Parameter	Type	Description
encryptionType	int	A EncryptionType value. The encryption type.
keyLength	int	The key length.

protect(protectionType, password)

Protects a workbook.

Parameter	Type	Description
protectionType	int	A ProtectionType value. Protection type.
password	String	Password to protect the workbook.

protectSharedWorkbook(password)

Protects a shared workbook.

Parameter	Type	Description
password	String	Password to protect the workbook.

unprotect(password)

Unprotects a workbook.

Parameter	Type	Description
password	String	Password to unprotect the workbook.

unprotectSharedWorkbook(password)

Unprotects a shared workbook.

Parameter	Type	Description
password	String	Password to unprotect the workbook.

removeMacro()

Removes VBA/macro from this spreadsheet.

removeDigitalSignature()

Removes digital signature from this spreadsheet.

acceptAllRevisions()

Accepts all tracked changes in the workbook.

removeExternalLinks()

Removes all external links in the workbook.

NOTE: This member is now obsolete. Instead, please use ExternalLinkCollection.Clear() method. This method will be removed 12 months later since December 2021. Aspose apologizes for any inconvenience you may have experienced.

getThemeColor(type)

Gets theme color.

Parameter	Type	Description
type	int	A ThemeColorType value. The theme color type.

Returns: The theme color.

setThemeColor(type, color)

Sets the theme color

Parameter	Type	Description
type	int	A ThemeColorType value. The theme color type.
color	Color	the theme color

customTheme(themeName, colors)

Customs the theme.

The length of colors should be 12. Array index Theme type 0 Backgournd1 1 Text1 2 Backgournd2 3 Text2 4 Accent1 5 Accent2 6 Accent3 7 Accent4 8 Accent5 9 Accent6 10 Hyperlink 11 Followed Hyperlink

Parameter	Type	Description
themeName	String	The theme name
colors	Color[]	The theme colors

copyTheme(source)

Copies the theme from another workbook.

Parameter	Type	Description
source	Workbook	Source workbook.

hasExernalLinks()

Indicates whether this workbook contains external links to other data sources.

NOTE: This member is now obsolete. Instead, please use ExternalLinkCollection.Count to check whether there are external links in this workbook. This method will be removed 12 months later since December 2021. Aspose apologizes for any inconvenience you may have experienced.

Returns: Whether this workbook contains external links to other data sources.

updateCustomFunctionDefinition(definition)

Updates definition of custom functions.

This method can be used for some special scenarios. For example, if user needs some parameters of some custom functions be calculated in array mode, then user may provide their own definition with implemented CustomFunctionDefinition.getArrayModeParameters(java.lang.String) for those functions. After the data of formulas being updated, those specified parameters will be calculated in array mode automatically when calculating corresponding custom functions.

Parameter	Type	Description
definition	CustomFunctionDefinition	Special definition of custom functions for user’s special requirement.

updateLinkedDataSource(externalWorkbooks)

If this workbook contains external links to other data source, Aspose.Cells will attempt to retrieve the latest data from give sources.

If corresponding external link cannot be found for one workbook, then this workbook will be ignored. So when you set a formula later with one new external link which you intend to make the ignored workbook be linked to it, the link cannot be performed until you call this this method again with those workbooks.

Parameter	Type	Description
externalWorkbooks	Workbook[]	Workbooks that will be used to update data of external links referenced by this workbook. The match of those workbooks with external links is determined by FileName and ExternalLink.DataSource . So please make sure FileName has been specified with the proper value for every workbook so they can be linked to corresponding external link.

importXml(url, sheetName, row, col) (1 of 2)

Imports/Updates an XML data file into the workbook.

Parameter	Type	Description
url	String	the url/path of the xml file.
sheetName	String	the destination sheet name.
row	int	the destination row
col	int	the destination column

importXml(stream, sheetName, row, col) (2 of 2)

Imports/Updates an XML data file into the workbook.

Parameter	Type	Description
stream	InputStream	the xml file stream.
sheetName	String	the destination sheet name.
row	int	the destination row.
col	int	the destination column.

exportXml(mapName, path) (1 of 2)

Export XML data linked by the specified XML map.

Parameter	Type	Description
mapName	String	name of the XML map that need to be exported
path	String	the export path

exportXml(mapName, stream) (2 of 2)

Export XML data linked by the specified XML map.

Parameter	Type	Description
mapName	String	name of the XML map that need to be exported
stream	OutputStream	the export stream

setDigitalSignature(digitalSignatureCollection)

Sets digital signature to an spreadsheet file (Excel2007 and later).

Only support adding Xmldsig Digital Signature

Parameter	Type	Description
digitalSignatureCollection	DigitalSignatureCollection

createWorkbookFromBytes(byte_array) (1 of 2)

Initializes a new instance of the Workbook class and open a byte array.

Parameter	Type	Description
byte_array	bytes	The byte array

Example:

import jpype
import asposecells
jpype.startJVM()
from asposecells.api import *

with open('Book2.xlsx', 'rb') as f:
    wb = Workbook.createWorkbookFromBytes(f.read())
    wb.save('result.xlsx')

jpype.shutdownJVM()

createWorkbookFromBytes(byte_array, loadOptions) (2 of 2)

Initializes a new instance of the Workbook class and open a byte array.

Parameter	Type	Description
byte_array	bytes	The byte array
loadOptions	LoadOptions	The load options

Example:

import jpype
import asposecells
jpype.startJVM()
from asposecells.api import *

loadOptions = LoadOptions()
with open('Book2.xlsx', 'rb') as f:
    wb = Workbook.createWorkbookFromBytes(f.read(), loadOptions=loadOptions)
    wb.save('result.xlsx')

jpype.shutdownJVM()

saveToBytes(saveOptions) (1 of 2)

Save the workbook to a byte array.

Parameter	Type	Description
saveOptions	SaveOptions	The save options

Returns: A byte array.

Example:

import jpype
import asposecells
jpype.startJVM()
from asposecells.api import *

wb = Workbook("Book2.xlsx")
saveOptions = XlsSaveOptions()
with open("wb.xls", "wb") as w:
    byte_array = wb.saveToBytes(saveOptions)
    w.write(byte_array)

jpype.shutdownJVM()

saveToBytes(saveFormat) (2 of 2)

Save the workbook to a byte array.

Parameter	Type	Description
saveFormat	int	The save file format type

Returns: A byte array.

Example:

import jpype
import asposecells
jpype.startJVM()
from asposecells.api import *

wb = Workbook("Book2.xlsx")
with open("wb.xlsx", "wb") as w:
    byte_array = wb.saveToBytes(SaveFormat.XLSX)
    w.write(byte_array)

jpype.shutdownJVM()

WeightType Enum WorkbookDesigner Class