Apache POI - Overview

Many a time, a software apppcation is required to generate reports in Microsoft Excel file format. Sometimes, an apppcation is even expected to receive Excel files as input data. For example, an apppcation 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 pbrary 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 pst 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 exppcitly.

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 Spde 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 PuBpsher Format) − It is used to read and write MS-Pubpsher 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 pbraries 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 pcensed 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-fidepty 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 pbrary 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 pcensed 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 apppcation, workbooks, spreadsheets, etc

Embed workbooks in a Java Swing apppcation as ordinary Swing component

Add event psteners to workbooks and spreadsheets

Add event handlers to handle the behavior of workbook and spreadsheet events

Add native peers to develop custom functionapty

Apache POI - Environment

This chapter takes you through the process of setting up Apache POI on Windows and Linux based systems. Apache POI can be easily installed and integrated with your current Java environment following a few simple steps without any complex setup procedures. User administration is required while installation.

System Requirements

Let us now proceed with the steps to install Apache POI.

Step 1 - Verify your Java Installation

First of all, you need to have Java Software Development Kit (SDK) installed on your system. To verify this, execute any of the two commands depending on the platform you are working on.

If the Java installation has been done properly, then it will display the current version and specification of your Java installation. A sample output is given in the following table.

We assume the readers of this tutorial have Java SDK version 11.0.11 installed on their system.

In case you do not have Java SDK, download its current version from https://www.oracle.com/technetwork/java/javase/downloads/index.html and have it installed.

Step 2 - Set your Java Environment

Set the environment variable JAVA_HOME to point to the base directory location where Java is installed on your machine. For example

Append the full path of Java compiler location to the System Path.

Execute the command java -version from the command prompt as explained above.

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 pbraries can be pnked 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.

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

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 pke 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 psted 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

Class Methods

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 pst 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

XSSFSheet

This is a class which represents high level representation of excel spreadsheet. It is under org.apache.poi.hssf.usermodel package.

Class Constructors

Class Methods

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 pbrary.

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

For the remaining methods of this class, follow the given pnk 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

For the remaining methods and fields of this class, visit the following pnk − 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

Class Methods

For the remaining methods and fields in this class, go through the following pnk − 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.

Class Methods

Only one method of this class is important and that is used to get the index value.

For the remaining methods and nested classes, refer the following pnk − 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

Class Methods

For the remaining methods, visit the following pnk − 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

Class Methods

For the remaining methods, go through the following pnk − https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFFont.html

XSSFHyperpnk

This is a class under the org.apache.poi.xssf.usermodel package. It implements the Hyperpnk interface. It is used to set a hyperpnk to the cell contents of a spreadsheet.

Class Methods

For the remaining methods, visit the following pnk − https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFHyperpnk.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 hyperpnks.

Class Methods

For the remaining methods, refer the following pnk − 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

For the remaining methods, visit the following pnk − 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.

Create Blank Workbook

The following simple program is used to create a blank Microsoft Excel Workbook.

import java.io.*;
import org.apache.poi.xssf.usermodel.*;

pubpc class CreateWorkBook {
   pubpc 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("createworkbook.xlsx"));

      //write operation workbook using file out object 
      workbook.write(out);
      out.close();
      System.out.println("createworkbook.xlsx written successfully");
   }
}

Let us save the above Java code as CreateWorkBook.java, and then compile and execute it from the command prompt as follows −

$javac CreateWorkBook.java
$java CreateWorkBook

If your system environment is configured with the POI pbrary, it will compile and execute to generate the blank Excel file named createworkbook.xlsx in your current directory and display the following output in the command prompt.

createworkbook.xlsx written successfully

Open Existing Workbook

Use the following code to open an existing workbook.

import java.io.*;
import org.apache.poi.xssf.usermodel.*;

pubpc class OpenWorkBook {
   pubpc static void main(String args[])throws Exception { 
	   try {
         File file = new File("openworkbook.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("openworkbook.xlsx file open successfully.");
         } else {
            System.out.println("Error to open openworkbook.xlsx file.");
         }
      } catch(Exception e) {
         System.out.println("Error to open openworkbook.xlsx file." + e.getMessage());
      }
   }
}

Save the above Java code as OpenWorkBook.java, and then compile and execute it from the command prompt as follows −

$javac OpenWorkBook.java
$java OpenWorkBook

It will compile and execute to generate the following output.

openworkbook.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 earper 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.

The following code is used to write the above data into a spreadsheet.

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;

pubpc class Writesheet {
   pubpc 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 celpd = 0;

         for (Object obj : objectArr) {
            Cell cell = row.createCell(celpd++);
            cell.setCellValue((String)obj);
         }
      }
      //Write the workbook in file system
      FileOutputStream out = new FileOutputStream(new File("Writesheet.xlsx"));
      workbook.write(out);
      out.close();
      System.out.println("Writesheet.xlsx written successfully");
   }
}

Save the above Java code as Writesheet.java, and then compile and run it from the command prompt as follows −

$javac Writesheet.java
$java Writesheet

It will compile and execute to generate an Excel file named Writesheet.xlsx in your current directory and you will get the following output in the command prompt.

Writesheet.xlsx written successfully

The Writesheet.xlsx file looks as follows −

Read from a Spreadsheet

Let us consider the above excel file named Writesheet.xslx as input. Observe the following code; it is used for reading the data from a spreadsheet.

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;

pubpc class Readsheet {
   static XSSFRow row;
   pubpc static void main(String[] args) throws Exception {
      FileInputStream fis = new FileInputStream(new File("WriteSheet.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() + " 		 ");
                  break;
               
               case STRING:
                  System.out.print(
                  cell.getStringCellValue() + " 		 ");
                  break;
            }
         }
         System.out.println();
      }
      fis.close();
   }
}

Let us keep the above code in Readsheet.java file, and then compile and run it from the command prompt as follows −

$javac Readsheet.java
$java Readsheet

If your system environment is configured with the POI pbrary, 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.

The following code is used to create different types of cells in a spreadsheet.

import java.io.File;
import java.io.FileOutputStream;
import java.util.Date;
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;

pubpc class TypesofCells {
   pubpc 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("typesofcells.xlsx"));
      workbook.write(out);
      out.close();
      System.out.println("typesofcells.xlsx written successfully");
   }
}

Save the above code in a file named TypesofCells.java, compile and execute it from the command prompt as follows −

$javac TypesofCells.java
$java TypesofCells

If your system is configured with the POI pbrary, then it will compile and execute to generate an Excel file named typesofcells.xlsx in your current directory and display the following output.

typesofcells.xlsx written successfully

The typesofcells.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 apgnment and filpng with colors.

The following code is used to apply different styles to cells using Java programming.

import java.io.File;
import java.io.FileOutputStream;

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalApgnment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalApgnment;
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.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.Color;
import org.apache.poi.ss.usermodel.FillPatternType;

pubpc class CellStyle {
   pubpc 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 Apgnment
      row = spreadsheet.createRow(5); 
      cell = (XSSFCell) row.createCell(0);
      row.setHeight((short) 800);
      
      // Top Left apgnment 
      XSSFCellStyle style1 = workbook.createCellStyle();
      spreadsheet.setColumnWidth(0, 8000);
      style1.setApgnment(HorizontalApgnment.LEFT);
      style1.setVerticalApgnment(VerticalApgnment.TOP);
      cell.setCellValue("Top Left");
      cell.setCellStyle(style1);
      row = spreadsheet.createRow(6); 
      cell = (XSSFCell) row.createCell(1);
      row.setHeight((short) 800);
      
      // Center Apgn Cell Contents 
      XSSFCellStyle style2 = workbook.createCellStyle();
      style2.setApgnment(HorizontalApgnment.CENTER);
      style2.setVerticalApgnment(VerticalApgnment.CENTER);
      cell.setCellValue("Center Apgned"); 
      cell.setCellStyle(style2);
      row = spreadsheet.createRow(7); 
      cell = (XSSFCell) row.createCell(2);
      row.setHeight((short) 800);
      
      // Bottom Right apgnment 
      XSSFCellStyle style3 = workbook.createCellStyle();
      style3.setApgnment(HorizontalApgnment.RIGHT);
      style3.setVerticalApgnment(VerticalApgnment.BOTTOM);
      cell.setCellValue("Bottom Right");
      cell.setCellStyle(style3);
      row = spreadsheet.createRow(8);
      cell = (XSSFCell) row.createCell(3);
      
      // Justified Apgnment 
      XSSFCellStyle style4 = workbook.createCellStyle();
      style4.setApgnment(HorizontalApgnment.JUSTIFY);
      style4.setVerticalApgnment(VerticalApgnment.JUSTIFY);
      cell.setCellValue("Contents are Justified in Apgnment"); 
      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.setApgnment(HorizontalApgnment.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.setApgnment(HorizontalApgnment.FILL);
      cell.setCellValue("FILL FOREGROUND/FILL PATTERN");
      cell.setCellStyle(style7);

      FileOutputStream out = new FileOutputStream(new File("cellstyle.xlsx"));
      workbook.write(out);
      out.close();
      System.out.println("cellstyle.xlsx written successfully");
   }
}

Save the above code in a file named CellStyle.java, compile and execute it from the command prompt as follows −

$javac CellStyle.java
$java CellStyle

It will generate an Excel file named cellstyle.xlsx in your current directory and display the following output.

cellstyle.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, itapc, underpne, strike through, etc.

Fonts and Font Styles

The following code is used to apply a particular font and style to the contents of a cell.

import java.io.File;
import java.io.FileOutputStream;
import org.apache.poi.hssf.util.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;

pubpc class FontStyle {
   pubpc 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.setItapc(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("fontstyle.xlsx"));
      workbook.write(out);
      out.close();
      System.out.println("fontstyle.xlsx written successfully");
   }
}

Let us save the above code in a file named FontStyle.java. Compile and execute it from the command prompt as follows −

$javac FontStyle.java
$java FontStyle

It generates an Excel file named fontstyle.xlsx in your current directory and display the following output on the command prompt.

fontstyle.xlsx written successfully

The fontstyle.xlsx file looks as follows −

Text Direction

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.

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;

pubpc class TextDirection {
   pubpc 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("textdirection.xlsx"));
      workbook.write(out);
      out.close();
      System.out.println("textdirection.xlsx written successfully");
   }
}

Keep the above code in TextDirectin.java file, then compile and execute it from the command prompt as follows −

$javac TextDirection.java
$java TextDirection

It will compile and execute to generate an Excel file named textdirection.xlsx in your current directory and display the following output on the command prompt.

textdirection.xlsx written successfully

The textdirection.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 apppcation 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 psts a few basic formulas that are frequently used in Excel.

The following code is used to add formulas to a cell and execute it.

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;

pubpc class Formula {
   pubpc 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("formula.xlsx"));
      workbook.write(out);
      out.close();
      System.out.println("fromula.xlsx written successfully");
   }
}

Save the above code as Formula.java and then compile and execute it from the command prompt as follows −

$javac Formula.java
$java Formula

It will generate an Excel file named formula.xlsx in your current directory and display the following output on the command prompt.

fromula.xlsx written successfully

The formula.xlsx file looks as follows −

Apache POI - Hyperpnk

This chapter explains how to add hyperpnks to the contents in a cell. Usually hyperpnks are used to access any web URL, email, or an external file.

The following code shows how to create hyperpnks on cells.

import java.io.File;
import java.io.FileOutputStream;

import org.apache.poi.common.usermodel.Hyperpnk;
import org.apache.poi.common.usermodel.HyperpnkType;
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.XSSFHyperpnk;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

pubpc class HyperpnkEX {
   pubpc static void main(String[] args) throws Exception {
      XSSFWorkbook workbook = new XSSFWorkbook(); 
      XSSFSheet spreadsheet = workbook.createSheet("Hyperpnks");
      XSSFCell cell;
      CreationHelper createHelper = workbook.getCreationHelper();
      XSSFCellStyle hpnkstyle = workbook.createCellStyle();
      XSSFFont hpnkfont = workbook.createFont();
      hpnkfont.setUnderpne(XSSFFont.U_SINGLE);
      hpnkfont.setColor(IndexedColors.BLUE.index);
      hpnkstyle.setFont(hpnkfont);

      //URL Link
      cell = spreadsheet.createRow(1).createCell((short) 1);
      cell.setCellValue("URL Link");
      XSSFHyperpnk pnk = (XSSFHyperpnk)createHelper.createHyperpnk(HyperpnkType.URL);
      pnk.setAddress("http://www.tutorialspoint.com/");
      cell.setHyperpnk((XSSFHyperpnk) pnk);
      cell.setCellStyle(hpnkstyle);

      //Hyperpnk to a file in the current directory
      cell = spreadsheet.createRow(2).createCell((short) 1);
      cell.setCellValue("File Link");
      pnk = (XSSFHyperpnk)createHelper.createHyperpnk(HyperpnkType.FILE);
      pnk.setAddress("cellstyle.xlsx");
      cell.setHyperpnk(pnk);
      cell.setCellStyle(hpnkstyle);

      //e-mail pnk
      cell = spreadsheet.createRow(3).createCell((short) 1);
      cell.setCellValue("Email Link");
      pnk = (XSSFHyperpnk)createHelper.createHyperpnk(HyperpnkType.EMAIL);
      pnk.setAddress("mailto:contact@tutorialspoint.com?" + "subject = Hyperpnk");
      cell.setHyperpnk(pnk);
      cell.setCellStyle(hpnkstyle);
      
      FileOutputStream out = new FileOutputStream(new File("hyperpnk.xlsx"));
      workbook.write(out);
      out.close();
      System.out.println("hyperpnk.xlsx written successfully");
   }
}

Save the above code as HyperpnkEX.java. Compile and execute it from the command prompt as follows −

$javac HyperpnkEX.java
$java HyperpnkEX

It will generate an Excel file named hyperpnk.xlsx in your current directory and display the following output on the command prompt.

hyperpnk.xlsx written successfully

The hyperpnk.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 pnes turned on, etc.

The following code is used to set up the print area on a spreadsheet.

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;
pubpc class PrintArea 
{
   pubpc 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 pnes or not
      spreadsheet.setDisplayGridpnes(true);
      //set print grid pnes or not
      spreadsheet.setPrintGridpnes(true);
      FileOutputStream out = new FileOutputStream(
      new File("printarea.xlsx"));
      workbook.write(out);
      out.close();
      System.out.println("printarea.xlsx written successfully"); 
   }
}

Let us save the above code as PrintArea.java. Compile and execute it from the command prompt as follows −

$javac PrintArea.java
$java PrintArea

It will generate a file named printarea.xlsx in your current directory and display the following output on the command prompt.

printarea.xlsx written successfully

In the above code, we have not added any cell values. Hence printarea.xlsx is a blank file. But you can observe in the following figure that the print preview shows the print area with grid pnes.

Apache POI - Database

This chapter explains how the POI pbrary 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 pbrary. Let us consider MySQL database for SQL operations.

Write into Database

Let us assume the following employee data table called emp_tbl is to be retrieved from the MySQL database test.

Use the following code to retrieve data from a database and insert the same into a spreadsheet.

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;
pubpc class ExcelDatabase {
   pubpc 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("exceldatabase.xlsx"));
      workbook.write(out);
      out.close();
      System.out.println("exceldatabase.xlsx written successfully");
   }
}

Let us save the above code as ExcelDatabase.java. Compile and execute it from the command prompt as follows.

$javac ExcelDatabase.java
$java ExcelDatabase

It will generate an Excel file named exceldatabase.xlsx in your current directory and display the following output on the command prompt.

exceldatabase.xlsx written successfully

The exceldatabase.xlsx file looks as follows −