Represents a root object to create an Excel spreadsheet.
The Workbook class denotes an Excel spreadsheet. Each spreadsheet can contain multiple worksheets.
The basic feature of the class is to open and save native excel files.
The class has some advanced features like copying data from other Workbooks, combining two Workbooks and protecting the Excel spreadsheet.
Example:
//Open a designer file
String designerFile = "designer.xls";
Workbook workbook = new Workbook(designerFile);
//Set scroll bars
workbook.getSettings().setHScrollBarVisible(false);
workbook.getSettings().setVScrollBarVisible(false);
//Replace the placeholder string with new values
int newInt = 100;
workbook.replace("OldInt", newInt);
String newString = "Hello!";
workbook.replace("OldString", newString);
workbook.save("result.xls");
Constructor Summary
Workbook()
Initializes a new instance of the Workbook class.
Workbook(int fileFormatType)
Initializes a new instance of the Workbook class.
Workbook(java.lang.String file)
Initializes a new instance of the Workbook class and open a file.
Workbook(java.io.InputStream stream)
Initializes a new instance of the Workbook class and open a stream.
Workbook(java.lang.String file, LoadOptions loadOptions)
Initializes a new instance of the Workbook class and open a file.
Workbook(java.io.InputStream stream, LoadOptions loadOptions)
Initializes a new instance of the Workbook class and open stream.
If this workbook contains external links to other data source,
Aspose.Cells will attempt to retrieve the latest data.
Constructor Detail
Workbook
public 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:
Workbook workbook = new Workbook();
Workbook
public Workbook(int fileFormatType)
Initializes a new instance of the Workbook class.
The default file format type is Excel97To2003.
Parameters:
fileFormatType - A FileFormatType value.
The new file format.
Example:
Workbook workbook = new Workbook(FileFormatType.XLSX);
Workbook
public Workbook(java.lang.String file)
throws java.lang.Exception
Initializes a new instance of the Workbook class and open a file.
Parameters:
file - The file name.
Workbook
public Workbook(java.io.InputStream stream)
throws java.lang.Exception
Initializes a new instance of the Workbook class and open a stream.
Parameters:
stream - The stream.
Workbook
public Workbook(java.lang.String file, LoadOptions loadOptions)
throws java.lang.Exception
Initializes a new instance of the Workbook class and open a file.
Parameters:
file - The file name.
loadOptions - The load options
Workbook
public Workbook(java.io.InputStream stream, LoadOptions loadOptions)
throws java.lang.Exception
Initializes a new instance of the Workbook class and open stream.
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:
Performs application-defined tasks associated with freeing, releasing, or
resetting unmanaged resources.
parseFormulas
public void parseFormulas(boolean ignoreError)
Parses all formulas which have not been parsed when they were loaded from template file or set to a cell.
Parameters:
ignoreError - 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
public void startAccessCache(int 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.
public int replace(java.lang.String placeHolder, java.lang.String[] newValues, boolean isVertical)
Replaces a cell's value with a new string array.
Parameters:
placeHolder - Cell placeholder
newValues - String array to replace
isVertical - True - Vertical, False - Horizontal
Example:
Workbook workbook = new Workbook();
////......
String[] newValues = new String[]{"Tom", "Alice", "Jerry"};
workbook.replace("AnOldValue", newValues, true);
replace
public int replace(java.lang.String placeHolder, int[] newValues, boolean isVertical)
Replaces cells' values with an integer array.
Parameters:
placeHolder - Cell placeholder
newValues - Integer array to replace
isVertical - True - Vertical, False - Horizontal
Example:
Workbook workbook = new Workbook();
////......
int[] newValues = new int[]{1, 2, 3};
workbook.replace("AnOldValue", newValues, true);
replace
public int replace(java.lang.String placeHolder, double[] newValues, boolean isVertical)
Replaces cells' values with a double array.
Parameters:
placeHolder - Cell placeholder
newValues - Double array to replace
isVertical - True - Vertical, False - Horizontal
Example:
Workbook workbook = new Workbook();
////......
double[] newValues = new double[]{1.23, 2.56, 3.14159};
workbook.replace("AnOldValue", newValues, true);
replace
public int replace(boolean boolValue, java.lang.Object newValue)
Replaces cells' values with new data.
Parameters:
boolValue - The boolean value to be replaced.
newValue - New value. Can be string, integer, double or DateTime value.
replace
public int replace(int intValue, java.lang.Object newValue)
Replaces cells' values with new data.
Parameters:
intValue - The integer value to be replaced.
newValue - New value. Can be string, integer, double or DateTime value.
replace
public int replace(java.lang.String placeHolder, java.lang.String newValue, ReplaceOptions options)
Replaces a cell's value with a new string.
Parameters:
placeHolder - Cell placeholder
newValue - String value to replace
options - The replace options
copy
public void copy(Workbook source, CopyOptions copyOptions)
throws java.lang.Exception
Copies data from a source Workbook object.
Parameters:
source - Source Workbook object.
copyOptions -
copy
public void copy(Workbook source)
throws java.lang.Exception
Copies data from a source Workbook object.
Parameters:
source - Source Workbook object.
combine
public void combine(Workbook secondWorkbook)
throws java.lang.Exception
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.
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.
Parameters:
index - The index.
Returns:
The style in the pool corresponds to given index, may be null.
public void changePalette(com.aspose.cells.Color color, int 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:
color - Color structure.
index - Palette index, 0 - 55.
isColorInPalette
public boolean isColorInPalette(com.aspose.cells.Color color)
Checks if a color is in the palette for the spreadsheet.
Parameters:
color - Color structure.
Returns:
Returns true if this color is in the palette. Otherwise, returns false
calculateFormula
public void 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
public void calculateFormula(boolean ignoreError)
Calculates the result of formulas.
Parameters:
ignoreError - Indicates if hide the error in calculating formulas. The error may be unsupported function, external links, etc.
calculateFormula
public void calculateFormula(boolean ignoreError, ICustomFunction 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:
ignoreError - Indicates if hide the error in calculating formulas. The error may be unsupported function, external links, etc.
customFunction - The custom formula calculation functions to extend the calculation engine.
public void refreshDynamicArrayFormulas(boolean calculate)
Refreshes dynamic array formulas(spill into new range of neighboring cells according to current data)
Parameters:
calculate - Whether calculates and update cell values for those dynamic array formulas when
the spilled range changes.
getMatchingColor
public com.aspose.cells.Color getMatchingColor(com.aspose.cells.Color rawColor)
Find best matching Color in current palette.
Parameters:
rawColor - Raw color.
Returns:
Best matching color.
setEncryptionOptions
public void setEncryptionOptions(int encryptionType, int keyLength)
Set Encryption Options.
Parameters:
encryptionType - A EncryptionType value. The encryption type.
keyLength - The key length.
protect
public void protect(int protectionType, java.lang.String password)
Protects a workbook.
Parameters:
protectionType - A ProtectionType value. Protection type.
password - Password to protect the workbook.
protectSharedWorkbook
public void protectSharedWorkbook(java.lang.String password)
Protects a shared workbook.
Parameters:
password - Password to protect the workbook.
unprotect
public void unprotect(java.lang.String password)
Unprotects a workbook.
Parameters:
password - Password to unprotect the workbook.
unprotectSharedWorkbook
public void unprotectSharedWorkbook(java.lang.String password)
Unprotects a shared workbook.
Parameters:
password - Password to unprotect the workbook.
removeMacro
public void removeMacro()
Removes VBA/macro from this spreadsheet.
removeDigitalSignature
public void removeDigitalSignature()
Removes digital signature from this spreadsheet.
acceptAllRevisions
public void acceptAllRevisions()
Accepts all tracked changes in the workbook.
removeExternalLinks
public void 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
public com.aspose.cells.Color getThemeColor(int type)
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.
updateLinkedDataSource
public void updateLinkedDataSource(com.aspose.cells.Workbook[] externalWorkbooks)
throws java.lang.Exception
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.
Parameters:
externalWorkbooks -
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
public void importXml(java.lang.String url, java.lang.String sheetName, int row, int col)
throws java.lang.Exception
Imports/Updates an XML data file into the workbook.
Parameters:
url - the url/path of the xml file.
sheetName - the destination sheet name.
row - the destination row
col - the destination column
Example:
Workbook wb = new Workbook("Book1.xlsx");
wb.importXml("xml.xml", "Sheet1", 0, 0);
wb.save("output.xlsx");
importXml
public void importXml(java.io.InputStream stream, java.lang.String sheetName, int row, int col)
throws java.lang.Exception
Imports/Updates an XML data file into the workbook.
Parameters:
stream - the xml file stream.
sheetName - the destination sheet name.
row - the destination row.
col - the destination column.
exportXml
public void exportXml(java.lang.String mapName, java.lang.String path)
throws java.lang.Exception
Export XML data linked by the specified XML map.
Parameters:
mapName - name of the XML map that need to be exported
path - the export path
Example:
Workbook wb = new Workbook("Book1.xlsx");
//Make sure that the source xlsx file contains a XmlMap.
XmlMap xmlMap = wb.getWorksheets().getXmlMaps().get(0);
wb.exportXml(xmlMap.getName(), "output.xml");
exportXml
public void exportXml(java.lang.String mapName, java.io.OutputStream stream)
throws java.lang.Exception
Export XML data linked by the specified XML map.
Parameters:
mapName - name of the XML map that need to be exported
com.aspose.cells.Workbook