Apache POI is a robust open-source library that provides APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft’s OLE2 compound document format (OLE2). Being Java-based, it’s readily used by Java developers for creating, reading, and modifying Excel files.
To create a new Excel file using Apache POI, you will want to follow these basic steps:
1. Create a Workbook
You create an instance of Workbook which represents an Excel file. Workbook is an interface and the instance can be created by using one of its implementing classes like HSSFWorkbook (for .xls) or XSSFWorkbook (for .xlsx).
package org.kodejava.poi;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
public class CreateWorkbook {
public static void main(String[] args) {
// Create Blank workbook
try (XSSFWorkbook workbook = new XSSFWorkbook()) {
// Write the workbook to a file
try (FileOutputStream fileOut = new FileOutputStream("workbook.xlsx")) {
workbook.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
This block of code will create a new Excel file called “workbook.xlsx” in your project directory.
2. Create a Sheet
You create a Sheet in the Workbook. A sheet is a collection of rows where your data resides. To create a new sheet using Apache POI, you can use the createSheet() method of the Workbook interface. This method returns an object of the Sheet interface.
Below is an example of creating a new sheet in an Excel workbook:
package org.kodejava.poi;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
public class CreateSheetExample {
public static void main(String[] args) {
// Create a workbook
try (XSSFWorkbook workbook = new XSSFWorkbook()) {
// Create a blank sheet within the workbook
XSSFSheet sheet = workbook.createSheet("New Sheet");
// Write the workbook in a file system
try (FileOutputStream out = new FileOutputStream("New.xlsx")) {
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
This code will create a new sheet named “New Sheet” in an Excel file named New.xlsx.
3. Create Rows and Cells
Inside your Sheet, you create Row. Inside your Row, you create Cell.
Creating new rows and cells with Apache POI is quite straightforward. Here’s a step-by-step way to do this:
- Create a workbook.
- Within the workbook, create a sheet.
- Within the sheet, create a row.
- Within the row, create a cell.
Each Cell can then be populated with data. Apache POI supports various data types including a text, numeric, date, boolean and formula.
Here is an example using Apache POI in Java:
package org.kodejava.poi;
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;
import java.io.FileOutputStream;
import java.io.IOException;
public class CreateCellExample {
public static void main(String[] args) {
// Create a workbook
try (XSSFWorkbook workbook = new XSSFWorkbook()) {
// Create a blank sheet within the workbook
XSSFSheet sheet = workbook.createSheet("New Sheet");
// Create a row within the sheet
XSSFRow row = sheet.createRow(0);
// Create a cell within the row
XSSFCell cell = row.createCell(0);
// Set the value of the cell
cell.setCellValue("Hello, Apache POI!");
// Write the workbook to the file system
try (FileOutputStream out = new FileOutputStream("Cell.xlsx")) {
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
This code will create a new workbook with a sheet named New Sheet. In that sheet, it will create a row at index 0. In that row, it will create a cell at index 0 and set its value to “Hello, Apache POI!”
This will all be saved to a new Excel file named Cell.xlsx in the current project directory.
4. Creating a Date cell
Using Apache POI involves a few steps outlined below:
- First, you create a new Workbook, Sheet, and Row, as shown previously.
- Then, you create a cell in any given row and set its type to
CellType.NUMERIC. - You then use
CellStyleto format the date according to your needs.
Here’s an example:
package org.kodejava.poi;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
public class CreateDateCellExample {
public static void main(String[] args) {
// Create a workbook
try (Workbook workbook = new XSSFWorkbook()) {
// Create a blank sheet within the workbook
Sheet sheet = workbook.createSheet("Date Example");
// Create a row within the sheet
Row row = sheet.createRow(0);
// Create a cell within the row and set its type to NUMERIC
Cell cell = row.createCell(0, CellType.NUMERIC);
// Create a CellStyle
CellStyle cellStyle = workbook.createCellStyle();
CreationHelper creationHelper = workbook.getCreationHelper();
// Set the built-in date format style
cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy h:mm"));
cell.setCellStyle(cellStyle);
// Set the value of the cell as today's date
cell.setCellValue(new Date());
// Write the workbook to the file system
try (FileOutputStream out = new FileOutputStream("DateExample.xlsx")) {
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
In this example, a date cell has been created, and the date format has been set to m/d/yy h:mm, but there are many other ways to format the date or time. You can use a custom format if you prefer, so long as it follows the rule of Excel custom date format.
5. Creating various types of cell
Using Apache POI, you need to create a cell and set the cell type and value accordingly.
Let’s take a look at some examples:
package org.kodejava.poi;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
public class CreateVariousCellsExample {
public static void main(String[] args) throws IOException {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("New Sheet");
Row row = sheet.createRow(0);
// Create a numeric cell
Cell numericCell = row.createCell(0, CellType.NUMERIC);
numericCell.setCellValue(123.456);
// Create a string cell
Cell stringCell = row.createCell(1, CellType.STRING);
stringCell.setCellValue("Hello, POI!");
// Create a boolean cell
Cell booleanCell = row.createCell(2, CellType.BOOLEAN);
booleanCell.setCellValue(true);
// Create a formula cell
Cell formulaCell = row.createCell(3, CellType.FORMULA);
formulaCell.setCellFormula("A1*B1");
try (FileOutputStream fileOut = new FileOutputStream("./differentCellTypes.xlsx")) {
workbook.write(fileOut);
}
workbook.close();
}
}
This code will create an Excel file named “differentCellTypes.xlsx” with a sheet named “New Sheet”. In the first row of this sheet, there will be four cells:
- A numeric cell containing the number
123.456 - A string cell containing the value “Hello, POI!”
- A boolean cell containing the boolean value of
true - A formula cell containing a formula which multiplies the values of the first and second cells.
And that’s the basic of creating an Excel file using Apache POI. More advanced features include formatting and styling cells, adding images, creating charts, formulas, hyperlinks and so forth. You would find Apache POI’s API and its official documentation very resourceful for covering those.
Do note, it’s important to always close the Workbook instance at the end to free up resources and to prevent memory leaks.
Maven Dependencies
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.5</version>
</dependency>
