Apache POI – Core Classes
This chapter explains a few classes and methods under the Apache POI API that are critical to work on Excel files using Java programs.
Workbook
This is the super-interface of all classes that create or maintain Excel workbooks. It belongs to the org.apache.poi.ss.usermodel package. The two classes that implement this interface are as follows −
HSSFWorkbook − This class has methods to read and write Microsoft Excel files in .xls format. It is compatible with MS-Office versions 97–2003.
XSSFWorkbook − This class has methods to read and write Microsoft Excel and OpenOffice xml files in .xls or .xlsx format. It is compatible with MS-Office versions 2007 or later.
HSSFWorkbook
It is a high-level class under the org.apache.poi.hssf.usermodel package. It implements the Workbook interface and is used for Excel files in .xls format. Listed below are some of the methods and constructors under this class.
Class Constructors
| Sr.No. |
Constructor & Description |
| 1 |
HSSFWorkbook()
Creates a new HSSFWorkbook object from scratch. |
| 2 |
HSSFWorkbook(DirectoryNode directory, boolean preserveNodes)
Creates a new HSSFWworkbook objectinside a specific directory. |
| 3 |
HSSFWorkbook(DirectoryNode directory, POIFSFileSystem fs, boolean preserveNodes)
Given a POIFSFileSystem object and a specific directory within it, it creates an SSFWorkbook object to read a specified workbook.
|
| 4 |
HSSFWorkbook(java.io.InputStream s)
Creates a new HSSFWorkbook object using an input stream. |
| 5 |
HSSFWorkbook(java.io.InputStream s, boolean preserveNodes)
Constructs a POI file system around your input stream. |
| 6 |
HSSFWorkbook(POIFSFileSystem fs)
Constructs a new HSSFWorkbook object using a POIFSFileSystem object. |
| 7 |
HSSFWorkbook(POIFSFileSystem fs, boolean preserveNodes)
Given a POIFSFileSystem object, it creates a new HSSFWorkbook object to read a specified workbook. |
The frequently used parameters inside these constructors are −
directory − It is the POI filesystem directory to process from.
fs − It is the POI filesystem that contains the workbook stream.
preservenodes − This is an optional parameter that decides whether to preserve other nodes like macros. It consumes a lot of memory as it stores all the POIFileSystem in memory (if set).
Note − The HSSFWorkbook class contains a number of methods; however they are compatible with xls format only. In this tutorial, the focus is on the latest version of Excel file formats. Hence, the class methods of HSSFWorkbook are not listed here. If you require these class methods, then refer POI-HSSFWorkbook class API at https://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFWorkbook.html.
XSSFWorkbook
It is a class that is used to represent both high and low level Excel file formats. It belongs to the org.apache.xssf.usemodel package and implements the Workbook interface. Listed below are the methods and constructors under this class.
Class Constructors
| Sr.No. |
Constructor & Description |
| 1 |
XSSFWorkbook()
Creates a new XSSFworkbook object from scratch. |
| 2 |
XSSFWorkbook(java.io.File file)
Constructs an XSSFWorkbook object from a given file. |
| 3 |
XSSFWorkbook(java.io.InputStream is)
Constructs an XSSFWorkbook object, by buffering the whole input stream into memory and then opening an OPCPackage object for it. |
| 4 |
XSSFWorkbook(java.lang.String path)
Constructs an XSSFWorkbook object given the full path of a file. |
Class Methods
| Sr.No. |
Method & Description |
| 1 |
createSheet()
Creates an XSSFSheet for this workbook, adds it to the sheets, and returns the high level representation. |
| 2 |
createSheet(java.lang.String sheetname)
Creates a new sheet for this Workbook and returns the high level representation. |
| 3 |
createFont()
Creates a new font and adds it to the workbook's font table. |
| 4 |
createCellStyle()
Creates a new XSSFCellStyle and adds it to the workbook's style table. |
| 5 |
createFont()
Creates a new font and adds it to the workbook's font table. |
| 6 |
setPrintArea(int sheetIndex, int startColumn, int endColumn, int startRow,int endRow)
Sets the print area of a given sheet as per the specified parameters. |
For the remaining methods of this class, refer the complete API document at: https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFWorkbook.html. for the complete list of methods.
Sheet
Sheet is an interface under the org.apache.poi.ss.usermodel package and it is a super-interface of all classes that create high or low level spreadsheets with specific names. The most common type of spreadsheet is worksheet, which is represented as a grid of cells.
HSSFSheet
This is a class under the org.apache.poi.hssf.usermodel package. It can create excel spreadsheets and it allows to format the sheet style and sheet data.
Class Constructors
| Sr.No. |
Constructor & Description |
| 1 |
HSSFSheet(HSSFWorkbook workbook)
Creates new HSSFSheet called by HSSFWorkbook to create a sheet from scratch. |
| 2 |
HSSFSheet(HSSFWorkbook workbook, InternalSheet sheet)
Creates an HSSFSheet representing the given sheet object. |
XSSFSheet
This is a class which represents high level representation of excel spreadsheet. It is under org.apache.poi.hssf.usermodel package.
Class Constructors
| Sr.No. |
Constructor & Description |
| 1 |
XSSFSheet()
Creates new XSSFSheet - called by XSSFWorkbook to create a sheet from scratch. |
| 2 |
XSSFSheet(PackagePart part, PackageRelationship rel)
Creates an XSSFSheet representing the given package part and relationship. |
Class Methods
| Sr.No. |
Method & Description |
| 1 |
addMergedRegion(CellRangeAddress region)
Adds a merged region of cells (hence those cells form one). |
| 2 |
autoSizeColumn(int column)
Adjusts the column width to fit the contents. |
| 3 |
iterator()
This method is an alias for rowIterator() to allow foreach loops |
| 4 |
addHyperlink(XSSFHyperlink hyperlink)
Registers a hyperlink in the collection of hyperlinks on this sheet |
For the remaining methods of this class, refer the complete API at: https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFSheet.html.
Row
This is an interface under the org.apache.poi.ss.usermodel package. It is used for high-level representation of a row of a spreadsheet. It is a super-interface of all classes that represent rows in POI library.
XSSFRow
This is a class under the org.apache.poi.xssf.usermodel package. It implements the Row interface, therefore it can create rows in a spreadsheet. Listed below are the methods and constructors under this class.
Class Methods
| Sr.No. |
Method & Description |
| 1 |
createCell(int columnIndex)
Creates new cells within the row and returns it. |
| 2 |
setHeight(short height)
Sets the height in short units. |
For the remaining methods of this class, follow the given link https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFRow.html
Cell
This is an interface under the org.apache.poi.ss.usermodel package. It is a super-interface of all classes that represent cells in the rows of a spreadsheet.
Cells can take various attributes such as blank, numeric, date, error, etc. Cells should have their own numbers (0 based) before being added to a row.
XSSFCell
This is a class under the org.apache.poi.xssf.usermodel package. It implements the Cell interface. It is a high-level representation of cells in the rows of a spreadsheet.
Field Summary
Listed below are some of the fields of the XSSFCell class along with their description.
| Sr.No. |
Cell Type & Description |
| 1 |
CELL_TYPE_BLANK
Represents blank cell |
| 2 |
CELL_TYPE_BOOLEAN
Represents Boolean cell (true or false) |
| 3 |
CELL_TYPE_ERROR
Represents error value on a cell |
| 4 |
CELL_TYPE_FORMULA
Represents formula result on a cell |
| 5 |
CELL_TYPE_NUMERIC
Represents numeric data on a cell |
| 6 |
CELL_TYPE_STRING
Represents string (text) on a cell |
Class Methods
| Sr.No. |
Method & Description |
| 1 |
setCellStyle(CellStyle style)
Sets the style for the cell. |
| 2 |
setCellType(int cellType)
Sets the type of cells (numeric, formula, or string). |
| 3 |
setCellValue(boolean value)
Sets a boolean value for the cell. |
| 4 |
setCellValue(java.util.Calendar value)
Sets a date value for the cell. |
| 5 |
setCellValue(double value)
Sets a numeric value for the cell. |
| 6 |
setCellValue(java.lang.String str)
Sets a string value for the cell. |
| 7 |
setHyperlink(Hyperlink hyperlink)
Assigns a hyperlink to this cell. |
For the remaining methods and fields of this class, visit the following link: https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFCell.html
XSSFCellStyle
This is a class under the org.apache.poi.xssf.usermodel package. It will provide possible information regarding the format of the content in a cell of a spreadsheet. It also provides options for modifying that format. It implements the CellStyle interface.
Field Summary
The following table lists a few fields that are inherited from the CellStyle interface.
| Sr.No. |
Field & Description |
| 1 |
ALIGN_CENTER
Center align the cell contents |
| 2 |
ALIGN_CENTER_SELECTION
Center-selection horizontal alignment |
| 3 |
ALIGN_FILL
Cell fit to the content size |
| 4 |
ALIGN_JUSTIFY
Fit cell contents to its width |
| 5 |
ALIGN_LEFT
Left align the cell contents |
| 6 |
ALIGN_RIGHT
Right align the cell contents |
| 7 |
BORDER_DASH_DOT
Cell style with dash and dot |
| 8 |
BORDER_DOTTED
Cell style with dotted border |
| 9 |
BORDER_DASHED
Cell style with dashed border |
| 10 |
BORDER_THICK
Cell style with thick border |
| 11 |
BORDER_THIN
Cell style with thin border |
| 12 |
VERTICAL_BOTTOM
Align the cell contents vertical bottom |
| 13 |
VERTICAL_CENTER
Align the cell contents vertical center |
| 15 |
VERTICAL_JUSTIFY
Align and justify the cell contents vertically |
| 16 |
VERTICAL_TOP
Top aligned vertical alignment |
Class Constructors
| Sr.No. |
Constructor & Description |
| 1 |
XSSFCellStyle(int cellXfId, int cellStyleXfId, StylesTable stylesSource, ThemesTable theme)
Creates a cell style from the supplied parts |
| 2 |
XSSFCellStyle(StylesTable stylesSource)
Creates an empty cell Style
|
Class Methods
| S.No |
Method & Description |
| 1 |
setAlignment(short align)
Sets the type of horizontal alignment for the cell
|
| 2 |
setBorderBottom(short border)
Sets the type of border for the bottom border of the cell |
| 3 |
setBorderColor(XSSFCellBorder.BorderSide side, XSSFColor color)
Sets the color for the selected border
|
| 4 |
setBorderLeft(Short border)
Sets the type of border for the left border of the cell
|
| 5 |
setBorderRight(short border)
Sets the type of border for the right border of the cell
|
| 6 |
setBorderTop(short border)
Sets the type of border for the top border of the cell
|
| 7 |
setFillBackgroundColor(XSSFColor color)
Sets the background fill color represented as an XSSFColor value.
|
| 8 |
setFillForegroundColor(XSSFColor color)
Sets the foreground fill color represented as an XSSFColor value. |
| 9 |
setFillPattern(short fp)
Specifies the cell fill information for pattern and solid color cell fills.
|
| 10 |
setFont(Font font)
Sets the font for this style. |
| 11 |
setRotation(short rotation)
Sets the degree of rotation for the text in the cell. |
| 12 |
setVerticalAlignment(short align)
Sets the type of vertical alignment for the cell. |
For the remaining methods and fields in this class, go through the following link: https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFCellStyle.html
HSSFColor
This is a class under the org.apache.poi.hssf.util package. It provides different colors as nested classes. Usually these nested classes are represented by using their own indexes. It implements the Color interface.
Nested classes
All nested classes of this class are static and each class has its index. These nested color classes are used for cell formatting such as cell content, border, foreground, and background. Listed below are some of the nested classes.
| Sr.No. |
Class names (colors) |
| 1 |
HSSFColor.AQUA |
| 2 |
HSSFColor.AUTOMATIC |
| 3 |
HSSFColor.BLACK |
| 4 |
HSSFColor.BLUE |
| 5 |
HSSFColor.BRIGHT_GREEN |
| 6 |
HSSFColor.BRIGHT_GRAY |
| 7 |
HSSFColor.CORAL |
| 8 |
HSSFColor.DARK_BLUE |
| 9 |
HSSFColor.DARK_GREEN |
| 10 |
HSSFColor.SKY_BLUE |
| 11 |
HSSFColor.WHITE |
| 12 |
HSSFColor.YELLOW |
Class Methods
Only one method of this class is important and that is used to get the index value.
| Sr.No. |
Method & Description |
| 1 |
getIndex()
This method is used to get the index value of a nested class. |
For the remaining methods and nested classes, refer the following link: https://poi.apache.org/apidocs/org/apache/poi/hssf/util/HSSFColor.html
XSSFColor
This is a class under the org.apache.poi.xssf.usermodel package. It is used to represent color in a spreadsheet. It implements the Color interface. Listed below are some of its methods and constructors.
Class Constructors
| Sr.No. |
Constructor & Description |
| 1 |
XSSFColor()
Creates a new instance of XSSFColor. |
| 2 |
XSSFColor(byte[] rgb)
Creates a new instance of XSSFColor using RGB. |
| 3 |
XSSFColor(java.awt.Color clr)
Creates a new instance of XSSFColor using the Color class from the awt package. |
Class Methods
| Sr.No. |
Method & Description |
| 1 |
setAuto(boolean auto)
Sets a boolean value to indicate that the ctColor is automatic and the system ctColor is dependent. |
| 2 |
setIndexed(int indexed)
Sets indexed ctColor value as system ctColor. |
For the remaining methods, visit the following link: https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFColor.html
XSSFFont
This is a class under the org.apache.poi.xssf.usermodel package. It implements the Font interface and therefore it can handle different fonts in a workbook.
Class Constructor
| Sr.No. |
Constructor & Description |
| 1 |
XSSFFont()
Creates a new XSSFont instance. |
Class Methods
| Sr.No. |
Method & Description |
| 1 |
setBold(boolean bold)
Sets a Boolean value for the 'bold' attribute. |
| 2 |
setColor(short color)
Sets the indexed color for the font. |
| 3 |
setColor(XSSFColor color)
Sets the color for the font in Standard Alpha RGB color value. |
| 4 |
setFontHeight(short height)
Sets the font height in points. |
| 5 |
setFontName(java.lang.String name)
Sets the name for the font. |
| 6 |
setItalic(boolean italic)
Sets a Boolean value for the 'italic' property. |
For the remaining methods, go through the following link: https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFFont.html
XSSFHyperlink
This is a class under the org.apache.poi.xssf.usermodel package. It implements the Hyperlink interface. It is used to set a hyperlink to the cell contents of a spreadsheet.
Fields
The fields of this class are as follows. Here, fields mean the types of hyperlinks used.
| Sr.No. |
Field & Description |
| 1 |
LINK_DOCUMENT
Used to link any other document |
| 2 |
LINK_EMAIL
Used to link email |
| 3 |
LINK_FILE
Used to link any other file in any format |
| 4 |
LINK_URL
Used to link a web URL |
Class Methods
| Sr.No. |
Method & Description |
| 1 |
setAddress(java.lang.String address)
Hyperlink address. |
For the remaining methods, visit the following link: https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFHyperlink.html
XSSFCreationHelper
This is a class under the org.apache.poi.xssf.usermodel package. It implements the CreationHelper interface. It is used as a support class for formula evaluation and setting up hyperlinks.
Class methods
| Sr.No. |
Method & Description |
| 1 |
createFormulaEvaluator()
Creates an XSSFFormulaEvaluator instance, the object that evaluates formula cells. |
| 2 |
createHyperlink(int type)
Creates a new XSSFHyperlink. |
For the remaining methods, refer the following link: https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFCreationHelper.html
XSSFPrintSetup
This is a class under the org.apache.poi.xsssf.usermodel package. It implements the PrintSetup interface. It is used to set print page size, area, options, and settings.
Class Methods
| Sr.No. |
Method & Description |
| 1 |
setLandscape(boolean ls)
Sets a boolean value to allow or block landscape printing. |
| 2 |
setLeftToRight(boolean ltor)
Sets whether to go left to right or top down in ordering while printing. |
| 3 |
setPaperSize(short size)
Sets the paper size. |
For the remaining methods, visit the following link: https://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFPrintSetup.html
