
- Apache POI - Home
- Apache POI - Overview
- Apache POI - Java Excel APIs
- Apache POI - Environment
- Apache POI - Core Classes
- Apache POI - Workbooks
- Apache POI - Spreadsheets
- Apache POI - Cells
- Apache POI - Fonts
- Apache POI - Formula
- Apache POI - Hyperlink
- Apache POI - Print Area
- Apache POI - Database
- Apache POI Resources
- Apache POI - Questions & Answers
- Apache POI - Quick Guide
- Apache POI - Useful Resources
- Apache POI - Discussion
Apache POI - Quick Guide
Apache POI - Overview
Many a time, a software application is required to generate reports in Microsoft Excel file format. Sometimes, an application is even expected to receive Excel files as input data. For example, an application developed for the Finance department of a company will be required to generate all their outputs in Excel.
Any Java programmer who wants to produce MS Office files as output must use a predefined and read-only API to do so.
What is Apache POI?
Apache POI is a popular API that allows programmers to create, modify, and display MS Office files using Java programs. It is an open source library developed and distributed by Apache Software Foundation to design or modify Microsoft Office files using Java program. It contains classes and methods to decode the user input data or a file into MS Office documents.
Components of Apache POI
Apache POI contains classes and methods to work on all OLE2 Compound documents of MS Office. The list of components of this API is given below.
POIFS (Poor Obfuscation Implementation File System) − This component is the basic factor of all other POI elements. It is used to read different files explicitly.
HSSF (Horrible Spreadsheet Format) − It is used to read and write xls format of MS-Excel files.
XSSF (XML Spreadsheet Format) − It is used for xlsx file format of MS-Excel.
HPSF (Horrible Property Set Format) − It is used to extract property sets of the MS-Office files.
HWPF (Horrible Word Processor Format) − It is used to read and write doc extension files of MS-Word.
XWPF (XML Word Processor Format) − It is used to read and write docx extension files of MS-Word.
HSLF (Horrible Slide Layout Format) − It is used for read, create, and edit PowerPoint presentations.
HDGF (Horrible DiaGram Format) − It contains classes and methods for MS-Visio binary files.
HPBF (Horrible PuBlisher Format) − It is used to read and write MS-Publisher files.
This tutorial guides you through the process of working on Excel files using Java. Therefore the discussion is confined to HSSF and XSSF components.
Note − Older versions of POI support binary file formats such as doc, xls, ppt, etc. Version 3.5 onwards, POI supports OOXML file formats of MS-Office such as docx, xlsx, pptx, etc.
Like Apache POI, there are other libraries provided by various vendors for Excel file generation. These include Aspose cells for Java by Aspose, JXL by Commons Libraries, and JExcel by Team Dev.
Apache POI - Java Excel APIs
This chapter takes you through some of the flavors of Java Excel API and their features. There are many vendors who provide Java Excel related APIs; some of them are considered in this chapter.
Aspose Cells for Java
Aspose Cells for Java is a purely licensed Java Excel API developed and distributed by the vendor Aspose. The latest version of this API is 8.1.2, released in July 2014. It is a rich and heavy API (combination of plain Java classes and AWT classes) for designing the Excel component that can read, write, and manipulate spreadsheets.
The common uses of this API are as follows −
- Excel reporting, build dynamic Excel reports
- High-fidelity Excel rendering and printing
- Import and export data from Excel spreadsheets
- Generate, edit, and convert spreadsheets
JXL
JXL is a third-party framework designed for Selenium that supports data driven automation on web browsers (auto-update of data on web browsers). However it is also used as a common support library for JExcel API because it has basic features to create, read, and write spreadsheets.
The basic features are as follows −
- Generate Excel files
- Import data from workbooks and spreadsheets
- Obtain the total number of rows and columns
Note − JXL supports only .xls file format and it cannot handle large data volume.
JExcel
JExcel is a purely licensed API provided by Team Dev. Using this, programmers can easily read, write, display, and modify Excel workbooks in both .xls and .xlsx formats. This API can be easily embedded with Java Swing and AWT. The latest version of this API is Jexcel-2.6.12, released in 2009.
The main features are as follows −
- Automate Excel application, workbooks, spreadsheets, etc
- Embed workbooks in a Java Swing application as ordinary Swing component
- Add event listeners to workbooks and spreadsheets
- Add event handlers to handle the behavior of workbook and spreadsheet events
- Add native peers to develop custom functionality
Apache POI - Environment Setup
This chapter will guide you on how to prepare a development environment to start your work with Apache POI. It will also teach you how to set up JDK on your machine before you set up Apache POI −
Setup Java Development Kit (JDK)
You can download the latest version of SDK from Oracle's Java site − Java SE Downloads. You will find instructions for installing JDK in downloaded files, follow the given instructions to install and configure the setup. Finally set PATH and JAVA_HOME environment variables to refer to the directory that contains java and javac, typically java_install_dir/bin and java_install_dir respectively.
If you are running Windows and have installed the JDK in C:\jdk-19, you would have to put the following line in your C:\autoexec.bat file.
set PATH=C:\jdk-19;%PATH% set JAVA_HOME=C:\jdk-19
Alternatively, on Windows NT/2000/XP, you will have to right-click on My Computer, select Properties → Advanced → Environment Variables. Then, you will have to update the PATH value and click the OK button.
On Unix (Solaris, Linux, etc.), if the SDK is installed in /usr/local/jdk-19 and you use the C shell, you will have to put the following into your .cshrc file.
setenv PATH /usr/local/jdk-19/bin:$PATH setenv JAVA_HOME /usr/local/jdk-19
Alternatively, if you use an Integrated Development Environment (IDE) like Borland JBuilder, Eclipse, IntelliJ IDEA, or Sun ONE Studio, you will have to compile and run a simple program to confirm that the IDE knows where you have installed Java. Otherwise, you will have to carry out a proper setup as given in the document of the IDE.
Popular Java Editors
To write your Java programs, you need a text editor. There are many sophisticated IDEs available in the market. But for now, you can consider one of the following −
Notepad − On Windows machine, you can use any simple text editor like Notepad (Recommended for this tutorial), TextPad.
Netbeans − It is a Java IDE that is open-source and free, which can be downloaded from www.netbeans.org/index.html.
Eclipse − It is also a Java IDE developed by the eclipse open-source community and can be downloaded from www.eclipse.org.
Step 3 − Install Apache POI Library
Download the latest version of Apache POI from https://poi.apache.org/download.html and unzip its contents to a folder from where the required libraries can be linked to your Java program. Let us assume the files are collected in a folder on C drive.
Add the complete path of the required jars as shown below to the CLASSPATH.
Sr.No. | Platform & Description |
---|---|
1 |
Windows Append the following strings to the end of the user variable CLASSPATH − C:\poi-bin-5.4.1\poi-5.4.1.jar; C:\poi-bin-5.4.1\lib\commons-codec-1.18.0.jar; C:\poi-bin-5.4.1\lib\commons-collections4-4.4.jar; C:\poi-bin-5.4.1\lib\commons-math3-3.6.1.jar; C:\poi-bin-5.4.1\lib\commons-io-2.18.0.jar; C:\poi-bin-5.4.1\lib\SparseBitSet-1.3.jar; C:\poi-bin-5.4.1\lib\log4j-api-2.24.3.jar; C:\poi-bin-5.4.1\poi-ooxml-5.4.1.jar; C:\poi-bin-5.4.1\poi-ooxml-full-5.4.1.jar; C:\poi-bin-5.4.1\ooxml-lib\xmlbeans-5.3.0.jar C:\poi-bin-5.4.1\ooxml-lib\commons-compress-1.27.1.jar C:\poi-bin-5.4.1\ooxml-lib\curvesapi-1.08.jar C:\poi-bin-5.4.1\ooxml-lib\commons-lang3-3.16.0.jar |
2 |
Linux Export CLASSPATH = $CLASSPATH: /usr/share/poi-bin-5.4.1/poi-5.4.1.jar; /usr/share/poi-bin-5.4.1/lib/commons-codec-1.18.0.jar; /usr/share/poi-bin-5.4.1/lib/commons-collections4-4.4.jar; /usr/share/poi-bin-5.4.1/lib/commons-math3-3.6.1.jar; /usr/share/poi-bin-5.4.1/lib/commons-io-2.18.0.jar; /usr/share/poi-bin-5.4.1/lib/SparseBitSet-1.3.jar; /usr/share/poi-bin-5.4.1/lib/log4j-api-2.24.3.jar; /usr/share/poi-bin-5.4.1/poi-ooxml-5.4.1.jar; /usr/share/poi-bin-5.4.1/poi-ooxml-full-5.4.1.jar; /usr/share/poi-bin-5.4.1/ooxml-lib/xmlbeans-5.3.0.jar /usr/share/poi-bin-5.4.1/ooxml-lib/commons-compress-1.27.1.jar /usr/share/poi-bin-5.4.1/ooxml-lib/curvesapi-1.08.jar /usr/share/poi-bin-5.4.1/ooxml-lib/commons-lang3-3.16.0.jar |
pom.xml
Following is the pom.xml file to run the programs in this tutorial.
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>ApachePoi</groupId> <artifactId>ApachePoi</artifactId> <version>0.0.1-SNAPSHOT</version> <build> <sourceDirectory>src</sourceDirectory> <plugins> <plugin> <artifactId>maven-compiler-plugin</artifactId> <version>3.8.1</version> <configuration> <source>19</source> <target>19</target> <compilerArgs> <arg>--add-modules</arg> <arg>java.se,java.desktop</arg> </compilerArgs> </configuration> </plugin> </plugins> </build> <dependencies> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>5.4.1</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>5.4.1</version> </dependency> </dependencies> </project>
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.
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.
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
Sr.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.
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
Apache POI - Workbooks
Here the term 'Workbook' means Microsoft Excel file. After completion of this chapter, you will be able to create new Workbooks and open existing Workbooks with your Java program.
Example - Create Blank Workbook
The following simple program is used to create a blank Microsoft Excel Workbook.
ApachePoiDemo.java
package com.tutorialspoint; import java.io.File; import java.io.FileOutputStream; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ApachePoiDemo { public static void main(String[] args)throws Exception { //Create Blank workbook XSSFWorkbook workbook = new XSSFWorkbook(); //Create file system using specific name FileOutputStream out = new FileOutputStream(new File("example.xlsx")); //write operation workbook using file out object workbook.write(out); out.close(); workbook.close(); System.out.println("example.xlsx written successfully"); } }
Output
If your system environment is configured with the POI library, it will compile and execute to generate the blank Excel file named example1.xlsx in your current directory and display the following output in the command prompt.
example1.xlsx written successfully
Example - Open Existing Workbook
Use the following code to open an existing workbook.
ApachePoiDemo.java
package com.tutorialspoint; import java.io.File; import java.io.FileInputStream; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ApachePoiDemo { public static void main(String args[])throws Exception { try { File file = new File("example.xlsx"); FileInputStream fIP = new FileInputStream(file); //Get the workbook instance for XLSX file XSSFWorkbook workbook = new XSSFWorkbook(fIP); if(file.isFile() && file.exists()) { System.out.println("example.xlsx file open successfully."); } else { System.out.println("Error to open openworkbook.xlsx file."); } workbook.close(); } catch(Exception e) { System.out.println("Error to open openworkbook.xlsx file." + e.getMessage()); } } }
Output
Run the above code and verify the output −
example.xlsx file open successfully.
After opening a workbook, you can perform read and write operations on it.
Apache POI - Spreadsheets
This chapter explains how to create a spreadsheet and manipulate it using Java. Spreadsheet is a page in an Excel file; it contains rows and columns with specific names.
After completing this chapter, you will be able to create a spreadsheet and perform read operations on it.
Create a Spreadsheet
First of all, let us create a spreadsheet using the referenced classes discussed in the earlier chapters. By following the previous chapter, create a workbook first and then we can go on and create a sheet.
The following code snippet is used to create a spreadsheet.
//Create Blank workbook XSSFWorkbook workbook = new XSSFWorkbook(); //Create a blank spreadsheet XSSFSheet spreadsheet = workbook.createSheet("Sheet Name");
Rows on Spreadsheet
Spreadsheets have a grid layout. The rows and columns are identified with specific names. The columns are identified with alphabets and rows with numbers.
The following code snippet is used to create a row.
XSSFRow row = spreadsheet.createRow((short)1);
Write into a Spreadsheet
Let us consider an example of employee data. Here the employee data is given in a tabular form.
Emp Id | Emp Name | Designation |
---|---|---|
Tp01 | Gopal | Technical Manager |
TP02 | Manisha | Proof Reader |
Tp03 | Masthan | Technical Writer |
Tp04 | Satish | Technical Writer |
Tp05 | Krishna | Technical Writer |
Example - Writing Data to a Spreadsheet
The following code is used to write the above data into a spreadsheet.
ApachePoiDemo.java
package com.tutorialspoint; import java.io.File; import java.io.FileOutputStream; import java.util.Map; import java.util.Set; import java.util.TreeMap; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ApachePoiDemo { public static void main(String[] args) throws Exception { //Create blank workbook XSSFWorkbook workbook = new XSSFWorkbook(); //Create a blank sheet XSSFSheet spreadsheet = workbook.createSheet(" Employee Info "); //Create row object XSSFRow row; //This data needs to be written (Object[]) Map < String, Object[] > empinfo = new TreeMap < String, Object[] >(); empinfo.put( "1", new Object[] { "EMP ID", "EMP NAME", "DESIGNATION" }); empinfo.put( "2", new Object[] { "tp01", "Gopal", "Technical Manager" }); empinfo.put( "3", new Object[] { "tp02", "Manisha", "Proof Reader" }); empinfo.put( "4", new Object[] { "tp03", "Masthan", "Technical Writer" }); empinfo.put( "5", new Object[] { "tp04", "Satish", "Technical Writer" }); empinfo.put( "6", new Object[] { "tp05", "Krishna", "Technical Writer" }); //Iterate over data and write to sheet Set < String > keyid = empinfo.keySet(); int rowid = 0; for (String key : keyid) { row = spreadsheet.createRow(rowid++); Object [] objectArr = empinfo.get(key); int cellid = 0; for (Object obj : objectArr) { Cell cell = row.createCell(cellid++); cell.setCellValue((String)obj); } } //Write the workbook in file system FileOutputStream out = new FileOutputStream(new File("example.xlsx")); workbook.write(out); out.close(); workbook.close(); System.out.println("example.xlsx written successfully"); } }
Output
It will compile and execute to generate an Excel file named example.xlsx in your current directory and you will get the following output in the command prompt −
example.xlsx written successfully
The example.xlsx file looks as follows −

Example - Reading data from a Spreadsheet
Let us consider the above excel file named example.xslx as input. Observe the following code; it is used for reading the data from a spreadsheet.
ApachePoiDemo.java
package com.tutorialspoint; import java.io.File; import java.io.FileInputStream; import java.util.Iterator; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ApachePoiDemo { static XSSFRow row; public static void main(String[] args) throws Exception { FileInputStream fis = new FileInputStream(new File("example.xlsx")); XSSFWorkbook workbook = new XSSFWorkbook(fis); XSSFSheet spreadsheet = workbook.getSheetAt(0); Iterator < Row > rowIterator = spreadsheet.iterator(); while (rowIterator.hasNext()) { row = (XSSFRow) rowIterator.next(); Iterator < Cell > cellIterator = row.cellIterator(); while ( cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case NUMERIC: System.out.print(cell.getNumericCellValue() + " \t\t "); break; case STRING: System.out.print( cell.getStringCellValue() + " \t\t "); break; } } System.out.println(); } fis.close(); workbook.close(); } }
Output
If your system environment is configured with the POI library, it will compile and execute to generate the following output in the command prompt.
EMP ID EMP NAME DESIGNATION tp01 Gopal Technical Manager tp02 Manisha Proof Reader tp03 Masthan Technical Writer tp04 Satish Technical Writer tp05 Krishna Technical Writer
Apache POI - Cells
Any data that you enter into a spreadsheet is always stored in a cell. We use the labels of rows and columns to identify a cell. This chapter describes how to manipulate data in cells in a spreadsheet using Java programming.
Create a Cell
You need to create a row before creating a cell. A row is nothing but a collection of cells.
The following code snippet is used for creating a cell.
//create new workbook XSSFWorkbook workbook = new XSSFWorkbook(); //create spreadsheet with a name XSSFSheet spreadsheet = workbook.createSheet("new sheet"); //create first row on a created spreadsheet XSSFRow row = spreadsheet.createRow(0); //create first cell on created row XSSFCell cell = row.createCell(0);
Types of Cells
The cell type specifies whether a cell can contain strings, numeric value, or formulas. A string cell cannot hold numeric values and a numeric cell cannot hold strings.
Example - Creating different types of Cells in Spreadsheet
The following code is used to create different types of cells in a spreadsheet.
ApachePoiDemo.java
package com.tutorialspoint; import java.io.File; import java.io.FileOutputStream; import java.util.Date; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ApachePoiDemo { public static void main(String[] args)throws Exception { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet = workbook.createSheet("cell types"); XSSFRow row = spreadsheet.createRow((short) 2); row.createCell(0).setCellValue("Type of Cell"); row.createCell(1).setCellValue("cell value"); row = spreadsheet.createRow((short) 3); row.createCell(0).setCellValue("set cell type BLANK"); row.createCell(1); row = spreadsheet.createRow((short) 4); row.createCell(0).setCellValue("set cell type BOOLEAN"); row.createCell(1).setCellValue(true); row = spreadsheet.createRow((short) 5); row.createCell(0).setCellValue("set cell type date"); row.createCell(1).setCellValue(new Date()); row = spreadsheet.createRow((short) 6); row.createCell(0).setCellValue("set cell type numeric"); row.createCell(1).setCellValue(20 ); row = spreadsheet.createRow((short) 7); row.createCell(0).setCellValue("set cell type string"); row.createCell(1).setCellValue("A String"); FileOutputStream out = new FileOutputStream(new File("example.xlsx")); workbook.write(out); out.close(); workbook.close(); System.out.println("example.xlsx written successfully"); } }
Output
Compile and execute to generate an Excel file named example.xlsx in your current directory and display the following output −
example.xlsx written successfully
The example.xlsx file looks as follows −

Cell Styles
Here you can learn how to do cell formatting and apply different styles such as merging adjacent cells, adding borders, setting cell alignment and filling with colors.
Example - Applying Styles to Cells of a Spreadsheet
The following code is used to apply different styles to cells using Java programming.
ApachePoiDemo.java
package com.tutorialspoint; import java.io.File; import java.io.FileOutputStream; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.FillPatternType; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ApachePoiDemo { public static void main(String[] args)throws Exception { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet = workbook.createSheet("cellstyle"); XSSFRow row = spreadsheet.createRow((short) 1); row.setHeight((short) 800); XSSFCell cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("test of merging"); //MEARGING CELLS //this statement for merging cells spreadsheet.addMergedRegion( new CellRangeAddress( 1, //first row (0-based) 1, //last row (0-based) 1, //first column (0-based) 4 //last column (0-based) ) ); //CELL Alignment row = spreadsheet.createRow(5); cell = (XSSFCell) row.createCell(0); row.setHeight((short) 800); // Top Left alignment XSSFCellStyle style1 = workbook.createCellStyle(); spreadsheet.setColumnWidth(0, 8000); style1.setAlignment(HorizontalAlignment.LEFT); style1.setVerticalAlignment(VerticalAlignment.TOP); cell.setCellValue("Top Left"); cell.setCellStyle(style1); row = spreadsheet.createRow(6); cell = (XSSFCell) row.createCell(1); row.setHeight((short) 800); // Center Align Cell Contents XSSFCellStyle style2 = workbook.createCellStyle(); style2.setAlignment(HorizontalAlignment.CENTER); style2.setVerticalAlignment(VerticalAlignment.CENTER); cell.setCellValue("Center Aligned"); cell.setCellStyle(style2); row = spreadsheet.createRow(7); cell = (XSSFCell) row.createCell(2); row.setHeight((short) 800); // Bottom Right alignment XSSFCellStyle style3 = workbook.createCellStyle(); style3.setAlignment(HorizontalAlignment.RIGHT); style3.setVerticalAlignment(VerticalAlignment.BOTTOM); cell.setCellValue("Bottom Right"); cell.setCellStyle(style3); row = spreadsheet.createRow(8); cell = (XSSFCell) row.createCell(3); // Justified Alignment XSSFCellStyle style4 = workbook.createCellStyle(); style4.setAlignment(HorizontalAlignment.JUSTIFY); style4.setVerticalAlignment(VerticalAlignment.JUSTIFY); cell.setCellValue("Contents are Justified in Alignment"); cell.setCellStyle(style4); //CELL BORDER row = spreadsheet.createRow((short) 10); row.setHeight((short) 800); cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("BORDER"); XSSFCellStyle style5 = workbook.createCellStyle(); style5.setBorderBottom(BorderStyle.THICK); style5.setBottomBorderColor(IndexedColors.BLUE.getIndex()); style5.setBorderLeft(BorderStyle.DOUBLE); style5.setLeftBorderColor(IndexedColors.GREEN.getIndex()); style5.setBorderRight(BorderStyle.HAIR); style5.setRightBorderColor(IndexedColors.RED.getIndex()); style5.setBorderTop(BorderStyle.DOTTED); style5.setTopBorderColor(IndexedColors.CORAL.getIndex()); cell.setCellStyle(style5); //Fill Colors //background color row = spreadsheet.createRow((short) 10 ); cell = (XSSFCell) row.createCell((short) 1); XSSFCellStyle style6 = workbook.createCellStyle(); style6.setFillBackgroundColor(IndexedColors.LIME.index); style6.setFillPattern(FillPatternType.LESS_DOTS); style6.setAlignment(HorizontalAlignment.FILL); spreadsheet.setColumnWidth(1,8000); cell.setCellValue("FILL BACKGROUNG/FILL PATTERN"); cell.setCellStyle(style6); //Foreground color row = spreadsheet.createRow((short) 12); cell = (XSSFCell) row.createCell((short) 1); XSSFCellStyle style7 = workbook.createCellStyle(); style7.setFillForegroundColor(IndexedColors.BLUE.index); style7.setFillPattern( FillPatternType.LESS_DOTS); style7.setAlignment(HorizontalAlignment.FILL); cell.setCellValue("FILL FOREGROUND/FILL PATTERN"); cell.setCellStyle(style7); FileOutputStream out = new FileOutputStream(new File("example.xlsx")); workbook.write(out); out.close(); workbook.close(); System.out.println("example.xlsx written successfully"); } }
Output
It will generate an Excel file named example.xlsx in your current directory and display the following output −
example.xlsx written successfully
Apache POI - Fonts
This chapter explains how to set different fonts, apply styles, and display text in different angles of direction in an Excel spreadsheet.
Every system comes bundled with a huge collection of fonts such as Arial, Impact, Times New Roman, etc. The collection can also be updated with new fonts, if required. Similarly there are various styles in which a font can be displayed, for example, bold, italic, underline, strike through, etc.
Example - Fonts and Font Styles in Spreadsheet
The following code is used to apply a particular font and style to the contents of a cell.
ApachePoiDemo.java
package com.tutorialspoint; import java.io.File; import java.io.FileOutputStream; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ApachePoiDemo { public static void main(String[] args)throws Exception { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet = workbook.createSheet("Fontstyle"); XSSFRow row = spreadsheet.createRow(2); //Create a new font and alter it. XSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 30); font.setFontName("IMPACT"); font.setItalic(true); font.setColor(IndexedColors.BRIGHT_GREEN.index); //Set font into style XSSFCellStyle style = workbook.createCellStyle(); style.setFont(font); // Create a cell with a value and set style to it. XSSFCell cell = row.createCell(1); cell.setCellValue("Font Style"); cell.setCellStyle(style); FileOutputStream out = new FileOutputStream(new File("example.xlsx")); workbook.write(out); out.close(); workbook.close(); System.out.println("example.xlsx written successfully"); } }
Output
It generates an Excel file named example.xlsx in your current directory and display the following output on the command prompt.
example.xlsx written successfully
The example1.xlsx file looks as follows −

Example - Text Directions in spreadsheet
Here you can learn how to set the text direction in different angles. Usually cell contents are displayed horizontally, from left to right, and at 00 angle; however you can use the following code to rotate the text direction, if required.
ApachePoiDemo.java
package com.tutorialspoint; import java.io.File; import java.io.FileOutputStream; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ApachePoiDemo { public static void main(String[] args)throws Exception { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet = workbook.createSheet("Text direction"); XSSFRow row = spreadsheet.createRow(2); XSSFCellStyle myStyle = workbook.createCellStyle(); myStyle.setRotation((short) 0); XSSFCell cell = row.createCell(1); cell.setCellValue("0D angle"); cell.setCellStyle(myStyle); //30 degrees myStyle = workbook.createCellStyle(); myStyle.setRotation((short) 30); cell = row.createCell(3); cell.setCellValue("30D angle"); cell.setCellStyle(myStyle); //90 degrees myStyle = workbook.createCellStyle(); myStyle.setRotation((short) 90); cell = row.createCell(5); cell.setCellValue("90D angle"); cell.setCellStyle(myStyle); //120 degrees myStyle = workbook.createCellStyle(); myStyle.setRotation((short) 120); cell = row.createCell(7); cell.setCellValue("120D angle"); cell.setCellStyle(myStyle); //270 degrees myStyle = workbook.createCellStyle(); myStyle.setRotation((short) 270); cell = row.createCell(9); cell.setCellValue("270D angle"); cell.setCellStyle(myStyle); //360 degrees myStyle = workbook.createCellStyle(); myStyle.setRotation((short) 360); cell = row.createCell(12); cell.setCellValue("360D angle"); cell.setCellStyle(myStyle); FileOutputStream out = new FileOutputStream(new File("example.xlsx")); workbook.write(out); out.close(); workbook.close(); System.out.println("example.xlsx written successfully"); } }
Output
It will compile and execute to generate an Excel file named example.xlsx in your current directory and display the following output on the command prompt.
example.xlsx written successfully
The example.xlsx file looks as follows −

Apache POI - Formula
This chapter takes you through the process of applying different formulas on cells using Java programming. The basic purpose of Excel application is to maintain numerical data by applying formulas on it.
In a formula, we pass dynamic values or locations of the values in the Excel sheet. On executing this formula, you get the desired result. The following table lists a few basic formulas that are frequently used in Excel.
Operation | Syntax |
---|---|
Adding multiple numbers | = SUM(Loc1:Locn) or = SUM(n1,n2,) |
Count | = COUNT(Loc1:Locn) or = COUNT(n1,n2,) |
Power of two numbers | = POWER(Loc1,Loc2) or = POWER(number, power) |
Max of multiple numbers | = MAX(Loc1:Locn) or = MAX(n1,n2,) |
Product | = PRODUCT(Loc1:Locn) or = PRODUCT(n1,n2,) |
Factorial | = FACT(Locn) or = FACT(number) |
Absolute number | = ABS(Locn) or = ABS(number) |
Today date | =TODAY() |
Converts lowercase | = LOWER(Locn) or = LOWER(text) |
Square root | = SQRT(locn) or = SQRT(number) |
Example - Adding Formula in a Spreadsheet
The following code is used to add formulas to a cell and execute it.
ApachePoiDemo.java
package com.tutorialspoint; import java.io.File; import java.io.FileOutputStream; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ApachePoiDemo { public static void main(String[] args)throws Exception { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet = workbook.createSheet("formula"); XSSFRow row = spreadsheet.createRow(1); XSSFCell cell = row.createCell(1); cell.setCellValue("A = "); cell = row.createCell(2); cell.setCellValue(2); row = spreadsheet.createRow(2); cell = row.createCell(1); cell.setCellValue("B = "); cell = row.createCell(2); cell.setCellValue(4); row = spreadsheet.createRow(3); cell = row.createCell(1); cell.setCellValue("Total = "); cell = row.createCell(2); // Create SUM formula cell.setCellFormula("SUM(C2:C3)"); cell = row.createCell(3); cell.setCellValue("SUM(C2:C3)"); row = spreadsheet.createRow(4); cell = row.createCell(1); cell.setCellValue("POWER ="); cell=row.createCell(2); // Create POWER formula cell.setCellFormula("POWER(C2,C3)"); cell = row.createCell(3); cell.setCellValue("POWER(C2,C3)"); row = spreadsheet.createRow(5); cell = row.createCell(1); cell.setCellValue("MAX = "); cell = row.createCell(2); // Create MAX formula cell.setCellFormula("MAX(C2,C3)"); cell = row.createCell(3); cell.setCellValue("MAX(C2,C3)"); row = spreadsheet.createRow(6); cell = row.createCell(1); cell.setCellValue("FACT = "); cell = row.createCell(2); // Create FACT formula cell.setCellFormula("FACT(C3)"); cell = row.createCell(3); cell.setCellValue("FACT(C3)"); row = spreadsheet.createRow(7); cell = row.createCell(1); cell.setCellValue("SQRT = "); cell = row.createCell(2); // Create SQRT formula cell.setCellFormula("SQRT(C5)"); cell = row.createCell(3); cell.setCellValue("SQRT(C5)"); workbook.getCreationHelper().createFormulaEvaluator().evaluateAll(); FileOutputStream out = new FileOutputStream(new File("example.xlsx")); workbook.write(out); out.close(); workbook.close(); System.out.println("example.xlsx written successfully"); } }
Output
It will generate an Excel file named formula.xlsx in your current directory and display the following output on the command prompt.
example.xlsx written successfully
The example.xlsx file looks as follows −

Apache POI - Hyperlink
This chapter explains how to add hyperlinks to the contents in a cell. Usually hyperlinks are used to access any web URL, email, or an external file.
The following code shows how to create hyperlinks on cells.
Example - Adding Hyperlink in a Spreadsheet
ApachePoiDemo.java
package com.tutorialspoint; import java.io.File; import java.io.FileOutputStream; import org.apache.poi.common.usermodel.Hyperlink; import org.apache.poi.common.usermodel.HyperlinkType; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.CreationHelper; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFHyperlink; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ApachePoiDemo { public static void main(String[] args) throws Exception { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet = workbook.createSheet("Hyperlinks"); XSSFCell cell; CreationHelper createHelper = workbook.getCreationHelper(); XSSFCellStyle hlinkstyle = workbook.createCellStyle(); XSSFFont hlinkfont = workbook.createFont(); hlinkfont.setUnderline(XSSFFont.U_SINGLE); hlinkfont.setColor(IndexedColors.BLUE.index); hlinkstyle.setFont(hlinkfont); //URL Link cell = spreadsheet.createRow(1).createCell((short) 1); cell.setCellValue("URL Link"); XSSFHyperlink link = (XSSFHyperlink)createHelper.createHyperlink(HyperlinkType.URL); link.setAddress("https://www.tutorialspoint.com/"); cell.setHyperlink((XSSFHyperlink) link); cell.setCellStyle(hlinkstyle); //Hyperlink to a file in the current directory cell = spreadsheet.createRow(2).createCell((short) 1); cell.setCellValue("File Link"); link = (XSSFHyperlink)createHelper.createHyperlink(HyperlinkType.FILE); link.setAddress("cellstyle.xlsx"); cell.setHyperlink(link); cell.setCellStyle(hlinkstyle); //e-mail link cell = spreadsheet.createRow(3).createCell((short) 1); cell.setCellValue("Email Link"); link = (XSSFHyperlink)createHelper.createHyperlink(HyperlinkType.EMAIL); link.setAddress("mailto:contact@tutorialspoint.com?" + "subject=Hyperlink"); cell.setHyperlink(link); cell.setCellStyle(hlinkstyle); FileOutputStream out = new FileOutputStream(new File("example.xlsx")); workbook.write(out); out.close(); workbook.close(); System.out.println("hyperlink.xlsx written successfully"); } }
Output
It will generate an Excel file named example.xlsx in your current directory and display the following output on the command prompt.
example.xlsx written successfully
The example.xlsx file looks as follows −

Apache POI - Print Area
This chapter explains how to set the print area on a spreadsheet. The usual print area is from left top to right bottom on Excel spreadsheets. Print area can be customized according to your requirement. It means you can print a particular range of cells from the whole spreadsheet, customize the paper size, print the contents with the grid lines turned on, etc.
Example - Setting up Print Area of Spreadsheet
The following code is used to set up the print area on a spreadsheet.
ApachePoiDemo.java
package com.tutorialspoint; import java.io.File; import java.io.FileOutputStream; import org.apache.poi.xssf.usermodel.XSSFPrintSetup; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ApachePoiDemo { public static void main(String[] args)throws Exception { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet = workbook.createSheet("Print Area"); //set print area with indexes workbook.setPrintArea( 0, //sheet index 0, //start column 5, //end column 0, //start row 5 //end row ); //set paper size spreadsheet.getPrintSetup().setPaperSize(XSSFPrintSetup.A4_PAPERSIZE); //set display grid lines or not spreadsheet.setDisplayGridlines(true); //set print grid lines or not spreadsheet.setPrintGridlines(true); FileOutputStream out = new FileOutputStream(new File("example.xlsx")); workbook.write(out); out.close(); workbook.close(); System.out.println("example.xlsx written successfully"); } }
Output
It will generate a file named example.xlsx in your current directory and display the following output on the command prompt.
example.xlsx written successfully
In the above code, we have not added any cell values. Hence example.xlsx is a blank file. But you can observe in the following figure that the print preview shows the print area with grid lines.

Apache POI - Database
This chapter explains how the POI library interacts with a database. With the help of JDBC, you can retrieve data from a database and insert that data into a spreadsheet using the POI library. Let us consider MySQL database for SQL operations.
Write into Excel from Database
Let us assume the following employee data table called emp_tbl is to be retrieved from the MySQL database test.
EMP ID | EMP NAME | DEG | SALARY | DEPT |
---|---|---|---|---|
1201 | Gopal | Technical Manager | 45000 | IT |
1202 | Manisha | Proof reader | 45000 | Testing |
1203 | Masthanvali | Technical Writer | 45000 | IT |
1204 | Kiran | Hr Admin | 40000 | HR |
1205 | Kranthi | Op Admin | 30000 | Admin |
Example - Inserting Data from Database into a Spreadsheet
Use the following code to retrieve data from a database and insert the same into a spreadsheet.
ApachePoiDemo.java
package com.tutorialspoint; import java.io.File; import java.io.FileOutputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ApachePoiDemo { public static void main(String[] args) throws Exception { Class.forName("com.mysql.jdbc.Driver"); Connection connect = DriverManager.getConnection( "jdbc:mysql://localhost:3306/test" , "root" , "root" ); Statement statement = connect.createStatement(); ResultSet resultSet = statement.executeQuery("select * from emp_tbl"); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet = workbook.createSheet("employe db"); XSSFRow row = spreadsheet.createRow(1); XSSFCell cell; cell = row.createCell(1); cell.setCellValue("EMP ID"); cell = row.createCell(2); cell.setCellValue("EMP NAME"); cell = row.createCell(3); cell.setCellValue("DEG"); cell = row.createCell(4); cell.setCellValue("SALARY"); cell = row.createCell(5); cell.setCellValue("DEPT"); int i = 2; while(resultSet.next()) { row = spreadsheet.createRow(i); cell = row.createCell(1); cell.setCellValue(resultSet.getInt("eid")); cell = row.createCell(2); cell.setCellValue(resultSet.getString("ename")); cell = row.createCell(3); cell.setCellValue(resultSet.getString("deg")); cell = row.createCell(4); cell.setCellValue(resultSet.getString("salary")); cell = row.createCell(5); cell.setCellValue(resultSet.getString("dept")); i++; } FileOutputStream out = new FileOutputStream(new File("example.xlsx")); workbook.write(out); out.close(); workbook.close(); System.out.println("workbook.xlsx written successfully"); } }
Output
It will generate an Excel file named example.xlsx in your current directory and display the following output on the command prompt.
example.xlsx written successfully
The example.xlsx file looks as follows.
