In this article, we will focus on how to create a sheet using Apache POI, providing a detailed example along the way. Apache POI (Poor Obfuscation Implementation) is a powerful Java library that allows developers to create, modify, and manipulate Microsoft Office documents, including spreadsheets, presentations, and documents.
Table of Contents
What is Apache POI?
Apache POI provides Java APIs for working with Microsoft Office formats, enabling developers to perform tasks such as reading, writing, and editing Excel files without relying on external applications or libraries. With Apache POI, developers can generate complex Excel documents programmatically, making it an invaluable tool for automating spreadsheet-related tasks. Get started with Apache POI.
Setting Up Apache POI
Before we begin, ensure that you have Apache POI added to your Java project’s dependencies. You can download the latest version of Apache POI from the Apache POI website or include it as a dependency in your project’s build file if you are using a build management tool like Maven or Gradle.
For Maven, add the following dependency:
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.1.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.1.0</version>
</dependency>
For Gradle, add the following to your dependencies:
implementation 'org.apache.poi:poi:5.1.0'
implementation 'org.apache.poi:poi-ooxml:5.1.0'
NOTE: These lines specify the dependencies for the poi and poi-ooxml artifacts with version 5.1.0. Make sure to sync your Gradle project after adding these dependencies to download and include the Apache POI library in your project.
How to Create a Sheet?
Let’s walk through the steps to create a simple spreadsheet using Apache POI:
Step 1: Create a new Java Class
Start by creating a new Java class in your project. You can name it SpreadsheetCreator or choose any suitable name for your class.
Step 2: Import Apache POI Libraries
Import the necessary Apache POI libraries at the beginning of your Java class:
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
Step 3: Write the Spreadsheet Creation Logic
Inside your Java class, write the below logic to create the spreadsheet:
public class SpreadsheetCreator {
public static void main(String[] args) {
// Create a new Workbook
Workbook workbook = new XSSFWorkbook();
// Create a Sheet
Sheet sheet = workbook.createSheet("SampleSheet");
// Create a Row
Row row = sheet.createRow(0);
// Create Cells and add data
Cell cell0 = row.createCell(0);
cell0.setCellValue("Name");
Cell cell1 = row.createCell(1);
cell1.setCellValue("Age");
// Create another Row
Row row1 = sheet.createRow(1);
// Create Cells and add data
Cell cell2 = row1.createCell(0);
cell2.setCellValue("John");
Cell cell3 = row1.createCell(1);
cell3.setCellValue(30);
// Write the workbook content to a file
try {
FileOutputStream fileOutputStream = new FileOutputStream("SampleSpreadsheet.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
System.out.println("Spreadsheet created successfully!");
} catch (IOException e) {
e.printStackTrace();
}
}
}
Step 4: Run the Code
Compile and run the SpreadsheetCreator class. This will generate a new Excel file named SampleSpreadsheet.xlsx in the project directory with the specified content.
Details of Used Classes and Methods
- Workbook: Represents the Excel workbook. In our example, we used
XSSFWorkbook, which is the implementation for .xlsx files. - Sheet: Represents a sheet within the workbook. We created a new sheet named “SampleSheet” using
createSheetmethod. - Row: Represents a row within a sheet. We created rows using the
createRowmethod. - Cell: Represents a cell within a row. We created cells using the
createCellmethod and set their values using thesetCellValuemethod. - FileOutputStream: Writes data to a file. We used it to write the workbook content to a file named “SampleSpreadsheet.xlsx”.
Complete Example – How to Create a Sheet, Creating a sheet using POI
Here’s the Java code combining all the steps into a single class for creating a spreadsheet using Apache POI:
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
public class SpreadsheetCreator {
public static void main(String[] args) {
// Create a new Workbook
Workbook workbook = new XSSFWorkbook();
// Create a Sheet
Sheet sheet = workbook.createSheet("SampleSheet");
// Create a Row
Row row = sheet.createRow(0);
// Create Cells and add data
Cell cell0 = row.createCell(0);
cell0.setCellValue("Name");
Cell cell1 = row.createCell(1);
cell1.setCellValue("Age");
// Create another Row
Row row1 = sheet.createRow(1);
// Create Cells and add data
Cell cell2 = row1.createCell(0);
cell2.setCellValue("John");
Cell cell3 = row1.createCell(1);
cell3.setCellValue(30);
// Write the workbook content to a file
try {
FileOutputStream fileOutputStream = new FileOutputStream("SampleSpreadsheet.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
System.out.println("Spreadsheet created successfully!");
} catch (IOException e) {
e.printStackTrace();
}
}
}
OUTPUT:
Conclusion
In this article, we learned how to create a sheet using Apache POI, a powerful Java library for working with Microsoft Office documents. By following the steps outlined in this guide, you can generate Excel files programmatically, customize their content, and automate spreadsheet-related tasks with ease. Apache POI’s rich feature set and flexibility make it an indispensable tool for Java developers working with Excel files in their applications.