Setting Up the Project Dependencies
Before writing any code, you must ensure that the necessary Apache POI libraries are included in your project. The most common way to manage these dependencies is through Maven or Gradle. If you are using Maven, you need to add thepoi and poi-ooxml artifacts to your pom.xml file. The poi library handles the basic file formats, while poi-ooxml is required for working with modern Excel files (XLSX).
Here is the correct dependency configuration:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.3</version>
</dependency>< and >. It is critical to avoid syntax errors such as < which will cause build failures. Additionally, always specify a stable version number to prevent unexpected breaking changes in future updates. If you are using Gradle, the equivalent implementation would be implementation 'org.apache.poi:poi:5.2.3' and implementation 'org.apache.poi:poi-ooxml:5.2.3'.
Creating the Workbook and Sheet
Once your dependencies are set up, the first step in the coding process is to instantiate the workbook. In Apache POI, workbooks represent the entire Excel file. You should use try-with-resources to ensure that the file streams are closed properly after execution, preventing resource leaks and file locking issues.import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
public class ExcelWriter {
public static void main(String[] args) {
String filePath = "output.xlsx";
// Create workbook using try-with-resources
try (Workbook workbook = new XSSFWorkbook();
FileOutputStream outputStream = new FileOutputStream(filePath)) {
// Create a sheet named "Data"
Sheet sheet = workbook.createSheet("Data");
// Proceed to add data here
workbook.write(outputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
}XSSFWorkbook instance, which is suitable for .xlsx files. We also open a FileOutputStream to write the final file. The try-with-resources block automatically closes both the workbook and the output stream when the block exits, whether normally or due to an exception. Inside the block, we create a sheet using workbook.createSheet(). This method returns a Sheet object that we will use to manipulate rows and cells.
Populating Rows and Columns
To add row data to columns, you need to iterate through your data structure. Assume you have a list of objects or arrays representing your rows. For each item in your data source, you create a new row in the Excel sheet. Then, for each column value, you create a cell within that row and set its value.import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import java.util.List;
import java.util.ArrayList;
// Sample data: List of arrays where each array represents a row
List rowData = new ArrayList<>();
rowData.add(new String[]{"Name", "Age", "City"}); // Header
rowData.add(new String[]{"Alice", "30", "New York"});
rowData.add(new String[]{"Bob", "25", "London"});
int rowNum = 0;
for (String[] data : rowData) {
Row row = sheet.createRow(rowNum++);
int colNum = 0;
for (String field : data) {
Cell cell = row.createCell(colNum++);
cell.setCellValue(field);
}
} sheet.createRow(rowNum) creates a new row at the specified index. The variable rowNum increments with each iteration to place data in subsequent rows. Inside the inner loop, row.createCell(colNum++) creates a cell at the specific column index. cell.setCellValue(field) assigns the string value to the cell. This method effectively maps your data structure into a grid format, ensuring that each piece of data lands in the correct column for its respective row.
Handling Different Data Types
While strings are common, Excel supports various data types including numbers, booleans, and dates. Apache POI provides different methods on theCell interface to handle these types appropriately. Using the correct setter ensures that Excel interprets the data correctly, allowing for proper sorting and formatting.
For numeric values, use cell.setCellValue(doubleValue). For boolean values, use cell.setCellValue(booleanValue). If you need to insert a date, you can use cell.setCellValue(java.util.Date), but note that you may need to configure the cell style to display the date correctly. It is important to check the type of your data before setting the value to avoid casting exceptions or incorrect displays.
Cell numCell = row.createCell(1);
numCell.setCellValue(42.5);
Cell boolCell = row.createCell(2);
boolCell.setCellValue(true);Saving and Closing Resources
After populating all the rows and columns, you must save the workbook to the output stream. As shown in the earlier example, callingworkbook.write(outputStream) writes the in-memory representation of the spreadsheet to the file system. This step is crucial because without it, no data is persisted to disk.
Finally, ensure that all resources are closed. In the try-with-resources example provided earlier, the workbook and output stream are closed automatically. However, if you are not using try-with-resources, you must manually call workbook.close() and outputStream.close() in a finally block or similar structure. Failure to close these resources can lead to memory leaks and locked files that cannot be opened by other processes.
For more advanced formatting, consider exploring how to Fill an Excel Cell based on data in two other cells to automate content generation. Additionally, if you need to summarize the data you just wrote, you might look into Summarizing max. values for each row in an excel table techniques.
Frequently Asked Questions
How do I append data to an existing Excel file?
You cannot directly append to a file using the same API easily; instead, you read the existing file into a Workbook object, modify it, and then write it back out. Use WorkbookFactory.create(inputStream) to load an existing file. After making your changes, write the modified workbook to a new file or overwrite the original.
What happens if I exceed the maximum number of rows in Excel?
Excel has a limit of 1,048,576 rows per sheet. If you attempt to create a row beyond this limit, Apache POI will throw an IllegalArgumentException. To handle large datasets, consider splitting your data across multiple sheets or using a database instead of Excel.
Can I set column widths automatically?
Yes, you can use sheet.autoSizeColumn(columnIndex) to adjust the width of a column to fit its content. However, this method can be slow for large sheets. A better approach for performance is to calculate the optimal width manually or set fixed widths using sheet.setColumnWidth().
How do I handle null values in my data?
If your data source contains nulls, check for them before calling setCellValue. You can either skip creating the cell entirely or set it to an empty string using cell.setCellValue(""). Leaving a cell unset results in a blank cell in Excel, which is often the desired behavior for missing data.
Is Apache POI thread-safe?
No, instances of Workbook, Sheet, and Row are not thread-safe. You should not share these objects between multiple threads. If you need concurrent access, create separate workbook instances for each thread or use synchronization mechanisms to ensure only one thread modifies the workbook at a time.