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.$designerFile = "book1.xls"; $workbook = new 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); $newString = "Hello!"; $workbook->replace("OldString", newString); $saveOptions = new cells\XlsSaveOptions(); $workbook->save("res.xls", $saveOptions);
Constructor Summary |
---|
Workbook()
Initializes a new instance of the |
Workbook(fileFormatType)
Initializes a new instance of the |
Workbook(file)
Initializes a new instance of the |
Workbook(file, loadOptions)
Initializes a new instance of the |
Property Getters/Setters Summary | ||
---|---|---|
function | getAbsolutePath() | |
function | setAbsolutePath(value) | |
Gets and sets the absolute path of the file. | ||
function | getBuiltInDocumentProperties() | |
Returns a DocumentProperties collection that represents all the built-in document properties of the spreadsheet. | ||
function | getColors() | |
Returns colors in the palette for the spreadsheet. | ||
function | getContentTypeProperties() | |
Gets the contenttypeproperties objects in the workbook. | ||
function | getCountOfStylesInPool() | |
Gets number of the styles in the style pool. | ||
function | getCustomDocumentProperties() | |
Returns a DocumentProperties collection that represents all the custom document properties of the spreadsheet. | ||
function | getCustomXmlParts() | |
Represents a InnerCustom XML Data Storage Part (custom XML data within a package). | ||
function | getDataConnections() | |
Gets the |
||
function | getDataSorter() | |
Gets a DataSorter object to sort data. | ||
function | getDefaultStyle() | |
function | setDefaultStyle(value) | |
Gets or sets the default |
||
function | getFileFormat() | |
function | setFileFormat(value) | |
Gets and sets the file format. The value of the property is FileFormatType integer constant. | ||
function | getFileName() | |
function | setFileName(value) | |
Gets and sets the current file name. | ||
function | hasMacro() | |
Indicates if this spreadsheet contains macro/VBA. | ||
function | hasRevisions() | |
Gets if the workbook has any tracked changes | ||
function | getInterruptMonitor() | |
function | setInterruptMonitor(value) | |
Gets and sets the interrupt monitor. | ||
function | isDigitallySigned() | |
Indicates if this spreadsheet is digitally signed. | ||
function | isLicensed() | |
Indicates whether license is set. | ||
function | getRibbonXml() | |
function | setRibbonXml(value) | |
Gets and sets the XML file that defines the Ribbon UI. | ||
function | getSettings() | |
Represents the workbook settings. | ||
function | getTheme() | |
Gets the theme name. | ||
function | getVbaProject() | |
Gets the |
||
function | getWorksheets() | |
Gets the |
Method Summary | ||
---|---|---|
function | acceptAllRevisions() | |
Accepts all tracked changes in the workbook. | ||
function | addDigitalSignature(digitalSignatureCollection) | |
Adds digital signature to an OOXML spreadsheet file (Excel2007 and later). | ||
function | calculateFormula() | |
Calculates the result of formulas. | ||
function | calculateFormula(ignoreError) | |
Calculates the result of formulas. | ||
function | calculateFormula(ignoreError, customFunction) | |
Calculates the result of formulas. | ||
function | calculateFormula(options) | |
Calcualting formulas in this workbook. | ||
function | changePalette(color, index) | |
Changes the palette for the spreadsheet in the specified index. | ||
function | combine(secondWorkbook) | |
Combines another Workbook object. | ||
function | copy(source0) | |
Copies data from a source Workbook object. | ||
function | copyTheme(source) | |
Copies the theme from another workbook. | ||
function | createBuiltinStyle(type) | |
Creates built-in style by given type. | ||
function | createCellsColor() | |
Creates a |
||
function | createStyle() | |
Creates a new style. | ||
function | customTheme(themeName, colors) | |
Customs the theme. | ||
function | dispose() | |
Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources. | ||
function | exportXml(mapName, path) | |
Export XML data. | ||
function | getDigitalSignature() | |
Gets digital signature from file. | ||
function | getFonts() | |
Gets all fonts in the style pool. | ||
function | getMatchingColor(rawColor) | |
Find best matching Color in current palette. | ||
function | getNamedStyle(name) | |
Gets the named style in the style pool. | ||
function | 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. | ||
function | getThemeColor(type) | |
Gets theme color. | ||
function | hasExernalLinks() | |
Indicates whether this workbook contains external links to other data sources. | ||
function | importXml(url, sheetName, row, col) | |
Imports an xml file into the workbook. | ||
function | isColorInPalette(color) | |
Checks if a color is in the palette for the spreadsheet. | ||
function | protect(protectionType, password) | |
Protects a workbook. | ||
function | protectSharedWorkbook(password) | |
Protects a shared workbook. | ||
function | removeDigitalSignature() | |
Removes digital signature from this spreadsheet. | ||
function | removeExternalLinks() | |
Removes all external links in the workbook. | ||
function | removeMacro() | |
Removes VBA/macro from this spreadsheet. | ||
function | removeUnusedStyles() | |
Remove all unused styles. | ||
function | replace(placeHolder, newValue) | |
Replaces a cell's value with a new double. | ||
function | replace(placeHolder, newValue) | |
Replaces a cell's value with a new integer. | ||
function | replace(placeHolder, newValue) | |
Replaces a cell's value with a new string. | ||
function | replace(placeHolder, newValue, options) | |
Replaces a cell's value with a new string. | ||
function | replace(placeHolder, newValues, isVertical) | |
Replaces a cell's value with a new string array. | ||
function | save(fileName) | |
function | save(fileName, saveOptions) | |
Saves the workbook to the disk. | ||
function | save(fileName, saveFormat) | |
Saves the workbook to the disk. | ||
function | setDigitalSignature(digitalSignatureCollection) | |
Sets digital signature to an spreadsheet file (Excel2007 and later). | ||
function | setEncryptionOptions(encryptionType, keyLength) | |
Set Encryption Options. | ||
function | setThemeColor(type, color) | |
Sets the theme color | ||
function | unprotect(password) | |
Unprotects a workbook. | ||
function | unprotectSharedWorkbook(password) | |
Unprotects a shared workbook. | ||
function | updateLinkedDataSource(exteralWorkbooks) | |
If this workbook contains external links to other data source, Aspose.Cells will attempt to retrieve the latest data. |
Constructor Detail |
---|
function Workbook()
function Workbook(fileFormatType)
fileFormatType: Number
- A Example:
The following code shows how to use the Workbook constructor to create and initialize a new instance of the class.$workbook = new cells\Workbook(cells\FileFormatType::XLSX);
function Workbook(file)
file: String
- The file name.function Workbook(file, loadOptions)
file: String
- The file name.loadOptions: LoadOptions
- The load optionsProperty Getters/Setters Detail |
---|
getSettings : WorkbookSettings | |
function getSettings() |
getWorksheets : WorksheetCollection | |
function getWorksheets() |
isLicensed : boolean | |
function isLicensed() |
getColors : Color[] | |
function getColors() |
getCountOfStylesInPool : Number | |
function getCountOfStylesInPool() |
getDefaultStyle/setDefaultStyle : Style | |
function getDefaultStyle() / function setDefaultStyle(value) |
Example:
The following code creates and instantiates a new Workbook and sets a default Style to it.$workbook = new cells\Workbook(); $defaultStyle = $workbook->getDefaultStyle(); $defaultStyle->getFont()->setName("Tahoma"); $workbook->setDefaultStyle($defaultStyle);
isDigitallySigned : boolean | |
function isDigitallySigned() |
getVbaProject : VbaProject | |
function getVbaProject() |
hasMacro : boolean | |
function hasMacro() |
hasRevisions : boolean | |
function hasRevisions() |
getFileName/setFileName : String | |
function getFileName() / function setFileName(value) |
getDataSorter : DataSorter | |
function getDataSorter() |
getTheme : String | |
function getTheme() |
getBuiltInDocumentProperties : BuiltInDocumentPropertyCollection | |
function getBuiltInDocumentProperties() |
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
Example:
$workbook = new cells\Workbook(); $doc = $workbook->getBuiltInDocumentProperties()->get("Author"); $doc->setValue("John Smith");
getCustomDocumentProperties : CustomDocumentPropertyCollection | |
function getCustomDocumentProperties() |
Example:
$workbook = new cells\Workbook(); $workbook->getCustomDocumentProperties()->add("Checked by", "Jane");
getFileFormat/setFileFormat : Number | |
function getFileFormat() / function setFileFormat(value) |
getInterruptMonitor/setInterruptMonitor : InterruptMonitor | |
function getInterruptMonitor() / function setInterruptMonitor(value) |
getContentTypeProperties : ContentTypePropertyCollection | |
function getContentTypeProperties() |
getCustomXmlParts : CustomXmlPartCollection | |
function getCustomXmlParts() |
getRibbonXml/setRibbonXml : String | |
function getRibbonXml() / function setRibbonXml(value) |
getAbsolutePath/setAbsolutePath : String | |
function getAbsolutePath() / function setAbsolutePath(value) |
getDataConnections : ExternalConnectionCollection | |
function getDataConnections() |
Method Detail |
---|
save | |
function save(fileName, saveFormat) |
fileName: String
- The file name.saveFormat: Number
- A Example:
$workbook = new cells\Workbook(); $sheets = $workbook->getWorksheets(); $cells = $sheets->get(0)->getCells(); $cells->get("A1")->putValue("Hello world!"); $workbook->save("res.xls", cells\SaveFormat::EXCEL_97_TO_2003);
save | |
function save(fileName) |
fileName: String
- save | |
function save(fileName, saveOptions) |
fileName: String
- The file name.saveOptions: SaveOptions
- The save options.removeUnusedStyles | |
function removeUnusedStyles() |
createStyle | |
function createStyle() |
createBuiltinStyle | |
function createBuiltinStyle(type) |
type: Number
- A createCellsColor | |
function createCellsColor() |
replace | |
function replace(placeHolder, newValue) |
placeHolder: String
- Cell placeholdernewValue: String
- String value to replaceExample:
$workbook = new cells\Workbook(); $cells = $workbook->getWorksheets()->get(0)->getCells(); $cells->get(0, 0)->putValue("AnOldValue"); $cells->get(0, 1)->putValue("AnotherOldValue"); $workbook->replace("AnOldValue", "NewValue");
replace | |
function replace(placeHolder, newValue) |
placeHolder: String
- Cell placeholdernewValue: Number
- Integer value to replaceExample:
$workbook = new cells\Workbook(); $cells = $workbook->getWorksheets()->get(0)->getCells(); $cells->get(0, 0)->putValue("AnOldValue"); $cells->get(0, 1)->putValue("AnotherOldValue"); $newValue = 100; $workbook->replace("AnOldValue", $newValue);
replace | |
function replace(placeHolder, newValue) |
placeHolder: String
- Cell placeholdernewValue: Number
- Double value to replaceExample:
$workbook = new cells\Workbook(); $cells = $workbook->getWorksheets()->get(0)->getCells(); $cells->get(0, 0)->putValue("AnOldValue"); $cells->get(0, 1)->putValue("AnotherOldValue"); $newValue = 100.1; $workbook->replace("AnOldValue", $newValue);
replace | |
function replace(placeHolder, newValues, isVertical) |
placeHolder: String
- Cell placeholdernewValues: String[]
- String array to replaceisVertical: boolean
- True - Vertical, False - HorizontalExample:
$workbook = new cells\Workbook(); $cells = $workbook->getWorksheets()->get(0)->getCells(); $cells->get(0, 0)->putValue("AnOldValue"); $cells->get(0, 1)->putValue("AnotherOldValue"); $ExampleUtil = new Java("ExampleUtil"); $newValues = ["Tom", "Alice", "Jerry"]; $workbook->replace("AnOldValue", $newValues, true);
replace | |
function replace(placeHolder, newValue, options) |
placeHolder: String
- Cell placeholdernewValue: String
- String value to replaceoptions: ReplaceOptions
- The replace optionscopy | |
function copy(source0) |
source0: Workbook
- Source Workbook object.combine | |
function combine(secondWorkbook) |
secondWorkbook: Workbook
- Another Workbook object.getStyleInPool | |
function getStyleInPool(index) |
index: Number
- The index.getFonts | |
function getFonts() |
getNamedStyle | |
function getNamedStyle(name) |
name: String
- name of the stylechangePalette | |
function changePalette(color, index) |
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 |
color: Color
- Color structure.index: Number
- Palette index, 0 - 55.isColorInPalette | |
function isColorInPalette(color) |
color: Color
- Color structure.calculateFormula | |
function calculateFormula() |
Now Workbook built-in functions are not supported in this method:
[A]
ASC
[B]
BAHTTEXT
[C]
CALL, CLEAN, CODE, CONVERT, CUBEKPIMEMBER, CUBEMEMBER, CUBEMEMBERPROPERTY, CUBERANKEDMEMBER, CUBESET, CUBESETCOUNT, CUBEVALUE
[E]
EUROCONVERT
[I]
INFO
[J]
JIS
[P]
PHONETIC
[R]
REGISTER.ID, RTD
[S]
SQL.REQUEST
calculateFormula | |
function calculateFormula(ignoreError) |
ignoreError: boolean
- Indicates if hide the error in calculating formulas. The error may be unsupported function, external links, etc.calculateFormula | |
function calculateFormula(ignoreError, customFunction) |
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 | |
function calculateFormula(options) |
options: CalculationOptions
- Options for calculationgetMatchingColor | |
function getMatchingColor(rawColor) |
rawColor: Color
- Raw color.setEncryptionOptions | |
function setEncryptionOptions(encryptionType, keyLength) |
encryptionType: Number
- A keyLength: Number
- The key length.protect | |
function protect(protectionType, password) |
protectionType: Number
- A password: String
- Password to protect the workbook.protectSharedWorkbook | |
function protectSharedWorkbook(password) |
password: String
- Password to protect the workbook.unprotect | |
function unprotect(password) |
password: String
- Password to unprotect the workbook.unprotectSharedWorkbook | |
function unprotectSharedWorkbook(password) |
password: String
- Password to unprotect the workbook.removeMacro | |
function removeMacro() |
removeDigitalSignature | |
function removeDigitalSignature() |
acceptAllRevisions | |
function acceptAllRevisions() |
removeExternalLinks | |
function removeExternalLinks() |
getThemeColor | |
function getThemeColor(type) |
type: Number
- A setThemeColor | |
function setThemeColor(type, color) |
type: Number
- A color: Color
- the theme colorcustomTheme | |
function customTheme(themeName, colors) |
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 |
themeName: String
- The theme namecolors: Color[]
- The theme colorscopyTheme | |
function copyTheme(source) |
source: Workbook
- Source workbook.hasExernalLinks | |
function hasExernalLinks() |
updateLinkedDataSource | |
function updateLinkedDataSource(exteralWorkbooks) |
exteralWorkbooks: 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.
importXml | |
function importXml(url, sheetName, row, col) |
url: String
- the path of the xml file.sheetName: String
- the destination sheet name .row: Number
- the destination row of the xml.col: Number
- the destination column of the xml.exportXml | |
function exportXml(mapName, path) |
mapName: String
- name of the XML map that need to be exportedpath: String
- the export pathsetDigitalSignature | |
function setDigitalSignature(digitalSignatureCollection) |
digitalSignatureCollection: DigitalSignatureCollection
- addDigitalSignature | |
function addDigitalSignature(digitalSignatureCollection) |
digitalSignatureCollection: DigitalSignatureCollection
- getDigitalSignature | |
function getDigitalSignature() |
dispose | |
function dispose() |