Write Data To Excel

In this tutorial, I’d love to share with you guys some examples of writing data to Excel files using the Apache POI library. If today is the first day you get to know Apache POI and you haven’t written any code snippet to read/write Excel files yet, I recommend you to read the sections 1 and 2 in the tutorial How to Read Excel Files in Java using Apache POI to understand the fundamentals of Apache POI.

Or if you are a kind of person who likes getting your hands-dirty first, let jump directly into the following examples.

 

1. Apache POI API Basics for Writing Excel Files

The fundamental interfaces include Workbook, Sheet, Row and Cell. For basic formatting, use the CellStyle and Font interfaces. Concrete implementing classes include:

• Excel 2003: HSSFWorkbook, HSSFSheet, HSSFRow, HSSFCell, etc.
• Excel 2007: XSSFWorkbook, XSSFSheet, XSSFRow, XSSFCell, etc.

But I recommend using the common interfaces for greater flexibility with both Excel formats 2003 (XLS) and 2007(XLSX).

Here are the basic steps for writing an Excel file:

1. Create a Workbook.
2. Create a Sheet.
3. Repeat the following steps until all data is processed:
   1. Create a Row.
   2. Create Cellsin a Row. Apply formatting using CellStyle.
4. Write to an OutputStream.
5. Close the output stream.

Now, let’s see some examples that demonstrate writing a list of books to an Excel file.

 

2. A Simple Example to create an Excel file in Java

The following code snippet is a very simple program that demonstrates writing a list of books to an Excel file in the simplest and dirty form:

This program creates an Excel 2007 document which looks like the following screenshot (File: JavaBooks.xlsx):

 

3. A More Object-Oriented Example to create an Excel file in Java

The following code snippets demonstrate a nicer program that focuses on OOP approach. That makes the program more flexible and easy to extend or upgrade in the future.

• Create the model class (Book.java):

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

  package net.codejava.excel;   public class Book {     private String title;     private String author;     private float price;       public Book() {     }       public Book(String title, String author, double price) {         this.title = title;         this.author = author;         this.price = price;     }       // getters and setters }

• The method that writes a list of books to an Excel file (in 2003 format):

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

  public void writeExcel(List<Book> listBook, String excelFilePath) throws IOException {     Workbook workbook = new HSSFWorkbook();     Sheet sheet = workbook.createSheet();       int rowCount = 0;       for (Book aBook : listBook) {         Row row = sheet.createRow(++rowCount);         writeBook(aBook, row);     }       try (FileOutputStream outputStream = new FileOutputStream(excelFilePath)) {         workbook.write(outputStream);     } }

• The method that writes information of a book to cells:

  1 2 3 4 5 6 7 8 9 10

  private void writeBook(Book aBook, Row row) {     Cell cell = row.createCell(1);     cell.setCellValue(aBook.getTitle());       cell = row.createCell(2);     cell.setCellValue(aBook.getAuthor());       cell = row.createCell(3);     cell.setCellValue(aBook.getPrice()); }

• The following method creates some dummy data (a list of books):

  1 2 3 4 5 6 7 8 9 10

  private List<Book> getListBook() {     Book book1 = new Book("Head First Java", "Kathy Serria", 79);     Book book2 = new Book("Effective Java", "Joshua Bloch", 36);     Book book3 = new Book("Clean Code", "Robert Martin", 42);     Book book4 = new Book("Thinking in Java", "Bruce Eckel", 35);       List<Book> listBook = Arrays.asList(book1, book2, book3, book4);       return listBook; }

• And the following code snippet is for testing:

  1 2 3 4 5 6

  NiceExcelWriterExample excelWriter = new NiceExcelWriterExample();   List<Book> listBook = excelWriter.getListBook(); String excelFilePath = "NiceJavaBooks.xls";   excelWriter.writeExcel(listBook, excelFilePath);

 

4. Formatting Cells of the Excel file

Of course you may need to format the Excel file to make it looks nicely and professionally. Formatting is diversity and quite complex so in this introductory tutorial, I just show you how to format the basics like setting font style. Here are the steps:

• Create a CellStyle object what holds formatting information:

  1	CellStyle cellStyle = sheet.getWorkbook().createCellStyle();

• Invoke the appropriate setters to apply the formatting you want. For example:

  1 2 3

  cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setFont(font); cellStyle.setWrapText(true);

For example, the following method writes a header row for the document with font style bold and size is 16:

You can see the complete program (FormattedExcelWriterExample.java) which can be found in the source code attached to this article.

 

5. Writing both Excel 2003 and Excel 2007 formats in Java

For better flexibility (supporting both common Excel formats), I recommend writing a factory method that either returns a HSSFWorkbook or XSSFWorkbook, depending on the extension of the file (.xls or .xlsx). Here’s the method:

You can see the complete program (FlexibleExcelWriterExample.java) which can be found in the source code attached to this article.

That's how to read Excel files in Java programmatically. To learn more in-depth about Java programming, this Java software developers course would be good choice.