Apache POI - Common 5.3.0 · org.apache.poi.ss.usermodel.Workbook

Modifier and Type	Field and Description
public static final int	MAX_SENSITIVE_SHEET_NAME_LEN Excel silently truncates long sheet names to 31 chars.
public static final int	PICTURE_TYPE_DIB Device independent bitmap
public static final int	PICTURE_TYPE_EMF Extended windows meta file
public static final int	PICTURE_TYPE_JPEG JPEG format
public static final int	PICTURE_TYPE_PICT Mac PICT format
public static final int	PICTURE_TYPE_PNG PNG format
public static final int	PICTURE_TYPE_WMF Windows Meta File

Method Summary

Modifier and Type	Method and Description
public int	Returns: the index of the added ole object, i.e. the storage id addOlePackage(byte[] the payload oleData, String the label of the payload label, String the original filename fileName, String the command to open the payload command) Adds an OLE package manager object with the given content to the sheet
public int	Returns: the index to this picture (1 based). addPicture(byte[] The bytes of the picture pictureData, int The format of the picture. format) Adds a picture to the workbook.
public void	addToolPack(UDFFinder the toolpack to register toolpack) Register a new toolpack in this workbook.
public Sheet	Returns: Sheet representing the cloned sheet. cloneSheet(int sheetNum) Create a Sheet from an existing sheet in the Workbook.
public void	close() Redeclares java.io.Closeable.close. Close the underlying input resource (File or Stream), from which the Workbook was read.
public CellStyle	Returns: the new Cell Style object createCellStyle() Create a new Cell style and add it to the workbook's style table
public DataFormat	Returns: the DataFormat object createDataFormat() Returns the instance of DataFormat for this workbook.
public EvaluationWorkbook	Returns: an evaluation workbook createEvaluationWorkbook()
public Font	Returns: new font object createFont() Create a new Font and add it to the workbook's font table
public Name	Returns: new defined name object createName() Creates a new (uninitialised) defined name in this workbook
public Sheet	Returns: Sheet representing the new sheet. createSheet() Create a Sheet for this Workbook, adds it to the sheets and returns the high level representation.
public Sheet	Returns: Sheet representing the new sheet. createSheet(String The name to set for the sheet. sheetname) Create a new sheet for this Workbook and return the high level representation.
public Font	Returns: the font with the matched attributes or `null` findFont(boolean bold, short color, short fontHeight, String name, boolean italic, boolean strikeout, short typeOffset, byte underline) Finds a font that matches the one with the supplied attributes
public int	Returns: the index of the active sheet (0-based) getActiveSheetIndex() Convenience method to get the active sheet.
public List<? extends Name>	Returns: a list of the defined names. An empty list is returned if none is found. getAllNames() Returns all defined names.
public List<? extends PictureData>	Returns: the list of pictures (a list of `PictureData` objects.) getAllPictures() Gets all pictures from the Workbook.
public CellReferenceType	Returns: the type of cell references used getCellReferenceType()
public CellStyle	Returns: CellStyle object at the index getCellStyleAt(int index within the set of styles (0-based) idx) Get the cell style object at the given index
public CreationHelper	getCreationHelper() Returns an object that handles instantiating concrete classes of the various instances one needs for HSSF and XSSF.
public int	Returns: the first tab that to display in the list of tabs (0-based). getFirstVisibleTab() Gets the first tab that is displayed in the list of tabs in excel.
public Font	Returns: font at the index getFontAt(int index number (0-based) idx) Get the font at the given index number
public boolean	getForceFormulaRecalculation() Whether Excel will be asked to recalculate all formulas when the workbook is opened.
public Row.MissingCellPolicy	getMissingCellPolicy() Retrieves the current policy on what to do when getting missing or blank cells from a row.
public Name	Returns: the defined name with the specified name. `null` if not found. getName(String the name of the defined name name)
public List<? extends Name>	Returns: a list of the defined names with the specified name. An empty list is returned if none is found. getNames(String the name of the defined name name) Returns all defined names with the given name.
public int	Returns: number of fonts (as int since POI 5.0.0) getNumberOfFonts() Get the number of fonts in the font table
public int	Returns: number of fonts getNumberOfFontsAsInt() Deprecated Get the number of fonts in the font table
public int	Returns: the total number of defined names in this workbook getNumberOfNames()
public int	Returns: the number of sheets getNumberOfSheets() Get the number of spreadsheets in the workbook
public int	Returns: count of cell styles getNumCellStyles() Get the number of styles the workbook contains
public String	Returns: String Null if no print area has been defined getPrintArea(int Zero-based sheet index (0 Represents the first sheet to keep consistent with java) sheetIndex) Retrieves the reference for the printarea of the specified sheet, the sheet name is appended to the reference even if it was not specified.
public Sheet	Returns: Sheet with the name provided or `null` if it does not exist getSheet(String of the sheet name) Get sheet with the given name
public Sheet	Returns: Sheet at the provided index getSheetAt(int of the sheet number (0-based physical & logical) index) Get the Sheet object at the given index.
public int	Returns: index of the sheet (0 based) getSheetIndex(String the sheet name name) Returns the index of the sheet by his name
public int	Returns: index of the sheet (0 based) getSheetIndex(Sheet the sheet to look up sheet) Returns the index of the given sheet
public String	Returns: Sheet name getSheetName(int sheet number (0 based) sheet) Get the sheet name
public SheetVisibility	Returns: the sheet visibility getSheetVisibility(int the index of the sheet sheetIx) Get the visibility (visible, hidden, very hidden) of a sheet in this workbook
public SpreadsheetVersion	Returns: SpreadsheetVersion enum getSpreadsheetVersion() Returns the spreadsheet version of this workbook
public boolean	Returns: `false` if this workbook is not visible in the GUI isHidden()
public boolean	Returns: `true` if sheet is hidden isSheetHidden(int Number sheetIx) Check whether a sheet is hidden.
public boolean	Returns: `true` if sheet is very hidden isSheetVeryHidden(int sheet index to check sheetIx) Check whether a sheet is very hidden.
public default Iterator<Sheet>	iterator() Implements java.lang.Iterable.iterator. Alias for `sheetIterator()` to allow foreach loops
public int	linkExternalWorkbook(String The name the workbook will be referenced as in formulas name, Workbook The open workbook to fetch the link required information from workbook) Adds the linking required to allow formulas referencing the specified external workbook to be added to this one.
public void	removeName(Name the name of the defined name name) Remove a defined name
public void	removePrintArea(int Zero-based sheet index (0 = First Sheet) sheetIndex) Delete the printarea for the sheet specified
public void	removeSheetAt(int of the sheet to remove (0-based) index) Removes sheet at the given index
public void	setActiveSheet(int index of the active sheet (0-based) sheetIndex) Convenience method to set the active sheet.
public void	setCellReferenceType(CellReferenceType the type of cell references used cellReferenceType)
public void	setFirstVisibleTab(int the first tab that to display in the list of tabs (0-based) sheetIndex) Sets the first tab that is displayed in the list of tabs in excel.
public void	setForceFormulaRecalculation(boolean true if the application will perform a full recalculation of workbook values when the workbook is opened value) Whether the application shall perform a full recalculation when the workbook is opened.
public void	setHidden(boolean pass `false` to make the workbook visible in the GUI hiddenFlag)
public void	setMissingCellPolicy(Row.MissingCellPolicy missingCellPolicy) Sets the policy on what to do when getting missing or blank cells from a row.
public void	setPrintArea(int Zero-based sheet index (0 Represents the first sheet to keep consistent with java) sheetIndex, String Valid name Reference for the Print Area reference) Sets the printarea for the sheet provided i.e. Reference = $A$1:$B$2
public void	setPrintArea(int Zero-based sheet index (0 = First Sheet) sheetIndex, int Column to begin printarea startColumn, int Column to end the printarea endColumn, int Row to begin the printarea startRow, int Row to end the printarea endRow) For the Convenience of Java Programmers maintaining pointers.
public void	setSelectedTab(int the index of the sheet to select (0 based) index) Sets the tab whose data is actually seen when the sheet is opened.
public void	setSheetHidden(int the sheet index (0-based) sheetIx, boolean True to mark the sheet as hidden, false otherwise hidden) Hide or unhide a sheet.
public void	setSheetName(int number (0 based) sheet, String name) Set the sheet name.
public void	setSheetOrder(String the name of the sheet to reorder sheetname, int the position that we want to insert the sheet into (0 based) pos) Sets the order of appearance for a given sheet.
public void	setSheetVisibility(int the sheet index (0-based) sheetIx, SheetVisibility the sheet visibility to set visibility) Hide or unhide a sheet.
public Iterator<Sheet>	Returns: an iterator of the sheets. sheetIterator() Returns an iterator of the sheets in the workbook in sheet order.
public default Spliterator<Sheet>	Returns: a spliterator of the sheets. spliterator() Overrides default java.lang.Iterable.spliterator. Returns a spliterator of the sheets in the workbook in sheet order.
public void	write(OutputStream - the java OutputStream you wish to write to stream) Write out this workbook to an OutputStream.

Inherited from java.lang.Iterable:: forEach

MAX_SENSITIVE_SHEET_NAME_LEN	back to summary
public static final int MAX_SENSITIVE_SHEET_NAME_LEN Excel silently truncates long sheet names to 31 chars. This constant is used to ensure uniqueness in the first 31 chars

PICTURE_TYPE_DIB	back to summary
public static final int PICTURE_TYPE_DIB Device independent bitmap

PICTURE_TYPE_EMF	back to summary
public static final int PICTURE_TYPE_EMF Extended windows meta file

PICTURE_TYPE_JPEG	back to summary
public static final int PICTURE_TYPE_JPEG JPEG format

PICTURE_TYPE_PICT	back to summary
public static final int PICTURE_TYPE_PICT Mac PICT format

PICTURE_TYPE_PNG	back to summary
public static final int PICTURE_TYPE_PNG PNG format

PICTURE_TYPE_WMF	back to summary
public static final int PICTURE_TYPE_WMF Windows Meta File

Method Detail

addOlePackage	back to summary
public int addOlePackage(byte[] oleData, String label, String fileName, String command) throws IOException Adds an OLE package manager object with the given content to the sheet Parameters oleData:byte[] the payload label:String the label of the payload fileName:String the original filename command:String the command to open the payload Returns:int the index of the added ole object, i.e. the storage id Exceptions IOException: if the object can't be embedded

addOlePackage

back to summary

public int addOlePackage(byte[] oleData, String label, String fileName, String command) throws IOException

Adds an OLE package manager object with the given content to the sheet

Parameters

oleData:byte[]: the payload
label:String: the label of the payload
fileName:String: the original filename
command:String: the command to open the payload

Returns:int

the index of the added ole object, i.e. the storage id

Exceptions

IOException:: if the object can't be embedded

addPicture back to summary

addPicture	back to summary
public int addPicture(byte[] pictureData, int format) Adds a picture to the workbook. Parameters pictureData:byte[] The bytes of the picture format:int The format of the picture. Returns:int the index to this picture (1 based). See Also `PICTURE_TYPE_EMF`, `PICTURE_TYPE_WMF`, `PICTURE_TYPE_PICT`, `PICTURE_TYPE_JPEG`, `PICTURE_TYPE_PNG`, `PICTURE_TYPE_DIB`

public int addPicture(byte[] pictureData, int format)

Adds a picture to the workbook.

Parameters

pictureData:byte[]: The bytes of the picture
format:int: The format of the picture.

Returns:int

the index to this picture (1 based).

addToolPack	back to summary
public void addToolPack(UDFFinder toolpack) Register a new toolpack in this workbook. Parameters toolpack:UDFFinder the toolpack to register

addToolPack

back to summary

public void addToolPack(UDFFinder toolpack)

Parameters

toolpack:UDFFinder: the toolpack to register

cloneSheet	back to summary
public Sheet cloneSheet(int sheetNum) Create a Sheet from an existing sheet in the Workbook. Returns:Sheet Sheet representing the cloned sheet.

cloneSheet

back to summary

public Sheet cloneSheet(int sheetNum)

Create a Sheet from an existing sheet in the Workbook.

Returns:Sheet: Sheet representing the cloned sheet.

close	back to summary
public void close() throws IOException Redeclares java.io.Closeable.close. Close the underlying input resource (File or Stream), from which the Workbook was read. Once this has been called, no further operations, updates or reads should be performed on the Workbook. Annotations @Override Exceptions IOException: Doc from java.io.Closeable.close. if an I/O error occurs

back to summary

public void close() throws IOException

Redeclares java.io.Closeable.close.

Close the underlying input resource (File or Stream), from which the Workbook was read.

Once this has been called, no further operations, updates or reads should be performed on the Workbook.

Annotations

@Override

Exceptions

IOException:

Doc from java.io.Closeable.close.

if an I/O error occurs

createCellStyle	back to summary
public CellStyle createCellStyle() Create a new Cell style and add it to the workbook's style table Returns:CellStyle the new Cell Style object Exceptions IllegalStateException: if the number of cell styles exceeded the limit for this type of Workbook.

createCellStyle

back to summary

public CellStyle createCellStyle()

Create a new Cell style and add it to the workbook's style table

Returns:CellStyle

the new Cell Style object

Exceptions

IllegalStateException:: if the number of cell styles exceeded the limit for this type of Workbook.

createDataFormat	back to summary
public DataFormat createDataFormat() Returns the instance of DataFormat for this workbook. Returns:DataFormat the DataFormat object

createDataFormat

back to summary

public DataFormat createDataFormat()

Returns the instance of DataFormat for this workbook.

Returns:DataFormat: the DataFormat object

createEvaluationWorkbook	back to summary
public EvaluationWorkbook createEvaluationWorkbook() Returns:EvaluationWorkbook an evaluation workbook

createFont	back to summary
public Font createFont() Create a new Font and add it to the workbook's font table Returns:Font new font object

createFont

back to summary

public Font createFont()

Create a new Font and add it to the workbook's font table

Returns:Font: new font object

createName	back to summary
public Name createName() Creates a new (uninitialised) defined name in this workbook Returns:Name new defined name object

createName

back to summary

public Name createName()

Creates a new (uninitialised) defined name in this workbook

Returns:Name: new defined name object

createSheet	back to summary
public Sheet createSheet() Create a Sheet for this Workbook, adds it to the sheets and returns the high level representation. Use this to create new sheets. Returns:Sheet Sheet representing the new sheet.

createSheet

back to summary

public Sheet createSheet()

Create a Sheet for this Workbook, adds it to the sheets and returns the high level representation. Use this to create new sheets.

Returns:Sheet: Sheet representing the new sheet.

createSheet back to summary

createSheet	back to summary
public Sheet createSheet(String sheetname) Create a new sheet for this Workbook and return the high level representation. Use this to create new sheets. Note that Excel allows sheet names up to 31 chars in length but other applications (such as OpenOffice) allow more. Some versions of Excel crash with names longer than 31 chars, others - truncate such names to 31 character. POI's SpreadsheetAPI silently truncates the input argument to 31 characters. Example: `Sheet sheet = workbook.createSheet("My very long sheet name which is longer than 31 chars"); // will be truncated assert 31 == sheet.getSheetName().length(); assert "My very long sheet name which i" == sheet.getSheetName();` Except the 31-character constraint, Excel applies some other rules: Sheet name MUST be unique in the workbook and MUST NOT contain the any of the following characters: 0x0000 0x0003 colon (:) backslash (\) asterisk (*) question mark (?) forward slash (/) opening square bracket ([) closing square bracket (]) The string MUST NOT begin or end with the single quote (') character. See `org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)` for a safe way to create valid names Parameters sheetname:String The name to set for the sheet. Returns:Sheet Sheet representing the new sheet. Exceptions IllegalArgumentException: if the name is null or invalid or workbook already contains a sheet with this name See Also `org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)`

public Sheet createSheet(String sheetname)

Create a new sheet for this Workbook and return the high level representation. Use this to create new sheets.

Note that Excel allows sheet names up to 31 chars in length but other applications (such as OpenOffice) allow more. Some versions of Excel crash with names longer than 31 chars, others - truncate such names to 31 character.

POI's SpreadsheetAPI silently truncates the input argument to 31 characters. Example:

Sheet sheet = workbook.createSheet("My very long sheet name which is longer than 31 chars"); // will be truncated
    assert 31 == sheet.getSheetName().length();
    assert "My very long sheet name which i" == sheet.getSheetName();

Except the 31-character constraint, Excel applies some other rules:

Sheet name MUST be unique in the workbook and MUST NOT contain the any of the following characters:

0x0000
0x0003
colon (:)
backslash (\)
asterisk (*)
question mark (?)
forward slash (/)
opening square bracket ([)
closing square bracket (])

The string MUST NOT begin or end with the single quote (') character.

See org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal) for a safe way to create valid names

Parameters

sheetname:String: The name to set for the sheet.

Returns:Sheet

Sheet representing the new sheet.

Exceptions

IllegalArgumentException:: if the name is null or invalid or workbook already contains a sheet with this name

findFont back to summary

findFont	back to summary
public Font findFont(boolean bold, short color, short fontHeight, String name, boolean italic, boolean strikeout, short typeOffset, byte underline) Finds a font that matches the one with the supplied attributes Returns:Font the font with the matched attributes or `null`

public Font findFont(boolean bold, short color, short fontHeight, String name, boolean italic, boolean strikeout, short typeOffset, byte underline)

Finds a font that matches the one with the supplied attributes

Returns:Font: the font with the matched attributes or null

getActiveSheetIndex	back to summary
public int getActiveSheetIndex() Convenience method to get the active sheet. The active sheet is is the sheet which is currently displayed when the workbook is viewed in Excel. 'Selected' sheet(s) is a distinct concept. Returns:int the index of the active sheet (0-based)

getActiveSheetIndex

back to summary

public int getActiveSheetIndex()

Convenience method to get the active sheet. The active sheet is is the sheet which is currently displayed when the workbook is viewed in Excel. 'Selected' sheet(s) is a distinct concept.

Returns:int: the index of the active sheet (0-based)

getAllNames	back to summary
public List<? extends Name> getAllNames() Returns all defined names. Returns:List<? extends Name> a list of the defined names. An empty list is returned if none is found.

getAllNames

back to summary

public List<? extends Name> getAllNames()

Returns all defined names.

Returns:List<? extends Name>: a list of the defined names. An empty list is returned if none is found.

getAllPictures back to summary

getAllPictures	back to summary
public List<? extends PictureData> getAllPictures() Gets all pictures from the Workbook. Returns:List<? extends PictureData> the list of pictures (a list of `PictureData` objects.)

public List<? extends PictureData> getAllPictures()

Gets all pictures from the Workbook.

Returns:List<? extends PictureData>: the list of pictures (a list of PictureData objects.)

getCellReferenceType	back to summary
public CellReferenceType getCellReferenceType() Returns:CellReferenceType the type of cell references used Since POI 5.2.1

getCellStyleAt	back to summary
public CellStyle getCellStyleAt(int idx) Get the cell style object at the given index Parameters idx:int index within the set of styles (0-based) Returns:CellStyle CellStyle object at the index

getCellStyleAt

back to summary

public CellStyle getCellStyleAt(int idx)

Get the cell style object at the given index

Parameters

idx:int: index within the set of styles (0-based)

Returns:CellStyle

CellStyle object at the index

getCreationHelper	back to summary
public CreationHelper getCreationHelper() Returns an object that handles instantiating concrete classes of the various instances one needs for HSSF and XSSF.

getFirstVisibleTab	back to summary
public int getFirstVisibleTab() Gets the first tab that is displayed in the list of tabs in excel. Returns:int the first tab that to display in the list of tabs (0-based).

getFirstVisibleTab

back to summary

public int getFirstVisibleTab()

Gets the first tab that is displayed in the list of tabs in excel.

Returns:int: the first tab that to display in the list of tabs (0-based).

getFontAt	back to summary
public Font getFontAt(int idx) Get the font at the given index number Parameters idx:int index number (0-based) Returns:Font font at the index Since 4.0.0

getFontAt

back to summary

public Font getFontAt(int idx)

Get the font at the given index number

Parameters

idx:int: index number (0-based)

Returns:Font

font at the index

Since

4.0.0

getForceFormulaRecalculation	back to summary
public boolean getForceFormulaRecalculation() Whether Excel will be asked to recalculate all formulas when the workbook is opened. Since 3.8

getForceFormulaRecalculation

back to summary

public boolean getForceFormulaRecalculation()

Whether Excel will be asked to recalculate all formulas when the workbook is opened.

Since: 3.8

getMissingCellPolicy back to summary

getMissingCellPolicy	back to summary
public Row.MissingCellPolicy getMissingCellPolicy() Retrieves the current policy on what to do when getting missing or blank cells from a row. The default is to return blank and null cells. `MissingCellPolicy`

public Row.MissingCellPolicy getMissingCellPolicy()

Retrieves the current policy on what to do when getting missing or blank cells from a row.

The default is to return blank and null cells. MissingCellPolicy

getName	back to summary
public Name getName(String name) Parameters name:String the name of the defined name Returns:Name the defined name with the specified name. `null` if not found.

getNames	back to summary
public List<? extends Name> getNames(String name) Returns all defined names with the given name. Parameters name:String the name of the defined name Returns:List<? extends Name> a list of the defined names with the specified name. An empty list is returned if none is found.

getNames

back to summary

public List<? extends Name> getNames(String name)

Returns all defined names with the given name.

Parameters

name:String: the name of the defined name

Returns:List<? extends Name>

a list of the defined names with the specified name. An empty list is returned if none is found.

getNumberOfFonts	back to summary
public int getNumberOfFonts() Get the number of fonts in the font table Returns:int number of fonts (as int since POI 5.0.0)

getNumberOfFonts

back to summary

public int getNumberOfFonts()

Get the number of fonts in the font table

Returns:int: number of fonts (as int since POI 5.0.0)

getNumberOfFontsAsInt	back to summary
public int getNumberOfFontsAsInt() Deprecated Get the number of fonts in the font table Returns:int number of fonts Annotations @Deprecated @Removal version:6.0.0 Since 4.0.0

getNumberOfFontsAsInt

back to summary

public int getNumberOfFontsAsInt()

Deprecated

Get the number of fonts in the font table

Returns:int

number of fonts

Annotations

@Deprecated
@Removal
version:6.0.0

Since

4.0.0

getNumberOfNames	back to summary
public int getNumberOfNames() Returns:int the total number of defined names in this workbook

getNumberOfSheets	back to summary
public int getNumberOfSheets() Get the number of spreadsheets in the workbook Returns:int the number of sheets

getNumberOfSheets

back to summary

public int getNumberOfSheets()

Get the number of spreadsheets in the workbook

Returns:int: the number of sheets

getNumCellStyles	back to summary
public int getNumCellStyles() Get the number of styles the workbook contains Returns:int count of cell styles

getNumCellStyles

back to summary

public int getNumCellStyles()

Get the number of styles the workbook contains

Returns:int: count of cell styles

getPrintArea	back to summary
public String getPrintArea(int sheetIndex) Retrieves the reference for the printarea of the specified sheet, the sheet name is appended to the reference even if it was not specified. Parameters sheetIndex:int Zero-based sheet index (0 Represents the first sheet to keep consistent with java) Returns:String String Null if no print area has been defined

getPrintArea

back to summary

public String getPrintArea(int sheetIndex)

Retrieves the reference for the printarea of the specified sheet, the sheet name is appended to the reference even if it was not specified.

Parameters

sheetIndex:int: Zero-based sheet index (0 Represents the first sheet to keep consistent with java)

Returns:String

String Null if no print area has been defined

getSheet back to summary

getSheet	back to summary
public Sheet getSheet(String name) Get sheet with the given name Parameters name:String of the sheet Returns:Sheet Sheet with the name provided or `null` if it does not exist

public Sheet getSheet(String name)

Get sheet with the given name

Parameters

name:String: of the sheet

Returns:Sheet

Sheet with the name provided or null if it does not exist

getSheetAt	back to summary
public Sheet getSheetAt(int index) Get the Sheet object at the given index. Parameters index:int of the sheet number (0-based physical & logical) Returns:Sheet Sheet at the provided index Exceptions IllegalArgumentException: if the index is out of range (index < 0 \|\| index >= getNumberOfSheets()).

getSheetAt

back to summary

public Sheet getSheetAt(int index)

Get the Sheet object at the given index.

Parameters

index:int: of the sheet number (0-based physical & logical)

Returns:Sheet

Sheet at the provided index

Exceptions

IllegalArgumentException:: if the index is out of range (index < 0 || index >= getNumberOfSheets()).

getSheetIndex	back to summary
public int getSheetIndex(String name) Returns the index of the sheet by his name Parameters name:String the sheet name Returns:int index of the sheet (0 based)

getSheetIndex

back to summary

public int getSheetIndex(String name)

Returns the index of the sheet by his name

Parameters

name:String: the sheet name

Returns:int

index of the sheet (0 based)

getSheetIndex	back to summary
public int getSheetIndex(Sheet sheet) Returns the index of the given sheet Parameters sheet:Sheet the sheet to look up Returns:int index of the sheet (0 based)

getSheetIndex

back to summary

public int getSheetIndex(Sheet sheet)

Returns the index of the given sheet

Parameters

sheet:Sheet: the sheet to look up

Returns:int

index of the sheet (0 based)

getSheetName	back to summary
public String getSheetName(int sheet) Get the sheet name Parameters sheet:int sheet number (0 based) Returns:String Sheet name

getSheetName

back to summary

public String getSheetName(int sheet)

Get the sheet name

Parameters

sheet:int: sheet number (0 based)

Returns:String

Sheet name

getSheetVisibility	back to summary
public SheetVisibility getSheetVisibility(int sheetIx) Get the visibility (visible, hidden, very hidden) of a sheet in this workbook Parameters sheetIx:int the index of the sheet Returns:SheetVisibility the sheet visibility Since POI 3.16 beta 2

getSheetVisibility

back to summary

public SheetVisibility getSheetVisibility(int sheetIx)

Get the visibility (visible, hidden, very hidden) of a sheet in this workbook

Parameters

sheetIx:int: the index of the sheet

Returns:SheetVisibility

the sheet visibility

Since

POI 3.16 beta 2

getSpreadsheetVersion	back to summary
public SpreadsheetVersion getSpreadsheetVersion() Returns the spreadsheet version of this workbook Returns:SpreadsheetVersion SpreadsheetVersion enum Since 3.14 beta 2

getSpreadsheetVersion

back to summary

public SpreadsheetVersion getSpreadsheetVersion()

Returns the spreadsheet version of this workbook

Returns:SpreadsheetVersion: SpreadsheetVersion enum
Since: 3.14 beta 2

isHidden	back to summary
public boolean isHidden() Returns:boolean `false` if this workbook is not visible in the GUI

isSheetHidden back to summary

isSheetHidden	back to summary
public boolean isSheetHidden(int sheetIx) Check whether a sheet is hidden. Note that a sheet could instead be set to be very hidden, which is different (`isSheetVeryHidden(int)`) Parameters sheetIx:int Number Returns:boolean `true` if sheet is hidden See Also `getSheetVisibility(int)`

public boolean isSheetHidden(int sheetIx)

Check whether a sheet is hidden.

Note that a sheet could instead be set to be very hidden, which is different (isSheetVeryHidden(int))

Parameters

sheetIx:int: Number

Returns:boolean

true if sheet is hidden

See Also

getSheetVisibility(int)

isSheetVeryHidden back to summary

isSheetVeryHidden	back to summary
public boolean isSheetVeryHidden(int sheetIx) Check whether a sheet is very hidden. This is different from the normal hidden status (`isSheetHidden(int)`) Parameters sheetIx:int sheet index to check Returns:boolean `true` if sheet is very hidden See Also `getSheetVisibility(int)`

public boolean isSheetVeryHidden(int sheetIx)

Check whether a sheet is very hidden.

This is different from the normal hidden status (isSheetHidden(int))

Parameters

sheetIx:int: sheet index to check

Returns:boolean

true if sheet is very hidden

See Also

getSheetVisibility(int)

iterator back to summary

public default Iterator<Sheet> iterator()

Implements java.lang.Iterable.iterator.

Alias for sheetIterator() to allow foreach loops

Returns:Iterator<Sheet>

Doc from java.lang.Iterable.iterator.

an Iterator.

Annotations

@Override

linkExternalWorkbook back to summary

public int linkExternalWorkbook(String name, Workbook workbook)

Adds the linking required to allow formulas referencing the specified external workbook to be added to this one.

In order for formulas such as "[MyOtherWorkbook]Sheet3!$A$5" to be added to the file, some linking information must first be recorded. Once a given external workbook has been linked, then formulas using it can added. Each workbook needs linking only once.

This linking only applies for writing formulas. To link things for evaluation, see FormulaEvaluator#setupReferencedWorkbooks(java.util.Map)

Parameters

name:String: The name the workbook will be referenced as in formulas
workbook:Workbook: The open workbook to fetch the link required information from

removeName

back to summary

public void removeName(Name name)

Remove a defined name

Parameters

name:Name: the name of the defined name

removePrintArea

back to summary

public void removePrintArea(int sheetIndex)

Delete the printarea for the sheet specified

Parameters

sheetIndex:int: Zero-based sheet index (0 = First Sheet)

removeSheetAt

back to summary

public void removeSheetAt(int index)

Removes sheet at the given index

Parameters

index:int: of the sheet to remove (0-based)

setActiveSheet

back to summary

public void setActiveSheet(int sheetIndex)

Convenience method to set the active sheet. The active sheet is is the sheet which is currently displayed when the workbook is viewed in Excel. 'Selected' sheet(s) is a distinct concept.

Parameters

sheetIndex:int: index of the active sheet (0-based)

setCellReferenceType	back to summary
public void setCellReferenceType(CellReferenceType cellReferenceType) Parameters cellReferenceType:CellReferenceType the type of cell references used Since POI 5.2.1

setFirstVisibleTab

back to summary

public void setFirstVisibleTab(int sheetIndex)

Sets the first tab that is displayed in the list of tabs in excel.

Parameters

sheetIndex:int: the first tab that to display in the list of tabs (0-based)

setForceFormulaRecalculation

back to summary

public void setForceFormulaRecalculation(boolean value)

Whether the application shall perform a full recalculation when the workbook is opened.

Typically you want to force formula recalculation when you modify cell formulas or values of a workbook previously created by Excel. When set to true, this flag will tell Excel that it needs to recalculate all formulas in the workbook the next time the file is opened.

Note, that recalculation updates cached formula results and, thus, modifies the workbook. Depending on the version, Excel may prompt you with "Do you want to save the changes in filename?" on close.

Parameters

value:boolean: true if the application will perform a full recalculation of workbook values when the workbook is opened

Since

3.8

setHidden	back to summary
public void setHidden(boolean hiddenFlag) Parameters hiddenFlag:boolean pass `false` to make the workbook visible in the GUI

setMissingCellPolicy	back to summary
public void setMissingCellPolicy(Row.MissingCellPolicy missingCellPolicy) Sets the policy on what to do when getting missing or blank cells from a row. This will then apply to all calls to `Row#getCell(int)` }. See `MissingCellPolicy`

setPrintArea

back to summary

public void setPrintArea(int sheetIndex, String reference)

Sets the printarea for the sheet provided

i.e. Reference = $A$1:$B$2

Parameters

sheetIndex:int: Zero-based sheet index (0 Represents the first sheet to keep consistent with java)
reference:String: Valid name Reference for the Print Area

setPrintArea back to summary

public void setPrintArea(int sheetIndex, int startColumn, int endColumn, int startRow, int endRow)

For the Convenience of Java Programmers maintaining pointers.

Parameters

sheetIndex:int: Zero-based sheet index (0 = First Sheet)
startColumn:int: Column to begin printarea
endColumn:int: Column to end the printarea
startRow:int: Row to begin the printarea
endRow:int: Row to end the printarea

See Also

setPrintArea(int, String)

setSelectedTab back to summary

public void setSelectedTab(int index)

Sets the tab whose data is actually seen when the sheet is opened. This may be different from the "selected sheet" since excel seems to allow you to show the data of one sheet when another is seen "selected" in the tabs (at the bottom).

Parameters

index:int: the index of the sheet to select (0 based)

See Also

Sheet#setSelected(boolean)

setSheetHidden back to summary

public void setSheetHidden(int sheetIx, boolean hidden)

Hide or unhide a sheet. Please note that the sheet currently set as active sheet (sheet 0 in a newly created workbook or the one set via setActiveSheet()) cannot be hidden.

Parameters

sheetIx:int: the sheet index (0-based)
hidden:boolean: True to mark the sheet as hidden, false otherwise

setSheetName back to summary

public void setSheetName(int sheet, String name)

Set the sheet name.

See org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal) for a safe way to create valid names

Parameters

sheet:int: number (0 based)

Exceptions

IllegalArgumentException:: if the name is null or invalid or workbook already contains a sheet with this name

setSheetOrder

back to summary

public void setSheetOrder(String sheetname, int pos)

Sets the order of appearance for a given sheet.

Parameters

sheetname:String: the name of the sheet to reorder
pos:int: the position that we want to insert the sheet into (0 based)

setSheetVisibility

back to summary

public void setSheetVisibility(int sheetIx, SheetVisibility visibility)

Hide or unhide a sheet. Please note that the sheet currently set as active sheet (sheet 0 in a newly created workbook or the one set via setActiveSheet()) cannot be hidden.

Parameters