Methods

new Workbook()

Initializes a new instance of the Workbook class. The default file format type is Excel97To2003.If want create other format file type, please call Workbook(FileFormatType fileFormatType).

Example

The following example creates a Workbook, opens a file named designer.xls in it and makes the horizontal and vertical scroll bars invisible for the Workbook. It then replaces two string values with an Integer value and string value respectively within the spreadsheet and finally save it to file named result.xls.
//Use Aspose.Cells for Node.js via Java
var aspose = aspose || {};
aspose.cells = require("aspose.cells");
//Open a designer file
var designerFile = "designer.xls";
var workbook = new aspose.cells.Workbook(designerFile);
//Set scroll bars
workbook.getSettings().setHScrollBarVisible(false);
workbook.getSettings().setVScrollBarVisible(false);
//Replace the placeholder string with new values
workbook.replace("OldInt", 100);
var newString = "Hello!";
workbook.replace("OldString", newString);
var saveOptions = new aspose.cells.XlsSaveOptions();
workbook.save("result.xls", saveOptions);

Methods

static

createWorkbookFromStream(stream, callback)

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

Example

var aspose = aspose || {};
aspose.cells = require("aspose.cells");
var fs = require("fs");
var readStream = fs.createReadStream("Book2.xlsx");
aspose.cells.Workbook.createWorkbookFromStream(readStream, function(workbook, err) {
if (err) {
console.log("open workbook error");
return;
}
workbook.save('result.xlsx');
console.log('saved to file');
});

Parameters

Name Type Optional Description

stream

ReadableStream

 

The stream

callback

Callback

 

The callback function

static

createWorkbookFromStream(stream, loadOptions, callback)

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

Example

var aspose = aspose || {};
aspose.cells = require("aspose.cells");
var fs = require("fs");
var readStream = fs.createReadStream("Book2.xlsx");
var loadOptions = new aspose.cells.LoadOptions();
aspose.cells.Workbook.createWorkbookFromStream(readStream, loadOptions, function(workbook, err) {
if (err) {
console.log("open workbook error");
return;
}
workbook.save('result.xlsx');
console.log('saved to file');
});

Parameters

Name Type Optional Description

stream

ReadableStream

 

The stream

loadOptions

LoadOptions

 

The load options

callback

Callback

 

The callback function

static

save(workbook, stream, saveOptions)

Save the workbook to the stream.

Example

var aspose = aspose || {};
aspose.cells = require("aspose.cells");
var fs = require("fs");
var workbook = new aspose.cells.Workbook("Book2.xlsx");
var saveOptions = new aspose.cells.XlsSaveOptions();
var writeStream = fs.createWriteStream("result-stream.xls");
aspose.cells.Workbook.saveToStream(workbook, writeStream, saveOptions);

Parameters

Name Type Optional Description

workbook

Workbook

 

The workbook object to save

stream

WritableStream

 

The stream

saveOptions

SaveOptions

 

The save options

static

save(workbook, stream, saveFormat)

Save the workbook to the stream.

Example

var aspose = aspose || {};
aspose.cells = require("aspose.cells");
var fs = require("fs");
var workbook = new aspose.cells.Workbook("Book2.xlsx");
var writeStream = fs.createWriteStream("result-stream.xlsx");
aspose.cells.Workbook.saveToStream(workbook, writeStream, aspose.cells.SaveFormat.XLSX);

Parameters

Name Type Optional Description

workbook

Workbook

 

The workbook object to save

stream

WritableStream

 

The stream

saveFormat

Number

 

The save file format type

acceptAllRevisions()

Accepts all tracked changes in the workbook.

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

Name Type Optional Description

digitalSignatureCollection

DigitalSignatureCollection

 

calculateFormula()

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)

Calculates the result of formulas.

Parameter

Name Type Optional Description

ignoreError

boolean

 

Indicates if hide the error in calculating formulas. The error may be unsupported function, external links, etc.

calculateFormula(ignoreError, customFunction)

Calculates the result of formulas. NOTE: This member is now obsolete. Instead, please use CalculateFormula(CalculationOptions) method. This method will be removed 12 months later since August 2020. Aspose apologizes for any inconvenience you may have experienced.

Parameters

Name Type Optional Description

ignoreError

boolean

 

Indicates if hide the error in calculating formulas. The error may be unsupported function, external links, etc.

customFunction

ICustomFunction

 

The custom formula calculation functions to extend the calculation engine.

calculateFormula(options)

Calculating formulas in this workbook.

Parameter

Name Type Optional Description

options

CalculationOptions

 

Options for calculation

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

Parameters

Name Type Optional Description

color

Color

 

Color structure.

index

Number

 

Palette index, 0 - 55.

closeAccessCache(opts)

Closes the session that uses caches to access data.

Parameter

Name Type Optional Description

opts

Number

 

AccessCacheOptions

combine(secondWorkbook)

Combines another Workbook object. Currently, only cell data and cell style of the second Workbook object can be combined. Images, charts and other drawing objects are not supported.

Parameter

Name Type Optional Description

secondWorkbook

Workbook

 

Another Workbook object.

constructor_overload$1(fileFormatType)

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

Example

The following code shows how to use the Workbook constructor to create and initialize a new instance of the class.
var aspose = aspose || {};
aspose.cells = require("aspose.cells");
var workbook = new aspose.cells.Workbook(aspose.cells.FileFormatType.XLSX);

Parameter

Name Type Optional Description

fileFormatType

Number

 

FileFormatType

constructor_overload$2(file)

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

Parameter

Name Type Optional Description

file

String

 

The file name.

constructor_overload$3(file, loadOptions)

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

Parameters

Name Type Optional Description

file

String

 

The file name.

loadOptions

LoadOptions

 

The load options

copy(source, copyOptions)

Copies data from a source Workbook object.

Parameters

Name Type Optional Description

source

Workbook

 

Source Workbook object.

copyOptions

CopyOptions

 

copy(source)

Copies data from a source Workbook object.

Parameter

Name Type Optional Description

source

Workbook

 

Source Workbook object.

copyTheme(source)

Copies the theme from another workbook.

Parameter

Name Type Optional Description

source

Workbook

 

Source workbook.

createBuiltinStyle(type) → Style

Creates built-in style by given type.

Parameter

Name Type Optional Description

type

Number

 

BuiltinStyleType

Returns

Style style object

createCellsColor() → CellsColor

Creates a CellsColor object.

Returns

CellsColor Returns a CellsColor object.

createStyle() → Style

Creates a new style.

Returns

Style Returns a style object.

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

Parameters

Name Type Optional Description

themeName

String

 

The theme name

colors

Array of Color

 

The theme colors

dispose()

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

exportXml(mapName, path)

Export XML data linked by the specified XML map.

Parameters

Name Type Optional Description

mapName

String

 

name of the XML map that need to be exported

path

String

 

the export path

exportXml(mapName, stream)

Export XML data linked by the specified XML map.

Parameters

Name Type Optional Description

mapName

String

 

name of the XML map that need to be exported

stream

OutputStream

 

the export stream

getAbsolutePath()

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

getBuiltInDocumentProperties()

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: TitleSubjectAuthorKeywordsCommentsTemplateLast AuthorRevision NumberApplication NameLast Print DateCreation DateLast Save TimeTotal Editing TimeNumber of PagesNumber of WordsNumber of CharactersSecurityCategoryFormatManagerCompanyNumber of BytesNumber of LinesNumber of ParagraphsNumber of SlidesNumber of NotesNumber of Hidden SlidesNumber of Multimedia Clips

Example

var doc = workbook.getBuiltInDocumentProperties().get("Author");
doc.setValue("John Smith");

getColors()

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

getContentTypeProperties()

Gets the list of ContentTypeProperty objects in the workbook.

getCountOfStylesInPool()

Gets number of the styles in the style pool.

getCustomDocumentProperties()

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

Example

var excel = new aspose.cells.Workbook();
excel.getCustomDocumentProperties().add("Checked by", "Jane");

getCustomXmlParts()

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

getDataConnections()

Gets the Aspose.Cells.ExternalConnections.ExternalConnection collection.

getDataMashup()

Gets mashup data.

getDataSorter()

Gets a DataSorter object to sort data.

getDefaultStyle()

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

Example

The following code creates and instantiates a new Workbook and sets a default Style to it.
var workbook = new aspose.cells.Workbook();
var defaultStyle = workbook.getDefaultStyle();
defaultStyle.getFont().setName("Tahoma");
workbook.setDefaultStyle(defaultStyle);

getDigitalSignature()

Gets digital signature from file.

getFileFormat()

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

getFileName()

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.

getFonts()

Gets all fonts in the style pool.

getInterruptMonitor()

Gets and sets the interrupt monitor.

getMatchingColor(rawColor) → Color

Find best matching Color in current palette.

Parameter

Name Type Optional Description

rawColor

Color

 

Raw color.

Returns

Color Best matching color.

getNamedStyle(name) → Style

Gets the named style in the style pool.

Parameter

Name Type Optional Description

name

String

 

name of the style

Returns

Style named style, maybe null.

getRibbonXml()

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

getSettings()

Represents the workbook settings.

getStyleInPool(index) → Style

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

Name Type Optional Description

index

Number

 

The index.

Returns

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

getTheme()

Gets the theme name.

getThemeColor(type) → Color

Gets theme color.

Parameter

Name Type Optional Description

type

Number

 

ThemeColorType

Returns

Color The theme color.

getVbaProject()

Gets the VbaProject in a spreadsheet.

getWorksheets() → WorksheetCollection

Gets the WorksheetCollection collection in the spreadsheet.

Returns

WorksheetCollection WorksheetCollection collection

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.@return {boolean} Whether this workbook contains external links to other data sources.

hasMacro()

Indicates if this spreadsheet contains macro/VBA.

hasRevisions()

Gets if the workbook has any tracked changes

importXml(url, sheetName, row, col)

Imports/Updates an XML data file into the workbook.

Parameters

Name Type Optional Description

url

String

 

the url/path of the xml file.

sheetName

String

 

the destination sheet name.

row

Number

 

the destination row

col

Number

 

the destination column

importXml(stream, sheetName, row, col)

Imports/Updates an XML data file into the workbook.

Parameters

Name Type Optional Description

stream

InputStream

 

the xml file stream.

sheetName

String

 

the destination sheet name.

row

Number

 

the destination row.

col

Number

 

the destination column.

isColorInPalette(color) → boolean

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

Parameter

Name Type Optional Description

color

Color

 

Color structure.

Returns

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

isDigitallySigned()

Indicates if this spreadsheet is digitally signed.

isLicensed()

Indicates whether license is set.

isWorkbookProtectedWithPassword()

Indicates whether structure or window is protected with password.

parseFormulas(ignoreError)

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

Parameter

Name Type Optional 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.

protect(protectionType, password)

Protects a workbook.

Parameters

Name Type Optional Description

protectionType

Number

 

ProtectionType

password

String

 

Password to protect the workbook.

protectSharedWorkbook(password)

Protects a shared workbook.

Parameter

Name Type Optional Description

password

String

 

Password to protect the workbook.

refreshDynamicArrayFormulas(calculate)

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

Parameter

Name Type Optional Description

calculate

boolean

 

Whether calculates and update cell values for those dynamic array formulas when the spilled range changes.

removeDigitalSignature()

Removes digital signature from this spreadsheet.

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.

removeMacro()

Removes VBA/macro from this spreadsheet.

removePersonalInformation()

Removes personal information.

removeUnusedStyles()

Remove all unused styles.

replace(placeHolder, newValue)

Replaces a cell's value with a new string.

Example

var workbook = new aspose.cells.Workbook();
workbook.replace("AnOldValue", "NewValue");

Parameters

Name Type Optional Description

placeHolder

String

 

Cell placeholder

newValue

String

 

String value to replace

replace(placeHolder, newValue)

Replaces a cell's value with a new integer.

Example

var workbook = new aspose.cells.Workbook();
var newValue = 100;
workbook.replace("AnOldValue", newValue);

Parameters

Name Type Optional Description

placeHolder

String

 

Cell placeholder

newValue

Number

 

Integer value to replace

replace(placeHolder, newValue)

Replaces a cell's value with a new double.

Example

var workbook = new aspose.cells.Workbook();
var newValue = 100.0;
workbook.replace("AnOldValue", newValue);

Parameters

Name Type Optional Description

placeHolder

String

 

Cell placeholder

newValue

Number

 

Double value to replace

replace(placeHolder, newValues, isVertical)

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

Example

var aspose = aspose || {};
aspose.cells = require("aspose.cells");
var java = require("java");
var workbook = new aspose.cells.Workbook();
var newValues = java.newArray("java.lang.String", ["Tom", "Alice", "Jerry"]);
workbook.replace("AnOldValue", newValues, true);

Parameters

Name Type Optional Description

placeHolder

String

 

Cell placeholder

newValues

Array of String

 

String array to replace

isVertical

boolean

 

True - Vertical, False - Horizontal

replace(placeHolder, newValue, options)

Replaces a cell's value with a new string.

Parameters

Name Type Optional Description

placeHolder

String

 

Cell placeholder

newValue

String

 

String value to replace

options

ReplaceOptions

 

The replace options

save(fileName, saveFormat)

Saves the workbook to the disk.

Example

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

Parameters

Name Type Optional Description

fileName

String

 

The file name.

saveFormat

Number

 

SaveFormat

save(fileName)

Save the workbook to the disk.

Parameter

Name Type Optional Description

fileName

String

 

save(fileName, saveOptions)

Saves the workbook to the disk.

Parameters

Name Type Optional Description

fileName

String

 

The file name.

saveOptions

SaveOptions

 

The save options.

setAbsolutePath()

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

setDefaultStyle()

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

Example

The following code creates and instantiates a new Workbook and sets a default Style to it.
var workbook = new aspose.cells.Workbook();
var defaultStyle = workbook.getDefaultStyle();
defaultStyle.getFont().setName("Tahoma");
workbook.setDefaultStyle(defaultStyle);

setDigitalSignature(digitalSignatureCollection)

Sets digital signature to an spreadsheet file (Excel2007 and later). Only support adding Xmldsig Digital Signature

Parameter

Name Type Optional Description

digitalSignatureCollection

DigitalSignatureCollection

 

setEncryptionOptions(encryptionType, keyLength)

Set Encryption Options.

Parameters

Name Type Optional Description

encryptionType

Number

 

EncryptionType

keyLength

Number

 

The key length.

setFileFormat()

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

setFileName()

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.

setInterruptMonitor()

Gets and sets the interrupt monitor.

setRibbonXml()

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

setThemeColor(type, color)

Sets the theme color

Parameters

Name Type Optional Description

type

Number

 

ThemeColorType

color

Color

 

the theme color

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

Name Type Optional Description

opts

Number

 

AccessCacheOptions

unprotect(password)

Unprotects a workbook.

Parameter

Name Type Optional Description

password

String

 

Password to unprotect the workbook.

unprotectSharedWorkbook(password)

Unprotects a shared workbook.

Parameter

Name Type Optional Description

password

String

 

Password to unprotect the workbook.

updateLinkedDataSource(externalWorkbooks)

If this workbook contains external links to other data source, Aspose.Cells will attempt to retrieve the latest data. If the method is not called before calculating formulas, Aspose.Cells will use the previous information(cached in the file); Please set CellsHelper.StartupPath,CellsHelper.AltStartPath,CellsHelper.LibraryPath. And please set Workbook.FilePath if this workbook is from a stream, otherwise Aspose.Cells could not get the external link full path sometimes.

Parameter

Name Type Optional Description

externalWorkbooks

Array of Workbook

 

External workbooks are referenced by this workbook. If it's null, we will directly open the external linked files.. If it's not null, we will check whether the external link in the array first; if not, we will open the external linked files again.