com.aspose.cells
Class Workbook

java.lang.Object
    com.aspose.cells.Workbook

public class Workbook 
extends java.lang.Object

//Open a designer file
String designerFile = "designer.xls";
Workbook workbook = new Workbook(designerFile);

//Set scroll bars
workbook.setHScrollBarVisible(false);
workbook.setVScrollBarVisible(false);

//Replace the placeholder string with new values
int newInt = 100;
workbook.replace("OldInt", newInt);

String newString = "Hello!";
workbook.replace("OldString", newString);
XlsSaveOptions saveOptions = new XlsSaveOptions();
workbook.save("result.xls", saveOptions);

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:

The following code shows how to use the Workbook constructor to create and initialize a new instance of the class.

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:

The following code shows how to use the Workbook constructor to create and initialize a new instance of the class.

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.

Parameters:

stream - The stream.

loadOptions - The load options

public WorkbookSettings getSettings() / public void setSettings(WorkbookSettings value)

Gets the workbook settings.

public WorksheetCollection getWorksheets()

Gets the WorksheetCollection collection in the spreadsheet.

Returns:

WorksheetCollection collection

public StyleCollection getStyles()

Gets the StyleCollection collection.

public com.aspose.cells.Color[] getColors()

Returns colors in the palette for the spreadsheet.

The palette has 56 entries, each represented by an RGB value.

public Style getDefaultStyle() / public void setDefaultStyle(Style value)

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.

Workbook workbook = new Workbook();
Style defaultStyle = workbook.getDefaultStyle();
defaultStyle.getFont().setName("Tahoma");
workbook.setDefaultStyle(defaultStyle);

public boolean isProtected()

Gets a value that indicates whether the Workbook is protected.

NOTE: This member is now obsolete. Instead, please use Workbook.Settings.IsProtected property. This property will be removed 12 months later since February 2010. Aspose apologizes for any inconvenience you may have experienced.

public int getLanguage() / public void setLanguage(int value)

Gets or sets the user interface language of the Workbook version based on CountryCode that has saved the file. The value of the property is CountryCode integer constant.

Please use Workbook.Settings.LanguageCode property. This property will be removed 12 months later since February 2010. Aspose apologizes for any inconvenience you may have experienced.

public int getRegion() / public void setRegion(int value)

Gets or sets the system regional settings based on CountryCode at the time the file was saved. The value of the property is CountryCode integer constant.

If you do not want to use the region saved in the file, please reset it after reading the file. NOTE: This member is now obsolete. Instead, please use Workbook.Settings.Region property. This property will be removed 12 months later since February 2010. Aspose apologizes for any inconvenience you may have experienced.

public boolean hasMacro()

Indicates if this spreadsheet contains macro/VBA.

public boolean isDigitallySigned()

Indicates if this spreadsheet is digitally signed.

public boolean hasRevisions()

Returns true if the workbook has any tracked changes

public java.lang.String getFileName() / public void setFileName(java.lang.String value)

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.

public DataSorter getDataSorter()

Gets a DataSorter object to sort data.

public SaveOptions getSaveOptions()

Gets the saving and printing options

public java.lang.String getTheme()

Gets the theme name.

public BuiltInDocumentPropertyCollection getBuiltInDocumentProperties()

Returns a DocumentProperties 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

Example:

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

public CustomDocumentPropertyCollection getCustomDocumentProperties()

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

Example:

excel.getCustomDocumentProperties().add("Checked by", "Jane");

public int getFileFormat() / public void setFileFormat(int value)

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

public InterruptMonitor getInterruptMonitor() / public void setInterruptMonitor(InterruptMonitor value)

Gets and sets the interrupt monitor.

public ContentTypePropertyCollection getContentTypeProperties()

gets the contenttypeproperties objects in the workbook.

public java.lang.String getRibbonXml() / public void setRibbonXml(java.lang.String value)

gets and sets the ribbonXml in the workbook.

public boolean hasExernalLinks()

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

Returns:

Whether this workbook contains external links to other data source.

public void updateLinkedDataSource(com.aspose.cells.Workbook[] exteralWorkbooks)
                           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:

exteralWorkbooks - External workbooks are refered 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.

public void importXml(java.lang.String url, java.lang.String sheetName, int row, int col)
              throws java.lang.Exception

import a xml to the workbook.

Parameters:

url - the path of the xml file.

sheetName - the destination sheet name .

row - the destination row of the xml.

col - the destination column of the xml.

public void setDigitalSignature(DigitalSignatureCollection digitalSignatureCollection)

Set digital signature to file.

Only support adding digital signature to OOXML file.

Parameters:

digitalSignatureCollection -

public DigitalSignatureCollection getDigitalSignature()

Get digital signature from file.

public void open(java.io.InputStream stream)
         throws java.lang.Exception

Open the filestream.

NOTE: This member is now obsolete. Instead, please use Workbook(Stream) constructor. This property will be removed 12 months later since JANUARY 2012. Aspose apologizes for any inconvenience you may have experienced.

Parameters:

stream - The input stream.

public void open(java.io.InputStream stream, int fileFormatType)
         throws java.lang.Exception

Open the filestream.

NOTE: This member is now obsolete. Instead, please use Workbook(Stream,LoadOptions) constructor. This property will be removed 12 months later since JANUARY 2012. Aspose apologizes for any inconvenience you may have experienced.

Parameters:

stream - The input stream.

fileFormatType - A FileFormatType value.

public void open(java.io.InputStream stream, int fileFormatType, java.lang.String password)
         throws java.lang.Exception

Open the filestream.

NOTE: This member is now obsolete. Instead, please use Workbook(Stream,LoadOptions) constructor. This property will be removed 12 months later since JANUARY 2012. Aspose apologizes for any inconvenience you may have experienced.

Parameters:

stream - The input stream.

fileFormatType - A FileFormatType value. the file format type.

password - The password of the opening the file.

public void open(java.lang.String fileName)
         throws java.lang.Exception

Open the file.

NOTE: This member is now obsolete. Instead, please use Workbook(string) constructor. This property will be removed 12 months later since JANUARY 2012. Aspose apologizes for any inconvenience you may have experienced.

Parameters:

fileName - The file name.

public void open(java.lang.String fileName, int fileFormatType)
         throws java.lang.Exception

Open the filestream.

NOTE: This member is now obsolete. Instead, please use Workbook(string, LoadOptions) constructor. This property will be removed 12 months later since JANUARY 2012. Aspose apologizes for any inconvenience you may have experienced.

Parameters:

fileName - The file name.

fileFormatType - A FileFormatType value. the file format type.

public void open(java.lang.String fileName, int fileFormatType, java.lang.String password)
         throws java.lang.Exception

Open the file.

NOTE: This member is now obsolete. Instead, please use Workbook(string,LoadOptions) constructor. This property will be removed 12 months later since JANUARY 2012. Aspose apologizes for any inconvenience you may have experienced.

Parameters:

fileName - The file name.

fileFormatType - A FileFormatType value. The file format type.

password - The password for opening the file.

public void open(java.io.InputStream stream, char separator, boolean convertNumericData, java.lang.String encoding)
         throws java.lang.Exception

Open the file.

NOTE: This member is now obsolete. Instead, please use Workbook(Stream,LoadOptions) constructor. This property will be removed 12 months later since JANUARY 2012. Aspose apologizes for any inconvenience you may have experienced.

Parameters:

stream - The file stream.

separator - The separator

convertNumericData - Whether convert the string value to number or date time value.

encoding - The file encoding.

public void open(java.lang.String fileName, char separator, boolean convertNumericData, java.lang.String encoding)
         throws java.lang.Exception

Open the file.

NOTE: This member is now obsolete. Instead, please use Workbook(string,LoadOptions) constructor. This property will be removed 12 months later since JANUARY 2012. Aspose apologizes for any inconvenience you may have experienced.

Parameters:

fileName - The file name.

separator - The separator

convertNumericData - Whether convert the string value to number or date time value.

encoding - The file encoding.

public void setOleSize(int startRow, int endRow, int startColumn, int endColumn)

public void initialize()

Initialize the workbook object.

public void save(java.lang.String fileName)
         throws java.lang.Exception

Creates and saves the specified file to the disk.

File format is according to file extension.

After calling Save method, data in Workbook instance will be reset.

Parameters:

fileName - The name of created file.

public void save(java.lang.String fileName, int fileFormatType)
         throws java.lang.Exception

Creates and saves the specified to the disk.

After calling Save method, data in Workbook instance will be reset.NOTE: This member is now obsolete. Instead, please use Workbook.Save(string,SaveFormat) method. This property will be removed 12 months later since JULY 2010. Aspose apologizes for any inconvenience you may have experienced.

Parameters:

fileName - The name of created file.

fileFormatType - A FileFormatType value. Excel file format type

Example:

Workbook workbook = new Workbook();
WorksheetCollection sheets = workbook.getWorksheets();
Cells cells = sheets.get(0).getCells();
cells.get("A1").putValue("Hello world!");
workbook.Save("D:\\test.xls", FileFormatType.EXCEL_97_TO_2003);

public void save(java.lang.String fileName, SaveOptions saveOptions)
         throws java.lang.Exception

Save the workbook to the disk.

Parameters:

fileName - The file name.

saveOptions - The save options.

public void save(java.io.OutputStream stream, int saveFormat)
         throws java.lang.Exception

Save the workbook to the stream.

Parameters:

stream - The file stream.

saveFormat - A SaveFormat value. The save file format type.

public void save(java.io.OutputStream stream, SaveOptions saveOptions)
         throws java.lang.Exception

Save the workbook to the stream.

Parameters:

stream - The file stream.

saveOptions - The save options.

public Style createStyle()

Creates a new style.

Returns:

Returns a style object.

public CellsColor createCellsColor()

Creates a CellsColor object.

Returns:

Returns a CellsColor object.

public int replace(java.lang.String placeHolder, java.lang.String newValue)

Replaces a cell's value with a new string.

Parameters:

placeHolder - Cell placeholder

newValue - String value to replace

Example:

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

public int replace(java.lang.String placeHolder, int newValue)

Replaces a cell's value with a new integer.

Parameters:

placeHolder - Cell placeholder

newValue - Integer value to replace

Example:

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

public int replace(java.lang.String placeHolder, double newValue)

Replaces a cell's value with a new double.

Parameters:

placeHolder - Cell placeholder

newValue - Double value to replace

Example:

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

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);

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);

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);

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.

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.

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

public void copy(Workbook source)
         throws java.lang.Exception

Copies data from a source Workbook object.

Parameters:

source - Source Workbook object.

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.

Parameters:

secondWorkbook - Another Workbook object.

public Style getStyleInPool(int index)

Gets the style in the style pool. All styles in the workbook will be gathered into a pool. There is only a simple referrence 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:

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.

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

public void calculateFormula()

Calculates the result of formulas.

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

[G]

GETPIVOTDATA

[I]

INFO

[J]

JIS

[P]

PHONETIC

[R]

REGISTER.ID, RTD

[S]

SQL.REQUEST

[Y]

YIELD, YIELDDISC

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.

public void calculateFormula(boolean ignoreError, ICustomFunction customFunction)

Calculates the result of formulas.

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 boolean validateFormula(java.lang.String formula)

Checks if a formula is valid.

Parameters:

formula - Formula string.

Returns:

True: valid formula. False: invalid formula or unsupported function.

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.

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.

public void unprotect(java.lang.String password)

Unprotects a workbook.

Parameters:

password - Password to unprotect the workbook.

public void decrypt(java.lang.String password)

Decrypts an Excel file.

Parameters:

password - Password to decrypt the Excel file.

public void removeMacro()

Removes VBA/macro from this spreadsheet.

public void removeDigitallySign()

Removes digital signature from this spreadsheet.

Returns:

public void acceptAllRevisions()

Accepts all tracked changes in the workbook.

public void removeExternalLinks()

Removes all external links in the workbook.

public void setEncryptionOptions(int encryptionType, int keyLength)

Set Encryption Options.

Parameters:

encryptionType - A EncryptionType value. The encryption type.

keyLength - The key length.

public com.aspose.cells.Color getThemeColor(int type)

Gets theme color.

Parameters:

type - A ThemeColorType value. The theme color type.

Returns:

The theme color.

public void setThemeColor(int type, com.aspose.cells.Color color)

Sets the theme color

Parameters:

type - A ThemeColorType value. The theme color type.

color - the theme color

public void customTheme(java.lang.String themeName, com.aspose.cells.Color[] colors)

Customs the theme.

The length ofcolors 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:

themeName - The theme name

colors - The theme colors

public void copyTheme(Workbook source)

Copy the theme from other workbook.

Parameters:

source - Another workbook.