We will learn to read, write excel, evaluate formula in cells and do custom formatting to the generated excel file by using Apache POI library. Read and write excel file in java is very simple using some libraries which we will discuss below.

Reading and Writing excel by using program is often required in software projects.

Apache POI is very useful library among many other open source libraries to use in  such ways involving excel files. Using POI, we can Read and write excel file in java. It can be used to read and write MS Word and MS PowerPoint also.

Apache POI – Maven Dependency

If you are using maven project then you can include the POI dependency in pom.xml  as:

<dependencies>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>

</dependencies>

If you are not using the maven project then you can download below jar files  Apache POI – Download Release Artifacts:

• poi-4.1.0.jar
• commons-codec-1.12.jar
• commons-collections4-4.3.jar
• commons-math3-3.6.1.jar

If you are reading and writing Excel 2007 format then you have to include the following files:

• poi-ooxml-VERSION.jar
• poi-ooxml-schemas-VERSION.jar
• xmlbeans-VERSION.jar

Apache POI API Basics

HSSF: API for Excel 2003 and earlier.

XSSF: API for Excel 2007 and later.

Workbook: Broad level representation for Excel workbook. Implementations are HSSFWorkbook and XSSFWorkbook.

Sheet: Broad level representation for Excel worksheet. Implementations classes are HSSFSheet and XSSFSheet.

Row: Broad level representation for row in a spreadsheet. HSSFRow and XSSFRow are two implementing classes.

Cell: Broad level representation for cell in a spreadsheet row. HSSFCell and XSSFCell are the implementing classes.

Style Classes : Classes such as CellStyle, BuiltinFormats, ConditionalFormattingRule,ComparisonOperator, IndexedColors, PatternFormatting,  FontFormatting, SheetConditionalFormatting etc. are used when you are formatting in a sheet based on rules.

FormulaEvaluator: Class FormulaEvaluator is used for evaluating the formula in cells of excel sheet.

Apache POI – Write an excel file

Writing excel involves following steps:

1. Create a spreadsheet
2. Create a row in spreadsheet
3. Add cells in spreadsheet
4. Repeat step 3 and 4 to write more data

Java Program for Writing Excel File:

package com.techblogstation;

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.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteExcelFile {

private static String[] columns = {"ID", "FirstName", "LastName"};

public static void main(String[] args) 
{

//Blank workbook
XSSFWorkbook workbook = new XSSFWorkbook();

//Create a blank sheet
XSSFSheet sheet = workbook.createSheet("Cricketer Data");

//This data needs to be written (Object[])
Map<String, Object[]> data = new TreeMap<String, Object[]>();

data.put("2", new Object[] {1, "Sachin", "Tendulkar"});
data.put("3", new Object[] {2, "Saurav", "Ganguly"});
data.put("4", new Object[] {3, "Rahul", "Dravid"});
data.put("5", new Object[] {4, "Virat", "Kohli"});

//Iterate over data and write to sheet
Set<String> keyset = data.keySet();
int rownum = 1;

// Create a Font for styling header cells
Font headerFont = workbook.createFont();
headerFont.setBold(true);
headerFont.setFontHeightInPoints((short) 14);
headerFont.setColor(IndexedColors.RED.getIndex());

// Create a CellStyle with the font
CellStyle headerCellStyle = workbook.createCellStyle();
headerCellStyle.setFont(headerFont);

// Create a Header Row
Row headerRow = sheet.createRow(0);

// Create cells
for(int i = 0; i < columns.length; i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(columns[i]);
cell.setCellStyle(headerCellStyle);
}

for (String key : keyset)
{
Row row = sheet.createRow(rownum++);
Object [] objArr = data.get(key);
int cellnum = 0;
for (Object obj : objArr)
{
Cell cell = row.createCell(cellnum++);
if(obj instanceof String)
cell.setCellValue((String)obj);
else if(obj instanceof Integer)
cell.setCellValue((Integer)obj);
}
}
try
{
//Write the workbook in file system
FileOutputStream out = new FileOutputStream(new File("TechBlogStation.xlsx"));
workbook.write(out);
out.close();
System.out.println("****File written successfully*****");
}
catch (Exception e)
{
e.printStackTrace();
}


}

}

Created Excel File from above Program :

Apache POI – Read an excel file

Reading an excel file using POI has these steps.

1. Create workbook from spreadsheet
2. Increment row number
3. iterate over all cells in a row
4. repeat step 3 and 4 until all data is read

It will read all the column names and the values cell by cell.

Java program to read excel file using apache POI api –

package com.techblogstation;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;

import org.apache.poi.ss.usermodel.Cell;
//import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcelFile {


public static void main(String[] args) 
{

try
{
FileInputStream file = new FileInputStream(new File("TechBlogStation.xlsx"));

//Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(file);

//Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);

//Iterate through each rows one by one
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
Row row = rowIterator.next();
//For each row, iterate through all the columns
Iterator<Cell> cellIterator = row.cellIterator();

while (cellIterator.hasNext())
{
Cell cell = cellIterator.next();

switch (cell.getCellType())
{


case NUMERIC : 
System.out.print(cell.getNumericCellValue() + "\t");
break;
case STRING:
System.out.print(cell.getStringCellValue() + "\t");
break;
}
//Check the cell type and format accordingly
}
System.out.println("\n");
}


file.close();
}
catch (Exception e)
{
e.printStackTrace();
} 
}

}

Conclusion

That’s all techies! In this article, You have learnt how to read excel files in Java using Apache POI library.