In this post we’ll see how you can Write Excel sheet in Java using Apache POI library.
- Refer How to Read Excel File in Java Using Apache POI to see how to read from excel sheet using Java program.
Apache POI is an open source library using which you can read and write Excel files from your Java program.
Table of contents
Maven Dependencies
You will need to include following maven dependencies for Apache POI in your pom.xml file.
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.0.0</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.0.0</version> </dependency>
Here the first dependency is required for working with older excel format having .xls extension. Second dependency is required for working with the OOXML based file format having .xlsx extension.
These dependencies add the following jars-
poi-4.0.0.jar commons-codec-1.10.jar commons-collections4-4.2.jar poi-ooxml-4.0.0.jar poi-ooxml-schemas-4.0.0.jar xmlbeans-3.0.1.jar commons-compress-1.18.jar curvesapi-1.04.jar
Apache POI classes for working with Excel spreadsheets
Before getting into example for writing to excel in Java using Apache POI first let’s get some idea about the classes that are used in the code.
With in Apache POI there are two implementations for two types of spread sheets-
- HSSF- It is the POI Project's pure Java implementation of the Excel '97(-2007) file format (.xls).
- XSSF- It is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.
Note that there is a component module that attempts to provide a common high level Java API to both OLE2 and OOXML document formats which is SS for Excel workbooks. So it's better to use SS package as much as possible so that one implementation can be replaced by another seamlessly.
The following interfaces from the SS model will be used in the example to write excel file in Java using Apache POI-
- org.apache.poi.ss.usermodel.Workbook- High level representation of a Excel workbook. This is the first object most users will construct whether they are reading or writing a workbook. Implementing classes for the HSSF and XSSF respectively are HSSFWorkbook and XSSFWorkbook.
- org.apache.poi.ss.usermodel.Sheet- High level representation of a Excel worksheet. Implementing classes for the HSSF and XSSF respectively are HSSFSheet and XSSFSheet.
- org.apache.poi.ss.usermodel.Row- High level representation of a row of a spreadsheet. Implementing classes for the HSSF and XSSF respectively are HSSFRow and XSSFRow.
- org.apache.poi.ss.usermodel.Cell- High level representation of a cell in a row of a spreadsheet. Cells can be numeric, formula-based or string-based (text). Implementing classes for the HSSF and XSSF respectively are HSSFCell and XSSFCell.
Writing excel file in Java using Apache POI example
The example shown here writes a List of object of type User to an Excel sheet using a Java program.
Steps you need to follow are as follows-
- First thing is to create a workbook instance of type XSSFWorkbook or HSSFWorkbook based on whether you want Excel with .xslx or .xls extension.
- Then create a sheet with in a workbook.
- Create row in a sheet and then cells with in a row to write data.
- You can also style cells by providing font, color, alignment, border etc. values.
Model bean (User.java)
public class User {
private String firstName;
private String lastName;
private String email;
private Date dob;
public User() {
}
public User(String firstName, String lastName, String email, Date dob) {
this.firstName = firstName;
this.lastName = lastName;
this.email = email;
this.dob = dob;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getDob() {
return dob;
}
public void setDob(Date dob) {
this.dob = dob;
}
}
Class used for writing to excel sheet.
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.ParseException;
import java.time.LocalDate;
import java.time.Month;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.netjs.Model.User;
public class WriteExcel {
private static final String PATH_TO_EXCEL="G:\\Test\\user.xlsx";
public static void main(String[] args) throws ParseException{
WriteExcel writeExcel = new WriteExcel();
List<User> users = writeExcel.getListOfUsers();
writeExcel.writeExcel(PATH_TO_EXCEL, users);
}
private void writeExcel(String pathToExcel, List<User> users) {
final String[] header= {"First Name", "Last Name", "Email", "DOB"};
Workbook workbook = null;
try {
workbook = new XSSFWorkbook();
// for HSSF (.xls extension)
//workbook = new HSSFWorkbook();
// Creating sheet with in the workbook
Sheet sheet = workbook.createSheet("Users");
/*For Header*/
Font font = workbook.createFont();
font.setFontName("TIMES_ROMAN");
font.setColor(IndexedColors.WHITE.getIndex());
font.setBold(true);
CellStyle style = workbook.createCellStyle();
style.setFont(font);
style.setWrapText(true);
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setFillForegroundColor(IndexedColors.CORNFLOWER_BLUE.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
Row row = sheet.createRow(0);
for(int i = 0; i < header.length; i++) {
// each column 12 characters wide
sheet.setColumnWidth(i, 12*256);
Cell cell = row.createCell(i);
cell.setCellValue(header[i]);
cell.setCellStyle(style);
}
/* Header ends*/
/** Rows in the sheet **/
CellStyle dateStyle = workbook.createCellStyle();
// Setting format For the date column
dateStyle.setDataFormat(workbook.getCreationHelper()
.createDataFormat()
.getFormat("dd/MM/yyyy"));
int rowNum = 1;
for(User user : users) {
// create new row
row = sheet.createRow(rowNum++);
row.createCell(0).setCellValue(user.getFirstName());
row.createCell(1).setCellValue(user.getLastName());
row.createCell(2).setCellValue(user.getEmail());
Cell cell = row.createCell(3);
cell.setCellValue(user.getDob());
cell.setCellStyle(dateStyle);
}
// Writing sheet data
FileOutputStream outputStream = new FileOutputStream(pathToExcel);
workbook.write(outputStream);
}catch (EncryptedDocumentException | IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(workbook != null)
workbook.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
// Dummy method for adding List of Users
private List<User> getListOfUsers() throws ParseException {
List<User> users = new ArrayList<User>();
Calendar dob = Calendar.getInstance();
dob.set(1975,6,12);
users.add(new User("Jack", "Reacher", "abc@xyz.com", dob.getTime()));
// Using LocalDate from new time&date API
LocalDate date = LocalDate.of(2016, Month.APRIL, 28);
users.add(new User("Remington", "Steele", "rs@cbd.com",
Date.from(date.atStartOfDay().atZone(ZoneId.systemDefault()).toInstant())));
dob.set(1965,12,6);
users.add(new User("Jonathan", "Raven", "jr@sn.com", dob.getTime()));
return users;
}
}
The sheet you get by running this code is as below.
That's all for this topic How to Write Excel File in Java Using Apache POI. If you have any doubt or any suggestions to make please drop a comment. Thanks!
>>>Return to Java Programs Page
Related Topics
You may also like-