Efficient Work with Excel in Java: A Look at Apache POI
Discover in our latest blog post how to effectively process Excel files in Java using Apache POI. From simple data extraction to writing complex formulas – we guide you through all the steps. Learn why Apache POI is the preferred choice for integrating Excel into your Java applications.
Why Apache POI?
Apache POI is a powerful library designed specifically for working with Microsoft Excel documents. Here are some reasons why Apache POI is an excellent choice:
- Support for both formats: Apache POI can handle both older XLS and modern XLSX file formats.
- Apache support: As part of the Apache Foundation, POI offers reliable and active support.
- Stream-based processing: This allows efficient processing of large files with less memory usage.
Maven Dependencies
If you use Maven, you can easily integrate Apache POI into your project:
org.apache.poi
poi
3.10-FINAL
org.apache.poi
poi-ooxml
3.10-FINAL
Reading Excel Files
To read data from an Excel file, we can use the `Workbook` instance and extract the desired data from the sheets. Here’s a simple example:
public class ReadExcelFileToList {
public static List readExcelData(String fileName) {
List countriesList = new ArrayList<>();
try {
FileInputStream fis = new FileInputStream(fileName);
Workbook workbook = fileName.toLowerCase().endsWith("xlsx") ? new XSSFWorkbook(fis) : new HSSFWorkbook(fis);
int numberOfSheets = workbook.getNumberOfSheets();
for (int i = 0; i < numberOfSheets; i++) {
Sheet sheet = workbook.getSheetAt(i);
Iterator rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
// Reading the cells and extracting the data
}
}
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
return countriesList;
}
}
Writing Excel Files
Writing data to Excel using Apache POI is similarly simple. Here’s an example that writes a list of countries to an Excel file:
public class WriteListToExcelFile {
public static void writeCountryListToFile(String fileName, List countryList) throws Exception {
Workbook workbook = fileName.endsWith("xlsx") ? new XSSFWorkbook() : new HSSFWorkbook();
Sheet sheet = workbook.createSheet("Countries");
Iterator iterator = countryList.iterator();
int rowIndex = 0;
while (iterator.hasNext()) {
// Writing the data into the cells
}
FileOutputStream fos = new FileOutputStream(fileName);
workbook.write(fos);
fos.close();
System.out.println(fileName + " successfully written");
}
}
Conclusion
Apache POI is a powerful library for working with Excel files in Java. With support for both formats, easy integration with Maven, and extensive functionality for editing Excel documents, it is the preferred choice for many developers. Try it out and see for yourself how easy it is to work with Excel files in Java!
You might also be interested in:
Apache Spark in Java : A Simple Word Counter Program
